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.