ACID Transactions, in depth: what each letter promises, the mechanism behind it, the settings that quietly weaken it and how to test it

The worked example: moving money

Take a table of accounts with a balance column and a rule that no balance may go negative. A transfer debits one account and credits another. Three things can go wrong. A crash between debit and credit destroys money. Two concurrent transfers each read a balance of 100, each decide 80 is affordable, and together overdraw it. And a power loss just after the client is told success can erase a transfer the user already has a receipt for.

Atomicity handles the first, isolation the second and durability the third. Consistency is the rule itself: the balance check, plus the invariant that the total money in the system is constant. The test harness at the end checks all of it under load and a hard crash.

Atomicity: all or nothing, and what nothing means

Atomicity is implemented with a log. Every change is described in the write-ahead log before the changed data page may be written to disk, and a transaction is committed at exactly one instant: when its commit record is in the log. After a crash, recovery replays the log, so committed changes reach the data files even if their pages were never written, and changes from transactions without a commit record are undone or ignored. InnoDB rolls back uncommitted row changes from its undo log; PostgreSQL stamps every row version with its creating transaction, so versions from uncommitted transactions are simply invisible.

The subtle part is what happens on an error inside a transaction, because the two most common engines disagree. In PostgreSQL, any error puts the whole transaction into an aborted state; every later statement fails with current transaction is aborted, commands ignored until end of transaction block and the only way out is ROLLBACK, or rolling back to a SAVEPOINT taken earlier. In InnoDB, most statement errors, such as a duplicate key, roll back only that statement and leave the transaction open, so code that ignores the error and then commits will commit a partial transfer. Deadlocks and lock wait timeouts are different again: a deadlock rolls back the whole InnoDB transaction, while a lock wait timeout rolls back only the statement unless innodb_rollback_on_timeout is enabled.

The rule for both engines: on any exception, roll back the whole transaction and decide whether to retry it from the start. Never catch an error, carry on and commit.

Consistency: the database checks rules, you own invariants

The C in ACID is the odd letter out. The database cannot know that money must be conserved; it can only enforce the rules you declare. Those are NOT NULL, CHECK, UNIQUE, primary and foreign keys, and in PostgreSQL exclusion constraints, such as no two bookings for the same room with overlapping time ranges. A transaction that would violate a declared rule is rejected, and atomicity guarantees that its other changes go with it.

Some rules span rows and cannot be a constraint, such as the sum of all balances being constant. These invariants are preserved only if every transaction that touches the data is individually correct and transactions are isolated enough not to interleave badly. So consistency is a joint product: declared constraints from the database, correct transaction logic from you, and enough isolation to make the logic hold under concurrency.

First, put every rule you can into the schema; a CHECK constraint is enforced for every writer, including the ad hoc fix someone runs at 2am. Second, a foreign key declared DEFERRABLE INITIALLY DEFERRED is checked at commit, so a transaction may insert rows in any order.

Isolation: the letter with a dial

Full isolation means the outcome is as if transactions ran one at a time in some order, which is called serializability. Engines offer weaker levels because serializability costs throughput, and the defaults are weaker than many people assume: PostgreSQL, Oracle and SQL Server default to READ COMMITTED and InnoDB defaults to REPEATABLE READ. None of those is serializable.

The anomaly that bites most often in application code is the lost update. Two transactions read a balance of 100, each computes a new value in application code, and each writes it back; one write silently overwrites the other. Three fixes work. Make the update relative in a single statement, UPDATE accounts SET balance = balance - 20 WHERE id = 1, so the database computes it under a row lock. Lock the row before reading it with SELECT ... FOR UPDATE. Or run at SERIALIZABLE and retry the transactions the database aborts. The third also protects invariants that span rows.

For ACID purposes, remember one fact: an engine can only catch interleavings that it can see, so isolation protects you only if all of the reads and writes that support a decision happen inside the same transaction.

Retry-safe transaction code

Serializable isolation and deadlock detection both work by aborting a transaction and expecting the client to try again. PostgreSQL reports these as SQLSTATE 40001 (serialization failure) and 40P01 (deadlock detected); InnoDB reports a deadlock as error 1213, which also maps to SQLSTATE 40001. The retry must rerun the whole transaction, including its reads. The deadlock mechanics are covered in deadlock detection.

The code below uses psycopg 3 against PostgreSQL. Note the request id. It solves the ambiguous commit problem: if the connection drops after the client sends COMMIT but before it receives the reply, the client cannot know whether the transfer happened. Recording a unique request id inside the same transaction makes the retry a safe no-op instead of a double payment.

import uuid
import psycopg
from psycopg import errors

RETRYABLE = {"40001", "40P01"}   # serialization_failure, deadlock_detected

def transfer(conn, src, dst, amount, request_id=None, attempts=5):
    request_id = request_id or str(uuid.uuid4())
    for attempt in range(attempts):
        try:
            with conn.transaction():   # BEGIN ... COMMIT, or ROLLBACK on exception
                conn.execute("SET TRANSACTION ISOLATION LEVEL SERIALIZABLE")
                # Idempotency: a retried request that already committed is a no-op.
                cur = conn.execute(
                    "INSERT INTO transfers (request_id, src, dst, amount) "
                    "VALUES (%s, %s, %s, %s) ON CONFLICT (request_id) DO NOTHING",
                    (request_id, src, dst, amount))
                if cur.rowcount == 0:
                    return "already-applied"
                conn.execute("UPDATE accounts SET balance = balance - %s WHERE id = %s",
                             (amount, src))
                conn.execute("UPDATE accounts SET balance = balance + %s WHERE id = %s",
                             (amount, dst))
            return "committed"
        except errors.CheckViolation:
            return "insufficient-funds"       # CHECK (balance >= 0) fired; whole txn rolled back
        except psycopg.Error as e:
            if e.sqlstate in RETRYABLE and attempt < attempts - 1:
                continue                      # rerun the WHOLE transaction, never one statement
            raise

The CHECK violation is not retried because it is a business outcome, not a concurrency accident; any exception inside with conn.transaction() rolls back everything before the loop tries again.

Durability: committed means flushed, unless you said otherwise

Durability means an acknowledged commit survives a crash of the database process, the operating system or the power supply. The mechanism is simple: the commit record must be on stable storage before the client is told committed. That requires an fsync, which is slow relative to memory, especially on consumer drives and networked volumes. Engines amortize it by flushing many transactions' commit records with one fsync, which is group commit.

Every engine has settings that skip or delay the flush, and each trades away durability.

Single-machine durability is also not the same as surviving the loss of that machine. If the disk dies, a locally durable commit is gone unless it was replicated. Synchronous replication (PostgreSQL synchronous_standby_names, MySQL semi-sync) makes the acknowledgment wait for a replica. Relaxed settings are fine for regenerable data, as a documented decision.

Testing ACID instead of trusting it

You cannot inspect a configuration file and know that commits survive power loss, because the storage stack can lie. You can test it. The harness below runs concurrent transfers against a disposable PostgreSQL container, kills the server with SIGKILL mid-load, restarts it and checks three invariants: total money unchanged, no negative balances, and every transfer the client saw acknowledged is present. It reuses the transfer function and assumes 100 accounts seeded with 1,000 each.

# Crash test: disposable instance only.
import random, subprocess, threading, psycopg

DSN = "postgresql://test@localhost:5433/acid"
acked = []                         # request_ids the client saw COMMIT succeed for
lock = threading.Lock()

def worker(stop):
    with psycopg.connect(DSN, autocommit=True) as conn:
        while not stop.is_set():
            src, dst = random.sample(range(1, 101), 2)
            rid = f"{threading.get_ident()}-{random.random()}"
            try:
                if transfer(conn, src, dst, random.randint(1, 50), rid) == "committed":
                    with lock:
                        acked.append(rid)
            except psycopg.Error:
                return             # the server died under us; stop this worker

stop = threading.Event()
threads = [threading.Thread(target=worker, args=(stop,)) for _ in range(16)]
for t in threads: t.start()
threading.Event().wait(20)                                         # load for 20 s
subprocess.run(["docker", "kill", "--signal=KILL", "acid-pg"])     # crash, no clean shutdown
stop.set()
for t in threads: t.join()
subprocess.run(["docker", "start", "acid-pg"]); threading.Event().wait(10)

with psycopg.connect(DSN) as conn:
    total = conn.execute("SELECT sum(balance) FROM accounts").fetchone()[0]
    negative = conn.execute("SELECT count(*) FROM accounts WHERE balance < 0").fetchone()[0]
    present = {r[0] for r in conn.execute("SELECT request_id FROM transfers")}
assert total == 100 * 1000, f"money created or destroyed: {total}"     # A and I
assert negative == 0, "constraint bypassed"                            # C
missing = [r for r in acked if r not in present]
assert not missing, f"{len(missing)} acknowledged commits lost"        # D
print("ACID invariants hold;", len(acked), "acknowledged transfers verified")

Killing a container tests process crashes and WAL replay, not power loss, because the OS page cache survives. For the full durability path, hard-reset a VM running the same harness. Rerun it after every change of storage, database major version or durability setting.

Failure modes that look like ACID bugs

• Autocommit by accident. With driver or ORM autocommit, each statement is its own transaction: the debit commits, the credit fails.
• The ambiguous commit. A timeout after COMMIT is sent leaves the outcome unknown. Without an idempotency key, retries double-apply and non-retries lose work.
• Long transactions. An open transaction holds locks and blocks vacuum or purge of old row versions; the system slows until it falls over. Alert on transaction age.
• ACID across systems. A transaction that writes the database and publishes to a message queue is not atomic across both. Either use an outbox table written in the same transaction and relayed afterwards, or a distributed commit protocol with its known blocking problem, covered in two-phase commit.

Trade-offs and a sane default

Each letter has a price: log volume and undo space for atomicity, blocking or aborts under contention for isolation, a flush per commit group for durability and a network round trip if replicated synchronously. For business data the defensible default is all rules in the schema, explicit transaction boundaries, SERIALIZABLE or row locks on read-modify-write paths, whole-transaction retries with idempotency keys, and fully durable settings on power-loss-protected storage.

What to do next

1. Print the durability settings on every production database: fsync, synchronous_commit and full_page_writes on PostgreSQL, innodb_flush_log_at_trx_commit and sync_binlog on MySQL, and confirm the storage has power-loss protection.
2. Grep your data access code for read-modify-write patterns and convert each to a relative UPDATE, SELECT FOR UPDATE, or a SERIALIZABLE transaction with retries.
3. Wrap every transaction in a helper that rolls back on any exception and retries the whole unit on SQLSTATE 40001 and 40P01, with a bounded attempt count.
4. Add an idempotency key to every externally triggered write so an ambiguous commit can be retried safely.
5. Move every rule you can express into CHECK, UNIQUE, foreign key or exclusion constraints.
6. Run the crash-and-invariant harness against a staging copy, then repeat it with a VM hard reset to test the real durability path.
7. Alert on transaction age and on replication lag, the two slow failures that ACID does not prevent.