Starts the transaction block
Data / Knowledge library
Database Transaction
Groups operations into one commit or rollback boundary
Why learn Database Transaction?
It preserves business invariants across related reads and writes
The problem it solves
It prevents partial application of a logical data change
Where it came from
Database Transaction is a database concept that groups operations into one commit or rollback boundary. Its guarantees and tradeoffs depend on the concrete implementation and operating context.
Build your understanding
Work through these exercises in order. Try the task before opening a reference.
- Define Database Transaction and distinguish it from a neighboring concept.
- Choose a realistic task where Database Transaction is appropriate.
- Identify a failure mode caused by misusing Database Transaction.
- Interpret a small design or result involving Database Transaction.
- Explain one tradeoff or limitation of Database Transaction.
Documentation and sources
- PostgreSQL Documentation (www.postgresql.org)
Connect the ideas
Explore next
ACID PropertiesUnit of Work PatternTransaction Isolation LevelOnline Transaction Processing (OLTP)Database Schema MigrationSQL Injection
Real World Example
Atomic Inventory Reconciliation for Retail Logistics
Retail logistics providers managing multi-warehouse inventory updates.
The business problem
In a distributed inventory system, updating stock levels across multiple tables (e.g., Warehouse_Stock and Order_Log) often fails midway due to network interruptions or constraint violations. Without atomicity, the system enters an inconsistent state where an order is recorded but stock is not deducted, leading to overselling.
How this topic solves it
Database transactions group these related operations into a single boundary. By utilizing the BEGIN, COMMIT, and ROLLBACK commands, the system ensures that either every operation in the sequence succeeds or none of them are applied, maintaining data integrity.
- Initiate the transaction block using the BEGIN command to start the atomic boundary.
- Execute the first data manipulation statement to decrement stock from the source warehouse table.
- Execute the second data manipulation statement to insert a record into the order fulfillment log.
- Evaluate the success of both operations; if any error occurs, issue a ROLLBACK to revert all changes.
- Finalize the process with COMMIT to permanently save the changes to the database storage.
This diagram illustrates the lifecycle of a transaction from initiation to finalization or reversal.
Decrement inventory count
Insert record into log
Persist all changes
Discard all changes
- BEGIN → proceeds → Update Stock
- Update Stock → success → Log Order
- Log Order → success → COMMIT
- Update Stock → failure → ROLLBACK
- Log Order → failure → ROLLBACK
Implementation example
This code demonstrates the grouping of an inventory update and an order log insertion within a single transaction block.
BEGIN; -- Start atomic boundary
UPDATE warehouse_stock SET qty = qty - 1 WHERE id = 101;
INSERT INTO order_log (order_id, status) VALUES (5001, 'processed');
COMMIT; -- Persist changes if both succeedExpected business outcome
The system achieves a consistent state where inventory counts always match the recorded order history, eliminating partial updates.
Limitations and checks
Long-running transactions hold locks on rows for extended periods, which can lead to contention and block concurrent processes. This is checked by monitoring the pg_locks system view to identify sessions holding locks longer than expected.