Advisory locks in PostgreSQL - Deep Dive

Overview - Advisory locks

What is it?

Advisory locks in PostgreSQL are a way for applications to coordinate access to shared resources by using custom lock identifiers. Unlike regular locks that protect database rows or tables, advisory locks are application-controlled and do not block database operations automatically. They let different parts of an application agree on exclusive or shared access to resources using numbers as keys.

Why it matters

Without advisory locks, applications might face race conditions or conflicts when multiple processes try to use the same resource simultaneously. Advisory locks provide a flexible, lightweight way to prevent such conflicts without locking database data itself. This helps keep the database responsive and avoids deadlocks caused by traditional locking.

Where it fits

Before learning advisory locks, you should understand basic database locking and transactions. After mastering advisory locks, you can explore advanced concurrency control, distributed locking, and application-level synchronization techniques.

Mental Model

Core Idea

Advisory locks let applications use custom keys to coordinate exclusive or shared access to resources without locking database data directly.

Think of it like...

Imagine a group of friends sharing a toolbox. Instead of locking the toolbox itself, they agree to put a numbered tag on the toolbox handle when someone is using it. Others check the tag before taking tools, so they don’t interfere with each other.

┌───────────────────────────────┐
│         Application            │
│  requests advisory lock with  │
│  a numeric key (e.g., 12345)  │
└──────────────┬────────────────┘
               │
               ▼
┌───────────────────────────────┐
│       PostgreSQL Server        │
│  manages advisory locks table │
│  keyed by the numeric value   │
└──────────────┬────────────────┘
               │
               ▼
┌───────────────────────────────┐
│  Lock granted or denied based  │
│  on current lock state for    │
│  that key                     │
└───────────────────────────────┘

Build-Up - 7 Steps

1

Foundation

What are advisory locks

Concept: Introduction to advisory locks as application-controlled locks using numeric keys.

Advisory locks are special locks that applications can request by providing a number as a key. Unlike normal locks that protect database rows or tables automatically, advisory locks are purely cooperative. The database just tracks which keys are locked and by whom, but it does not enforce locking on data. Applications must agree to use these locks to coordinate.

Result

You understand that advisory locks are a flexible way to coordinate access using numbers, not tied to specific database rows or tables.

Understanding that advisory locks are cooperative and key-based helps you see their flexibility and why they don’t block normal database operations.

2

Foundation

How to acquire and release advisory locks

Concept: Learn the basic SQL functions to get and release advisory locks.

PostgreSQL provides functions like pg_advisory_lock(key) to acquire a lock and pg_advisory_unlock(key) to release it. When you call pg_advisory_lock, your session waits until the lock is free, then acquires it. Releasing the lock frees it for others. Locks are held per session and automatically released if the session ends.

Result

You can write queries like SELECT pg_advisory_lock(12345); to lock and SELECT pg_advisory_unlock(12345); to unlock.

Knowing the exact functions to acquire and release locks is essential to use advisory locks safely and avoid leaving locks held unintentionally.

3

Intermediate

Exclusive vs shared advisory locks

🤔Before reading on: do you think advisory locks only support exclusive locking or also shared locking? Commit to your answer.

Concept: Advisory locks can be exclusive (one session only) or shared (multiple sessions can hold simultaneously).

PostgreSQL supports exclusive advisory locks (pg_advisory_lock) where only one session can hold the lock at a time. It also supports shared advisory locks (pg_advisory_lock_shared) where multiple sessions can hold the lock simultaneously, but exclusive locks wait for shared locks to release and vice versa. This allows flexible coordination patterns.

Result

You can use shared locks for read-like access and exclusive locks for write-like access on the same key.

Understanding shared vs exclusive advisory locks lets you design more nuanced concurrency controls, similar to read-write locks in programming.

4

Intermediate

Using two 32-bit keys vs one 64-bit key

🤔Before reading on: do you think advisory locks accept only one number or can they use multiple numbers as keys? Commit to your answer.

Concept: Advisory locks can use either a single 64-bit key or two 32-bit keys to identify locks.

PostgreSQL provides functions that accept either one big integer (64-bit) or two smaller integers (32-bit each) as keys. Using two keys can help organize locks hierarchically or by category. For example, pg_advisory_lock(12345) uses one key, while pg_advisory_lock(1, 2345) uses two keys.

Result

You can choose the key format that best fits your application's locking needs.

Knowing the key formats helps you structure your locks logically and avoid collisions in complex applications.

5

Intermediate

Non-blocking advisory lock attempts

🤔Before reading on: do you think advisory lock functions always wait until the lock is free, or can they try once and return immediately? Commit to your answer.

Concept: PostgreSQL offers non-blocking advisory lock functions that try to acquire the lock and return immediately if unavailable.

Functions like pg_try_advisory_lock(key) attempt to get the lock but do not wait if it’s held by another session. They return true if successful, false otherwise. This is useful when you want to avoid waiting and handle lock contention gracefully.

Result

You can write code that tries to get a lock and decides what to do if it’s busy, improving responsiveness.

Understanding non-blocking lock attempts helps you build more responsive applications that don’t hang waiting for locks.

6

Advanced

Session-based lock lifetime and cleanup

🤔Before reading on: do you think advisory locks persist after the session ends or are automatically cleaned up? Commit to your answer.

Concept: Advisory locks are tied to the database session and automatically released when the session ends or disconnects.

When a session holding advisory locks ends, PostgreSQL automatically releases all locks held by that session. This prevents locks from being held forever if a client crashes or disconnects unexpectedly. However, if your application uses connection pooling, locks may persist longer than expected if sessions are reused.

Result

You understand that advisory locks are session-scoped and must be managed carefully with connection pools.

Knowing the session scope of advisory locks prevents bugs where locks appear stuck due to connection pooling or unexpected session reuse.

7

Expert

Advisory locks and deadlock risks

🤔Before reading on: do you think advisory locks can cause deadlocks like regular database locks? Commit to your answer.

Concept: Advisory locks can cause deadlocks if multiple sessions acquire locks in conflicting orders without careful design.

Because advisory locks are cooperative, the database does not detect deadlocks automatically. If two sessions each hold one advisory lock and wait for the other’s lock, they can deadlock indefinitely. Applications must implement consistent lock ordering or timeout logic to avoid this. PostgreSQL does not kill advisory lock deadlocks automatically.

Result

You realize that advisory locks require careful design to avoid subtle deadlocks.

Understanding that advisory locks can deadlock despite being application-controlled highlights the importance of disciplined locking order and timeout strategies.

Under the Hood

PostgreSQL maintains an internal lock table that tracks advisory locks by their numeric keys and the session holding them. When a session requests an advisory lock, the server checks this table to see if the lock is free or held. If free, it records the session as the owner. If held, the session waits or returns failure depending on the function used. Locks are stored in memory and tied to session state, automatically released on session end.

Why designed this way?

Advisory locks were designed to provide flexible, lightweight application-level locking without interfering with database row or table locks. This separation allows applications to coordinate resources outside the database schema, avoiding overhead and deadlocks common with traditional locks. The numeric key approach is simple and efficient, enabling easy integration with application logic.

┌───────────────┐
│ Session 1     │
│ Requests lock │
│ key=12345     │
└───────┬───────┘
        │
        ▼
┌─────────────────────────────┐
│ PostgreSQL Lock Manager      │
│ Checks advisory lock table  │
│ for key=12345               │
│ If free, assigns to Session 1│
└───────┬─────────────────────┘
        │
        ▼
┌───────────────┐
│ Session 1     │
│ Holds lock    │
│ key=12345     │
└───────────────┘

Myth Busters - 4 Common Misconceptions

Quick: do you think advisory locks automatically protect database rows or tables? Commit to yes or no.

Common Belief: Advisory locks lock database rows or tables automatically like regular locks.

Reality: Advisory locks do not lock any database data automatically; they only lock numeric keys that applications agree to use for coordination.

Why it matters: Assuming advisory locks protect data can lead to data corruption or race conditions because the database does not enforce data locking with advisory locks.

Quick: do you think advisory locks are released automatically when a transaction commits? Commit to yes or no.

Common Belief: Advisory locks are released automatically at the end of a transaction.

Reality: Advisory locks are held for the entire database session, not just a transaction, and are only released when explicitly unlocked or when the session ends.

Why it matters: Misunderstanding lock lifetime can cause locks to remain held longer than expected, causing blocking or deadlocks.

Quick: do you think advisory locks cannot cause deadlocks because they are cooperative? Commit to yes or no.

Common Belief: Advisory locks cannot cause deadlocks since they are application-controlled.

Reality: Advisory locks can cause deadlocks if sessions acquire locks in conflicting orders without coordination, and PostgreSQL does not detect or resolve these automatically.

Why it matters: Ignoring deadlock risks can cause applications to hang indefinitely, requiring manual intervention.

Quick: do you think non-blocking advisory lock functions wait for the lock to become free? Commit to yes or no.

Common Belief: Non-blocking advisory lock functions wait until the lock is free before returning.

Reality: Non-blocking advisory lock functions return immediately with success or failure, never waiting.

Why it matters: Misusing non-blocking functions as blocking can cause unexpected failures or busy-wait loops.

Expert Zone

1

Advisory locks are session-scoped, so connection pooling can cause locks to persist unexpectedly if sessions are reused without releasing locks.

2

Using two 32-bit keys instead of one 64-bit key allows hierarchical or composite locking schemes, which can reduce lock collisions in complex applications.

3

PostgreSQL does not provide built-in deadlock detection for advisory locks, so applications must implement their own timeout or ordering strategies to prevent deadlocks.

When NOT to use

Avoid advisory locks when you need strict, automatic data consistency enforced by the database, such as row-level locking or transactional integrity. Use standard row or table locks instead. Also, avoid advisory locks for distributed systems without additional coordination, as they only work within a single PostgreSQL instance.

Production Patterns

In production, advisory locks are often used to coordinate background jobs, prevent duplicate processing, or serialize access to external resources. Developers combine them with application logic to implement leader election, rate limiting, or resource pooling. They are also used with connection pools carefully to avoid lock leakage.

Connections

Mutex (Mutual Exclusion) in Operating Systems

Advisory locks serve a similar purpose as mutexes by controlling exclusive access to resources.

Understanding mutexes helps grasp how advisory locks coordinate access without blocking unrelated operations, emphasizing cooperative locking.

Distributed Locking Systems (e.g., ZooKeeper, Redis Redlock)

Advisory locks are a local, single-node form of locking, while distributed locking systems coordinate locks across multiple machines.

Knowing advisory locks clarifies the challenges and limitations when scaling locking to distributed environments.

Semaphore in Computer Science

Shared advisory locks behave like semaphores allowing multiple holders, while exclusive locks behave like binary semaphores (mutexes).

Recognizing this connection helps design concurrency controls using advisory locks that mimic classic synchronization primitives.

Common Pitfalls

#1 Holding advisory locks across multiple transactions without releasing them.

Wrong approach:

BEGIN;
SELECT pg_advisory_lock(12345);
-- do some work
COMMIT;
-- lock still held here unintentionally

Correct approach:

BEGIN;
SELECT pg_advisory_lock(12345);
-- do some work
COMMIT;
SELECT pg_advisory_unlock(12345);

Root cause:

Misunderstanding that advisory locks are session-scoped, not transaction-scoped, causing locks to remain held after commit.

#2 Assuming advisory locks protect database rows automatically.

Wrong approach:

SELECT pg_advisory_lock(12345);
UPDATE mytable SET value = 1 WHERE id = 1;
-- assuming no other session can update id=1

Correct approach:

Use standard row-level locks (SELECT FOR UPDATE) to protect rows, or combine advisory locks with application logic to coordinate access.

Root cause:

Confusing advisory locks with built-in row or table locks that the database enforces.

#3 Not handling deadlocks caused by advisory locks.

Wrong approach:

Session A: SELECT pg_advisory_lock(1);
Session B: SELECT pg_advisory_lock(2);
Session A: SELECT pg_advisory_lock(2); -- waits
Session B: SELECT pg_advisory_lock(1); -- waits forever

Correct approach:

Implement consistent lock ordering or use timeouts and retries to avoid deadlocks.

Root cause:

Ignoring that advisory locks can deadlock and that PostgreSQL does not detect these deadlocks automatically.

Key Takeaways

Advisory locks are application-controlled locks identified by numeric keys, allowing flexible coordination without locking database data automatically.

They are session-scoped and must be explicitly acquired and released, with automatic release on session end.

PostgreSQL supports exclusive and shared advisory locks, enabling patterns similar to read-write locks.

Non-blocking advisory lock functions allow attempts without waiting, improving application responsiveness.

Careful design is required to avoid deadlocks and lock leakage, especially when using connection pools.