Transactions and Concurrency
Preventing race conditions
In the previous chapter, you learned that a SELECT running under READ COMMITTED doesn't see uncommitted changes made by another transaction.
This means two transactions can read the same committed row before either of them changes it.
Suppose an order is currently pending. A shipping request and a cancellation request arrive at almost the same time:
1234567Shipping request Cancellation requestread status = pending read status = pendingdecide shipping is allowed decide cancellation is allowedupdate status to shipped update status to cancelledBoth requests make their decision using the same earlier state. The final result depends on which request updates the row last.
This is a race condition: the result depends on the timing and order of concurrent operations.
The dangerous pattern is:
12345read the current value ↓make a decision ↓update the rowIf another transaction can change the row between the read and the update, the decision may no longer be valid when the update runs.
There are two common ways to prevent this:
Lock the row with
SELECT ... FOR UPDATEwhile the application makes its decision.Move the condition into the
UPDATEstatement itself.
Let's examine both approaches.
Solution 1: Lock the row with SELECT ... FOR UPDATE
Use SELECT ... FOR UPDATE when the application needs to read a row, make a decision based on it, and then update it.
First, create a pending order for customer 5:
1234567891011121314INSERT INTO orders ( customer_id, status, total_amount, payment_reference, placed_at)VALUES ( 5, 'pending', 49.99, 'txn_lock_001', CURRENT_TIMESTAMP);You should see INSERT 0 1.
Lock the order
Start a transaction:
1BEGIN;Now select the order and lock it:
123456SELECT id, statusFROM ordersWHERE payment_reference = 'txn_lock_001'FOR UPDATE;You'll see the order with a status of pending.
The important part is:
1FOR UPDATEFOR UPDATE locks the selected row until the transaction ends.
Another transaction can still read a previously committed version with a regular SELECT, but it can't update, delete, or acquire a conflicting lock on this row until the current transaction finishes.
Make the decision and update the order
In an application, the backend could now inspect the returned status and decide whether shipping is allowed.
The order is pending, so update it:
1234UPDATE ordersSET status = 'shipped', shipped_at = CURRENT_TIMESTAMPWHERE payment_reference = 'txn_lock_001';You should see UPDATE 1.
Commit the transaction:
1COMMIT;The transaction has ended, so PostgreSQL releases the row lock.
Verify the result:
123456SELECT id, status, shipped_atFROM ordersWHERE payment_reference = 'txn_lock_001';The order should now have a status of shipped.
What happens to a concurrent request?
The playground has one database session, so follow this two-session sequence rather than running it in the SQL editor:
123456789101112131415161718Transaction A Transaction BBEGIN BEGINSELECT ... FOR UPDATE→ status = pending→ row locked SELECT ... FOR UPDATE → waitsUPDATE status = 'shipped'COMMIT→ lock released continues → status = shippedTransaction B waits until Transaction A finishes. It then receives the latest row version with a status of shipped.
The application can see that the order is no longer pending and reject the cancellation.
The read and the later update are protected because the row remains locked while the application makes its decision.
FOR UPDATE must be used inside a transaction
PostgreSQL holds the row lock until the transaction ends.
If you run SELECT ... FOR UPDATE without an explicit transaction, PostgreSQL executes it inside an implicit transaction and releases the lock as soon as the statement finishes. The lock would no longer protect a later UPDATE.
Use BEGIN and COMMIT so the lock remains in place across the read, decision, and update.
Solution 2: Move the condition into the UPDATE
Sometimes the application doesn't need to read the value before deciding what to do.
Consider this rule:
Ship the order only if its current status is `pending`.
PostgreSQL can enforce that rule directly inside the UPDATE statement.
First, create another pending order, this time for customer 6:
1234567891011121314INSERT INTO orders ( customer_id, status, total_amount, payment_reference, placed_at)VALUES ( 6, 'pending', 49.99, 'txn_atomic_001', CURRENT_TIMESTAMP);You should see INSERT 0 1.
Now run:
123456UPDATE ordersSET status = 'shipped', shipped_at = CURRENT_TIMESTAMPWHERE payment_reference = 'txn_atomic_001' AND status = 'pending'RETURNING id, status, shipped_at;The important part is:
1AND status = 'pending'PostgreSQL updates the order only if its status is still pending when the UPDATE operates on the row.
RETURNING gives you the row that was updated. You should see the order with a status of shipped.
What if the condition is no longer true?
Try to cancel the same order, but only if it is still pending:
12345UPDATE ordersSET status = 'cancelled'WHERE payment_reference = 'txn_atomic_001' AND status = 'pending'RETURNING id, status;The statement should return no rows because the order's current status is shipped.
The condition:
1status = 'pending'is false, so PostgreSQL doesn't update the order.
The application can use the result to determine what happened:
one returned row means the update succeeded;
no returned rows means no order satisfied the condition.
What happens when two conditional updates compete?
Suppose two transactions try to change the same pending order at almost the same time:
12345678910111213Transaction A Transaction BUPDATE status = 'shipped' UPDATE status = 'cancelled'WHERE status = 'pending' WHERE status = 'pending'→ row updated → waitsCOMMIT checks the updated row again → status is shipped → condition is false → no row updatedAn UPDATE automatically locks the row it changes. If another transaction is already updating the same row, the second UPDATE waits.
After the first transaction commits, PostgreSQL checks the second UPDATE's WHERE condition against the latest row version. Because the order is no longer pending, the second statement updates nothing.
The condition and the change happen as part of the same statement, so there is no separate read-decision-write gap.
Choosing between the two approaches
Both approaches prevent the application from changing a row based on an outdated decision, but they suit different situations.
| Approach | Use it when | Important consideration |
|---|---|---|
SELECT ... FOR UPDATE | The application must read the row and perform logic before deciding how to change it. | It requires a transaction and holds the lock while the application makes its decision. |
Conditional UPDATE | The rule can be expressed directly in the WHERE clause. | The application must check whether the statement returned a row or updated zero rows. |
For a simple rule such as "ship only if the order is pending," the conditional UPDATE is usually preferable. It expresses the rule in one statement and avoids an additional query.
Use SELECT ... FOR UPDATE when the decision genuinely requires application logic or information that can't be expressed cleanly in one SQL statement.
A conditional UPDATE may still belong inside an explicit transaction. If shipping an order also requires inserting another row or changing other data, use a transaction so all those changes succeed or fail together.
What you need to remember
The dangerous pattern is:
12345read ↓decide ↓writeIf another transaction can change the data between those operations, the decision may become outdated.
Use SELECT ... FOR UPDATE when the application needs to read the row and make a decision before changing it.
Use a conditional UPDATE when the rule can be expressed directly in the statement that changes the row.
In both cases, the application must handle the possibility that another transaction has already changed the data.
In the next chapter, you'll learn how stronger isolation levels provide different guarantees across an entire transaction.
Want to see this in application code?
Read-write races are also a useful full-stack engineering interview topic. I was recently asked to diagnose one during an interview and wrote about the experience, including both solutions covered in this chapter: