ACID Transactions Explained With Examples
Atomicity, consistency, isolation and durability are the guarantees a transaction provides. Learn each one and the isolation levels.
By Velloreth
The four guarantees
Atomicity — a transaction either fully applies or not at all.
Consistency — a transaction moves the database from one valid state to another, respecting the constraints.
Isolation — concurrent transactions do not see each other’s intermediate state.
Durability — a committed transaction survives a crash.
Atomicity
A transfer moves money from one account to another. Both statements must apply, or neither.
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
If the second statement fails, the ROLLBACK undoes the first, so the money is not lost.
Without a transaction, a crash between the two statements leaves the money deducted and not credited.
The mechanism is a write-ahead log: the changes are written to a log before they are applied, so a crash can be recovered by replaying or undoing the log.
Consistency
A transaction respects the constraints, so the database is valid before and after.
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- A check constraint or a trigger rejects a negative balance
COMMIT;
The database enforces the invariant, not the application. A foreign key, a unique index, a check constraint, and a not-null constraint are all part of this.
The nuance: consistency here means the database’s own constraints, not the application’s business rules. A business rule that is not expressed as a constraint is the application’s responsibility.
Isolation
Concurrent transactions do not see each other’s intermediate state.
Without isolation, a transaction can read a value that is later rolled back, or two transactions can overwrite each other.
The anomalies
A dirty read — reading a value written by an uncommitted transaction. If that transaction rolls back, the value never existed.
A non-repeatable read — reading the same row twice and getting different values, because another transaction committed a change in between.
A phantom read — running the same query twice and seeing new rows, because another transaction inserted them.
A lost update — two transactions read the same value, both modify it, and one overwrites the other.
The isolation levels
| Level | Dirty read | Non-repeatable | Phantom |
|---|---|---|---|
| Read uncommitted | Possible | Possible | Possible |
| Read committed | No | Possible | Possible |
| Repeatable read | No | No | Possible |
| Serializable | No | No | No |
Read committed is the default in PostgreSQL, Oracle, and SQL Server. Each statement sees a snapshot of the committed data at the start of the statement.
Repeatable read takes a snapshot at the start of the transaction, so the same query returns the same rows.
Serializable makes the transactions appear to run one after another, which is the strongest and the slowest. It may abort a transaction with a serialization failure, which the application must retry.
MySQL InnoDB defaults to repeatable read, which is a notable difference.
The lost update
This is the most common concurrency bug.
-- Transaction A
BEGIN;
SELECT balance FROM accounts WHERE id = 1; -- 100
-- Transaction B
BEGIN;
SELECT balance FROM accounts WHERE id = 1; -- 100
-- Both compute 100 + 50 = 150
UPDATE accounts SET balance = 150 WHERE id = 1;
COMMIT;
-- B commits 150, overwriting A's 150. One update is lost.
The fix — an atomic update:
UPDATE accounts SET balance = balance + 50 WHERE id = 1;
The database does the arithmetic, so there is no read-modify-write window.
The fix — a row lock:
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
The lock blocks the other transaction until the first commits.
The fix — optimistic concurrency:
UPDATE accounts SET balance = 150, version = version + 1
WHERE id = 1 AND version = 5;
If the version changed, the update affects zero rows, and the application retries.
Durability
A committed transaction survives a crash.
The mechanism is the write-ahead log, flushed to disk on commit. A crash is recovered by replaying the log.
The cost is a disk flush per commit, which is why a transaction is slower than an autocommit statement.
The tuning: synchronous_commit can be relaxed to trade durability for speed, which is a deliberate risk. A setting of off means a crash can lose the last few transactions.
A replica does not provide durability on its own: an asynchronous replica may not have received the commit when the primary fails.
Deadlocks
Two transactions each hold a lock the other needs, so neither can proceed.
Transaction A: locks row 1, waits for row 2
Transaction B: locks row 2, waits for row 1
The database detects the cycle and aborts one transaction with a deadlock error.
The application should retry the aborted transaction, because it is a transient failure.
The prevention: acquire the locks in a consistent order. If every transaction locks row 1 before row 2, the cycle cannot form.
Keep a transaction short. A long transaction holds locks for longer, which increases the chance of a conflict.
Practical guidance
- Wrap a multi-statement change in a transaction.
- Keep a transaction short.
- Use an atomic update rather than a read-modify-write.
- Use
FOR UPDATEwhen you must read then write. - Use optimistic concurrency with a version column.
- Know the isolation level of your database.
- Retry a serialization failure and a deadlock.
- Acquire locks in a consistent order.
- Do not relax
synchronous_commitwithout understanding the risk.
For the related guides, see sql-joins-explained and database-migrations-guide. For the broader context, sql-vs-nosql-databases and caching-strategies-explained.
Quotable summary
Atomicity means a transaction either fully applies or not at all, with no partial state, and consistency means it moves the database from one valid state to another while respecting the constraints. Isolation means concurrent transactions do not see each other’s intermediate state, and durability means a committed transaction survives a crash, which is achieved with a write-ahead log flushed on commit. The isolation level is a trade between correctness and concurrency, and the default is not always the strongest: read committed is the default in PostgreSQL, Oracle, and SQL Server, while MySQL InnoDB defaults to repeatable read. A lost update is the most common concurrency bug, and an atomic update, a row lock, or optimistic concurrency with a version column prevents it.