ACID Transactions Explained: Commit, Rollback and Constraints

"What does ACID stand for?" is easy to answer and easy to answer badly. Reciting Atomicity, Consistency, Isolation, Durability earns nothing; what interviewers want is what each letter does when something goes wrong halfway through a group of writes. This article answers that with a real database, SQLite through Python's sqlite3 module, so every claim below is something you can watch happen.

The problem it solves

Most real operations are several writes that only make sense together. Moving an object on loan from one museum's register to another's is two updates: sign it out of one, sign it into the other. Transferring money is a debit and a credit. If the process crashes, a constraint fails, or another user reads between the two writes, the data can end up describing something that never happened: an object in neither register, or money that vanished.

A transaction groups statements into one unit of work, started with BEGIN and ended with COMMIT or ROLLBACK, and the database promises four things about that unit:

• Atomicity: all of its writes land, or none do.
• Consistency: constraints such as CHECK, NOT NULL and foreign keys hold before and after; a transaction that would break one is refused.
• Isolation: other connections do not see its half-finished state.
• Durability: once COMMIT returns, the change survives a crash.

The intuition

The key word is unit. A failure in the second statement does not just cancel the second statement; it undoes the first one too, even though the first one really was applied. The engine keeps enough information from BEGIN onwards to put everything back.

Consistency in ACID is narrower than it sounds. It means the database never moves from a valid state to an invalid one according to the rules you declared. A CHECK (objects >= 0) constraint is enforced; a business rule that lives only in application code is not. That is also different from the "consistency" of the CAP theorem, which is about replicas agreeing.

Isolation comes in levels, trading safety for concurrency; the anomalies each level allows are covered in SQL isolation levels explained. This article shows the basic promise: uncommitted writes are invisible to other connections.

Durability is the promise that makes COMMIT meaningful. The database writes enough to disk before confirming, in a journal or write-ahead log, that a crash afterwards cannot lose the change.

Watch it run

The animation starts from the rule: a transaction is one unit of work, and all of its writes land, or none of them do. BEGIN: the engine notes the state it would have to return to. The first statement takes Hallam from 3 to 9, and it really is applied. But nobody else can see it; that is the I in ACID, and another connection still reads 3. The second statement tries to set Verrey to -1. CHECK (objects >= 0) fires: the C in ACID refuses to let the database go invalid. Rollback. Verrey returns to 0, which is no surprise, and so does Hallam, back to 3. The unit of work is the transaction, not the statement, and the table reads [('Hallam', 3), ('Verrey', 0)], exactly as if nothing had run. Then the valid loan: sign the object out of one register and into the other, 3 to 2 and 0 to 1. Both checks hold, so COMMIT, and durable means it survives the crash that comes next.

The code

Run with Python's sqlite3 module on SQLite 3.37. Used as a context manager, a connection commits when the block succeeds and rolls back when it raises:

import sqlite3

def fresh(path=":memory:", **kw):
    db = sqlite3.connect(path, **kw)
    db.executescript("""
      DROP TABLE IF EXISTS loan;
      CREATE TABLE loan (museum TEXT PRIMARY KEY, objects INTEGER CHECK (objects >= 0));
      INSERT INTO loan VALUES ('Hallam', 3), ('Verrey', 0);
    """)
    return db

def lend(db, giver, taker):
    try:
        with db:                          # BEGIN ... COMMIT, or ROLLBACK if the block raises
            db.execute("UPDATE loan SET objects = objects + 1 WHERE museum = ?", (taker,))
            db.execute("UPDATE loan SET objects = objects - 1 WHERE museum = ?", (giver,))
        return "committed"
    except sqlite3.IntegrityError:
        return "rolled back"

db = fresh()
print(lend(db, "Verrey", "Hallam"))              # rolled back   Verrey would go to -1
print(db.execute("SELECT * FROM loan").fetchall())   # [('Hallam', 3), ('Verrey', 0)]
print(lend(db, "Hallam", "Verrey"))              # committed
print(db.execute("SELECT * FROM loan").fetchall())   # [('Hallam', 2), ('Verrey', 1)]

The same two statements without a transaction. In autocommit mode each statement is its own transaction, so the constraint undoes only the failing one, and an object appears from nowhere:

auto = fresh(isolation_level=None)               # autocommit: every statement is its own transaction
auto.execute("UPDATE loan SET objects = objects + 1 WHERE museum = 'Hallam'")
try:
    auto.execute("UPDATE loan SET objects = objects - 1 WHERE museum = 'Verrey'")
except sqlite3.IntegrityError as e:
    print(type(e).__name__)                      # IntegrityError   the CHECK fired
print(auto.execute("SELECT * FROM loan").fetchall())  # [('Hallam', 4), ('Verrey', 0)]

Isolation and durability need two connections to one file. The reader sees 3 until the writer commits; the committed 9 survives closing and reopening, and the uncommitted 0 does not:

import os, shutil, tempfile

folder = tempfile.mkdtemp()
path = os.path.join(folder, "museum.db")
writer = fresh(path)
reader = sqlite3.connect(path)
writer.execute("UPDATE loan SET objects = 9 WHERE museum = 'Hallam'")   # opens a transaction
print(reader.execute("SELECT objects FROM loan WHERE museum = 'Hallam'").fetchone())   # (3,)
writer.commit()
print(reader.execute("SELECT objects FROM loan WHERE museum = 'Hallam'").fetchone())   # (9,)

writer.execute("UPDATE loan SET objects = 0 WHERE museum = 'Hallam'")
writer.close()                                   # closed without COMMIT: the change is discarded
reopened = sqlite3.connect(path)
print(reopened.execute("SELECT objects FROM loan WHERE museum = 'Hallam'").fetchone())  # (9,)
reader.close()
reopened.close()
shutil.rmtree(folder)

The database against a brute-force model that applies each batch of updates by hand and keeps it only if no balance ever goes negative, over 300 random runs of 20 transactions each:

import random

random.seed(21)
ok = True
for _ in range(300):
    names = ["a", "b", "c", "d"]
    start = {n: random.randint(0, 5) for n in names}
    db = sqlite3.connect(":memory:")
    db.execute("CREATE TABLE acct (name TEXT PRIMARY KEY, bal INTEGER CHECK (bal >= 0))")
    db.executemany("INSERT INTO acct VALUES (?, ?)", start.items())
    db.commit()
    model = dict(start)                          # brute force: apply all or nothing by hand
    for _ in range(20):
        steps = [(random.choice(names), random.randint(-4, 4)) for _ in range(random.randint(1, 4))]
        try:
            with db:
                for name, delta in steps:
                    db.execute("UPDATE acct SET bal = bal + ? WHERE name = ?", (delta, name))
        except sqlite3.IntegrityError:
            pass
        trial = dict(model)
        valid = True
        for name, delta in steps:                # a CHECK fires if any intermediate value is negative
            trial[name] += delta
            valid &= trial[name] >= 0
        if valid:
            model = trial
        ok &= dict(db.execute("SELECT name, bal FROM acct")) == model
    db.close()
print(ok)                                        # True

The complexity

Transactions cost coordination rather than steps:

• Atomicity: the engine records how to undo each change, in a rollback journal or write-ahead log, so every write does a little extra work.
• Durability: a commit waits for the disk. Batching many small writes into one transaction is often far faster than committing each one.
• Isolation: stronger levels mean more locking or more aborted transactions under contention. A long transaction holds its locks for its whole length.

Where it goes wrong

• Relying on autocommit for multi-step changes. Each statement commits alone, so a failure leaves half the change behind.
• Catching the error and carrying on. Swallowing an exception inside the transaction block means it commits whatever ran.
• Checking a rule in application code only. Two concurrent requests can both pass the check. A constraint or a lock enforces it for everyone.
• Long transactions. Doing network calls while a transaction is open holds locks and blocks other writers.
• Confusing ACID consistency with CAP consistency. One is "constraints hold"; the other is "replicas agree".

When it shows up in interviews

It appears in backend and database rounds as "what is ACID?", "what happens if the server dies halfway through a transfer?", and inside system design questions about payments, bookings and inventory. It leads into isolation levels, into race conditions and mutexes for the same problem in application code, and into the CAP theorem for the distributed version.

How to say it in an interview

"A transaction groups statements into one unit of work. Atomicity means all of it commits or none of it does: if the second update of a transfer violates a constraint, the first update is rolled back too. Consistency means the declared constraints hold before and after. Isolation means other connections do not see uncommitted changes; how strictly depends on the isolation level. Durability means once commit returns, the change survives a crash, because it is in the log on disk. In practice I wrap multi-step changes in a transaction, put the rules I care about in constraints, and keep transactions short."