Database transaction

A database transaction is a sequence of database operations that the database treats as a single, indivisible unit — all-or-nothing. Either every operation in the transaction succeeds and the changes are made permanent, or none of them appear to have happened at all. Transactions are how a database keeps complex multi-step operations consistent in the face of failures.

The canonical example is transferring money between accounts. Two operations have to happen together: debit account A by $100, credit account B by $100. If only one of them happens — the network drops, the database crashes, a constraint is violated — one account ends up wrong. Wrapping both inside a transaction guarantees that both changes or neither become visible.

The control commands (TCL) are:

• COMMIT — make the transaction’s changes permanent.
• ROLLBACK — revert them, returning the database to the state at the last commit.
• SAVEPOINT — mark a checkpoint inside the transaction that can be rolled back to without abandoning the whole thing.

Transactions are characterized by the ACID properties:

• Atomicity — the all-or-nothing guarantee above.
• Consistency — the database moves from one valid state to another; constraints aren’t violated in between.
• Isolation — concurrent transactions don’t see each other’s intermediate state. The database behaves as if transactions ran one at a time, even when they’re actually interleaved.
• Durability — once COMMIT returns, the changes survive crashes. The data has been written somewhere it won’t be lost.

Different databases offer different isolation levels — READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE — that trade off how strictly isolation is enforced against how much concurrency the system can support. Stricter isolation eliminates more anomalies but holds locks longer (or aborts more often, under multi-version concurrency control) and reduces throughput. The anomalies the SQL standard defines are:

• Dirty read — a transaction reads data another transaction has written but not committed. Prevented by READ COMMITTED and stricter.
• Non-repeatable read — a transaction reads the same row twice and gets different values because another transaction committed an update in between. Prevented by REPEATABLE READ and stricter.
• Phantom read — a transaction runs the same query twice and gets a different set of rows because another transaction committed an insert or delete in between. Prevented by SERIALIZABLE.

The mapping of level to anomalies is the SQL standard’s definition; actual engines differ. PostgreSQL’s REPEATABLE READ also prevents phantom reads (it uses snapshot isolation). MySQL/InnoDB’s REPEATABLE READ prevents phantoms for plain SELECT but not always for SELECT ... FOR UPDATE. The default isolation level is READ COMMITTED in PostgreSQL and Oracle, REPEATABLE READ in MySQL.

Transactions are essential whenever multiple changes have to be coordinated, but they aren’t free — the database has to maintain logs and locks to support rollback, which costs performance. For purely read-only queries, transactions add overhead without benefit.