ACID Transactions Explained: Atomicity, Consistency, Isolation and Durability
When money, stock or bookings are involved, "almost saved" is not acceptable. ACID transactions are how databases guarantee that changes are all-or-nothing and survive crashes.
Imagine transferring 1,000 from account A to account B. The system subtracts 1,000 from A — and then the server crashes before adding it to B. The money has vanished. Preventing exactly this kind of failure is what database transactions are for, and ACID describes the guarantees they provide.
What is a transaction?
A transaction is a group of operations that the database treats as a single unit of work:
BEGIN;
UPDATE accounts SET balance = balance - 1000 WHERE id = 'A';
UPDATE accounts SET balance = balance + 1000 WHERE id = 'B';
COMMIT;If anything goes wrong before COMMIT, the application (or the database) issues ROLLBACK and nothing changes.
A — Atomicity
All operations in the transaction succeed, or none of them take effect. There is no state where money left A but never reached B. In the crash example, on restart the database discards the incomplete transaction.
C — Consistency
A transaction moves the database from one valid state to another, respecting every rule defined in it: data types, NOT NULL, unique constraints, foreign keys and check constraints such as CHECK (balance >= 0). If a transaction would break a rule, it fails. (Business rules that the database does not know about remain the application's responsibility.)
I — Isolation
Many transactions run at the same time. Isolation controls how much they can see of each other's unfinished work. Without it, concurrent operations produce anomalies:
- Dirty read — reading another transaction's change before it commits (and it may roll back).
- Non-repeatable read — reading the same row twice and getting different values because another transaction changed it in between.
- Phantom read — running the same query twice and getting new rows that another transaction inserted.
- Lost update — two transactions read the same value, both modify it, and one overwrites the other.
The SQL standard defines four isolation levels:
| Level | Prevents | Notes |
|---|---|---|
| Read Uncommitted | Little | Rarely appropriate |
| Read Committed | Dirty reads | Default in PostgreSQL, SQL Server and Oracle |
| Repeatable Read | Dirty and non-repeatable reads | Default in MySQL (InnoDB) |
| Serializable | All of the above — results as if transactions ran one after another | Strongest; may require retrying transactions that conflict |
Higher isolation means fewer surprises but potentially more waiting or retries. Many applications use the default level and add explicit locking where it matters:
BEGIN;
SELECT stock FROM products WHERE id = 7 FOR UPDATE; -- lock this row
-- check stock is sufficient in application code, then:
UPDATE products SET stock = stock - 1 WHERE id = 7;
COMMIT;Or let the database check atomically in one statement: UPDATE products SET stock = stock - 1 WHERE id = 7 AND stock > 0, then confirm that one row was updated.
D — Durability
Once a transaction is committed, it stays committed — even if the power fails a millisecond later. Databases achieve this with a write-ahead log (WAL; the redo log in InnoDB): changes are recorded in a log on durable storage before the commit is confirmed, and the log is replayed after a crash. Durability on one server does not replace backups and replication, which protect against disk loss and disasters.
Where ACID matters most
- Payments, wallets, invoices and accounting.
- Stock levels and order placement — preventing overselling.
- Bookings and reservations — preventing double booking.
- Any multi-step change where a half-finished result would be wrong.
ACID vs BASE
Some distributed NoSQL databases historically favoured BASE — Basically Available, Soft state, Eventually consistent — trading immediate consistency for availability and scale across many servers. Eventual consistency is fine for likes, view counts or activity feeds, but risky for balances and inventory. Today the distinction is less sharp: many NoSQL databases support ACID transactions in some form, and distributed SQL databases provide ACID at scale. See SQL vs NoSQL.
Common application mistakes
- Not wrapping related writes in a transaction.
- Calling slow external services (payment gateways, email) while holding a transaction open — this holds locks for too long. Do external calls before or after, and design for retries.
- Read-then-write logic without locking or atomic updates, causing lost updates under load.
- Ignoring serialization or deadlock errors instead of retrying the transaction.
Key takeaways
- Atomicity: all or nothing. Consistency: rules always hold. Isolation: concurrent transactions don't corrupt each other. Durability: committed means saved.
- Choose isolation levels and locking deliberately for money, stock and bookings.
- Keep transactions short and retry on conflicts.
How Wintecsys can help
We build back ends for e-commerce, bookings and financial workflows where correctness matters — with proper transactions, concurrency handling and tests that simulate real-world load.
