Transactions and Concurrency
Preventing race conditions
In the previous chapter, you learned that under READ COMMITTED, multiple requests can read the same committed state and then make decisions based on what they read.
Consider this pattern:
12345read the current value ↓make a decision in application code ↓update the rowIf other requests can change the same data concurrently, this can create a race condition.
In this chapter, you'll learn two important ways to avoid that problem:
lock the row while the application makes its decision;
move the condition into the
UPDATEitself.
Before we do that, there's one important PostgreSQL behavior to understand.
What happens when two transactions update the same row?
The following example is conceptual. You don't need to type it into the SQL editor.
Imagine a value is currently:
1quantity = 199Transaction A runs:
123456UPDATE quantity = quantity - 1result:198transaction still openBefore Transaction A commits, Transaction B tries to perform the same update.
PostgreSQL doesn't let Transaction B blindly modify the same row at the same time.
Conceptually:
123456789101112131415Transaction A Transaction BUPDATE quantity199 → 198 UPDATE same row → waitsCOMMIT continues 198 → 197 COMMITThe second UPDATE waits for the first transaction to finish.
After the first transaction commits, the waiting UPDATE proceeds using the latest row value.
The new material demonstrates this exact sequence: both transactions decrement the same value, the second writer waits, and the final result contains both decrements rather than one overwriting the other.
This gives us an important distinction:
Concurrent writes aren't automatically race conditions.
The dangerous situation often appears when we separate the read, decision, and write into different operations.
Locking a row with FOR UPDATE
Imagine an order can only be shipped while its status is pending.
A Node.js backend might first read:
123SELECT id, statusFROM ordersWHERE payment_reference = 'txn_race_001';Then the application checks:
1234Is status equal to pending?If yes: ship the orderThe problem is that another request could change the order after the SELECT but before our request performs its UPDATE.
We need a way to say:
I'm going to make a decision based on this row. Don't let another transaction change it until I'm finished.
PostgreSQL provides SELECT ... FOR UPDATE for this.
Using SELECT ... FOR UPDATE
First, create another pending order.
Type the following statement into the SQL editor and run it:
12345678910111213141516INSERT INTO orders ( customer_id, status, total_amount, payment_reference, placed_at)SELECT id, 'pending', 49.99, 'txn_lock_001', CURRENT_TIMESTAMPFROM customersORDER BY idLIMIT 1;Start a transaction:
1BEGIN;Now run:
1234SELECT id, statusFROM ordersWHERE payment_reference = 'txn_lock_001'FOR UPDATE;You should see the order with a status of pending.
The important part is:
1FOR UPDATEThis locks the selected row for updating until the transaction finishes.
What does the lock do?
Your practice workspace has one PostgreSQL session, so this example is conceptual.
12345678910111213141516Transaction A Transaction BBEGINSELECT ... FOR UPDATE→ row locked UPDATE same row → waitsUPDATE rowCOMMIT→ lock released continuesThis lets Transaction A read the row, make a decision in application code, and update it without another transaction changing the row in between.
Updating the locked row
Our transaction is still open.
Type the following statement into the SQL editor and run it:
1234UPDATE ordersSET status = 'shipped', shipped_at = CURRENT_TIMESTAMPWHERE payment_reference = 'txn_lock_001';Now commit:
1COMMIT;Check the result:
123SELECT id, status, shipped_atFROM ordersWHERE payment_reference = 'txn_lock_001';The order should now be shipped.
The sequence was:
123456789BEGIN ↓SELECT ... FOR UPDATE ↓application checks value ↓UPDATE ↓COMMITCan we avoid the separate SELECT?
Sometimes the application doesn't actually need to retrieve a value first and make the decision in Node.js.
Consider this rule:
Ship the order only if its current status is `pending`.
Instead of:
12345SELECT status ↓check in Node.js ↓UPDATEwe can express the rule directly in the UPDATE.
Create another pending order:
12345678910111213141516INSERT INTO orders ( customer_id, status, total_amount, payment_reference, placed_at)SELECT id, 'pending', 49.99, 'txn_atomic_001', CURRENT_TIMESTAMPFROM customersORDER BY idLIMIT 1;Now run:
123456UPDATE ordersSET status = 'shipped', shipped_at = CURRENT_TIMESTAMPWHERE payment_reference = 'txn_atomic_001' AND status = 'pending'RETURNING id, status, shipped_at;Notice:
1AND status = 'pending'PostgreSQL updates the order only if that condition is satisfied when the UPDATE operates on the row.
RETURNING gives us the row that was actually updated.
You should see the order returned with:
1status = shippedWhat if another request already changed the order?
Now run:
12345UPDATE ordersSET status = 'cancelled'WHERE payment_reference = 'txn_atomic_001' AND status = 'pending'RETURNING id, status;The statement should return no rows.
Why?
The order is no longer pending.
The previous statement changed it to:
1shippedso this condition is false:
1status = 'pending'and PostgreSQL doesn't update the row.
Moving the check into the write
This is the important comparison.
One approach separates the operations:
12345SELECT ↓application checks value ↓UPDATEIf the application genuinely needs to make a decision between the read and the write, SELECT ... FOR UPDATE can protect the row while it does so.
But if the rule can be expressed directly in SQL:
12345UPDATE ordersSET status = 'shipped'WHERE id = ... AND status = 'pending'RETURNING ...;the condition and change are handled together.
For simple rules like this, this is often preferable.
There is no separate read-check-write gap. And when another transaction is already updating the same row, PostgreSQL coordinates the competing writes rather than allowing them to blindly overwrite each other's unfinished work. The second writer can wait and then continue against the latest row state.
What you need to remember
When application code does:
12345read ↓check ↓writeask whether another request could change the data between those steps.
If your application genuinely needs to read a row and make a decision before changing it, consider:
12SELECT ...FOR UPDATE;If the rule can be expressed directly in the write, consider:
1234UPDATE ...WHERE ... AND conditionRETURNING ...;In the next chapter, you'll learn about stronger isolation levels that provide different guarantees for entire transactions.