Transaction

Picture a bank transfer that moves 500 from account A to account B. That is two writes: subtract 500 from A, add 500 to B. If the server crashes after the first write and before the second, the money has vanished. A transaction stops that from happening. You wrap both writes in BEGIN and COMMIT, and the database promises that either both writes land or neither does.

The guarantee is summarized by ACID: Atomicity (all or nothing), Consistency (the data follows every rule and constraint before and after), Isolation (concurrent transactions do not step on each other), and Durability (once committed, the change survives a crash). The acronym is from a 1983 paper by Theo Harder and Andreas Reuter, and it is still the contract every relational database advertises.

Without transactions you have to write fragile cleanup code by hand for every multi-step operation. With them, you describe the work as one unit and the database handles partial failure for you.

Atomicity and durability are usually backed by a write-ahead log (WAL). Before changing the actual data pages, the database appends a record of the intended change to an append-only log on disk. On COMMIT it flushes that log and tells you the work is safe. If the process dies mid-transaction, recovery on restart replays committed log records and discards uncommitted ones, so you never see a half-applied transaction. PostgreSQL, MySQL InnoDB, and SQLite all work this way.

Isolation is the harder part because many transactions run at once. Databases use locks, multi-version concurrency control (MVCC), or both. MVCC keeps multiple versions of each row so readers see a consistent snapshot without blocking writers. PostgreSQL and Oracle lean heavily on MVCC; that is why a long-running read in Postgres does not stall writers.

SQL defines four isolation levels: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE. Lower levels are faster but allow anomalies like dirty reads, non-repeatable reads, and phantom reads. SERIALIZABLE is the strictest and behaves as if every transaction ran one after another, at the cost of more aborts and retries. Most databases default to READ COMMITTED.

Use a transaction any time correctness depends on several writes happening together: transferring money, decrementing inventory while creating an order, or updating a record plus its audit row. If a partial result would corrupt your data, that work belongs in a transaction.

The cost is contention and latency. Holding locks or open snapshots while a transaction is alive blocks or slows other work, so keep transactions short and never wait on a network call or user input while one is open. Long transactions in MVCC systems also bloat storage because old row versions cannot be cleaned up until the transaction ends.

Transactions are easy on a single node and hard across many. Spanning multiple databases or services needs distributed protocols like two-phase commit, which is slow and can stall if a participant dies. That is why many distributed systems drop strict transactions in favor of eventual consistency or application-level patterns like the Saga, where each step has a compensating undo step instead of a global rollback.

Say a customer buys the last unit of a product. In one transaction the app inserts an order row, decrements the stock count from 1 to 0, and inserts a payment record. If the payment insert fails because the card is declined, the transaction rolls back: the order disappears and stock returns to 1, as if nothing happened. The next shopper still sees one unit available.

Now imagine two shoppers hit checkout at the same instant for that single unit. Isolation decides the outcome. Under SERIALIZABLE or with a SELECT FOR UPDATE row lock, one transaction wins and the other is rejected or retried, so you never oversell. Under a weaker level with no locking, both could read stock as 1 and both decrement it, leaving you at -1 and two angry customers. This is exactly why high-volume systems like Stripe and Shopify lean on transactional databases for the money and inventory paths.