Transactions & Programmability
Transactions and ACID
A transaction is a sequence of SQL statements treated as a single unit of work.
Overview
A transaction is a sequence of SQL statements treated as a single unit of work. ACID is the set of properties that a transaction manager aims to guarantee: Atomicity (all statements in the transaction either commit or none take effect), Consistency (the transaction moves the database from one valid state to another, respecting declared constraints), Isolation (concurrent transactions do not see each other's uncommitted intermediate states according to the chosen isolation level), and Durability (once committed, changes survive subsequent failures). The term was popularized by Härder and Reuter in 1983, building on earlier work by Gray and others. SQL-92 standardized transaction control statements such as COMMIT, ROLLBACK, and SET TRANSACTION ISOLATION LEVEL, though the exact isolation semantics and default levels vary by product. Transactions are central to correctness in multi-user systems: they let application code group related reads and writes so that partial failures, concurrent updates, and crashes do not leave the database in an inconsistent state. They are also the foundation for higher-level programmability features such as stored procedures, triggers, and savepoints, which execute within or alongside transaction boundaries.
Syntax and usage
Standard SQL provides transaction control statements. A typical explicit transaction begins with START TRANSACTION (or BEGIN), followed by data statements, then COMMIT or ROLLBACK. SET TRANSACTION ISOLATION LEVEL specifies the level for the next transaction. Savepoints allow partial rollback: SAVEPOINT sp1, then ROLLBACK TO SAVEPOINT sp1, then optionally RELEASE SAVEPOINT sp1. Many engines also support BEGIN TRANSACTION, and some default to autocommit mode, where each statement is its own transaction unless an explicit block is opened. Isolation levels defined in SQL-92 are READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE. Vendors differ: PostgreSQL and Oracle default to READ COMMITTED; MySQL InnoDB defaults to REPEATABLE READ; SQL Server defaults to READ COMMITTED. Some products also expose snapshot isolation, which is not part of SQL-92 but is widely implemented. Transaction control is generally not allowed inside a stored procedure in a way that commits the caller's transaction in some engines, so portability requires care.
How it works
A transaction manager coordinates three main mechanisms: logging, locking or versioning, and commit protocols. Write-ahead logging records changes before they are applied to data pages, so that after a crash the system can redo committed work and undo uncommitted work. Concurrency control uses either locking (two-phase locking) or multiversion techniques (MVCC) to enforce the chosen isolation level. Under two-phase locking, locks are acquired as data is read or written and released at commit or rollback. MVCC keeps multiple versions of rows, letting readers see a consistent snapshot without blocking writers. Atomicity is implemented by keeping undo information until commit; if the transaction aborts, the undo log restores prior values. Durability is provided by flushing the commit record to stable storage, often with group commit to amortize I/O. Distributed transactions add two-phase commit, where a coordinator asks participants to prepare, then commits or aborts. Isolation anomalies such as dirty reads, non-repeatable reads, and phantom reads are defined relative to these levels, and real implementations may permit or prevent additional anomalies.
Examples
A simple transfer between two accounts should be atomic. In standard SQL: START TRANSACTION; UPDATE accounts SET balance = balance - 100 WHERE id = 1; UPDATE accounts SET balance = balance + 100 WHERE id = 2; COMMIT;. If the second update fails, ROLLBACK restores the first. To control isolation: SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; START TRANSACTION; SELECT SUM(balance) FROM accounts; COMMIT;. Savepoints allow partial recovery: START TRANSACTION; INSERT INTO orders (id, total) VALUES (10, 50); SAVEPOINT after_order; INSERT INTO order_lines (order_id, sku) VALUES (10, 'A'); ROLLBACK TO SAVEPOINT after_order; COMMIT; Here the order row is kept while the line insert is undone. In procedural code, a stored procedure may run inside a transaction opened by the caller; for example, BEGIN; CALL transfer(1, 2, 100); COMMIT; where the procedure performs the two updates. These examples use standard statements; syntax for BEGIN versus START TRANSACTION and savepoint release varies by product.
Common pitfalls
A frequent mistake is leaving a transaction open while waiting for user input or a network call, which holds locks and can block other sessions. Another is assuming that READ COMMITTED prevents all anomalies; it does not prevent non-repeatable reads or phantoms. Mixing autocommit and explicit transactions can cause statements to commit earlier than intended. In some engines, DDL statements implicitly commit the current transaction, so schema changes cannot be rolled back with surrounding DML. Error handling matters: if an application catches an exception and continues without rollback, the transaction may remain open or be left in an aborted state. Long-running transactions increase undo retention and can cause bloat in MVCC systems. Nested BEGIN calls are often not truly nested; many products treat them as a warning or as a no-op, and only the outermost commit applies. Finally, relying on default isolation levels for portability can produce different behavior across vendors, so explicit levels are safer when correctness depends on them.
Best practices
Keep transactions short and focused on a single logical unit of work. Acquire locks in a consistent order to reduce deadlocks, and be prepared to retry transactions that fail with a serialization or deadlock error. Use the lowest isolation level that satisfies the application's correctness requirements, and document why a higher level is needed. Prefer explicit COMMIT or ROLLBACK in application code, and ensure rollback happens on every error path, typically with a try/catch/finally pattern. Avoid user interaction inside a transaction. Use savepoints for partial rollback when a procedure performs several independent steps. For batch work, consider committing in bounded chunks to limit undo growth. When using stored procedures, agree on whether the procedure or the caller owns the transaction boundary; many teams let the caller control it. Test concurrency behavior under realistic load, and verify that the chosen isolation level prevents the anomalies the application actually cares about.
Frequently asked questions
What is the difference between COMMIT and ROLLBACK? COMMIT makes the transaction's changes permanent and visible to other transactions; ROLLBACK undoes all changes made since the transaction began or since the last savepoint, returning the database to its prior state. Does every SQL statement need an explicit transaction? No. In autocommit mode, each statement is committed automatically. Explicit transactions are needed when multiple statements must succeed or fail together. Can a transaction span multiple databases or servers? Standard SQL does not define cross-server transactions, but many products support distributed transactions using two-phase commit, often through an external transaction manager. Such setups add coordination overhead and failure modes, so they are used only when required.