Transactions and ACID
A transaction groups several operations into one logical unit of work. Databases guarantee that transactions behave reliably, even with crashes and many simultaneous users.
What a transaction is
A transaction is a sequence of reads and writes treated as a single unit, such as transferring money: subtract from one account, add to another. In SQL it starts with BEGIN (or implicitly) and ends with COMMIT, making changes permanent, or ROLLBACK, undoing them. Many systems treat each single statement as its own transaction by default, called autocommit.
Atomicity
Atomicity means all of a transaction's effects happen or none do. If a transfer debits one account and the system crashes before crediting the other, the database must undo the debit on recovery. Atomicity also lets applications abort deliberately when a business rule fails, such as insufficient funds.
Consistency
Consistency means a transaction takes the database from one valid state to another, respecting all constraints: keys, foreign keys, checks, and application rules such as 'total money in the system is unchanged by transfers'. The DBMS enforces declared constraints; the application is responsible for writing transactions that preserve its own rules.
Isolation
Isolation means concurrent transactions do not see each other's incomplete work, ideally behaving as if they ran one after another. Without it, two simultaneous withdrawals could both read a balance of 100 and both succeed, overdrawing the account. Full isolation is expensive, so databases offer weaker isolation levels with documented anomalies.
Durability
Durability means that once a transaction commits, its changes survive crashes, power failures, and restarts. Databases achieve this by writing a log record of the changes to stable storage before acknowledging the commit, so the changes can be redone if needed. Replicating to other machines protects against losing the storage itself.
The ACID acronym and its origins
The term ACID was coined by Andreas Reuter and Theo Harder in 1983, building on transaction concepts developed by Jim Gray, who received the 1998 Turing Award for his work on transaction processing. ACID transactions underpin banking, booking, inventory, and almost every system where correctness matters.
Savepoints and long transactions
SAVEPOINT name marks a point within a transaction; ROLLBACK TO name undoes work back to it without abandoning the whole transaction, useful for retrying one step. Long-running transactions hold locks or old versions for a long time, blocking others and bloating storage, so transactions should be kept short and should never wait on user input.
Worked example
BEGIN; UPDATE accounts SET balance = balance - 200 WHERE id = 1; UPDATE accounts SET balance = balance + 200 WHERE id = 2; COMMIT. If account 1 has a CHECK (balance >= 0) constraint and only 150, the first UPDATE fails, and the application issues ROLLBACK: neither account changes. If the server crashes after COMMIT returns, recovery replays the logged changes, so the transfer persists.
Key takeaways
A transaction is a unit of work ended by COMMIT or ROLLBACK; autocommit makes each statement its own transaction. Atomicity: all or nothing. Consistency: valid state to valid state, with the DBMS enforcing constraints and applications their rules. Isolation: concurrent transactions do not interfere, with weaker levels trading safety for speed. Durability: committed changes survive failures, using logs written before commit. ACID was named in 1983, building on Jim Gray's Turing Award work. Savepoints allow partial rollback; keep transactions short.
Flashcard
Group of operations as one unit
Check your understanding
Two simultaneous withdrawals both read a balance of 100 and both subtract 80, leaving the account overdrawn. Which ACID property was not upheld?