I've been going deeper into PostgreSQL's locking mechanisms lately. Most of us know about transactions and maybe FOR UPDATE, but there's a whole category of locking that lives outside of rows and tables entirely: advisory locks. I ran into these while working with PgBoss (which uses them internally), and I realized I didn't really understand what was happening under the hood. So I went and studied them properly.
Here's what I found.
Quick Refresher: Transactions and Their Limits
Before we get to advisory locks, let's make sure the foundation is solid.
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;
Both updates happen, or neither does. Simple.
But here's what caught me off guard early in my career: 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 can't undo that.
The Classic Race Condition
Here's a scenario that I've actually seen 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's 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;
Now the check and the write are atomic. If the balance is already below $30 when the second worker's update runs, zero rows are affected and you can handle that gracefully.
Row-Level Locks
Sometimes you need more control than atomic updates. That's 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
This is great for correctness, but it limits 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're all just reading. But no transaction can write while shared locks are held.
The gotcha: write starvation. Under high read traffic, a waiting writer can get perpetually blocked because new readers keep acquiring shared locks ahead of it. Something to watch for in read-heavy systems.
SKIP LOCKED
This one is particularly relevant for job queues (and it's how PgBoss works internally):
SELECT * FROM jobs
WHERE status = 'pending'
ORDER BY created_at
LIMIT 1
FOR UPDATE SKIP LOCKED;
Instead of blocking when a row is locked, SKIP LOCKED silently skips it and moves to the next available row. Perfect for worker pools where multiple consumers grab jobs from the same table. No blocking, no contention, each worker grabs the next unlocked job.
Advisory Locks
Now we get to the interesting part. All the locking we've discussed so far operates on rows that exist in a table. But what if you need to coordinate access to something that isn't a row? Something conceptual, like:
- A unique external API call that shouldn't be duplicated
- A processing pipeline for a specific customer
- A migration or schema change that only one process should run
- A job queue polling mechanism (hello, PgBoss)
That's what advisory locks are for. They operate on application-chosen integer keys, completely independent of any table or row. PostgreSQL doesn't enforce them on any data. They're "advisory" because they only work if every piece of code that needs coordination agrees to acquire 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);
The danger: if your code errors out between acquire and release, the lock leaks until the database connection closes. Easy to forget, hard to debug.
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
Always prefer transaction-scoped advisory locks unless you have a specific reason not to. They're self-cleaning by design.
Non-blocking Try Variants
Sometimes you don't want to wait for a lock. You want to try and move on if it's taken:
-- Returns true if lock acquired, false if not
SELECT pg_try_advisory_xact_lock(42);
This is useful for:
- Worker processes that should skip work another worker is already doing
- Health check endpoints that shouldn't 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. And importantly, a collision just means two unrelated operations wait for each other (reduced throughput), not data corruption.
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
This is the most subtle pattern, and the one that took me the longest to understand. When you need to lock based on query results, you have a problem: the data might change between when you query it and when you acquire the lock.
The solution: fetch, lock, refetch, compare.
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;
}
}
Why refetch? Because between step 1 and step 2, another transaction might have already processed those jobs. The lock acquisition itself can take an unbounded amount of time (waiting for other holders), and during that wait, the world can change.
When to Use What
Here's my mental model:
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're protecting actual data in a table.
Use advisory locks when you're 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.
Real World: How PgBoss Uses Advisory Locks
This is where it clicked for me. I've been working with PgBoss at Xendit, and once I understood advisory locks, I finally understood what PgBoss was doing under the hood.
PgBoss uses advisory locks for one critical purpose: making sure only one instance runs the maintenance cycle at a time. When you have multiple application servers all running PgBoss, each instance tries to claim the maintenance lock on startup:
// 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();
}
Without this, you'd have multiple instances running maintenance simultaneously: double-archiving, double-scheduling cron jobs, race conditions everywhere. The advisory lock makes it so that exactly one instance is the "leader" for maintenance, and the others just process jobs.
This is a session-level lock (not transaction-scoped), because maintenance runs on an interval, not within a single transaction. If the leader process dies, the connection closes, the lock releases, and another instance picks it up. Self-healing by design.
Understanding this pattern also helped me debug a production issue where PgBoss maintenance was falling behind. The instance holding the maintenance lock was overloaded with job processing, so maintenance tasks (like expiring stalled jobs) weren't running on time. The fix was isolating the maintenance instance from the worker pool. Simple, but you need to understand the advisory lock pattern to even know where to look.
Closing Thoughts
I wish I'd understood advisory locks earlier. There were times at my previous jobs where we built custom coordination mechanisms using Redis distributed locks or application-level mutexes, when we already had PostgreSQL sitting right there with a built-in, transactional, deadlock-detecting locking system.
The key insight for me was that advisory locks are not about locking data. They're about locking ideas. The idea that only one worker should process customer X right now. The idea that only one migration should run at a time. The idea that this external API call shouldn't be duplicated.
If you're already using PostgreSQL (and most of us are), advisory locks are a tool you should have in your mental toolkit. I also wrote a deep dive on PgBoss vs BullMQ if you want to see how advisory locks fit into a full job queue architecture.