SQL ACID Properties

ACID is the acronym for the four guarantees a relational database makes about every transaction it runs: Atomicity, Consistency, Isolation, and Durability. Together they answer the question every application eventually has to ask: if the server crashes mid-write, or two users touch the same row at once, can the data still be trusted? Every serious relational engine — SQLite, PostgreSQL, MySQL/InnoDB, SQL Server, Oracle — is built around these four properties, and understanding them is what separates writing a SELECT statement from safely building the backend of a banking app, an inventory system, or anything else where state must never quietly go missing.

Overview: What Each Letter Means

A transaction is a sequence of one or more SQL statements the database treats as a single unit of work. You open one explicitly with BEGIN and close it with either COMMIT (make the changes permanent) or ROLLBACK (undo everything since BEGIN). ACID describes exactly what the engine promises about that unit of work.

Atomicity — all or nothing

A transaction either applies completely or not at all. If you transfer money by debiting one account and crediting another, atomicity guarantees you never end up with the debit applied but not the credit, even if the server loses power halfway through. Internally, the engine records every change made during the transaction — SQLite and most engines use a rollback journal or a write-ahead log (WAL) — so an incomplete transaction can be fully unwound.

Consistency — valid states only

A transaction moves the database from one valid state to another valid state; it never leaves data in a form that violates the rules you declared: PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, and NOT NULL constraints. If a statement inside a transaction would break a constraint, the engine rejects that statement instead of silently corrupting the table. “Consistency” here means consistent with the rules you defined — not the same value across replicas, which is a different, distributed-systems meaning of the word.

Isolation — transactions don’t see each other’s mess

Isolation controls what one in-progress transaction can see of another in-progress transaction’s uncommitted changes. Full isolation (serializable) makes concurrent transactions behave as if they ran one strictly after another, even though the engine may interleave them internally for performance using row locks or multi-version concurrency control (MVCC). Weaker isolation levels trade some of that safety for speed, and anomalies such as dirty reads, non-repeatable reads, and phantom reads become possible as isolation is loosened. SQLite defaults to serializable-style isolation because only one write transaction can be active at a time; PostgreSQL, MySQL, and SQL Server expose tunable levels such as READ COMMITTED and REPEATABLE READ.

Durability — once committed, it stays committed

After COMMIT returns successfully, the change survives — even a crash or power loss the instant afterward. The engine achieves this by flushing the transaction’s log records to physical storage (an fsync call) before reporting success, so the change can be replayed or confirmed on restart.

Syntax

Transaction control uses a small set of statements. Most engines auto-commit each individual statement unless you explicitly open a transaction first.

Statement	Purpose
BEGIN TRANSACTION; (or just BEGIN;)	Starts an explicit transaction; later statements are not permanent until committed.
COMMIT;	Makes every change since BEGIN permanent and durable.
ROLLBACK;	Discards every change since BEGIN, restoring the pre-transaction state.
SAVEPOINT name;	Marks a point inside a transaction that you can partially roll back to, without discarding the whole transaction.
ROLLBACK TO SAVEPOINT name;	Undoes everything after the named savepoint but keeps the transaction open.
RELEASE SAVEPOINT name;	Forgets the savepoint, without undoing anything, once you no longer need it.

Autocommit behavior differs by engine: SQLite and MySQL commit each statement automatically unless a transaction is open; PostgreSQL client connections behave the same way. Always check your driver’s default rather than assuming statements are (or aren’t) wrapped in an implicit transaction.

Examples

Example 1: Atomicity — a transfer that fully commits

CREATE TABLE accounts (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    balance REAL NOT NULL
);

INSERT INTO accounts (id, name, balance) VALUES
    (1, 'Alice', 500.00),
    (2, 'Bob', 200.00);

BEGIN TRANSACTION;

UPDATE accounts SET balance = balance - 100.00 WHERE id = 1;
UPDATE accounts SET balance = balance + 100.00 WHERE id = 2;

COMMIT;

SELECT id, name, balance FROM accounts ORDER BY id;

Both UPDATE statements happen inside one transaction. Because COMMIT succeeds, both changes become permanent together — Alice’s debit and Bob’s credit never exist independently of each other.

Result:

id	name	balance
1	Alice	400.0
2	Bob	300.0

Example 2: Atomicity — a transfer that fully rolls back

CREATE TABLE accounts (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    balance REAL NOT NULL
);

INSERT INTO accounts (id, name, balance) VALUES
    (1, 'Alice', 500.00),
    (2, 'Bob', 200.00);

BEGIN TRANSACTION;

UPDATE accounts SET balance = balance - 1000.00 WHERE id = 1;
UPDATE accounts SET balance = balance + 1000.00 WHERE id = 2;

ROLLBACK;

SELECT id, name, balance FROM accounts ORDER BY id;

This time the application decides the transfer shouldn’t proceed (in a real app, it would check for a negative balance first) and issues ROLLBACK instead of COMMIT. Both UPDATE statements are undone as a unit — the balances end up exactly as they started, proving neither change was ever partially applied.

Result:

id	name	balance
1	Alice	500.0
2	Bob	200.0

Example 3: Savepoints — partial rollback inside one transaction

CREATE TABLE orders_demo (
    order_id INTEGER PRIMARY KEY,
    item TEXT NOT NULL,
    quantity INTEGER NOT NULL
);

BEGIN TRANSACTION;

INSERT INTO orders_demo (order_id, item, quantity) VALUES (1, 'Widget', 5);

SAVEPOINT before_risky_insert;

INSERT INTO orders_demo (order_id, item, quantity) VALUES (2, 'Gadget', -3);

ROLLBACK TO SAVEPOINT before_risky_insert;

INSERT INTO orders_demo (order_id, item, quantity) VALUES (3, 'Gizmo', 7);

COMMIT;

SELECT order_id, item, quantity FROM orders_demo ORDER BY order_id;

A SAVEPOINT lets you undo part of a transaction without throwing away everything. Here, the suspicious negative-quantity insert (order 2) is undone by rolling back to the savepoint, while order 1 (inserted before the savepoint) and order 3 (inserted after the rollback) both survive to the final COMMIT.

Result:

order_id	item	quantity
1	Widget	5
3	Gizmo	7

Example 4: Consistency — a CHECK constraint protecting an invariant

CREATE TABLE accounts_checked (
    id INTEGER PRIMARY KEY,
    balance REAL NOT NULL CHECK (balance >= 0)
);

INSERT INTO accounts_checked (id, balance) VALUES (1, 100.00);

BEGIN TRANSACTION;
UPDATE accounts_checked SET balance = balance - 50 WHERE id = 1;
COMMIT;

SELECT id, balance FROM accounts_checked;

The CHECK (balance >= 0) constraint is the database’s own definition of a “valid state.” As long as every statement in the transaction respects it, the commit succeeds normally.

Result:

id	balance
1	50.0

Under the Hood: How a Transaction Actually Executes

When you issue BEGIN, the engine does not simply start writing to the table files. Instead it opens a log — a write-ahead log or rollback journal — and every subsequent INSERT/UPDATE/DELETE is recorded there before (or alongside) the in-memory data pages being modified. The engine also acquires locks (or, in MVCC engines like PostgreSQL, creates a new row version) so other transactions cannot see the uncommitted data. When you call COMMIT, the engine forces the log to disk, flips a marker that says “this transaction is complete,” and only then releases locks and reports success back to your application — that flush-before-acknowledge step is what makes durability possible even across a crash. ROLLBACK instead reads the log backward (or discards the new row versions) and restores the previous state without ever touching durable storage for the abandoned changes.

Isolation is what governs whether a transaction can see its own uncommitted writes versus writes from other connections. Within a single transaction, you always see your own changes immediately:

CREATE TABLE inventory (
    id INTEGER PRIMARY KEY,
    item TEXT NOT NULL,
    stock INTEGER NOT NULL
);

INSERT INTO inventory (id, item, stock) VALUES (1, 'Blue Widget', 50);

BEGIN TRANSACTION;

UPDATE inventory SET stock = stock - 5 WHERE id = 1;

SELECT id, item, stock FROM inventory WHERE id = 1;

ROLLBACK;

SELECT id, item, stock FROM inventory WHERE id = 1;

The first SELECT, run while the transaction is still open, sees the pending decrement (stock 45) because a transaction always sees its own writes. The second SELECT, run after ROLLBACK, sees the original value (stock 50) because the change was discarded. A separate connection querying the table between those two statements would have seen only 50 the whole time — it is isolated from the first connection’s uncommitted work.

Result (first SELECT): id 1, item Blue Widget, stock 45. Result (second SELECT, after rollback): id 1, item Blue Widget, stock 50.

Logical processing order matters here too: within each statement the engine still evaluates FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT as usual; ACID governs the transaction wrapped around those statements, not the order of clauses inside any one of them.

Common Mistakes

Mistake 1: Nesting BEGIN instead of using a savepoint

BEGIN TRANSACTION;
BEGIN TRANSACTION;

Most engines, including SQLite, do not allow a transaction to start inside another transaction and raise an error such as “cannot start a transaction within a transaction.” If you need a nested unit of work you can partially undo, use SAVEPOINT instead, as shown in Example 3.

Mistake 2: Assuming a failed statement rolls itself back

CREATE TABLE accounts_checked (
    id INTEGER PRIMARY KEY,
    balance REAL NOT NULL CHECK (balance >= 0)
);
INSERT INTO accounts_checked (id, balance) VALUES (1, 100.00);
BEGIN TRANSACTION;
UPDATE accounts_checked SET balance = balance - 500 WHERE id = 1;
COMMIT;

The UPDATE would push the balance to -400, violating the CHECK constraint, so the engine raises an error and rejects that statement. But the transaction itself is still open — many drivers do not automatically roll it back for you. Leaving it open holds locks and can leave later statements confused about the current state. Always catch the error in application code and explicitly issue ROLLBACK (or ROLLBACK TO a savepoint) before continuing.

Mistake 3: Forgetting to commit (or relying on auto-commit you don’t have)

A transaction left open — because the application crashed before calling COMMIT, or because a script exits without an explicit commit — holds its locks until the connection closes, blocking other writers and, on restart, gets rolled back, silently losing work the user believed was saved. Always pair every BEGIN with a COMMIT or ROLLBACK on every code path, including error handlers.

Best Practices

• Keep transactions short — the longer one is open, the longer it holds locks and blocks concurrent writers.
• Always pair BEGIN with exactly one terminating COMMIT or ROLLBACK, including in exception/error-handling paths.
• Let constraints (CHECK, FOREIGN KEY, UNIQUE, NOT NULL) enforce consistency at the database level — don’t rely solely on application code, which every future caller would also have to remember to run.
• Use SAVEPOINT for multi-step transactions where you want to discard one sub-step (e.g., a failed retry) without abandoning the whole transaction.
• Choose the weakest isolation level that’s actually safe for the operation — stronger isolation (e.g., SERIALIZABLE) costs more concurrency, so reserve it for logic that truly can’t tolerate anomalies like non-repeatable reads.
• Never perform non-database side effects (sending an email, calling an external API) inside an open transaction — if the transaction rolls back, the side effect can’t be undone.
• In high-concurrency systems, be prepared to retry a transaction that fails due to a serialization conflict or deadlock; this is normal, expected behavior under strict isolation, not a bug.

Practice Exercises

1. Create a products table with a stock INTEGER NOT NULL CHECK (stock >= 0) column, insert one row with stock = 10, then write a transaction that decrements it by 3 and commits. What is the final stock value?
2. Using the same table, write a transaction that attempts to decrement stock by 20 (which would make it negative), and show how you would detect the constraint violation and issue a ROLLBACK in response.
3. Write a transaction that inserts three rows, places a SAVEPOINT after the second insert, inserts a fourth row you then decide to discard via ROLLBACK TO SAVEPOINT, and commits. How many rows remain, and which ones?

Summary

• ACID stands for Atomicity, Consistency, Isolation, and Durability — the four guarantees a database makes about every transaction.
• Atomicity means a transaction fully applies or fully undoes; Consistency means it never violates your declared constraints; Isolation means concurrent transactions don’t see each other’s uncommitted work; Durability means a committed change survives a crash.
• Transactions are controlled with BEGIN, COMMIT, and ROLLBACK, with SAVEPOINT/ROLLBACK TO SAVEPOINT for partial undo within a transaction.
• Under the hood, engines use a write-ahead log or rollback journal plus locking or MVCC to implement all four properties.
• Always close every transaction explicitly on every code path, and let database constraints — not just application logic — enforce consistency.