ACID describes what a database transaction protects. A transaction groups reads and writes behind one commit decision. Atomicity prevents partial commit, consistency preserves declared invariants, isolation constrains concurrent histories, and durability defines what survives after commit.
These guarantees stop at the participating database and the isolation and durability settings it uses. They do not make a retried business operation idempotent, turn asynchronous replication into synchronous durability, or extend one local commit across other services.
The Four Properties
Atomicity
Atomicity gives the transaction one final outcome: all of its writes commit, or none of them do. A failed transaction must not leave a subset of its writes committed. Whether another transaction can observe tentative writes before that outcome is an isolation question, not an atomicity guarantee.
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 bothThe useful test is a failure trace, not the happy path:
| Event | Without one transaction | With one transaction |
|---|---|---|
| Debit A from 400 | Visible immediately | Tentative change owned by the transaction |
| Process crashes before crediting B | A stays at 100 disappeared | Recovery aborts the uncommitted transaction. A returns to $500 |
Both writes finish and COMMIT succeeds | Two unrelated writes happened to finish | Debit and credit become one committed outcome |
Atomicity does not make the transfer request safe to repeat. If the client times out after the server commits, retrying the same transaction can transfer another $100. An idempotency key or transfer ID stored under a uniqueness constraint lets a retry return the first result instead of applying the business operation again.
Consistency
A committed transaction preserves the invariants the system actually enforces. A database can check CHECK (balance >= 0), foreign keys, uniqueness, and trigger logic. It cannot infer a rule that exists only in application code. Cross-row rules such as “only one active booking may exist” need a constraint where one is expressible, or a transaction that protects the full predicate while it checks and writes.
ACID consistency is the validity of state before and after a transaction. The “C” in CAP concerns whether clients observe one current value across replicas during failures. They are different properties.
Isolation
Isolation controls what concurrent transactions may observe and which combined histories may commit. The name alone is not enough. The configured isolation level and the engine’s implementation define the contract:
| SQL level | Dirty read | Non-repeatable read | Phantom read | Serialization anomaly |
|---|---|---|---|---|
| Read Uncommitted | Possible | Possible | Possible | Possible |
| Read Committed | Prevented | Possible | Possible | Possible |
| Repeatable Read | Prevented | Prevented | Possible | Possible |
| Serializable | Prevented | 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.
The table gives the SQL standard’s minimum guarantees. Engines may provide more. PostgreSQL maps Read Uncommitted to Read Committed, and its Repeatable Read prevents phantoms while still allowing serialization anomalies. SQL Server Read Committed can use locks or row versions, depending on configuration. Each product’s documentation, not the generic table, defines the guarantees a specific engine provides.
MVCC and Snapshot Isolation
Engines implement isolation with locking, MVCC/versioning, or a combination. Database Locks covers lock modes and escalation. Under MVCC, writes create versions and a snapshot determines which version a statement can see. PostgreSQL Read Committed takes a new snapshot for each statement. Repeatable Read and Serializable keep a transaction snapshot. SQL Server uses row versioning for READ_COMMITTED_SNAPSHOT and SNAPSHOT when those modes are enabled.
MVCC removes much ordinary reader-writer blocking. It does not remove contention. Writers still conflict with writers, explicit locks can block, and schema changes may need stronger protection. Old versions also remain until no active snapshot needs them. PostgreSQL cleans dead tuples through VACUUM. SQL Server keeps row versions in its version store.
Write Skew — the Anomaly the Table Misses
The familiar dirty-read, non-repeatable-read, and phantom list misses some invalid histories. Snapshot isolation can permit write skew:
| Transaction A | Transaction B |
|---|---|
| Reads Alice and Bob: both are on call | Reads the same snapshot: both are on call |
| Sets Alice off call | Sets Bob off call |
| Commits because it changed only Alice | Commits because it changed only Bob |
Each transaction sees another doctor on call, then updates a different row. Both local decisions look valid. The combined result is not. Preventing it requires protecting the complete invariant with a constraint, explicit row or predicate protection, or Serializable isolation. PostgreSQL Serializable detects dangerous read/write dependencies and aborts one transaction with SQLSTATE 40001. Recovery then requires retrying the whole transaction, including every read and decision. Repeating only the final UPDATE reuses stale reasoning.
Durability
Durability begins when the engine acknowledges commit under its configured policy. With write-ahead logging (WAL), redo information reaches the log before the corresponding data pages. A strict commit waits for the commit record to reach durable storage. Recovery can then replay committed changes whose data pages were not written before the crash.
NOTE
Durability is a configured contract. Group commit can preserve local durability while several transactions share one log flush. PostgreSQL
synchronous_commit = offmay acknowledge before local WAL is flushed and accepts a bounded crash-loss window for lower commit latency. Replication is a separate choice about surviving node loss and availability: asynchronous replicas may lag, while synchronous replication adds a remote acknowledgement to the commit path. See Replication.
A committed row can still disappear when storage hardware lies about flush completion, a controller is misconfigured, or an operator restores an older backup. ACID defines the database protocol; the end-to-end durability claim also depends on the storage stack and recovery procedure.
Beyond a Single Database
A local database owns its commit decision. Once one operation spans two databases or services, no participant can provide atomicity by itself:
- Two-Phase Commit (2PC) — a coordinator asks every participant to prepare (vote), then commit or abort all together. A participant that has prepared but cannot learn the coordinator’s decision may have to retain locks and recovery state until the decision becomes available. Presumed-abort/commit variants, replicated coordinators, and timeout policies change the operational boundary but do not let a prepared participant decide independently.
- Saga pattern — instead of one distributed transaction, run a sequence of local ACID transactions, each with a compensating action or forward-recovery step when later work fails. This trades one atomic commit for explicit intermediate states, idempotency, compensation limits, and eventual completion. It fits only when the business operation can tolerate those semantics. See Distributed Transactions.
Concurrent transactions can also deadlock when each holds a resource the other needs. The engine chooses a victim and rolls it back, so transactional code needs a bounded retry policy. Deadlocks covers the wait cycle. A retry is safe only when an ambiguous commit result cannot apply the business operation twice.
Pitfalls
Choosing the Wrong Isolation Level
What goes wrong: a decision reads several rows under Read Committed, then a later statement acts after another transaction has changed part of that input. The transaction commits a decision that was never valid for one consistent view.
Why it happens: Read Committed is a common default, and the absence of dirty reads is mistaken for full transaction isolation.
Mitigation: the mechanism must fit the invariant. Repeatable Read generically keeps repeated row reads stable, but its implementation is engine-specific: PostgreSQL uses a transaction snapshot and prevents phantoms, while lock-based implementations can protect reread rows yet still permit phantoms. Either model may still allow serialization anomalies such as write skew. A decision spanning rows or a predicate needs Serializable isolation, complete lock coverage, an enforceable constraint, or an application-specific optimistic protocol that protects all decision inputs, including predicate and absent-row changes.
Long-Running Transactions
What goes wrong: a transaction holds locks or retains old versions for seconds or minutes. Other work blocks, aborts, or accumulates cleanup pressure.
Why it happens: application code performs an HTTP call, file operation, or user wait inside the transaction.
Mitigation: the transaction should cover only the database work that must share one commit decision. Unrelated external I/O belongs before or after it, with an outbox or another explicit handoff when later work must be reliable.
Tradeoffs
Transaction semantics
One transaction fits when several reads and writes share one invariant and one database can enforce it. The appropriate isolation level is the weakest one that prevents the histories capable of breaking that invariant. Stronger isolation spends coordination through blocking, dependency tracking, or retries. For a workflow across services, the choice is between distributed commit and explicit local transactions with messages and compensation.
Replication, consistency, and availability
ACID does not determine replica freshness or behavior during a partition. An ACID database can serve stale reads from asynchronous replicas or acknowledge a locally durable write before any replica has it. A leader or quorum can provide stronger read consistency. During a partition, topology and configuration determine which side accepts writes and whether conflicts can arise.
| Distributed decision | Stricter coordination | Less-coordinated alternative | Cost to make explicit |
|---|---|---|---|
| Read freshness | Leader, quorum, or linearizable read | Replica read with bounded staleness or eventual convergence | Latency and availability versus stale results |
| Write behavior during partition | Reject or delay writes without the required leader/quorum | Accept writes in multiple partitions and reconcile | Reduced write availability versus conflict semantics |
| Node-loss durability | Wait for synchronous replica acknowledgement | Acknowledge after local durability and replicate asynchronously | Commit latency versus acknowledged-write loss on failover |
Isolation level cost
| Level | Anomalies prevented | Lock contention | When to use |
|---|---|---|---|
| Read Committed | Dirty reads | Low | Independent point reads and writes whose invariants are enforced by constraints |
| Repeatable Read | Dirty + non-repeatable. Vendor implementations may prevent more | Medium | Stable transaction snapshots whose cross-row invariants have separate protection |
| Serializable | Serialization anomalies | Highest coordination or abort rate | Predicate-spanning financial, inventory, and booking invariants |
| Snapshot (SQL Server) | Dirty, non-repeatable, and phantom reads. Write skew remains possible | Low read-write contention (versioned) | High-read workloads whose cross-row invariants have separate protection |
Read Committed is a common starting point for independent point operations, though it is not every engine’s default. Repeatable Read fits work that needs stable rereads. A snapshot-based implementation such as PostgreSQL’s also gives one transaction snapshot, while a lock-based implementation may expose a different phantom boundary. Explicit Snapshot isolation provides a stable versioned view but can still allow write skew. Serializable fits when a predicate-spanning invariant must hold across concurrent transactions, and the complete transaction must then be designed for retries.
// Optimistic concurrency for one row: not a substitute for Serializable on cross-row or predicate invariants
// EF Core: a rowversion token detects a concurrent change to this row, preventing a lost update there; the UPDATE still takes normal write 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
What does ACID mean for a database transaction, and where do its guarantees stop?
ACID names four guarantees a database gives a transaction, a group of reads and writes that ends in one commit decision. They hold only inside that database and only as strongly as its configured isolation and durability settings allow.
- Atomicity: the transaction has one outcome. Either all of its writes commit or none do, so a crash or error cannot leave a partial commit.
- Consistency: a correct transaction moves the data from one valid state to another. Database constraints such as foreign keys, uniqueness and
CHECKenforce the rules the database can express. Application logic must enforce the rest.- Isolation: concurrent transactions are limited in what they can observe of each other and in which combined results can commit. The configured isolation level sets how strict this is.
- Durability: once the database acknowledges a commit, the changes survive the failures its durability configuration covers, such as a crash.
The guarantees stop at these boundaries:
- Configuration: weaker settings narrow the guarantees. For example, PostgreSQL asynchronous commit can acknowledge a commit before it is durably logged, so a crash can lose the most recently committed transactions.
- Retries: atomicity does not make an operation safe to repeat. If a client times out after the server has committed and then retries, the work is applied twice. Making the operation idempotent prevents this, for example with a unique request ID.
- Other databases and services: a local commit cannot include writes elsewhere. Work that spans services needs a separate protocol, such as two-phase commit or a saga of local transactions.
Microsoft: ACID properties · PostgreSQL: Asynchronous commit
What is write skew, and why can snapshot isolation allow it?
Write skew is a concurrency anomaly in which two transactions read the same data, each makes a decision that is valid for what it read, and each writes a different row. Both commit, but the combined result breaks an invariant that neither transaction broke alone.
Example: a rule requires at least one doctor on call, and Alice and Bob are both on call.
Step Transaction A Transaction B Read Sees Alice and Bob on call Sees the same state Decide Bob remains, so Alice may leave Alice remains, so Bob may leave Write Sets Alice off call Sets Bob off call Commit Succeeds Succeeds No doctor is left on call.
Snapshot isolation gives each transaction a stable snapshot. Each transaction checks the rule against a snapshot that does not contain the other’s change. Because they update different rows, there is no concurrent update of the same row to reject, so both commit. The problem is a read-write dependency: each transaction writes data that the other read when deciding.
Preventing write skew requires protecting the whole invariant: a constraint where it can be expressed, locks covering every row or the predicate the decision read, or Serializable isolation. PostgreSQL’s Serializable level detects this kind of dependency and aborts one transaction with a serialization failure. The application then retries the whole transaction.
How should concurrency protection be chosen for a read-modify-write transaction?
Concurrency protection is chosen from the invariant the decision depends on, not from the row being written. The mechanism must protect every input that informed the decision: the rows read, any predicate such as ‘at least one doctor on call’, and rows whose absence mattered.
Mechanisms that can cover the whole decision:
- Database constraint: when the invariant can be expressed as a constraint, the database rejects any commit that would violate it, whatever the interleaving.
- Serializable isolation: the database commits only results that some one-at-a-time order of the transactions could have produced. It may abort a transaction to enforce this. The whole transaction, including its reads and decisions, must then be retried. Retrying only the final write would reuse stale reasoning.
- Locks: explicit locks protect the decision only when they cover every relevant row or the whole predicate.
- Application-specific optimistic check: valid only when it detects a change to any decision input, not just to the updated row.
Two choices that look sufficient but are not:
- Repeatable Read keeps repeated reads or the transaction snapshot stable, depending on the engine. It can still allow write skew: two transactions read the same rows, each updates a different row, and together they break the invariant.
- A row-version token on the updated entity detects a concurrent change to that row only. It misses rows inserted or deleted under the predicate and changes to other rows that were read.
PostgreSQL 18: Transaction isolation · Microsoft: EF Core concurrency conflicts