ACID and Transactions
What a database transaction guarantees, what A, C, I and D each mean, and how a failed transfer rolls back cleanly. Includes a runnable example and how write-ahead logging gives durability.
ACID is the set of guarantees a database gives a transaction: Atomicity (all or nothing), Consistency (declared rules hold), Isolation (concurrent transactions do not see each other half done) and Durability (committed data survives a crash). Interviewers expect the mechanism: a write-ahead log is flushed to disk before commit and replayed after a crash to redo or undo work.
A transaction is a group of operations that the database treats as one unit. ACID stands for Atomicity (all or nothing), Consistency (rules stay true), Isolation (concurrent transactions do not see each other half done) and Durability (committed data survives a crash).
- A database transaction is atomic: if any step fails, a rollback undoes everything it did so far, so a failed transfer leaves both balances unchanged.
- In the SQLite example, transferring 500 from an account holding 70 fails the CHECK constraint bal >= 0 and rolls back, leaving the balances at 70 and 80.
- Durability is mostly achieved with a write-ahead log: the change is appended to the log and forced to disk with fsync before the database reports the commit.
- After a crash, the database replays the write-ahead log, redoing committed changes that had not reached the data pages and undoing changes from transactions that never committed.
- ACID consistency means declared integrity rules hold, while CAP consistency means all nodes return the same latest value, so the two terms describe different things.
01Why transactions exist
Moving 30 from account A to account B is two writes: subtract from A, add to B. If the server crashes between them, 30 has vanished. If two transfers run at once and interleave badly, balances can end up wrong.
A transaction wraps the writes so the database promises certain things about them. Those promises are the ACID properties. The word was coined in the early 1980s, and every serious relational database follows it.
02A: Atomicity
If any step fails, the database undoes everything the transaction did so far. This is a rollback. Here is a transfer on SQLite with a rule that balances may not go negative:
import sqlite3
c = sqlite3.connect(':memory:')
c.isolation_level = None # we issue BEGIN/COMMIT ourselves
c.execute("create table acct(id integer primary key, owner text, bal integer check(bal >= 0))")
c.execute("insert into acct values (1,'A',100), (2,'B',50)")
def transfer(src, dst, amt):
c.execute('begin')
try:
c.execute('update acct set bal = bal - ? where id = ?', (amt, src))
c.execute('update acct set bal = bal + ? where id = ?', (amt, dst))
c.execute('commit')
return 'committed'
except Exception as e:
c.execute('rollback')
return 'rolled back: ' + str(e)
print(transfer(1, 2, 30))
print(c.execute('select id, bal from acct').fetchall())
print(transfer(1, 2, 500))
print(c.execute('select id, bal from acct').fetchall())The first call prints committed and balances [(1, 70), (2, 80)]. The second tries to take 500 from an account holding 70, the check fails, and it prints rolled back: CHECK constraint failed: bal >= 0. The balances are still [(1, 70), (2, 80)], nothing was half applied.
03C: Consistency
Valid means all declared rules hold: primary keys are unique, foreign keys point to real rows, check constraints pass, and so on. In the example, bal >= 0 stopped the second transfer.
Consistency is the odd one out of the four. The database enforces the rules you declare, but it cannot know the rules you never wrote down. "Total money in the bank stays constant" is a business rule. Your transaction code has to preserve it, here by always making the debit and credit equal. So the C is partly the database's job and partly yours.
04I: Isolation
If two transactions touch the same rows at the same time, isolation decides what each can see of the other's unfinished work. Full isolation (serializable) makes the result identical to some one-at-a-time order, but costs throughput. Weaker levels let more things interleave and allow certain anomalies. The next topic in this path, Isolation Levels and Anomalies, covers them in detail.
Most databases use locks, or multi-version concurrency control (MVCC), where readers see a snapshot and do not block writers.
05D: Durability
Durability is mostly achieved with a write-ahead log (WAL). Before changing the data pages, the database appends a description of the change to a log file and forces it to disk (an fsync). Only then does it report the commit. Data pages themselves can be written to disk later, lazily.
If the machine dies, on restart the database replays the log. Committed changes that had not reached the data pages are redone. Changes from transactions that never committed are undone. This is why a commit costs a disk flush, and why disabling fsync makes a database much faster and much less safe.
Replicas add another layer. A commit is durable on that one machine, but if you want it to survive losing the machine you need synchronous replication.
06Using transactions well
Most databases run in autocommit mode by default, where each statement is its own transaction. You only need BEGIN when several statements must succeed together.
Keep transactions short. A transaction that holds locks while waiting on a user or a network call blocks everyone who needs those rows. Do the slow work first, then open the transaction, write, and commit.
Also handle failure. Deadlocks and serialisation failures are normal under concurrency, and the correct response is to roll back and retry the whole transaction.
Not every database gives full ACID. Many NoSQL systems trade some of it for scale. Some, like MongoDB, now offer multi-document transactions, but the guarantees and the cost differ, so read the documentation for the system you use.
07Common interview questions
Q: Explain ACID with an example.
A: A bank transfer. Atomic: both updates or neither. Consistent: no negative balance, total unchanged. Isolated: another transfer does not see half of it. Durable: after commit a crash does not lose it.
Q: How does a database achieve atomicity and durability?
A: Through a write-ahead log. Changes are logged and flushed before commit, and the log is used to redo or undo work after a crash.
Q: Difference between COMMIT and ROLLBACK?
A: COMMIT makes the transaction's changes permanent and visible. ROLLBACK discards them.
Q: What is a savepoint?
A: A marker inside a transaction that lets you roll back to that point without abandoning the whole transaction.
Q: Is consistency in ACID the same as consistency in CAP?
A: No. ACID consistency is about satisfying integrity rules. CAP consistency is about all nodes returning the same latest value.