ACID Transactions

A transaction is a bundle of writes

A transaction is a group of database operations that should succeed or fail together. They act like a single operation.

The classic example is a bank transfer. To move $100 from Alice to Bob, you do two things.

1. Take $100 from Alice's balance. 2. Add $100 to Bob's balance.

If only step 1 happens, money vanishes. If only step 2 happens, money is created. Either both happen or neither does.

The SQL for this looks like BEGIN; UPDATE ...; UPDATE ...; COMMIT;. You wrap the writes. If anything goes wrong, you use ROLLBACK instead of COMMIT. The database undoes everything as if it never started.

This bundle of promises is called ACID. It stands for four properties.

A is for Atomicity

Atomicity means a transaction is all or nothing. Every operation inside it commits together or rolls back together. The database promises you never see a half-finished transaction.

If the server crashes in the middle of a transaction, the database notices on restart. It rolls back the partial work. The user sees the world as if the transaction never started.

The real impact is this. You can write multi-step operations and trust they will not corrupt your data. You do not have to defensively check "did the first write succeed" before the second one.

Without atomicity, every multi-step operation becomes a tangle of "what if step 3 fails after step 2 already wrote." That is the kind of bug that causes real disasters in non-transactional systems.

C is for Consistency

Consistency means the database moves from one valid state to another. Constraints like foreign keys, uniqueness, and check constraints are always enforced.

An example. Your orders table requires every user_id to point at a real row in users. If a transaction tries to insert an order with a fake user, it fails. The constraint refuses to be broken.

Another example. Your accounts table has a check that says balance >= 0. A withdrawal that would push the balance below zero is rejected.

The database enforces these rules even in the middle of a transaction. You cannot commit a state that breaks a constraint.

A quick note. "Consistency" here is not the same as "consistency" in distributed systems. They are two different uses of the same word.

I is for Isolation

Isolation means transactions running at the same time do not step on each other. Two users updating the same row will be lined up. One happens, then the other. They never overlap.

This prevents a few bugs. Dirty reads, where you read another transaction's uncommitted changes. Non-repeatable reads, where the same query inside one transaction returns different results. Lost updates, where one transaction overwrites another one's changes.

Databases offer several isolation levels. Read Committed. Repeatable Read. Serializable. Stronger isolation means more correctness but slower performance. Most apps use the default, which is often Read Committed, and never think about it.

You get into trouble when you have many writes to the same rows at once. Then isolation level matters a lot.

D is for Durability

Durability means once a transaction is committed, it survives. Power loss. Server crash. Disk failure. The data is still there when the system comes back up.

How it works. The database writes to a write-ahead log (WAL) before updating the real data files. The WAL is flushed to disk before the database says "committed." If the server crashes, the WAL is replayed on restart. Any committed-but-not-yet-applied changes are reapplied.

This is also why databases can be slow on writes. Every commit means a disk flush. SSDs help. Write-back caching helps but gives up some safety. Cloud databases replicate across zones so they survive a whole-region failure.

Durability is what lets you trust the database. Once committed, it is real. Forever.

Why ACID matters

ACID is the contract that lets you build serious apps on databases. Banking. Online stores. Accounting. Inventory. Ticketing. Anything where you cannot accept inconsistent data.

Older NoSQL databases dropped some of these to gain scale, especially atomicity across documents and isolation across writes. Modern NoSQL has been adding ACID back. MongoDB has multi-document transactions. DynamoDB has TransactWrite.

For most apps, ACID is the safety net you do not notice until it is missing.

The next concept covers the other kind of consistency, the one from distributed systems. That is where things get truly strange.