ACID transactions

An ACID transaction is a group of reads and writes that a database treats as one unit. Either every change lands or none does (atomicity), the data moves from one valid state to another (consistency), concurrent transactions do not trip over each other’s half-done work (isolation), and once you are told “committed” the change survives a crash (durability). Each letter is delivered by a different mechanism, and knowing which mechanism backs which letter is what lets you reason about failures.

Picture it first: a bank voucher

Before computers, a bank moved ₹500 from Asha’s account to Bilal’s with a paper voucher. The clerk wrote the debit and the credit on one slip; the manager checked it, stamped it, and filed it in the strongroom register, with a carbon copy sent to head office.

• One slip for both lines. If the clerk was called away halfway, the slip was torn up. Nobody posted half a voucher.
• The manager’s check. A slip that broke a rule (an account below zero) never got stamped. But a slip with the wrong account number was perfectly legal and perfectly wrong.
• Drafts stay in the drawer. Another clerk totalling the day’s balances saw only stamped vouchers.
• Stamped means filed in ink. A fire at the counter could not undo a filed voucher; the head-office copy covered a fire in the strongroom.

Map it back: the slip is a transaction, tearing it up is ROLLBACK, the stamp is COMMIT, the manager’s rules are your constraints, the drawer is isolation, the strongroom register is the write-ahead log on disk, and the head-office copy is a synchronous replica.

What a transaction is

A transaction is a sequence of statements with one beginning and two possible endings. COMMIT makes every change permanent and visible to others at the same moment. ROLLBACK (an abort) throws all of them away.

BEGIN;                 -- MySQL also accepts START TRANSACTION
UPDATE ...;
INSERT ...;
COMMIT;                -- or ROLLBACK to discard everything since BEGIN

Autocommit. Without BEGIN, each statement is its own transaction: PostgreSQL wraps it in an implicit BEGIN and COMMIT, and MySQL starts every connection with autocommit on. One UPDATE touching 10,000 rows is all-or-nothing, but two UPDATEs sent separately are two transactions, and a crash between them leaves the first applied. Two traps: drivers and ORMs differ in whether they open a transaction for you, and MySQL implicitly commits the open transaction before most DDL such as ALTER TABLE.

Savepoints. A savepoint is a bookmark inside a transaction. ROLLBACK TO SAVEPOINT discards the work after it, keeps the work before it, and leaves the transaction open:

-- PostgreSQL and MySQL alike
BEGIN;
-- ... debit, credit and ledger rows for the transfer ...
SAVEPOINT before_cashback;
INSERT INTO coupon_redemptions (coupon_code, user_id) VALUES ('FEST50', 'asha');
-- ERROR: duplicate key, coupon already redeemed
ROLLBACK TO SAVEPOINT before_cashback;   -- undo only the coupon attempt
COMMIT;                                  -- the transfer still commits

ORMs build “nested transactions” from savepoints.

When a statement fails halfway

In PostgreSQL every statement after the error gets ERROR: current transaction is aborted, commands ignored until end of transaction block, until the block ends; ROLLBACK TO SAVEPOINT is the only way to keep the transaction going.

A worked example: a UPI-style wallet transfer

Here is a minimal wallet schema. Amounts are stored in paise as integers, so ₹500 is 50,000.

-- PostgreSQL syntax; the MySQL version differs only in types
CREATE TABLE wallets (
  user_id        text   PRIMARY KEY,
  balance_paise  bigint NOT NULL CHECK (balance_paise >= 0)
);
CREATE TABLE transfers (
  transfer_id    text   PRIMARY KEY,           -- the client's idempotency key
  from_user      text   NOT NULL REFERENCES wallets,
  to_user        text   NOT NULL REFERENCES wallets,
  amount_paise   bigint NOT NULL CHECK (amount_paise > 0)
);
CREATE TABLE ledger_entries (
  transfer_id    text   NOT NULL REFERENCES transfers,
  user_id        text   NOT NULL REFERENCES wallets,
  amount_paise   bigint NOT NULL                -- negative = debit
);

Asha sends Bilal ₹500. The transfer is four writes in one transaction (PostgreSQL, default READ COMMITTED isolation):

BEGIN;
INSERT INTO transfers VALUES ('tr_8f2c', 'asha', 'bilal', 50000);
UPDATE wallets SET balance_paise = balance_paise - 50000 WHERE user_id = 'asha';
UPDATE wallets SET balance_paise = balance_paise + 50000 WHERE user_id = 'bilal';
INSERT INTO ledger_entries VALUES ('tr_8f2c', 'asha', -50000),
                                  ('tr_8f2c', 'bilal', 50000);
COMMIT;

Three details matter:

• The arithmetic happens in the database. balance_paise - 50000 is computed under the row lock. Reading the balance into the app and writing it back invites the lost update.
• The CHECK constraint guards the overdraft. If Asha has only ₹300, the debit fails and the whole transfer rolls back. No if in the app can race past it.
• Lock order. If Bilal pays Asha at the same instant, the two transactions lock the same rows in opposite orders and can deadlock. Update wallets in a fixed order (by user_id) and retry the victim; see optimistic vs pessimistic locking.

Now send the same four writes without BEGIN, so each autocommits, and crash the server between them:

• Crash A, after the transfer row: no money moved, and a retry hits the duplicate transfer_id, concludes the transfer is done, and never moves it.
• Crash B, after the debit: ₹500 left Asha and reached nobody.
• Crash C, after both balance updates: the money moved but no ledger rows explain it, so reconciliation fails for two users.

Inside one transaction, the only question is whether the commit record reached the disk before the crash. If not, none of the four writes happened; if so, all four did.

The four letters, and who provides each

The four properties are provided by different parts of the system, and one of them is mostly your job.

Theo Härder and Andreas Reuter coined the acronym in 1983, in a survey paper on database recovery, with a pun: support for transactions was, they wrote, “the ACID test” of a system’s quality. Three definitions describe what the system guarantees; the fourth, consistency, describes what a correct transaction must do.

Atomicity: all of it or none of it

Atomicity means an aborted transaction leaves no trace, whether it aborted because you sent ROLLBACK, a constraint failed, it lost a deadlock, or the power went out. Härder and Reuter added a second half: whatever happens, the user must know which of the two states they are in. (Atomicity is about the final outcome; what others see meanwhile is isolation.)

InnoDB: change in place, keep an undo record. Before changing a row in its page, InnoDB writes an undo log record saying how to reverse the change. ROLLBACK applies the transaction’s undo records backwards, and the same records serve old row versions to readers (MVCC). Undoing costs real work: MySQL’s docs warn that rolling back an interrupted transaction during crash recovery can take three or four times as long as it had been running. Kill a batch update after 10 minutes and its rollback may take 30 to 40.

PostgreSQL: never overwrite, just stop counting it. Every insert or update writes a new row version stamped with the transaction’s ID; old versions stay put. Whether a version is visible depends on whether its transaction committed, which PostgreSQL records in the commit-status log in pg_xact (called pg_clog before version 10, renamed because people deleted it thinking it held disposable logs). Status takes two bits per transaction, so the default 200 million transactions of history need 200,000,000 × 2 bits ÷ 8 = 50 MB. ROLLBACK just flips the status to aborted: the versions vanish from view instantly, however many there are, and VACUUM reclaims the space later. You gain instant rollback; you pay with dead versions until vacuum runs.

After a crash, both engines replay the write-ahead log to bring pages up to date, then handle transactions with no commit record: PostgreSQL treats them as aborted, and InnoDB rolls them back from undo records in a background thread while already accepting connections. Either way, the commit record in the log is the single switch. Present: the transaction happened. Absent: it did not.

Consistency: the odd one out

Härder and Reuter’s definition is that a committing transaction preserves consistency because a successful transaction, by definition, commits only legal results. That is a promise about your transaction program, which no database can check. The engine supplies A, I and D; consistency is what correct transactions get on top. As the paper notes, a committed incorrect transaction can only be undone by another one that reverses it. No letter in ACID un-commits a bug.

Split your invariants into two groups:

• Declared invariants the database enforces on every write: NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, PostgreSQL’s EXCLUDE. CHECK (balance_paise >= 0) stops an overdraft from the app, the admin panel or a 2 am script alike. Push every invariant you can into database constraints.
• Everything else lives in your code: “a transfer’s ledger entries sum to zero”, “at most three linked UPI handles per user”. Atomicity commits your check and write together, and isolation keeps other writes out between them, though at weak levels a correct check can still lose to a concurrent transaction (write skew; see serializable isolation).

Even vendors disagree on the word: MySQL’s “InnoDB and the ACID Model” page files consistency under crash protection (the doublewrite buffer and crash recovery), not application invariants.

Isolation: alone, to a degree

Isolation promises that concurrent transactions do not interfere. The strongest form, serializable, means the outcome equals some one-at-a-time order. Weaker levels trade guarantees for concurrency and admit anomalies:

• Dirty read: you see another transaction’s uncommitted change.
• Non-repeatable read: the same row read twice gives two values.
• Phantom: the same query run twice returns new rows.
• Lost update: two read-modify-write cycles overlap and one overwrites the other.
• Write skew: two transactions check a condition, write different rows, and together break it.

Engines build isolation from locks held until commit and MVCC snapshots that let readers avoid blocking writers. The defaults are not serializable: READ COMMITTED in PostgreSQL, REPEATABLE READ in InnoDB. What each level really does per database is in transaction isolation levels.

Durability: committed means it stays

Durability means that once the database says COMMIT succeeded, the change survives a crash of the process, the operating system or the power.

The write-ahead rule. Writing every changed page at each commit would be slow, because pages are scattered across the disk. Instead the engine appends a compact record of each change to a sequential log (PostgreSQL’s WAL, InnoDB’s redo log) and flushes the log with fsync before replying OK. Pages are written later; after a crash, recovery replays the log. Details are in write-ahead logging.

Group commit. The flush is the slow step, so engines share it. Suppose one flush takes 1 ms on your disk (pg_test_fsync measures yours). A session waiting for its own flush commits at most 1,000 ÷ 1 = 1,000 transactions per second. If 50 sessions commit together, one flush carries all 50 commit records, so throughput can approach 50 × 1,000 = 50,000 commits per second while each commit still waits about one flush.

The knobs that acknowledge early

Both databases can send the OK before the flush. You can then lose the last moments of acknowledged commits, but recovery still produces a consistent database.

PostgreSQL lets you choose per transaction: SET LOCAL synchronous_commit TO OFF speeds up one low-value transaction while others stay fully durable. In MySQL keep sync_binlog = 1; with 0, a power failure can drop committed transactions from the binary log and replicas silently diverge.

Surviving the loss of the machine

A flushed log survives a power cut, not a dead disk. For that, the commit record must reach a second machine before the OK: PostgreSQL synchronous standbys (synchronous_standby_names), or MySQL’s semisynchronous or Group Replication; see data replication. You pay a network round trip per commit: light in fibre covers about 200,000 km/s, so a standby 1,000 km away adds at least 2,000 km ÷ 200,000 km/s = 10 ms. You also pay availability: PostgreSQL warns that commits may never complete if a required synchronous standby crashes, so name more than one candidate.

Durability is also only as honest as the hardware. MySQL’s docs warn that some operating systems and disks report a flush as done while data still sits in a volatile cache; use storage with power-loss protection.

Going deeper: fsyncgate

Durability rests on one system call telling the truth. In March 2018, Craig Ringer reported on the PostgreSQL hackers list that PostgreSQL assumed a successful fsync() meant everything written since the last one was on disk. On Linux that was not guaranteed: after a background writeback error, the kernel could mark the failed pages clean and report the error only once, or not at all to a process that opened the file later. PostgreSQL treated a failed fsync as retryable, so the retry could succeed although the data never reached the disk. The episode became known as “fsyncgate”.

The fix was blunt: PostgreSQL now PANICs on fsync failure, crashing on purpose so recovery replays from the intact WAL. It landed in PostgreSQL 12 and was back-patched to 9.4. The lessons: durability is a contract with the whole stack (kernel, filesystem, cache, firmware), and a flush error is not retryable. It means “state unknown”, and only the log is safe to trust.

Going deeper: the crash after COMMIT

Your service sends COMMIT. The server flushes the commit record, then dies (or the network drops) before the OK reaches you. Your side sees a connection error; the database has moved Asha’s ₹500.

The client cannot tell “crashed before the flush” (rolled back) from “crashed after” (committed). Härder and Reuter’s rule that the user always knows the state cannot be met by the database alone over a network, so your design closes the gap:

1. Make the transaction idempotent. The transfers primary key on a client-generated transfer_id is an idempotency key written in the same transaction. A retry either succeeds (the first attempt never committed) or hits a duplicate key (it did), which you report as “already done”.
2. Ask after reconnecting. Look the transfer up by its key. PostgreSQL also offers pg_xact_status(): note pg_current_xact_id() before COMMIT, and after reconnecting it reports committed, aborted or in progress while the status is still retained.
3. Hold irreversible side effects, such as the “Payment successful” SMS, until the commit is confirmed.

What ACID does not promise

ACID covers one transaction inside one database. It does not cover:

Anything outside that database. If the payout debits your wallet database and then calls a bank partner’s API, no local transaction spans both: your COMMIT cannot undo their write, and their timeout cannot undo yours. Use two-phase commit or a saga, with a transactional outbox so the “credit the bank” intent commits together with the debit.

Correct business logic. ACID will faithfully commit a transfer to the wrong account. The remedies are constraints, reconciliation jobs and reversing entries, which is why ledgers never edit history.

Consistency in the CAP sense. A lagging replica can serve a stale balance while every transaction on the primary is perfectly ACID. That is a consistency-model question.

Survival of your only disk. “Durable” means survives a crash, not survives losing the storage. Without a synchronous replica or archived WAL, a dead disk takes every commit since the last backup.

Freedom from races. Below SERIALIZABLE, some anomalies are allowed. “It runs in a transaction” does not mean “it is safe”.

Atomic views downstream. Change streams, search indexes and caches usually see a transaction’s writes one at a time; DynamoDB warns that stream records from one transaction can arrive at different times.

ACID outside relational databases

MongoDB has always made a single-document write atomic, which covers many cases if related data is embedded in one document. Multi-document ACID transactions arrived in 4.0 for replica sets and 4.2 for sharded clusters, with caveats: no standalone servers, a default limit of 60 seconds per transaction, durability that depends on your write concern, and, across shards, outside reads at read concern "local" that may see one shard’s writes before another’s.

DynamoDB offers TransactWriteItems: up to 100 actions on distinct items, at most 4 MB, one account and Region, all or nothing. It is serializable against single-item operations but only read-committed against Query and Scan. Each item costs two underlying writes (prepare and commit), so a transfer writing three items of up to 1 KB costs 3 × 2 = 6 write capacity units instead of 3, even if a condition cancels it.

At the other end sits BASE (basically available, soft state, eventually consistent): stores that stay writeable through failures and converge later rather than enforcing multi-item invariants at write time. The line has blurred; see ACID vs BASE.

What transactions cost

• Locks and conflicts. Writers to one row queue until commit. A hot row, such as one seller’s stock counter in a Flipkart-style flash sale, serialises every order.
• Flush latency. Every durable commit waits for a log flush, plus a round trip per synchronous replica.
• Long transactions. They hold locks and pin old snapshots, so PostgreSQL’s VACUUM cannot clean up and InnoDB’s undo history grows; MongoDB aborts them after 60 seconds by default.

Using transactions well

• Keep them short: milliseconds, not seconds.
• No network calls inside. Call the payment gateway before (pass the result in) or after (via an outbox), never while holding row locks.
• No user think time inside. For a tatkal-style seat hold, commit a hold row with an expiry; never keep a transaction open while the user pays.
• Retry the whole transaction on deadlocks and serialization failures (SQLSTATE 40001, plus PostgreSQL’s 40P01), from BEGIN, with backoff and jitter and an idempotency key.
• Choose isolation deliberately. For check-then-write across rows, use SELECT … FOR UPDATE, a constraint, or SERIALIZABLE with retries.
• Chunk big jobs. Backfilling 10 million rows in batches of 10,000 gives 10,000,000 ÷ 10,000 = 1,000 short transactions.
• Check row counts. An UPDATE that matched zero rows did not fail; it did nothing.

Cheat sheet

How to say it in the interview

“A transaction commits or aborts as a whole. Atomicity: a crash or error leaves no partial changes; InnoDB applies undo logs, PostgreSQL marks the transaction aborted. Consistency is mostly mine: the database enforces declared constraints, like a non-negative balance, and my code owns the rest. Isolation hides concurrent work to the degree the level promises; defaults aren’t serializable, so check-then-write gets locks or a stronger level. Durability: the commit record is fsynced before the OK, and a synchronous replica covers a dead machine. The wallet transfer is one short transaction with an idempotency key; anything crossing a service boundary goes through an outbox and a saga.”

Which ACID property does the write-ahead log give you? Mainly durability: the commit record is flushed before the OK, so recovery can replay it. It also anchors atomicity, because the commit record is the single switch; recovery discards or undoes work without one. It gives neither consistency nor isolation.

Is the C in ACID the C in CAP? No. ACID’s C means a transaction keeps invariants true, such as no negative balances. CAP’s C is linearizability: a read on any replica sees the latest acknowledged write to one object. You can have either without the other.

How do you get atomicity across two services? Not with local transactions; each stops at its own database. Use two-phase commit, which needs every participant to support it and blocks if the coordinator dies, or a saga of local transactions with compensating actions, driven by an outbox and made retry-safe with idempotency keys.

What happens if the server crashes after COMMIT is sent but before the client hears OK? It committed if the commit record reached disk, and rolled back otherwise, but the client cannot tell which. So it must not blindly retry: it retries with the same idempotency key, which the unique constraint turns into a no-op if the first attempt won, or it looks the outcome up after reconnecting (PostgreSQL offers pg_xact_status()).

Why not turn off synchronous commit to go faster? For low-value writes such as view counts, per transaction, yes: a crash loses at most about 600 ms of commits, never integrity. For money, no: you would tell a user “paid” and then forget it.

Where to go next

• Transaction isolation levels: every anomaly, and what each database does at each level.
• Write-ahead logging: redo, undo, checkpoints and torn pages.
• MVCC: the row versions behind non-blocking reads.
• Database constraints: turning invariants into enforced rules.
• Lost update problem: the race a transaction alone does not stop.
• Two-phase commit vs sagas: atomicity across services.
• Transactional outbox: a database change and a message, committed together.
• Idempotency: safe retries when the first outcome is unknown.
• ACID vs BASE: giving up multi-item guarantees for availability.
• Design a digital wallet: all of this in a real money-moving system.

References and further reading

1. Principles of Transaction-Oriented Database Recovery (Härder and Reuter, ACM Computing Surveys, 1983)cs-people.bu.eduThe paper that named ACID; its definitions of the four properties, including consistency as a property of correct transaction programs.
2. PostgreSQL documentation: Transactions (tutorial)postgresql.orgBEGIN, COMMIT and ROLLBACK, the implicit transaction around every statement, savepoints, and the aborted transaction state.
3. PostgreSQL documentation: Write-Ahead Logging (WAL)postgresql.orgLog first, flush the log at commit, write data pages later; one fsync can commit many transactions.
4. PostgreSQL documentation: Asynchronous Commitpostgresql.orgsynchronous_commit = off: lose the last few commits, never corrupt; the risk window is three times wal_writer_delay.
5. MySQL 8.4 Reference Manual: InnoDB and the ACID Modeldev.mysql.comWhich InnoDB features map to each letter, and the hardware and OS factors that durability depends on.
6. MySQL 8.4 Reference Manual: InnoDB startup options (innodb_flush_log_at_trx_commit)dev.mysql.comExact semantics of settings 0, 1 and 2, the once-per-second flush, and pairing with sync_binlog = 1.
7. MySQL 8.4 Reference Manual: InnoDB Error Handlingdev.mysql.comWhich errors roll back only the statement and which roll back the whole transaction.
8. PostgreSQL wiki: Fsync Errorswiki.postgresql.orgThe 2018 “fsyncgate” findings and the fix: PostgreSQL now PANICs on fsync failure, back-patched to 9.4.
9. MongoDB manual: Transactionsmongodb.comSingle-document atomicity, multi-document ACID transactions on replica sets and sharded clusters, and their visibility caveats.
10. Amazon DynamoDB Developer Guide: Transactions, how it worksdocs.aws.amazon.comTransactWriteItems limits (100 actions, 4 MB), isolation levels, idempotency tokens and the double capacity cost.