Why this chapter matters
Business workflows such as checkout or inventory updates require all-or-nothing correctness.
What you will learn
- Explain ACID guarantees in practical PostgreSQL terms.
- Use BEGIN, COMMIT, and ROLLBACK for safe multi-step operations.
- Recognize isolation concerns in concurrent transaction scenarios.
Understand the core ideas
Transactions in PostgreSQL group multiple statements into one unit of work so either all changes commit or none do. This is critical when business operations span tables, such as creating an order and decrementing inventory. Start a transaction with BEGIN, run dependent statements, and COMMIT only after all checks pass. If any step fails or a validation fails, issue ROLLBACK so the database returns to its prior consistent state. ACID concepts become practical when tied to real outcomes: atomic means no partial writes, consistent means constraints stay valid, isolated means concurrent work does not produce invalid interleavings, and durable means committed data survives crashes. Analysts and engineers both benefit from this because operational correctness directly affects reporting trust.
Isolation level choices matter when many sessions write related data at once. PostgreSQL defaults to READ COMMITTED, which is often fine, but sensitive workflows may need stronger guarantees or locking patterns. For stock decrement logic, read and update should happen in one transaction, often with FOR UPDATE when contention is possible, so two sessions do not oversell the same inventory. Keep transactions short to reduce lock duration and improve throughput. Error handling should classify retryable failures, such as serialization conflicts, versus hard business failures, such as insufficient stock. Always verify row grain after transactional writes by checking affected row counts. Transaction design is not only about preventing crashes, it is about preserving business invariants under normal concurrent load.
Key terms
- atomicity
- All statements in a transaction succeed together or all are undone.
- isolation level
- The concurrency rule set that governs what one transaction can observe of others.
- rollback
- Transaction command that undoes all uncommitted changes in the current unit of work.
- row lock
- A lock on selected rows that coordinates concurrent updates safely.
Order insert with inventory protection
Use inventory(sku, on_hand) and orders(order_id, sku, qty) with one row per inventory sku and one row per order line. Goal: write order and decrement stock atomically in PostgreSQL.
- Begin a transaction and lock the inventory row for the target sku using
SELECT on_hand FROM inventory WHERE sku = $1 FOR UPDATE. - Check available stock in application logic or a procedural block; if
on_hand < requested_qty, issueROLLBACKand return an insufficient stock error. - If stock is sufficient, insert the order row and update inventory with
on_hand = on_hand - requested_qtyin the same transaction. - Commit only after both statements succeed, ensuring order creation and stock decrement remain synchronized.
BEGIN; SELECT on_hand FROM inventory WHERE sku = 'SKU-100' FOR UPDATE; INSERT INTO orders (order_id, sku, qty) VALUES (9001, 'SKU-100', 2); UPDATE inventory SET on_hand = on_hand - 2 WHERE sku = 'SKU-100'; COMMIT; preserves one inventory row per sku and one order row per order event with all-or-nothing correctness.A common misconception
Claim: If one statement fails, PostgreSQL automatically commits successful earlier statements in that transaction.
Correction: Inside an explicit transaction, a failure should lead to rollback of the entire unit unless you intentionally use savepoints. Partial commit is not the default safe behavior.
Lessons in this chapter
- Transaction lifecycleControl units of work explicitly with BEGIN, COMMIT, and ROLLBACK. Read the full guide →
- Atomic workflow designGroup dependent SQL statements so partial updates cannot be committed.
- Isolation and concurrencyUnderstand how concurrent transactions can interact and why isolation levels matter.
- Failure handling patternsUse transactional guards and retries safely in application-facing SQL paths.
Study task
Chapter checkpoint
What should happen in PostgreSQL if one step in a transaction fails and you issue ROLLBACK?
All changes made in that transaction are undone, returning data to its prior consistent state.