"It's wrapped in a transaction, so it's safe" is one of the most expensive assumptions to find in a PostgreSQL code review. A transaction guarantees atomicity and consistency for its own operations, but nothing stops two concurrent transactions from reading the same row, reasoning about it independently, and each writing a result that silently overwrites the other's. That's a lost update, and it's the most common concurrency bug in relational databases.
The scenario that breaks under load
A textbook e-commerce case: decrementing stock at checkout.
-- transaction A -- transaction B
BEGIN; BEGIN;
SELECT quantity FROM stock SELECT quantity FROM stock
WHERE product_id = 42; WHERE product_id = 42;
-- returns 1 -- returns 1
-- (application logic: 1 - 1 = 0) -- (application logic: 1 - 1 = 0)
UPDATE stock SET quantity = 0
WHERE product_id = 42;
COMMIT;
UPDATE stock SET quantity = 0
WHERE product_id = 42;
COMMIT;
Both transactions read quantity = 1, each believes it got the last unit, each commits 0. Two orders just got accepted for a single item in stock. No SQL error is raised, no constraint is violated; the bug is purely logical and only shows up when someone audits the order ledger.
Why PostgreSQL lets this through
At the default isolation level, Read Committed, every statement sees a snapshot of data committed as of when that statement started, not when the transaction started. PostgreSQL relies on MVCC (Multi-Version Concurrency Control): every row update creates a new version, and a transaction only sees versions committed before its current statement began. This natively rules out dirty reads (impossible under PostgreSQL even at Read Committed, unlike some other engines), but it does nothing to stop two transactions from reading the same row version and each writing to it without ever seeing the other.
A point that trips people up: a plain SELECT takes no lock at all. PostgreSQL only locks a row at UPDATE/DELETE time, or when explicitly asked to. Between transaction A's SELECT and its UPDATE, nothing stops B from reading that exact same row.
Diagnosing the problem
The most reliable signal isn't an error, it's a business-level inconsistency discovered after the fact (stock can't go negative, but more orders were accepted than units available; a balance that doesn't match the sum of its movements). To reproduce locally, two side-by-side psql sessions with a manual BEGIN on each, interleaving the statements by hand, is enough to demonstrate the lost update in a handful of lines, exactly as in the block above.
The fix: explicitly lock the row you read
SELECT ... FOR UPDATE forces PostgreSQL to take an exclusive lock on the selected rows. Any other transaction attempting a SELECT FOR UPDATE or an UPDATE on that same row blocks until the first one commits or rolls back.
BEGIN;
SELECT quantity FROM stock
WHERE product_id = 42
FOR UPDATE;
-- a concurrent transaction attempting the same SELECT FOR UPDATE
-- blocks right here until this one COMMITs or ROLLBACKs
UPDATE stock SET quantity = quantity - 1
WHERE product_id = 42 AND quantity > 0;
COMMIT;
Notice UPDATE ... SET quantity = quantity - 1 instead of recomputing the value in application code: letting PostgreSQL do the arithmetic in the same statement removes the need to re-read after taking the lock. The WHERE quantity > 0 guard is what turns a silent bug into zero affected rows, which the application can detect and handle.
For "pull an available task" queues (several workers pulling from a jobs table), FOR UPDATE SKIP LOCKED keeps workers from blocking on each other: each worker skips rows already locked by another and grabs the next free one, giving a lock-free-feeling task distribution pattern without any shared blocking lock.
The middle ground: REPEATABLE READ
Between Read Committed and Serializable, PostgreSQL offers Repeatable Read, which guarantees a transaction sees a snapshot frozen at the time of its first SELECT, rather than a snapshot refreshed on every statement. That eliminates the non-repeatable read (reading the same row twice within a transaction and getting two different values because another transaction committed in between), but it still doesn't protect against the lost update from the stock example: both concurrent transactions each see quantity = 1 on their own frozen snapshot, and the second UPDATE to commit fails the same silent way it does under Read Committed, unless PostgreSQL detects an explicit write conflict on the row, which it only does when the row was already modified by a concurrent transaction that has already committed (in that specific case, Repeatable Read does raise a serialization error, unlike Read Committed). It's a genuine improvement for read consistency within a single transaction, but it isn't a substitute for SERIALIZABLE when the goal is cross-transaction integrity.
Coordinating without locking a row: advisory locks
When contention is on a logical resource that doesn't necessarily map to a single database row (say, "only one CSV import at a time per customer account," where the lock needs to key on a business identifier rather than a specific row), PostgreSQL offers advisory locks. They work by application convention: PostgreSQL doesn't attach them to any row or table, so it's up to the application to consistently acquire one before any concurrent operation on the targeted logical resource.
-- acquires an advisory lock keyed on the account id (hashed to a bigint)
SELECT pg_advisory_lock(hashtext('import:account:42'));
-- CSV import processing happens here
SELECT pg_advisory_unlock(hashtext('import:account:42'));
pg_try_advisory_lock returns false immediately instead of waiting if the lock is already held, which lets you implement "only one job runs at a time, everything else is rejected right away" without an implicit queue; pg_advisory_lock blocks until it becomes available, for a sequential queueing behavior instead.
The alternative: SERIALIZABLE with retry
Explicit locking requires knowing in advance which rows to protect, which gets hard once business logic touches several tables through different code paths. SERIALIZABLE isolation flips the problem around: PostgreSQL detects after the fact whether the concurrent execution could have produced a result impossible under strictly sequential execution, and if so, aborts one of the transactions with a serialization error (SQLSTATE 40001).
BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT quantity FROM stock WHERE product_id = 42;
-- application logic
UPDATE stock SET quantity = quantity - 1 WHERE product_id = 42;
COMMIT;
-- if COMMIT fails with SQLSTATE 40001 ("could not serialize access"),
-- the application must retry the whole transaction from the start
// application code, Node.js with the pg driver
async function decrementStockWithRetry(client, productId, maxAttempts = 3) {
for (let attempt = 1; attempt <= maxAttempts; attempt++) {
try {
await client.query("BEGIN ISOLATION LEVEL SERIALIZABLE");
await client.query(
"UPDATE stock SET quantity = quantity - 1 WHERE product_id = $1 AND quantity > 0",
[productId]
);
await client.query("COMMIT");
return;
} catch (err) {
await client.query("ROLLBACK");
if (err.code === "40001" && attempt < maxAttempts) continue;
throw err;
}
}
}
SERIALIZABLE also protects against write skew, a subtler variant of the lost update where two transactions read different rows linked by an implicit business constraint (the classic example: two on-call doctors each sign off at the same time because each one still sees the other listed as on-call, leaving zero doctors on call when the rule requires at least one).
The tradeoff you're accepting
FOR UPDATE costs throughput: concurrent transactions literally queue up behind each other on the locked row. SERIALIZABLE costs less waiting but requires the whole application layer to know how to retry an entire transaction on a serialization failure, which isn't free the moment there are non-idempotent side effects inside that transaction (a call to an external payment provider, sending an email). Mixing SERIALIZABLE with a non-replayable external call in the same transaction is a common mistake: if the retry replays the external call too, you get double billing instead of protection.
When it's not enough
Under heavy contention on a single hot row (a stock counter for a best-seller during a flash sale), neither FOR UPDATE nor SERIALIZABLE scales well: one serializes access outright, the other multiplies retries under high contention. At that point the fix isn't transactional anymore, it's architectural: reserve stock asynchronously through a queue, or move the hot counter to a dedicated structure (an atomic Redis counter periodically reconciled back into PostgreSQL) to take the hot row out of the critical transactional path entirely.
The isolation level is never a configuration detail to leave on its default without thinking about it. It's a design decision that depends directly on how critical the protected data is and how much contention to expect, one to make before writing the query, not after the inconsistency has already shown up in production.
