PostgreSQL Advisory Locks: Application-Level Coordination Locks

At the mention of a "distributed lock," most people first think of Redis: SETNX, Redlock, or Redisson. These solutions are mature, but they come at a cost: you must maintain another piece of middleware and handle its persistence, primary-replica failover, and network instability.

But if you already use PostgreSQL, things can be simpler. PostgreSQL includes an application-level locking mechanism called Advisory Locks. It does not lock any data. Instead, it locks a "name" that you define, serving purely as a coordination signal between processes. You do not need Redis or Zookeeper; the database alone can do the job.

These locks are extremely useful in real production systems: multiple workers competing for jobs, preventing migration scripts from running concurrently, idempotency control, and rate limiting. But the pitfalls are real too: session-level locks that are never released, "ghost locks" caused by connection reuse, and key collisions.

This article explains Advisory Locks in depth: the two modes, four core functions, key design, three practical scenarios, their fundamental differences from row locks, and the traps that can get you paged in the middle of the night.

1. What Exactly Are Advisory Locks?

Start with the most important concept:

Advisory Locks do not lock data. They lock a number, or key.

This is the fundamental difference between Advisory Locks and every other PostgreSQL lock. A row lock locks a specific row of data. A table lock locks a table. An Advisory Lock, however, locks something that does not exist in any table at all. It exists only in PostgreSQL's internal global lock table and is identified by either a 64-bit integer or a pair of 32-bit integers.

The word "advisory" is revealing. PostgreSQL does not force you to use these locks, and it does not automatically apply them to any query. It simply provides a locking primitive. What you coordinate with it, when you acquire it, and when you release it are entirely up to your application. PostgreSQL acts as a trusted third-party arbitrator.

Consider a straightforward example: you want to ensure that only one process across the entire system runs a particular scheduled job at any given time. That "job" is not a row of data. It is an abstract concept. How do you lock it? Traditional row locks cannot help because there is no concrete row to SELECT FOR UPDATE. This is where an Advisory Lock comes in:

-- Acquire the lock for the "order reconciliation job"
SELECT pg_advisory_lock(12345);
-- The lock has been acquired; start working...
-- Release it when the work is done
SELECT pg_advisory_unlock(12345);

The meaning of 12345 is defined entirely by your application. It might represent the "order reconciliation job" or "the profile of user 42." PostgreSQL does not care. It only guarantees that a single session can hold the lock for this key at any given time.

That is the essence of Advisory Locks: a globally visible coordination signal that is not bound to any data.

2. Two Modes: Session-Level and Transaction-Level

Advisory Locks have two lifecycle modes. Understanding their differences is essential to avoiding mistakes.

Session-Level

Once acquired, a session-level lock is held until the database connection closes or you explicitly call unlock. It is independent of transaction boundaries. If you acquire the lock in transaction A and then commit transaction A, the lock remains held. You must unlock it yourself.

BEGIN;
SELECT pg_advisory_lock(100);  -- Acquire the lock
-- Operations within the transaction
COMMIT;
-- Note: the lock is still held after COMMIT!
-- It must be released explicitly:
SELECT pg_advisory_unlock(100);

The semantics are: "This connection, or session, exclusively owns key 100." The advantage is flexibility because the lock can span multiple transactions. The disadvantage is that you must manage its lifecycle yourself. Forgetting to unlock it can be disastrous.

Transaction-Level

The lifecycle of a transaction-level lock is strictly bound to the current transaction. The lock is released automatically when the transaction commits or rolls back. You neither need nor have the ability to unlock it manually.

BEGIN;
SELECT pg_advisory_xact_lock(100);  -- Acquire a transaction-level lock
-- Operations within the transaction
COMMIT;  -- The lock is released automatically here

Notice xact in the function name, which is short for transaction. Its semantics are: "This lock follows the transaction and is released when the transaction ends."

Which One Should You Use? My Recommendation

Use transaction-level locks by default. The reason is simple: they guarantee that the lock will be released. Even if your application throws an exception, the connection pool reclaims a connection under unusual circumstances, or you forget to write an unlock call, the lock disappears as soon as the transaction ends, whether successfully or unsuccessfully. It is a guardrail against mistakes.

Consider session-level locks only when the lock must span multiple transactions or when you are not using a transaction at all. A long-running background job, for example, may naturally follow a workflow of "acquire the lock, perform a series of separate operations, release the lock." A session-level lock fits that scenario better. The cost is that you must take extreme care to ensure unlock always runs, ideally by wrapping it in try/finally.

Here is an antipattern I have seen repeatedly: a developer uses pg_advisory_lock, which is session-level, for convenience and then forgets to unlock it. The connection is returned to the pool. When the next request reuses that connection, the lock is still there. The system then appears to "hang" for no obvious reason, and hours of investigation eventually reveal a ghost lock left behind by the previous request. This kind of bug is extremely subtle because it is intermittent and completely unrelated to the logic of the current request. If a transaction-level lock works, do not use a session-level lock. That recommendation comes from experience.

3. Four Core Functions

PostgreSQL provides a set of functions for working with Advisory Locks. You only need to remember these four.

They form two dimensions: blocking vs. non-blocking and session vs. transaction.

Blocking: pg_advisory_lock / pg_advisory_xact_lock

If the lock is unavailable, these functions wait until it becomes available. They suit scenarios where "this operation must run, so queue it if necessary."

-- Block until the lock is acquired
SELECT pg_advisory_xact_lock(7);
-- Reaching this point means the lock has been acquired

The advantage is simplicity because you do not need to write polling logic. But be careful: if the session holding the lock hangs without releasing it, waiting sessions will block indefinitely. Long transactions combined with blocking locks are dangerous in production.

Non-Blocking: pg_try_advisory_lock / pg_try_advisory_xact_lock

If the lock is unavailable, these functions return false immediately. If it is acquired, they return true. They never wait. They suit scenarios where "if the lock is unavailable, give up or do something else."

-- Try to acquire the lock and return immediately if unavailable
SELECT pg_try_advisory_lock(7);
-- Acquired: true   Not acquired: false

In practice, the non-blocking variants are used more often. Most coordination scenarios need a quick answer about whether work can proceed, not a queue that waits. For example, when multiple workers compete for jobs, a worker that fails to acquire a lock should immediately try another job instead of sitting idle.

A common application-level wrapper looks like this in pseudocode:

# Try to acquire the lock; skip the job if it is unavailable
got = query("SELECT pg_try_advisory_xact_lock(%s)", [task_lock_key])
if not got:
    return  # Another worker is processing it; skip
# Process the job within the same transaction after acquiring the lock
process(task)
commit()  # The lock is released with the transaction

Note: pg_try_advisory_xact_lock and the subsequent business operations must run in the same transaction. Otherwise, the lock becomes disconnected from the work and no longer provides meaningful coordination.

Two Key Signatures

All of the functions above have two overloaded versions:

-- Single-bigint version: one 64-bit integer
pg_advisory_lock(bigint)

-- Two-int version: two 32-bit integers
pg_advisory_lock(int, int)

The two versions are equivalent. In theory, key1 * 2^32 + key2 can map into the same 64-bit space. Semantically, however, the two-int version is better for expressing a "type + ID" combination. For example, (1, user_id) can represent a "user resource," while (2, order_id) can represent an "order resource." The next section covers this in detail.

4. How to Design Keys

An Advisory Lock key is just a number, but it carries the full meaning of "what this lock protects." Key design requires the most thought when using this mechanism.

Single bigint: Simple and Direct

SELECT pg_advisory_lock(42);

This is suitable when there are few keys with fixed meanings. For example, if the system has only one global lock used to serialize a particular operation, a hard-coded magic number is enough.

But if you have several different types of locks and use a single bigint for all of them, you will soon be asking, "What was 42 again? What about 77?" That is magic-number hell. At that point, either define constants or switch to two integers.

Two ints: The Classic (Type, ID) Combination

-- key1 = resource type, key2 = specific resource ID
SELECT pg_advisory_lock(1, 1234);  -- Type 1 (for example, "user"), ID 1234
SELECT pg_advisory_lock(2, 5678);  -- Type 2 (for example, "order"), ID 5678

This is the approach I recommend most. Use the first int as a namespace and the second as the specific ID. The semantics are clear, and collisions are less likely.

A common engineering practice is to define type constants in the application:

LOCK_TYPE_USER    = 1
LOCK_TYPE_ORDER   = 2
LOCK_TYPE_MIGRATE = 100

# Lock user 1234
query("SELECT pg_advisory_xact_lock(%s, %s)", [LOCK_TYPE_USER, 1234])

String Keys? Hash Them

Advisory Locks do not support string keys. If you naturally have a string identifier, such as the job name "sync_inventory", you must first hash it into an integer:

-- Use PostgreSQL's built-in hash function
SELECT pg_advisory_lock(hashtext('sync_inventory'));

Note that hashtext returns int4, a 32-bit integer, so collisions are possible. This is usually acceptable for locks because an extremely rare collision merely causes unnecessary waiting rather than data corruption. If you want to be extra cautious, use:

-- Split it into two int4 values and use a (namespace, hash) combination
SELECT pg_advisory_lock(100, hashtext('sync_inventory'));

Here, 100 acts as the namespace, grouping all "job-name hash locks" together and preventing collisions with other uses.

Key Principle: A Global Namespace

Advisory Lock keys are global across the entire database, regardless of table or schema. Every application shares the same 64-bit space. You therefore need an internal naming convention to prevent different modules from choosing the same keys.

A practical recommendation is to create a "lock key dictionary" table that records the meaning, owning module, and responsible team for each key. This table does not participate in runtime logic; it exists purely as documentation. Three months later, when someone needs to determine what lock 9527 means, you will be glad it exists.

5. Three Practical Scenarios

With the theory covered, let us see how Advisory Locks work in real scenarios.

Scenario 1: Concurrent Job Scheduling

This is the classic use case. You have N worker processes competing for jobs from a queue. The requirement is that only one worker may process a given job at any time.

The straightforward approach is SELECT ... FOR UPDATE SKIP LOCKED, which is useful in its own right and will be compared later. Sometimes, however, the locking logic for a job is more complex than a row lock. A job might involve multiple writes across several tables, or you might want to lock an abstract dimension such as "job type + time window." Advisory Locks are a better fit in those cases.

-- Pseudocode flow for a worker claiming a job
BEGIN;

-- Use (job type, job ID) as the lock key
SELECT pg_try_advisory_xact_lock(task_type, task_id) AS got;

-- Decide based on the result
DO $$
BEGIN
  IF NOT (SELECT got) THEN
    RAISE EXCEPTION 'task locked by other worker';
  END IF;
END $$;

-- The lock has been acquired; process the job by writing to various tables
UPDATE tasks SET status = 'running', worker_id = $worker_id
  WHERE id = $task_id;

-- ... Business logic ...

UPDATE tasks SET status = 'done' WHERE id = $task_id;
COMMIT;  -- The lock is released automatically, allowing another worker to take the next job with the same key

Key points:

1. Use non-blocking pg_try_advisory_xact_lock. If acquisition fails, give up immediately and try the next job.
2. Keep the lock and business logic in the same transaction, ensuring that the job is processed while the lock is held and that the lock is released when the job finishes.
3. Use (task_type, task_id) as the key for precise granularity.

The advantage of this pattern is that even if the job logic involves multiple tables and writes, no other worker can enter while the lock is held. It is more reliable than relying solely on the optimistic claim pattern of UPDATE ... WHERE status = 'pending', which can still have edge cases under concurrency.

Scenario 2: Preventing Concurrent Migration Runs

Database migration scripts must run serially. Otherwise, schema changes can interfere with each other and corrupt data. But you cannot guarantee that an operator will not accidentally run migrate twice at the same time, or that CI will not retry a migration after the first run times out halfway through.

Add a migration lock:

-- At the beginning of the migration script
SELECT pg_advisory_lock(99999);  -- 99999 = key reserved for "database migration"

-- ... A series of DDL statements and data migrations ...

-- At the end of the script
SELECT pg_advisory_unlock(99999);

This example intentionally uses a session-level lock instead of a transaction-level lock. Why? Migration scripts often do not run within a single transaction. Some DDL can be problematic inside a transaction, or you may want the migration to commit in stages. A session-level lock spans the script's entire lifecycle and is released when the connection closes, which precisely covers the script from start to finish.

But take care: if the script exits abnormally, you must ensure that the connection really closes. Consider this Bash wrapper:

psql -c "SELECT pg_advisory_lock(99999)"
psql -f migration.sql
psql -c "SELECT pg_advisory_unlock(99999)"  # Runs under ideal conditions

This is wrong. The three psql commands create three separate connections. The first connection closes immediately after acquiring the lock, ending the session and releasing the lock, so it provides no protection at all. The correct approach is to run the lock and migration over the same connection:

psql <<EOF
  SELECT pg_advisory_lock(99999);
  \i migration.sql
  SELECT pg_advisory_unlock(99999);
EOF

Alternatively, wrap it in a transaction with pg_advisory_xact_lock if the migration supports transactions. This is a classic mistake that beginners commonly make.

Scenario 3: Idempotency Control

An endpoint or job may be triggered repeatedly because a user clicks several times, a message queue redelivers a message, or a retry mechanism runs. The business requirement, however, is to execute the operation only once. A common idempotency solution uses a unique constraint plus an INSERT, but sometimes you do not want to introduce an additional deduplication table, or the idempotency dimensions are complex.

Advisory Locks provide lightweight "short-term idempotency":

-- Process an operation that requires idempotency
BEGIN;
SELECT pg_try_advisory_xact_lock(operation_type, business_id);
-- Acquiring the lock means no other request is currently processing this operation for the same business_id
-- (Note: this prevents concurrency, not replay; see below)
process_business(business_id);
COMMIT;

Important warning: Advisory Locks prevent concurrent execution, not replay. If two identical requests arrive one after another, and the first has already finished and released the lock, the second can still acquire the lock and process the operation again. Preventing replay requires a persistent idempotency record, such as a processed_events table with a unique constraint on event_id.

The real value of Advisory Locks in an idempotency system is preventing concurrent duplicate processing while an operation is in progress. They provide a fast short-circuit layer in an idempotency architecture, not the entire solution. A complete idempotency design usually looks like this:

BEGIN;
-- 1. Advisory Lock prevents concurrency with a fast short circuit
SELECT pg_try_advisory_xact_lock(event_type, event_id);
-- 2. Check the idempotency table to prevent replay
SELECT 1 FROM processed_events WHERE event_id = $1;
-- 3. Process the business operation
process();
-- 4. Record that it has been processed
INSERT INTO processed_events(event_id) VALUES ($1);
COMMIT;

The two layers each serve a distinct purpose.

6. Differences from Row Locks (SELECT FOR UPDATE)

Many people conflate Advisory Locks with SELECT FOR UPDATE because both are "locks." Their purposes are completely different, however, and confusing them causes problems.

Row Locks: Locking the Data Itself

BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
-- This row is locked; other transactions cannot FOR UPDATE, UPDATE, or DELETE it
-- until the current transaction commits
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;

Row locks are part of the MVCC system. They are directly bound to specific rows and exist to serialize concurrent modifications to the same row. They are part of ACID and are managed automatically by PostgreSQL. You do not need, and cannot, release them manually; they are released automatically when the transaction ends.

Advisory Locks: Locking a Coordination Signal

BEGIN;
SELECT pg_advisory_xact_lock(12345);
-- Lock 12345 is held, but no specific data has been locked
-- Other transactions can still read and write every row in the accounts table normally
COMMIT;

An Advisory Lock does not lock any data. It is simply a signal telling other sessions that also try to acquire lock 12345: "I am using it; wait or give up." It has no coupling to data in any table.

Comparison Table

When Should You Use Each One?

Use a row lock when you need to protect specific data from concurrent modification. For example, a transfer must lock the account row to prevent two transactions from deducting funds simultaneously and producing an incorrect balance. This is exactly what row locks are for, and Advisory Locks cannot replace them because they do not prevent data modifications.

Use an Advisory Lock when the coordination target is not concrete data but a "logical intent" or "abstract resource." For example, you may want to allow only one global index-rebuild job to run at a time. An index rebuild is not a data row and cannot be locked as one, so you need an Advisory Lock.

A useful test is to ask: "Does the object I want to lock have a corresponding row in the database?" If it does, consider a row lock first. If it does not, or if the locking dimension extends beyond a single row, such as "a type of operation," use an Advisory Lock.

The two often work best in combination. In a job-scheduling system, for example, an Advisory Lock can provide a fast lock-acquisition decision that prevents large numbers of workers from rushing in, while a row lock protects changes to the job row and maintains data consistency.

7. Five Traps

Finally, let us cover the pitfalls. Used correctly, Advisory Locks are powerful. Used poorly, they are a time bomb.

Trap 1: Forgetting to Release a Session-Level Lock

This is the biggest and most classic pitfall. Code acquires a lock with pg_advisory_lock, but an exception causes the execution path to exit before reaching unlock. The lock remains held until the connection closes.

-- Antipattern
SELECT pg_advisory_lock(100);
-- ... An exception is thrown here ...
SELECT pg_advisory_unlock(100);  -- Never reached

If you use a connection pool, as nearly every production environment does, that connection will be reused. The next request on the connection discovers, "Why am I holding lock 100?" The system then exhibits all kinds of inexplicable serialization behavior.

Mitigations:

• If you can use pg_advisory_xact_lock, do not use pg_advisory_lock.
• If you truly need a session-level lock, use try/finally to guarantee that unlock runs.
• Monitor the pg_locks view and investigate advisory locks that have been held for a long time:

-- View currently held advisory locks
SELECT pid, locktype, objid, granted, query_start, state, query
FROM pg_locks
JOIN pg_stat_activity USING (pid)
WHERE locktype = 'advisory';

Trap 2: Session Lock Leakage with Connection Pools

This is a variation of Trap 1. Many ORMs and connection pools, such as PgBouncer in transaction pooling mode, reset connection state after a transaction ends. However, session-level Advisory Locks are not necessarily reset, depending on the implementation and configuration. As a result, you may think the lock was released when the transaction ended, only for it to follow the connection into the next request.

Mitigations:

• In PgBouncer transaction pooling mode, do not use session-level Advisory Locks. Use only transaction-level locks.
• If you need session-level semantics, PgBouncer must use session pooling mode.

Trap 3: Key Collisions

Developers in different modules each choose a number that "looks unused" as a key, and the keys collide. The symptom is that two seemingly unrelated features occasionally block each other. Investigation is extremely difficult because the code appears to have no relationship at all.

Mitigations:

• Maintain a global key dictionary, as mentioned earlier.
• Use the two-int version and require the first int to identify the module or namespace.
• Treat Advisory Lock keys as shared resources during code review.

Trap 4: Deadlocks

Like row locks, Advisory Locks can deadlock. If two sessions acquire multiple locks in different orders, they can form a circular wait.

-- Session A
SELECT pg_advisory_lock(1);
SELECT pg_advisory_lock(2);  -- Waits for B to release 2

-- Session B (concurrent)
SELECT pg_advisory_lock(2);
SELECT pg_advisory_lock(1);  -- Waits for A to release 1
-- Deadlock! PostgreSQL detects it and aborts one session

The good news is that PostgreSQL has a deadlock detector. After deadlock_timeout, which defaults to one second, it automatically aborts one session instead of hanging forever. The bad news is that you still receive ERROR: deadlock detected, affecting the business operation.

Mitigation: Apply the same rule used for row-lock deadlocks: maintain a globally consistent lock-acquisition order. If all code acquires locks in the order 1 → 2 → 3, no deadlock can occur.

Trap 5: Treating an Advisory Lock as a Data Lock

This is a conceptual mistake. Someone writes:

-- Attempt to prevent others from modifying account 1
SELECT pg_advisory_lock(1);
-- ... But another session can still run UPDATE accounts WHERE id = 1 ...

An Advisory Lock does not prevent any data operation. Other sessions can still run UPDATE or DELETE because this lock has no relationship to the accounts table. You think the data is locked, but it may already have been changed.

Mitigation: Remember this permanently: Advisory Locks do not protect data; they only provide coordination. To protect data, use row locks such as FOR UPDATE, constraints, or triggers. Advisory Locks are a supplement, not a replacement.

Summary

Let us collect the main points:

1. Advisory Locks are application-level locks, not data locks. They lock a numeric key defined by your application and exist solely for coordination between processes. PostgreSQL is only a trusted arbitrator and does not care what the key represents.

2. Prefer transaction-level locks (pg_advisory_xact_lock). They are released automatically with the transaction and cannot leak because someone forgot to unlock them. Use session-level locks only when a lock must span transactions, and guard them rigorously with try/finally.

3. Non-blocking pg_try_advisory_* functions are more common in practice. They return false immediately when acquisition fails and suit scenarios that require a quick decision about whether work can proceed. Blocking pg_advisory_* functions suit scenarios that must wait in line, but beware of indefinite blocking.

4. Key design is an engineering problem, not a technical one. Use the two-int version with a namespace and ID, maintain a global key dictionary, and avoid collisions between modules. Convert string keys to integers with hashtext.

5. Advisory Locks and row locks serve different purposes and work best together. Row locks protect data consistency as part of ACID. Advisory Locks coordinate application behavior such as claiming jobs, preventing concurrent runs, and rate limiting. The test is simple: does the locked object have a corresponding row in a table? If so, prefer a row lock. If not, use an Advisory Lock.

6. The main pitfalls involve lifecycle management and misuse. Session lock leakage, unexpected behavior under connection-pool modes, key collisions, deadlocks, and using Advisory Locks as data locks: the first five require discipline and monitoring, while the last requires correct understanding.

Here is one final statement I repeatedly emphasize to teams:

Advisory Locks are coordination signals, not data guardrails. Using them to block concurrent data modifications means you chose the wrong tool. Using them to let a group of processes compete for work in an orderly way means you chose exactly the right lock.

Do not reach for Redis automatically. First check whether the PostgreSQL instance already at hand can solve the problem.