PostgreSQL advisory locks coordinate operations through keys chosen by the application. They do not lock table rows. I encountered them at work with PgBoss and wanted to understand how they differ from FOR UPDATE.
Quick Refresher: Transactions and Their Limits
A short transaction refresher helps frame advisory locks.
A transaction guarantees atomicity: either every statement in the group succeeds, or none of them do.
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
The transaction commits both updates or neither update.
Transactions only coordinate database work. If your transaction calls an external API (sending an email, hitting a payment gateway), that external call is not part of the transaction. If the transaction rolls back after the API call, the email is already sent. The database cannot undo that.
The Classic Race Condition
I saw this race in production. Two workers read the same account balance at the same time:
sequenceDiagram
participant W1 as Worker 1
participant DB as Database
participant W2 as Worker 2
W1->>DB: SELECT balance WHERE id=1 (gets $40)
W2->>DB: SELECT balance WHERE id=1 (gets $40)
W1->>DB: UPDATE balance = 40 - 30 = $10
W2->>DB: UPDATE balance = 40 - 30 = $10
Note over DB: Balance is $10, but $60 was withdrawn from $40!
Both workers read $40, both approve a $30 debit, both write $10. The account should be at -$20 (or the second transaction should have been rejected), but instead it is at $10. Classic check then act race condition.
The fix: push the invariant check into the SQL itself.
UPDATE accounts
SET balance = balance - 30
WHERE id = 1 AND balance >= 30;
The check and write now form one atomic operation. If the balance is below $30 when the second update runs, it affects zero rows. The application can reject that debit.
Row Level Locks
Sometimes you need more control than atomic updates. That is where explicit row locking comes in.
FOR UPDATE
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
This acquires an exclusive lock on the selected row. Any other transaction trying to SELECT ... FOR UPDATE or UPDATE the same row will block until the first transaction commits or rolls back.
sequenceDiagram
participant T1 as Transaction 1
participant DB as Database
participant T2 as Transaction 2
T1->>DB: SELECT ... FOR UPDATE (row locked)
T2->>DB: SELECT ... FOR UPDATE (BLOCKED)
T1->>DB: UPDATE ... SET balance = 10
T1->>DB: COMMIT
T2->>DB: Lock acquired, gets fresh data
T2->>DB: UPDATE ... SET balance = ...
T2->>DB: COMMIT
Row locks preserve correctness, but they limit throughput. Every concurrent writer has to wait in line.
FOR SHARE
SELECT * FROM accounts WHERE id = 1 FOR SHARE;
Multiple transactions can hold shared locks simultaneously since they are all just reading. But no transaction can write while shared locks are held.
Shared row locks can delay conflicting writes until their transactions end. Keep these transactions short and measure lock waits. See the PostgreSQL lock conflict rules.
SKIP LOCKED
This one is particularly relevant for job queues (and it is how PgBoss works internally):
SELECT * FROM jobs
WHERE status = 'pending'
ORDER BY created_at
LIMIT 1
FOR UPDATE SKIP LOCKED;
The SKIP LOCKED option skips rows with conflicting row locks. Multiple workers can then claim different jobs from the same table. It does not eliminate table locks or all contention.
Advisory Locks
Advisory locks apply when the thing being coordinated is not a table row. They can represent:
- A unique external API call that should not be duplicated
- A processing pipeline for a specific customer
- A migration or schema change that only one process should run
- An application process that must coordinate work across instances
The application chooses integer keys for these locks. PostgreSQL does not automatically apply them to data. Coordination works only when all relevant code acquires the same lock.
Session vs Transaction Scope
Session level locks are held until you explicitly release them or the connection closes:
-- Acquire
SELECT pg_advisory_lock(42);
-- Do your work...
-- Release
SELECT pg_advisory_unlock(42);
If the code fails between acquisition and release, the session can retain the lock until its connection closes.
Transaction level locks are automatically released when the transaction commits or rolls back:
BEGIN;
SELECT pg_advisory_xact_lock(42);
-- Do your work...
COMMIT; -- Lock automatically released
Prefer advisory locks with transaction scope when the work fits within a transaction. PostgreSQL releases them when the transaction ends.
Non blocking Try Variants
Sometimes you do not want to wait for a lock. You want to try and move on if it is taken:
-- Returns true if lock acquired, false if not
SELECT pg_try_advisory_xact_lock(42);
Use try variants for:
- Worker processes that should skip work another worker is already doing
- Health check endpoints that should not block
- Graceful degradation when contention is high
// Non-blocking advisory lock in application code
async function processCustomerExclusive(customerId: number): Promise<boolean> {
const result = await db.query(
"SELECT pg_try_advisory_xact_lock($1) as acquired",
[customerId]
);
if (!result.rows[0].acquired) {
// Another worker is already processing this customer
return false;
}
// We have the lock, safe to process
await processCustomerData(customerId);
return true;
}
Key Packing
Advisory locks take either a single bigint or a pair of int values as the key. But often your lock key is a string (like a customer ID or job type). You need to convert it to an integer:
import { createHash } from "crypto";
function advisoryKey(namespace: string): bigint {
const hash = createHash("sha256").update(namespace).digest();
// Read first 8 bytes as a signed 64-bit integer
return hash.readBigInt64BE(0);
}
// Usage
const lockKey = advisoryKey("process-customer:cust_12345");
await db.query("SELECT pg_advisory_xact_lock($1)", [lockKey]);
Hash collisions are possible but unlikely. A collision means two unrelated operations wait for each other, reducing throughput but not corrupting data.
For composite keys, you can pack two 32-bit IDs into one 64-bit key:
function packKeys(a: number, b: number): bigint {
return (BigInt(a) << 32n) | (BigInt(b) & 0xFFFFFFFFn);
}
// Lock on (tenantId=5, resourceId=42)
const lockKey = packKeys(5, 42);
Deadlock Prevention: Total Ordering
If you need to acquire multiple advisory locks, you must always acquire them in the same order. Otherwise you get deadlocks:
graph LR
T1[Transaction 1] -->|holds Lock A| LA[Lock A]
T1 -->|wants Lock B| LB[Lock B]
T2[Transaction 2] -->|holds Lock B| LB
T2 -->|wants Lock A| LA
style LA fill:#fecaca
style LB fill:#fecaca
Transaction 1 holds A, wants B. Transaction 2 holds B, wants A. Neither can proceed. Deadlock.
The fix: sort your lock keys and always acquire in ascending order.
async function acquireLocksInOrder(keys: bigint[]): Promise<void> {
// Sort to prevent deadlocks
const sorted = [...keys].sort((a, b) => (a < b ? -1 : a > b ? 1 : 0));
for (const key of sorted) {
await db.query("SELECT pg_advisory_xact_lock($1)", [key]);
}
}
The Fetch Lock Refetch Pattern
The fetch lock refetch pattern matters when you need to lock based on query results. The data might change between the initial query and lock acquisition.
Fetch first, lock the keys, refetch the rows, then compare them.
async function processReadyJobs(): Promise<void> {
await db.query("BEGIN");
try {
// 1. Unsafe fetch: get candidate rows
const candidates = await db.query(
"SELECT id FROM jobs WHERE status = 'ready' LIMIT 10"
);
if (candidates.rows.length === 0) {
await db.query("COMMIT");
return;
}
// 2. Acquire locks in sorted order
const ids = candidates.rows.map((r) => r.id).sort();
for (const id of ids) {
await db.query("SELECT pg_advisory_xact_lock($1)", [id]);
}
// 3. Refetch with locks held
const confirmed = await db.query(
"SELECT id FROM jobs WHERE id = ANY($1) AND status = 'ready'",
[ids]
);
// 4. Compare: only process jobs that are still ready
for (const job of confirmed.rows) {
await processJob(job.id);
}
await db.query("COMMIT");
} catch (error) {
await db.query("ROLLBACK");
throw error;
}
}
Between step 1 and step 2, another transaction may process the jobs. Lock acquisition can wait indefinitely unless a timeout limits it. Read the rows again after acquisition to check their current state.
When to Use What
The decision tree is:
graph TD
A[Need to coordinate concurrent access?] --> B{Operating on existing rows?}
B -->|Yes| C{Need exclusive write access?}
C -->|Yes| D[FOR UPDATE]
C -->|No, readers are fine| E[FOR SHARE]
B -->|No| F{Need to lock a logical concept?}
F -->|Yes| G[Advisory Lock]
F -->|No| H[Maybe you don't need locking]
G --> I{Inside a transaction?}
I -->|Yes| J[pg_advisory_xact_lock]
I -->|No| K[pg_advisory_lock + careful cleanup]
Use row locks (FOR UPDATE, FOR SHARE) when you are protecting actual data in a table.
Use advisory locks when you are coordinating around a concept: a job type, a customer pipeline, a migration process, an external API call.
Use SKIP LOCKED when you need job queue semantics: multiple workers pulling from the same pool without blocking each other.
PgBoss schema locks and a maintenance example
Working with PgBoss at Xendit helped me understand how it uses advisory locks internally.
Current PgBoss documentation identifies pg_advisory_xact_lock as coordination for schema creation and migration. See the database backend documentation.
The following snippet illustrates a separate maintenance leader pattern. It is not the documented current PgBoss implementation:
// Simplified version of what PgBoss does internally
const result = await db.query(
"SELECT pg_try_advisory_lock($1) as acquired",
[MAINTENANCE_LOCK_KEY]
);
if (result.rows[0].acquired) {
// This instance owns maintenance: archive old jobs,
// expire stalled jobs, schedule cron jobs
await runMaintenance();
}
In this illustrative pattern, cooperating instances use the same lock key. One instance runs maintenance while it holds the lock. Other instances continue to process jobs.
The example uses a session lock because maintenance runs across multiple transactions. PostgreSQL releases the lock when the session ends. Another instance can then acquire it.
I also investigated delayed PgBoss maintenance in production. In that setup, job processing overloaded the instance responsible for maintenance, which delayed tasks such as job expiration. We isolated maintenance from the worker pool. That experience describes our deployment, not a guarantee about the internals of every PgBoss version.
Closing Thoughts
I wish I had understood advisory locks earlier. At previous jobs, we built coordination with Redis locks or application mutexes while PostgreSQL was already available. Its advisory locks could have been another option.
I now consider advisory locks when cooperating workers need exclusive access to an operation. The lock key can identify a customer, migration, or external API call.
If you are already using PostgreSQL, advisory locks can coordinate job types, customer pipelines, migrations, or external calls. For a comparison of PgBoss and BullMQ, see PgBoss vs BullMQ.
Loading comments...