ACID
ACID
ACID is a set of four properties that guarantee database transactions are processed reliably. A transaction is a unit of work that either completes fully or has no effect at all. ACID properties ensure that even under concurrent access, hardware failures, or application crashes, the database remains in a valid, consistent state.
Understanding ACID is essential for designing systems that handle money, inventory, or any data where partial updates are unacceptable.
The Four Properties
Atomicity
All operations in a transaction succeed together, or none of them take effect. There is no partial commit.
Example: transferring $100 from Account A to Account B requires two writes: debit A and credit B. If the debit succeeds but the credit fails (crash, constraint violation), the transaction rolls back — Account A is not debited.
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 'A';
UPDATE accounts SET balance = balance + 100 WHERE id = 'B';
COMMIT; -- both succeed, or ROLLBACK undoes both
Consistency
A committed transaction moves the database from one valid state to another. All constraints, triggers, and rules are enforced. A transaction that would violate a constraint (e.g., a negative balance check constraint) is rejected.
Note: consistency in ACID is about database-level rules (constraints, foreign keys, triggers). It is different from the "C" in CAP theorem, which refers to distributed consistency.
Isolation
Concurrent transactions do not observe each other's intermediate (uncommitted) states. The degree of isolation is controlled by isolation levels:
| Isolation Level | Dirty Read | Non-Repeatable Read | Phantom Read |
|---|---|---|---|
| Read Uncommitted | ✓ possible | ✓ possible | ✓ possible |
| Read Committed | ✗ prevented | ✓ possible | ✓ possible |
| Repeatable Read | ✗ prevented | ✗ prevented | ✓ possible |
| Serializable | ✗ prevented | ✗ prevented | ✗ prevented |
- Dirty read: reading uncommitted data from another transaction (which may roll back).
- Non-repeatable read: reading the same row twice in a transaction and getting different values because another transaction committed a change between reads.
- Phantom read: a query returns different rows on two executions because another transaction inserted or deleted rows.
Default in most databases: Read Committed (SQL Server, PostgreSQL). Serializable is the strongest but has the highest lock contention.
Durability
Once a transaction commits, its changes survive crashes. The database achieves this through write-ahead logging (WAL): changes are written to a durable log before being applied to data pages. On recovery, the log is replayed to restore committed state.
Pitfalls
Choosing the Wrong Isolation Level
What goes wrong: using Read Committed for financial calculations allows non-repeatable reads — a balance check and a debit in the same transaction can see different values if another transaction commits between them.
Why it happens: Read Committed is the default and feels "safe enough."
Mitigation: use Serializable or Repeatable Read for transactions that read and then write based on what they read (read-modify-write patterns). Use optimistic concurrency (row version/timestamp) as a lighter alternative to Serializable.
Long-Running Transactions
What goes wrong: a transaction holds locks for seconds or minutes, blocking other transactions and causing timeouts.
Why it happens: application code performs slow operations (HTTP calls, file I/O) inside a transaction.
Mitigation: keep transactions short. Do all I/O outside the transaction. Only open the transaction for the database operations themselves.
Tradeoffs
ACID vs BASE
| Dimension | ACID | BASE |
|---|---|---|
| Consistency | Strong (every commit is valid) | Eventual (replicas converge over time) |
| Availability | Lower under partition (must reject or delay) | Higher (accept writes, reconcile later) |
| Latency | Higher (coordination, locking) | Lower (no global coordination) |
| Use case | Financial transactions, inventory, bookings | Caches, DNS, social feeds, analytics |
Decision rule: use ACID for any data where partial updates are unacceptable (money, inventory, reservations). Use BASE (eventual consistency) for data where temporary divergence is tolerable and throughput matters more than strict correctness.
Isolation level cost
| Level | Anomalies prevented | Lock contention | When to use |
|---|---|---|---|
| Read Committed | Dirty reads | Low | Default; safe for most reads |
| Repeatable Read | Dirty + non-repeatable | Medium | Read-modify-write patterns |
| Serializable | All anomalies | High | Financial calculations, inventory decrement |
| Snapshot (SQL Server) | All anomalies | Low (optimistic) | High-read, low-conflict workloads |
Decision rule: start with Read Committed (the default). Upgrade to Repeatable Read or Serializable only for transactions that read and then write based on what they read. Use Snapshot isolation when you need Serializable semantics without the lock contention.
// Optimistic concurrency as a lighter alternative to Serializable
// EF Core: rowversion column prevents lost updates without table locks
public sealed class Account
{
public int Id { get; set; }
public decimal Balance { get; set; }
[Timestamp]
public byte[] RowVersion { get; set; } = []; // EF Core concurrency token
}
// If another transaction committed between our read and write,
// EF throws DbUpdateConcurrencyException — retry or surface conflict to user
await db.SaveChangesAsync();
Questions
Use Serializable or Repeatable Read. Read Committed (the default in SQL Server and PostgreSQL) allows non-repeatable reads: a balance check and a debit in the same transaction can see different values if another transaction commits between them. For financial calculations or inventory updates, this causes incorrect results. Serializable prevents all anomalies but has the highest lock contention. Repeatable Read prevents non-repeatable reads with lower overhead. Optimistic concurrency (row version/timestamp) is a lighter alternative to Serializable for low-conflict workloads.
Before applying any change to data pages, the database writes the change to a sequential log file (WAL). The log write is synchronous — the transaction does not commit until the log entry is flushed to durable storage. On crash recovery, the database replays the log to restore all committed transactions and undo any uncommitted ones. WAL makes durability cheap: sequential log writes are fast; the actual data page updates can be deferred (write-behind). This is why databases can commit thousands of transactions per second despite disk I/O.
References
- ACID (Wikipedia) — comprehensive overview of all four properties with historical context and database implementation details.
- Transaction isolation levels (PostgreSQL docs) — PostgreSQL's implementation of isolation levels with concrete examples of each anomaly.
- Transaction isolation in SQL Server (Microsoft Learn) — SQL Server's locking and row versioning guide; covers all isolation levels including the SNAPSHOT isolation level unique to SQL Server.
- Designing Data-Intensive Applications (Martin Kleppmann) — Chapter 7 covers transactions in depth: ACID semantics, isolation levels, and the tradeoffs between consistency and performance in distributed systems.