ACID

ACID is a set of four guarantees a database makes about a transaction, a group of changes treated as one unit of work. ACID stands for Atomicity, Consistency, Isolation and Durability. You meet it whenever you write BEGIN and COMMIT in SQL, and it is a common reason to choose a relational database for money, orders and inventory.

How it works

Each letter covers a different failure.

• Atomicity: the transaction is all-or-nothing. If any statement fails, every change made so far is undone (a rollback). Nobody ever sees half a transfer.
• Consistency: a committed transaction moves the database from one valid state to another. The database enforces the rules you declared, such as NOT NULL, UNIQUE, foreign keys and CHECK constraints. It cannot invent rules you never wrote.
• Isolation: transactions running at the same time do not see each other's unfinished work. How strict this is depends on the isolation level you choose, and weaker levels allow anomalies in exchange for speed.
• Durability: once COMMIT returns, the change survives a crash or power loss. Databases do this by writing a log or journal to disk before they confirm the commit.

SQLite documents itself as atomic, consistent, isolated and durable even if a program crash, operating system crash or power failure interrupts a transaction. In its default rollback-journal mode, it copies the original content of each page it is about to change into a journal file, so it can restore the database after a failure.

The example below moves 150 from an account that holds 100. The CHECK constraint rejects the second update, and the rollback also undoes the first update.

import sqlite3
print(sqlite3.sqlite_version)
db = sqlite3.connect(":memory:", isolation_level=None)
db.execute("CREATE TABLE account (id INTEGER PRIMARY KEY, balance INTEGER NOT NULL CHECK (balance >= 0))")
db.execute("INSERT INTO account VALUES (1, 100), (2, 50)")
try:
    db.execute("BEGIN")
    db.execute("UPDATE account SET balance = balance + 150 WHERE id = 2")
    db.execute("UPDATE account SET balance = balance - 150 WHERE id = 1")
    db.execute("COMMIT")
except sqlite3.IntegrityError as e:
    print("error:", e)
    db.execute("ROLLBACK")
print(db.execute("SELECT id, balance FROM account ORDER BY id").fetchall())

Output from SQLite 3.45.1:

3.45.1
error: CHECK constraint failed: balance >= 0
[(1, 100), (2, 50)]

Account 2 still holds 50, even though its update ran before the failure. That is atomicity at work.

What does ACID stand for?

ACID stands for Atomicity, Consistency, Isolation and Durability. The four properties describe what a transaction promises, not how a database stores data. A system can be ACID for single-row writes and still give weaker guarantees across several rows or machines, so check what the vendor means by "ACID" before relying on it.

ACID vs BASE

BASE stands for Basically Available, Soft state, Eventual consistency. Eric Brewer set it against ACID in his 2000 PODC keynote: BASE systems accept stale data and weaker consistency to stay available. ACID favors correctness at commit time. The line has blurred: MongoDB supports multi-document transactions on replica sets from version 4.0 and on sharded clusters from 4.2.

Common pitfalls

• Forgetting to commit or roll back: a transaction left open holds locks or blocks other writers. Always end it on every code path, ideally with a context manager or try/finally.
• Assuming the default isolation level is serializable: PostgreSQL defaults to Read Committed and MySQL InnoDB to Repeatable Read, so concurrent transactions can still see anomalies. SQLite transactions are serializable. Raise the level where a race would cost money.
• Treating consistency as automatic: the database only enforces constraints you declared. If the rule lives only in application code, ACID will not protect it.
• Mixing in side effects: sending an email or calling an API inside a transaction cannot be rolled back. Do those after the commit succeeds, and make them safe to retry.
• Auto-commit surprises: in auto-commit mode each statement is its own transaction, so a multi-step change is not atomic unless you open one explicitly.
• Expecting parallel writers in SQLite: SQLite gets serializable isolation by allowing only one writer at a time. Other writers wait their turn, so keep write transactions short.

Related terms

• SQL — the language where BEGIN, COMMIT and ROLLBACK start and end transactions.
• SQLite — an embedded database that documents full ACID behavior, used in the example above.
• Primary key — a constraint the database enforces to keep committed data valid.
• Idempotent — retrying an operation safely, which matters when a commit's outcome is unknown.

See also

• Cheatsheet: SQL Cheatsheet — transaction and constraint syntax in one place.