Advisory locks in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is an advisory lock in PostgreSQL?

An advisory lock is a user-controlled lock that applications can use to coordinate access to resources without locking database rows or tables. It is voluntary and does not block normal database operations.

beginner

How do you acquire a session-level advisory lock in PostgreSQL?

Use the function pg_advisory_lock(key) where key is a 64-bit integer. This lock is held until the session ends or the lock is explicitly released.

intermediate

What happens if you try to acquire an advisory lock that is already held by another session?

The session requesting the lock will wait (block) until the lock is released by the other session, unless you use the non-blocking version pg_try_advisory_lock() which returns immediately with success or failure.

beginner

How can you release an advisory lock in PostgreSQL?

You can release it explicitly using pg_advisory_unlock(key). If the session ends, all session-level advisory locks held by it are automatically released.

intermediate

What is the difference between session-level and transaction-level advisory locks?

Session-level locks last until the session ends or are explicitly released. Transaction-level locks last only until the current transaction ends (commit or rollback). Use pg_advisory_xact_lock() for transaction-level locks.

Which PostgreSQL function acquires a blocking session-level advisory lock?

A pg_advisory_lock(key)

B pg_try_advisory_lock(key)

C pg_advisory_unlock(key)

D pg_advisory_xact_lock(key)

pg_advisory_lock(key) acquires a blocking session-level advisory lock, waiting if necessary.

What does pg_try_advisory_lock(key) do if the lock is already held?

A Waits until the lock is free

B Releases the lock

C Throws an error

D Returns immediately with false

pg_try_advisory_lock(key) returns immediately with false if the lock is already held.

When are session-level advisory locks automatically released?

A At the end of the current transaction

B When the session ends

C After 5 minutes

D When the database restarts

Session-level advisory locks are released automatically when the session ends.

Which function acquires a transaction-level advisory lock?

A pg_advisory_xact_lock(key)

B pg_try_advisory_lock(key)

C pg_advisory_unlock(key)

D pg_advisory_lock(key)

pg_advisory_xact_lock(key) acquires a transaction-level advisory lock that lasts only for the current transaction.

Can advisory locks block normal database operations like row inserts?

A Only if the lock is held for more than 10 seconds

B Yes, always

C No, advisory locks do not block normal operations

D Only on tables with triggers

Advisory locks are voluntary and do not block normal database operations like inserts or updates.

Explain what advisory locks are in PostgreSQL and how they differ from regular locks.

Think about locks that applications use to coordinate without affecting database rows or tables.

Describe the difference between session-level and transaction-level advisory locks and when each is released.

Consider the scope and lifetime of the locks in relation to sessions and transactions.