When two deliveries about the same payment reach two workers at the same moment, the goal is that exactly one of them changes the payment’s state and the other finds that change already made. In PostgreSQL, the dependable way to get that outcome is to open one transaction, take a transaction-level advisory lock keyed to the payment, check whether the work has already been applied, make the change only if it has not, and commit. The lock releases itself at commit or rollback.
Two conditions make this work, and both are easy to miss. First, the lock is cooperative. PostgreSQL will not stop a code path that writes the same payment without taking the lock, so every writer must use the same key and the same protocol. Second, the check and the change must sit inside the same transaction that holds the lock. A check made before the lock is acquired, or in a separate transaction, protects nothing.
As an Amazon Associate I earn from qualifying purchases.
In this article, “one rank” means whichever single logical resource must be changed by one writer at a time. In most payment systems that is one payment row. The examples assume a payments table with a bigint primary key id and a text column status, plus a webhook_events table whose event_id column is a primary key. Adapt the names and the transition rules to your schema.
Free tools Windows power users keep installed
One-click scans. No signup required.
Why the lock has to be cooperative
PostgreSQL’s documentation on explicit locking describes advisory locks in one plain sentence: “PostgreSQL provides a means for creating locks that have application-defined meanings” (PostgreSQL Global Development Group, PostgreSQL documentation, explicit locking). The server stores the lock and makes competing sessions wait, but it attaches no business meaning to the number you pass in. Nothing prevents an admin script, a reconciliation job, or a second service from updating the same payment without calling the lock function. That code path is simply unprotected.
#1 Best Overall
For a webhook handler, the function you want is pg_advisory_xact_lock. It obtains an exclusive, transaction-level lock and waits if another session holds a conflicting lock. The documentation states the lifecycle directly: transaction-level lock requests “are automatically released at the end of the transaction, and there is no explicit unlock operation.” That property is what makes the pattern safe under errors. If the handler throws an exception and the transaction rolls back, the lock goes with it.
Choosing the lock key
Key the lock on a stable internal identifier, not on a value that callers might format differently. The two official call forms accept different key shapes:
| Form | Call | Key space | Collision and mapping considerations |
|---|---|---|---|
| One 64-bit key | pg_advisory_xact_lock(bigint) |
One 64-bit value | Passing a numeric primary key directly means distinct IDs never collide. Hashing a string identifier into 64 bits can still produce collisions, so the mapping must be documented. |
| Two 32-bit keys | pg_advisory_xact_lock(integer, integer) |
Two 32-bit values | Suits a fixed namespace constant plus a 32-bit ID. IDs wider than 32 bits do not fit, and hashing them into 32 bits makes collisions far more likely. |
Pick one form for each resource type and use it everywhere. If the payment’s internal ID is a bigint, pass it straight through with SELECT pg_advisory_xact_lock($1). Hashing is needed only when the identity is a string, such as an external provider’s object ID. In that case, put the hash in one function, call it from every write path, and accept that two different strings may hash to the same key.
A collision does not corrupt data. It makes unrelated payments wait behind each other for longer than necessary. The inconsistency is the real hazard: if one path locks on the numeric ID while another locks on a hash of the external ID, the two paths never coordinate, and the protocol silently stops working.
The handler transaction, step by step
The following sequence keeps the duplicate check and the state change inside one transaction that owns the lock. Run each step on the same database connection, between BEGIN and COMMIT.
Rank #2
-
Start the transaction. Issue
BEGIN, or use your driver’s equivalent. Keep everything from this point toCOMMITas database work only. A network call made while the lock is held keeps every competing writer waiting for as long as that call takes. -
Acquire the lock. Pass the payment’s internal ID. The statement returns only when this session holds the lock.
Special offer. See more information about Outbyte and uninstall instructions. Please review EULA and Privacy policy.SELECT pg_advisory_xact_lock($1); -
Record the event. Insert the event identifier and let the primary key reject a repeat. Because the event row and the state change commit together, a rollback also removes the event row, so a failed attempt can be retried cleanly.
INSERT INTO webhook_events (event_id, payment_id, received_at) VALUES ($2, $1, now()) ON CONFLICT (event_id) DO NOTHING RETURNING event_id;If the statement returns no row, a transaction that already committed recorded this event. Issue
COMMITwithout further changes and acknowledge the delivery. -
Apply the transition only if it is still valid. The
WHEREclause carries the business rule, so the write succeeds only when the payment is still in the expected state.UPDATE payments SET status = 'succeeded', updated_at = now() WHERE id = $1 AND status = 'pending';Zero updated rows means the payment has already moved past
pending, for example because a different event for the same payment applied the change first. Record that outcome in your logs rather than forcing the update.Crashes, No Sound, or Screen Glitches?
Random freezes, missing sound and display glitches usually trace back to one bad driver. Find and replace yours safely.Free scan · under a minutePC Slower Than It Used to Be?
A free scan shows the junk files, broken settings and background clutter dragging Windows down - then fixes them in one click.Free scan · Windows 10 & 11Special offer. See more information about Outbyte and uninstall instructions. Please review EULA and Privacy policy. -
Commit. Issue
COMMIT. The event row and the status change become visible together, and the advisory lock is released at this point. No unlock call is needed.
The conditional UPDATE is worth keeping even though the advisory lock already serializes the handler. It is a database-enforced guard on the transition itself, and it still holds if a future code path forgets the lock.
Session-level locks are the wrong default here
PostgreSQL also offers session-level advisory locks, which have a different lifecycle. They persist until they are explicitly released or the session ends, and rolling back a transaction does not release them.
| Property | Transaction-level | Session-level |
|---|---|---|
| Acquire | pg_advisory_xact_lock(key) |
pg_advisory_lock(key) |
| Release | Automatic at COMMIT or ROLLBACK |
Explicit pg_advisory_unlock(key), or session end |
| Effect of rollback | Lock is released with the transaction | Lock is not released by rollback |
| Risk for a webhook handler | Low when the protected work fits in one transaction | A missed unlock keeps the lock held; with a connection pool, the next client that receives that connection inherits the problem |
Use the transaction-level form for webhook handling. Session-level locks are better suited to work that must span several transactions, which is not the shape of this problem.
Recommended Free Tools
Blocking or try-lock
Blocking with pg_advisory_xact_lock
The blocking form waits. A second delivery for the same payment simply pauses until the first transaction commits or rolls back, then runs the check against the committed state and finds the event already recorded. That behavior suits short transactions. To cap the wait, set a lock timeout inside the transaction before step 2:
SET LOCAL lock_timeout = '2s';If the wait exceeds the timeout, PostgreSQL raises an error with SQLSTATE 55P03 (lock_not_available). Your handler should treat that as a failed attempt and decide its response deliberately.
Try-lock with pg_try_advisory_xact_lock
The try variant returns a boolean immediately, so the handler never waits:
SELECT pg_try_advisory_xact_lock($1);When it returns false, the handler has to choose a response. It can return a non-success status so the sender tries again later, or it can store the event for a worker to process after the lock is free. The first option depends on how the sender schedules redeliveries, which this article does not cover, so check your provider’s documentation before relying on it.
Do these 3 things before closing this tab:
1Scan for outdated or missing drivers - takes under a minute2Repair Windows errors before they cause bigger problems3Fix the driver behind crashes, sound loss and screen glitchesDeadlocks and retry policy
When one transaction must acquire more than one advisory lock, for example when a refund touches two payments, acquire them in ascending key order every time. Every path that takes both locks must follow the same order, or two transactions can each hold one lock and wait for the other.
PostgreSQL detects deadlocks and aborts one of the participating transactions with SQLSTATE 40P01 (deadlock_detected). The application has to handle that error. Retry the whole transaction from BEGIN, with a fixed maximum number of attempts and a short backoff between them. Do not retry only the UPDATE. The idempotency check has to run again inside the new transaction, because the state it saw in the aborted attempt may no longer be current.
Monitoring lock contention
When a handler appears slow or stalls, inspect the advisory locks currently held and requested:
SELECT l.pid, l.granted, a.state, a.query
FROM pg_locks AS l
JOIN pg_stat_activity AS a ON a.pid = l.pid
WHERE l.locktype = 'advisory';Rows with granted = false are sessions waiting for a lock. The key is split across the pg_locks columns. For a single bigint key, classid holds the high 32 bits and objid holds the low 32 bits, with objsubid set to 1. For a two-integer key, classid and objid hold the two integers and objsubid is 2. Use that decoding to map a waiting session back to a payment.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
Every writer must use the protocol
The protection covers only the code that takes the lock. Webhook endpoints, retry workers, reconciliation scripts, manual refund tools, and data migrations that update payments all need the same lock call before they touch a payment row. The most reliable way to enforce that is a single repository function or database function that every write path calls, so no caller builds its own update. Code review should flag any new statement that updates payments without going through that function.
What this pattern does not guarantee
Side effects outside the database
The transaction protects database state. It does not make emails, fulfillment requests, or calls to another ledger idempotent, because the lock does not reach across the network. A common approach is to write an outbox row in the same transaction as the state change, then let a separate worker perform the external action after commit. That worker needs its own idempotency key for each downstream call. This is general engineering practice rather than a guarantee that the database provides.
Stripe’s idempotency keys cover API requests, not webhook delivery
Stripe’s Idempotency-Key mechanism applies to API requests your code sends to Stripe, so a retried request returns the original result. It says nothing about how Stripe schedules webhook redeliveries or in what order events arrive. Stripe’s current API reference states that idempotency keys can be removed once they are at least 24 hours old, which is a retention limit on request replay, not a delivery guarantee. Stripe’s Events API reference states that events can be retrieved for the last 30 days, which sets the horizon for any reconciliation job that re-fetches events from Stripe. Beyond that window, the job cannot recover them from that endpoint.
Arrival order
The conditional update prevents a transition from being applied twice. It does not decide what happens when events arrive out of the expected order. If a refund event reaches the handler before the success event it depends on, the transition guard will reject the later success change. Define the allowed transitions explicitly, and decide whether an out-of-order event is recorded, held for later processing, or flagged for review.
The Tool Desk
Outbyte PC Repair FREERepair Windows errors before they cause bigger problemsFix Now →Outbyte Driver Updater FREEScan for outdated or missing drivers - takes under a minuteDriver Scan →Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
