ACID

ACID describes four properties a database promises about a transaction, which is a group of reads and writes treated as one unit. The classic example is a bank transfer: subtract 100 from account A and add 100 to account B. Those two writes must succeed or fail together. If the system credits B but crashes before debiting A, you just printed money.

Atomicity means a transaction is all-or-nothing. If any step fails, the database rolls back every change so it looks like the transaction never started. There is no partial result left behind.

Consistency means the transaction moves the database from one valid state to another. Rules like foreign keys, unique constraints, and check constraints are never violated by a committed transaction. Part of this is on the database and part is on the application writing correct logic.

Isolation means transactions running at the same time do not corrupt each other. Even though the database may run thousands of them concurrently, each one behaves as if it had the database to itself. Durability means once you get the commit confirmation, the data is permanently stored and will survive a crash, a restart, or a power cut a millisecond later.

Atomicity and durability are usually handled by a write-ahead log (WAL). Before changing the actual data pages, the database appends a record of the change to a log file and flushes it to disk. PostgreSQL, MySQL with InnoDB, and SQLite all do this. If the server dies mid-transaction, on restart it replays committed log records and discards uncommitted ones, so the database recovers to a clean state.

Durability specifically depends on that flush hitting stable storage. This is why the famous fsync setting matters: if you let writes sit in an OS buffer for speed, a power cut can lose committed data. Strict durability waits for disk confirmation, which is slower but honest.

Isolation is enforced with locking or with multi-version concurrency control (MVCC). PostgreSQL and Oracle use MVCC, where each transaction reads a consistent snapshot of the data and writers create new row versions instead of blocking readers. The SQL standard defines four isolation levels (read uncommitted, read committed, repeatable read, serializable) that trade strictness for concurrency. Read committed is the common default; serializable is the strongest and prevents anomalies like phantom reads at the cost of throughput.

Reach for full ACID whenever correctness of the data matters more than raw speed: money movement, inventory counts, order processing, ticket booking, anything where a double-spend or a lost write is a real bug with real cost. Relational databases give you these guarantees by default, which is a big reason banks and ledgers run on them.

The cost is performance and scaling. Strong isolation requires coordination, and that coordination gets expensive across many machines. A single Postgres node enforces ACID cheaply, but spreading one transaction across servers in different regions means network round trips and locking that can stall under load.

This is the tension behind NoSQL and the BASE model (Basically Available, Soft state, Eventually consistent). Systems like early DynamoDB and Cassandra relaxed isolation and immediate consistency to get higher availability and horizontal scale. The industry has since swung back: many distributed databases now offer ACID transactions again, just with more engineering behind them. Pick based on your workload, not fashion.

Imagine a checkout. You wrap three statements in one transaction: insert an order row, decrement the product stock count, and insert a payment record. Atomicity guarantees that if the payment insert fails, the order and the stock change both roll back, so you never ship a product you never charged for.

Isolation matters here when two shoppers buy the last unit at the same time. Without it, both transactions could read stock as 1, both decrement to 0, and you oversell. With proper isolation (a row lock or a serializable transaction), the second buyer is forced to wait and then sees stock as 0, so they get an out-of-stock error instead of a phantom order.

Durability is the final promise: after the database returns COMMIT, your service can safely tell the customer the order is placed. Even if the server loses power one millisecond later, the order is on disk in the WAL and will be there when the machine comes back.