SQL and data integrity
SQL transactions: prove the invariant with two sessions
By the BackendDrills editorial team · Published and checked October 6, 2026 · 7-minute read
Before you start: Basic SELECT, UPDATE and transaction boundaries. Find this reading in a study path →
Start with the property the database must preserve, then run two transactions that could violate it. A successful single-request test does not demonstrate concurrency safety. This lesson uses PostgreSQL semantics; verify the isolation behavior of your actual database.
One item, two reservations
An illustrative shop has one unit remaining. Two application workers each read “1”, approve a reservation and write “0”. The final counter looks plausible, yet two reservations were accepted. The business invariant is not merely that the counter stays nonnegative: accepted reservations must agree with available stock.
-- Illustrative single-row conditional update.
UPDATE inventory SET available = available - 1
WHERE sku = 'practice-kit' AND available > 0
RETURNING available;For a single-row decrement, make acceptance depend on the update returning a row. If a reservation record is also required, write it in the same transaction and specify how a retry is identified. A row lock can coordinate a read-then-write workflow, but its scope and duration matter. Keep remote calls out of a lock-holding section unless their cost and recovery contract are intentional.
What changes when the invariant spans rows?
Imagine a rota requiring at least one engineer on call. Two transactions each see the other engineer available, then independently mark themselves unavailable. Locking only the row each transaction updates does not by itself protect the joint rule. Choose a coordination boundary covering the actual invariant, or an isolation strategy with a complete retry policy. PostgreSQL serializable transactions can reject conflicting executions; callers must handle that outcome.
Build a controlled concurrency test
- Open two real database connections with the intended isolation level.
- Arrange a barrier after both reads, rather than hoping a sleep creates a race.
- Release both writers. Capture commit, rejection and retry results separately.
- Query authoritative rows from a fresh connection after both transactions finish.
- Assert accepted reservations, stock and operation identities together.
Add rollback and retry cases. If a retry reuses an earlier decision or emits an external effect twice, the database transaction alone has not solved the full workflow. Deadlock prevention also needs a consistent acquisition order when transactions touch several rows.
Keep a review note
Write the invariant, conflicting schedule, chosen boundary and a rejected alternative. Include the cost: waiting, rejected transactions or application complexity. Separately measure query count and returned row volume; an N+1 query issue is a fetch-shape problem and needs different evidence from a lost update.
Check the underlying behavior
Original illustrative examples, prepared with AI assistance and checked against the linked primary documentation. No customer incident or vendor endorsement is claimed. Our editorial approach.
Practice the next decision
Try a complete free backend drill: inspect evidence, make three decisions and review the reasoning. No account or card required.
Try a free incident drill →Selected practice from the study paths
- Two orders buy the last item. · Full edition drill
- The endpoint that runs 101 queries. · Full edition drill
Free readings need no account. Full edition drills require verified access; opening a paid link does not expose its answers.