Advisory locks in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What is the output of this advisory lock query?

Consider the following PostgreSQL query that attempts to acquire a session-level advisory lock with key 12345:

SELECT pg_try_advisory_lock(12345);

What will be the output if the lock is successfully acquired?

PostgreSQL

SELECT pg_try_advisory_lock(12345);

A true

B false

C null

D an error message

💡 Hint

pg_try_advisory_lock returns a boolean indicating success or failure.

The function pg_try_advisory_lock returns true if the lock was successfully acquired, false if it was already held by another session.

🧠 Conceptual

intermediate

Which statement about advisory locks is true?

Which of the following statements about PostgreSQL advisory locks is correct?

A Advisory locks block other sessions until explicitly released or session ends.

B Advisory locks can only be acquired using integer keys.

C Advisory locks are visible in the pg_locks system view with locktype 'advisory'.

D Advisory locks are automatically released at the end of the current transaction.

💡 Hint

Think about how advisory locks behave and how they appear in system views.

Advisory locks appear in pg_locks with locktype 'advisory'. They can be session-level or transaction-level, and session-level locks are released at session end, while transaction-level locks are released at transaction end.

📝 Syntax

advanced

Which query correctly releases a session-level advisory lock with key 9876?

You have acquired a session-level advisory lock with key 9876. Which of the following queries correctly releases that lock?

A SELECT pg_advisory_unlock_all();

B SELECT pg_advisory_unlock_shared(9876);

C SELECT pg_advisory_unlock(1234);

D SELECT pg_advisory_unlock(9876);

💡 Hint

Releasing a lock requires the exact key used to acquire it.

pg_advisory_unlock releases the session-level exclusive lock for the given key. Unlocking with a different key or using shared unlock will not release the exclusive lock.

❓ query_result

advanced

What is the result of this transaction-level advisory lock query?

Given the following query inside a transaction:

SELECT pg_try_advisory_xact_lock(5555);

What will be the output if the lock is already held by another transaction?

PostgreSQL

SELECT pg_try_advisory_xact_lock(5555);

A false

B the query waits until the lock is free

C true

D an error is raised

💡 Hint

pg_try_advisory_xact_lock tries to acquire the lock without waiting.

pg_try_advisory_xact_lock returns false immediately if the lock is held by another transaction; it does not wait or raise an error.

🔧 Debug

expert

Why does this advisory lock query cause an error?

Consider this query:

SELECT pg_advisory_lock('mylock');

Why does this query cause an error?

A The lock key 'mylock' is already held by another session.

B The function pg_advisory_lock requires integer arguments, not text.

C pg_advisory_lock cannot be called outside a transaction.

D The function pg_advisory_lock does not exist in PostgreSQL.

💡 Hint

Check the data type of the argument passed to pg_advisory_lock.

pg_advisory_lock requires one or two integer arguments. Passing a string causes a type error.

Practice

1. What is the main purpose of advisory locks in PostgreSQL?

easy

A. To control access to resources using user-defined keys

B. To automatically manage table-level locks during transactions

C. To speed up query execution by caching results

D. To backup the database safely without downtime

Solution

1. Step 1: Understand advisory locks concept

   Advisory locks allow applications to coordinate access to resources by using custom keys, not automatic locks on tables or rows.

2. Step 2: Compare options

   The other options describe other database features unrelated to advisory locks.

3. Final Answer:

   To control access to resources using user-defined keys -> Option A

4. Quick Check:

   Advisory locks = user-defined resource control [OK]

Hint: Advisory locks use keys to manage resource access [OK]

Common Mistakes:

• Confusing advisory locks with automatic table locks
• Thinking advisory locks speed up queries
• Assuming advisory locks handle backups

2. Which of the following is the correct syntax to acquire a session-level advisory lock with key 12345?

easy

A. SELECT pg_advisory_lock(12345);

B. LOCK TABLE pg_advisory_lock(12345);

C. SELECT acquire_lock(12345);

D. BEGIN LOCK 12345;

Solution

1. Step 1: Recall advisory lock syntax

   PostgreSQL uses the function pg_advisory_lock(key) to acquire a session-level advisory lock.

2. Step 2: Evaluate options

   SELECT pg_advisory_lock(12345); is the correct function call. The other options use invalid syntax or non-existent functions.

3. Final Answer:

   SELECT pg_advisory_lock(12345); -> Option A

4. Quick Check:

   pg_advisory_lock(key) = correct syntax [OK]

Hint: Use SELECT pg_advisory_lock(key) to lock [OK]

Common Mistakes:

• Using LOCK TABLE instead of function call
• Calling non-existent functions like acquire_lock
• Trying to lock with BEGIN LOCK syntax

3. What will be the result of this query if the advisory lock with key 999 is already held by another session?

SELECT pg_try_advisory_lock(999);

medium

A. true

B. false

C. null

D. error

Solution

1. Step 1: Understand pg_try_advisory_lock behavior

   This function tries to acquire the lock immediately and returns true if successful, false if the lock is held by someone else.

2. Step 2: Analyze the scenario

   Since the lock with key 999 is already held, the function returns false without waiting.

3. Final Answer:

   false -> Option B

4. Quick Check:

   pg_try_advisory_lock returns false if lock busy [OK]

Hint: pg_try_advisory_lock returns false if lock busy [OK]

Common Mistakes:

• Expecting true even if lock is held
• Thinking it returns null or error
• Confusing pg_try_advisory_lock with pg_advisory_lock

4. You wrote this code:

SELECT pg_advisory_unlock(123);

But the lock was never acquired before. What will happen?

medium

A. The function returns true and releases the lock

B. The function blocks until the lock is acquired

C. The function throws an error

D. The function returns false because no lock was held

Solution

1. Step 1: Understand pg_advisory_unlock behavior

   This function releases a lock if held and returns true; if no lock was held, it returns false.

2. Step 2: Analyze the scenario

   Since the lock was never acquired, the function returns false without error or blocking.

3. Final Answer:

   The function returns false because no lock was held -> Option D

4. Quick Check:

   Unlock returns false if lock not held [OK]

Hint: Unlock returns false if no lock held [OK]

Common Mistakes:

• Expecting an error when unlocking unheld lock
• Thinking unlock blocks or waits
• Assuming unlock always returns true

5. You want to ensure two different sessions do not run a critical section simultaneously using advisory locks. Which approach is best?

-- Session 1 and 2 run this code:
SELECT pg_try_advisory_lock(42);
-- If true, run critical section, then
SELECT pg_advisory_unlock(42);

hard

A. Use pg_advisory_unlock before acquiring lock to clear old locks

B. Use pg_try_advisory_lock to attempt lock and skip if busy

C. Use pg_advisory_lock to wait until lock is available before running

D. Use random keys each time to avoid conflicts

Solution

1. Step 1: Understand locking strategies

   pg_try_advisory_lock returns immediately and may skip critical section if lock busy; pg_advisory_lock waits until lock is free.

2. Step 2: Choose best approach for critical section

   To ensure only one session runs critical section at a time, waiting for the lock is safer than skipping it.

3. Step 3: Evaluate other options

   Unlocking before acquiring is unsafe and random keys defeat locking purpose.

4. Final Answer:

   Use pg_advisory_lock to wait until lock is available before running -> Option C

5. Quick Check:

   Waiting lock ensures exclusive access [OK]

Hint: Use pg_advisory_lock to wait for exclusive access [OK]

Common Mistakes:

• Using try lock and skipping critical section silently
• Unlocking before locking without owning lock
• Using random keys causing no real locking