DBMS deep dive
ACID Properties: deep dive
ACID is a promise about what happens when things go wrong: a crash, a constraint violation or two users touching the same row. Knowing which engine component keeps each promise is what separates a memorised answer from a strong interview answer.
Think in failures, not definitions
Each letter in ACID protects against a specific failure. Atomicity handles a transaction that dies halfway. Consistency handles a transaction that would break a rule such as a foreign key, a CHECK or a unique constraint. Isolation handles two transactions that run at the same time and see each other's half-finished work. Durability handles power loss right after the database said COMMIT succeeded. If you can name the failure, you can explain the mechanism.
Worked example: a crash in the middle of a transfer
Say ₹500 moves from account A to account B. The engine debits A, and the server loses power before it credits B. On restart, the database replays its log. It finds the debit's log records but no commit record for that transaction, so it treats the transaction as aborted and the debit never becomes visible. Now take the case where the commit record was flushed to disk just before the crash, but the changed data pages were still only in memory. Recovery replays the log (redo), and both the debit and the credit come back. The client was told the commit succeeded, so it has to survive.
The key point is that the log reaches disk before the data pages do. That rule is called write-ahead logging, and it is why a commit only has to wait for one fast sequential log write instead of many random page writes.
Which component keeps which promise
| Property | Failure it prevents | PostgreSQL | MySQL InnoDB |
|---|---|---|---|
| Atomicity | Partial transaction left behind | Aborted transaction IDs are marked in pg_xact, so their row versions are never visible | Undo log rolls back the changes |
| Consistency | Constraint or invariant broken | Constraints checked per statement, or at commit if DEFERRABLE | Constraints checked immediately, FKs row by row (no deferred constraints) |
| Isolation | Interference between concurrent transactions | MVCC snapshots plus SSI for SERIALIZABLE | MVCC through undo plus next-key locks |
| Durability | Committed data lost on crash | WAL fsync at commit (synchronous_commit) | Redo log flush (innodb_flush_log_at_trx_commit=1), doublewrite buffer |
Consistency is the odd one out. The database enforces only the rules you declare. If the invariant 'balance never goes negative' is not written as a CHECK constraint or guarded by a trigger, the database cannot keep it for you. That part of C belongs to the application.
Partial rollback with savepoints
Atomicity applies to the whole transaction, but savepoints let you undo part of one. This is useful when you try something optional inside a larger unit of work:
BEGIN;
UPDATE accounts SET balance = balance - 500 WHERE id = 'A';
UPDATE accounts SET balance = balance + 500 WHERE id = 'B';
SAVEPOINT before_reward;
INSERT INTO reward_points (account_id, points) VALUES ('A', 5);
-- Suppose this fails on a constraint: undo only the reward
ROLLBACK TO SAVEPOINT before_reward;
COMMIT; -- the transfer still commits atomically
-- Make the invariant enforceable (the C in ACID)
ALTER TABLE accounts ADD CONSTRAINT balance_non_negative CHECK (balance >= 0);In PostgreSQL, any error inside a transaction puts it in an aborted state, and every later statement fails until you ROLLBACK or roll back to a savepoint. Drivers that 'retry the statement' without a savepoint will keep failing. This trips people up in production.
Durability has knobs
Durability is configurable, and interviewers like to probe this. With PostgreSQL's synchronous_commit = off, or InnoDB's innodb_flush_log_at_trx_commit = 2, commits return before the log is fsynced. Throughput goes up, but a crash can lose the last fraction of a second of acknowledged commits. Group commit is the middle ground: many concurrent commits share one fsync, so you keep full durability at a much lower cost per commit.
Common mistakes
- Assuming ACID holds across services. Two databases, or a database plus a message queue, need two-phase commit, a saga or the transactional outbox pattern.
- Forgetting autocommit. Without an explicit
BEGIN, every statement is its own transaction, so a multi-step transfer is not atomic. - Treating isolation as all-or-nothing. Most engines default to READ COMMITTED or REPEATABLE READ, not SERIALIZABLE. See transaction isolation levels.
- Believing an fsync to disk is durable on hardware with volatile write caches and no power-loss protection.
- Holding transactions open across network calls, which keeps locks held and blocks cleanup of old row versions.
Interview questions
›What is write-ahead logging, and why does it make commits fast?
Every change is described in a sequential log, and the log is flushed before the matching data pages are written. A commit waits only for the log flush, which is sequential and can be batched with other commits. Data pages are written back lazily, and redo replay rebuilds them after a crash.
›How does PostgreSQL provide atomicity without an undo log?
An UPDATE writes a new row version and leaves the old one in place. If the transaction aborts, its ID is marked aborted in pg_xact. Visibility checks then ignore its new versions, and the old versions stay current. Vacuum removes the dead versions later.
›What is the difference between redo and undo?
Redo reapplies committed changes that had not reached the data files, which gives durability. Undo reverses changes from transactions that did not commit, which gives atomicity. InnoDB also uses undo records to rebuild older row versions for MVCC readers.
›Is a single UPDATE statement atomic without BEGIN?
Yes. In autocommit mode each statement is its own transaction, so one UPDATE touching a million rows either applies to all of them or to none. Atomicity across several statements needs an explicit transaction.
›How would you keep a transfer atomic across two microservices?
You cannot get a local ACID transaction across services. The usual options are a saga, where each step has a compensating action, the transactional outbox, where the event is written in the same local transaction and published later, or two-phase commit, which blocks if the coordinator fails.