3.4Transaction Concepts (Isolation Levels and Locking)

In scenarios like a bank transfer between accounts, integrity breaks unless multiple SQL statements either all succeed or all fail together. A transaction guarantees this "all or nothing" property, and it must be understood alongside isolation levels, which control how concurrently running transactions affect each other, and locking, the mechanism that preserves data consistency.

3.4.1BEGIN/COMMIT/ROLLBACK and isolation levels

• BEGIN starts a transaction (START TRANSACTION is equivalent). COMMIT finalizes the changes so far, making them permanent. ROLLBACK discards all changes made so far. In PostgreSQL, even a single statement run without an explicit BEGIN is treated as an implicit one-statement transaction.
• READ COMMITTED is PostgreSQL's default isolation level. Only committed data from other transactions is visible (uncommitted changes are not); however, each query within the same transaction sees the latest committed data at that moment, so running the same query twice can yield different results (a non-repeatable read).
• REPEATABLE READ maintains a snapshot from the start of the transaction, so running the same query repeatedly within that transaction always returns the same result (unaffected by other transactions' commits). SERIALIZABLE is the strictest isolation level, guaranteeing consistency as if multiple transactions ran one at a time in some serial order (in practice it may abort a transaction with a serialization-failure error when needed to preserve that guarantee).
• The three read anomalies that isolation levels address: a dirty read (reading another transaction's uncommitted change), a non-repeatable read (a re-read of the same row yields a different value), and a phantom read (the set/count of rows matching a condition changes). Stricter isolation levels prevent more of them. Note a PostgreSQL trait: because REPEATABLE READ uses snapshot isolation, it also prevents phantom reads, which the SQL standard permits at that level (so it blocks both non-repeatable and phantom reads).
• In PostgreSQL, specifying READ UNCOMMITTED internally behaves identically to READ COMMITTED (unlike some other DBMSs, it does not actually expose uncommitted data). This PostgreSQL-specific behavior is a favorite exam trap.

3.4.2The LOCK statement and deadlocks

• The LOCK statement explicitly requests a lock mode on an entire table (e.g., LOCK TABLE t IN ACCESS EXCLUSIVE MODE). Ordinary SQL statements (UPDATE/DELETE, etc.) automatically acquire the locks they need, so explicit LOCK is reserved for special cases.
• A row lock is acquired on a per-row basis for the rows being updated (UPDATE/DELETE take these automatically; other rows are unaffected, so concurrency stays high). A table lock applies to the entire table (needed for DDL operations, etc.; coarser granularity means lower concurrency).
• A deadlock is a state where multiple transactions each wait forever for a lock held by the other, unable to proceed (e.g., TxA holds lock X and waits for Y, while TxB holds Y and waits for X). PostgreSQL automatically detects deadlocks and forcibly aborts one of the transactions with an error, rolling it back to break the cycle.

Consider an inventory system's "check stock, then confirm the order" flow to see how isolation levels affect actual behavior. Under the default READ COMMITTED, if transaction A runs SELECT stock FROM products WHERE id=1 and confirms 10 units in stock, and then transaction B commits an update dropping stock to 5, re-running the same SELECT inside transaction A can now see the new value of 5 (this is a non-repeatable read). If you need a consistent value throughout the entire flow from stock check to order confirmation, switch to REPEATABLE READ: with BEGIN ISOLATION LEVEL REPEATABLE READ; SELECT stock FROM products WHERE id=1;, the snapshot is fixed at transaction start, so no matter what other transactions commit afterward, the same transaction keeps seeing the original value of 10. For even stricter consistency—treating concurrent transactions as if they ran in some strict serial order—use SERIALIZABLE, as in accounting workflows; the cost is that a serialization failure aborts the transaction with an error, so the application must implement retry logic. From a concurrency-control perspective, UPDATE products SET stock = stock - 1 WHERE id = 1 acquires a row lock only on the affected row, so it can proceed concurrently with an update to id=2; but a DDL statement like ALTER TABLE products ADD COLUMN ... that changes table structure requires a table lock, blocking other transactions' reads and writes meanwhile. If two transactions fall into a deadlock—"TxA holds a lock on id=1 and waits for id=2, while TxB holds a lock on id=2 and waits for id=1"—PostgreSQL detects this and forcibly aborts one of them with an error, preventing the whole system from freezing.

3.4.3Section summary

• BEGIN -> COMMIT (finalize) / ROLLBACK (discard). Default is READ COMMITTED; REPEATABLE READ fixes a snapshot; SERIALIZABLE is strictest
• Note the PostgreSQL-specific quirk: READ UNCOMMITTED behaves identically to READ COMMITTED
• Row lock = affected rows only (high concurrency); table lock = the whole table (low concurrency). PostgreSQL auto-detects deadlocks and rolls back one side