Transactions

A transaction groups one or more statements into a single atomic unit: either all changes commit together or none survive (`ROLLBACK`). ACID properties let invariants hold across errors, retries, and concurrent sessions.

					BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
  INSERT INTO ledger (debit, credit, amount) VALUES (1, 2, 100);
COMMIT;
-- ROLLBACK on any failure
				

| Property | Meaning in practice | |----------|---------------------| | Atomicity | All-or-nothing commit | | Consistency | App + DB rules hold after commit | | Isolation | Concurrent txs see controlled views | | Durability | Committed data survives crash (WAL) |

Keep transactions short — long txs hold locks and block vacuum. Idempotent retries need stable keys or deduplication tables so a retried transfer does not double-charge.

On interviews: sketch a money transfer or inventory decrement and where `BEGIN`/`COMMIT` boundaries belong versus optimistic retry in the app.

Common pitfalls: autocommit per statement hiding partial updates; huge transactions during batch jobs; retry without idempotency; mixing external API calls inside an open transaction.

The trade-off is balancing strong atomic invariants against lock duration and throughput — scope the transaction to the smallest set of statements that must move together.

Checklist:

• Define the invariant the transaction protects.
• Keep scope and duration minimal.
• Handle `ROLLBACK` and error paths explicitly.
• Design retries to be idempotent.
• Separate DB work from external side effects.