ACID Properties in Databases: Atomicity, Consistency, Isolation & Durability Explained

ACID (Atomicity, Consistency, Isolation, Durability) is the standard set of correctness guarantees that a relational database promises for every transaction, ensuring that data remains valid even in the face of concurrent writes, partial failures, and system crashes.

The term was coined by Andreas Reuter and Theo Härder in 1983, formalising properties that IBM's System R had already implemented. Today ACID is the baseline expectation for PostgreSQL, MySQL (InnoDB), Oracle, SQL Server, and most NewSQL databases. Understanding each property — and the failure modes it prevents — is essential for designing reliable systems at any scale.

A transaction is a logical unit of work that groups one or more SQL statements so they succeed or fail as a single operation. You open a transaction with BEGIN, execute your statements, then either COMMIT (persist the changes) or ROLLBACK (discard them). The database guarantees that no other session will see a half-finished transaction.

Atomicity means a transaction is all-or-nothing: either every statement inside it succeeds and the changes are committed, or a failure at any point causes every change to be rolled back, leaving the database exactly as it was before the transaction started. There is no intermediate state visible to the rest of the system.

Without atomicity, a crash between the debit and the credit in a bank transfer would leave Alice's account $200 lighter while Bob's balance never increased — money disappears from the system. Atomicity is enforced by the database's undo log (also called the rollback segment): before overwriting any page, the old value is written to the log so it can be restored if needed.

Consistency means a transaction can only bring the database from one valid state to another valid state, where validity is defined by all the constraints, triggers, and rules declared in the schema. If a transaction would violate a CHECK constraint, a UNIQUE index, a foreign-key rule, or any other invariant, the database rejects it and rolls it back automatically.

Consistency is partly a property of the database engine and partly a responsibility of the application developer: the engine enforces schema-level rules, but business-level invariants (for example, "an order total must equal the sum of its line items") must be baked into the transaction logic. Constraints defined in the schema are guaranteed; ones that exist only in application code are not.

Isolation means that concurrent transactions execute as if they were sequential — each transaction gets a consistent view of the data and does not see the half-finished writes of another transaction that is still in progress. Full isolation (serialisability) is the safest but most expensive mode; real databases expose a spectrum of weaker isolation levels that trade safety for throughput.

The SQL standard defines four isolation levels, each preventing a wider class of read anomaly at increasing cost. Understanding the three anomalies is the key to choosing the right level:

Dirty read — Transaction A reads a row that Transaction B has modified but not yet committed. If B rolls back, A has seen data that never officially existed.

Non-repeatable read — Transaction A reads the same row twice; between the two reads, Transaction B commits an update to that row, so A sees different values each time.

Phantom read — Transaction A executes the same range query twice; between the reads, Transaction B inserts or deletes rows that match the range, so A sees a different set of rows each time.

Most production systems run at Read Committed — it prevents the dangerous dirty-read anomaly while maintaining high concurrency. Bump to Repeatable Read or Serializable only for reporting queries, financial reconciliation, or anywhere correctness is more important than raw throughput. Serializable isolation in modern databases like PostgreSQL uses Serializable Snapshot Isolation (SSI), which achieves full correctness with far less lock contention than traditional two-phase locking.

Durability guarantees that once a transaction has been committed, it will remain committed — even if the server crashes a millisecond later. The mechanism that makes this possible is the Write-Ahead Log (WAL, also called the redo log). The key insight is simple: a change must be written to the durable log before it is written to the actual data pages on disk. If the server crashes between the two, recovery replays the log to reconstruct the committed state.

WAL records are sequential and append-only, making them far faster to write than random in-place updates to data pages. This is why databases can handle commit-intensive workloads without committing each data page to disk synchronously on every write — only the WAL entry must be flushed. PostgreSQL calls this the WAL; MySQL/InnoDB calls it the redo log; the concept is identical.

The full lifecycle of an ACID transaction is: BEGIN (open the transaction and acquire any needed locks) → execute statements (writes buffered, not yet visible to others) → COMMIT (flush WAL, release locks, changes become durable and visible) or ROLLBACK (apply undo log, release locks, database returns to pre-transaction state). A transaction is also implicitly rolled back if the connection drops or a statement raises an unhandled error.

Savepoints let you roll back to a named checkpoint inside a transaction without discarding the whole transaction — useful when one part of a complex operation fails but you want to retry just that sub-step without restarting everything from BEGIN.

Not every system needs full ACID guarantees. NoSQL and distributed databases often opt for BASE (Basically Available, Soft-state, Eventually consistent) — a model that trades strict consistency for higher availability and lower latency across geographically distributed nodes. The CAP theorem frames the trade-off: in a network partition, a system must choose between consistency and availability. ACID systems (partition-intolerant) choose consistency; BASE systems choose availability and accept that replicas may temporarily diverge.

In practice most large systems are hybrid: an ACID relational store for the financial core (orders, payments, balances) alongside an eventually-consistent cache or document store for content, sessions, or analytics. Choosing the wrong model in either direction — BASE for financial records, ACID for massive-scale read replicas — creates serious correctness or performance problems.

Not necessarily. Many modern NoSQL databases have added ACID support. MongoDB 4.x introduced multi-document ACID transactions. Amazon DynamoDB Transactions offer full ACID semantics within a single region. Google Cloud Spanner provides globally distributed ACID transactions using TrueTime. The distinction today is less "SQL vs NoSQL" and more "what consistency model does your workload actually need?" — many systems benefit from a document or wide-column store that also provides transactions for critical operations.

Start with your database's default — Read Committed for PostgreSQL, Oracle, and SQL Server; Repeatable Read for MySQL InnoDB. These defaults work for most web application workloads. Escalate to Serializable only when your logic requires true snapshot integrity: generating end-of-day financial reports, running an audit query, or executing a reservation that must not double-book. Be aware that Serializable increases the risk of serialization failures (the database aborts a transaction and asks you to retry) under high concurrency, so your application code must handle retries.

The WAL is a performance enabler as much as it is a durability mechanism. Because WAL writes are sequential appends, they are far faster than random in-place page updates and take excellent advantage of modern NVMe SSDs. The main cost is the fsync at commit time — the database must wait for the OS to confirm the WAL buffer is on persistent storage before returning success to the client. You can tune this: PostgreSQL's synchronous_commit = off skips the fsync and acknowledges commits before the WAL hits disk, trading a small risk of losing the last few milliseconds of commits (without data corruption) for significantly higher write throughput. Use it for non-critical bulk imports, but never for financial or inventory writes.