The Four ACID Properties
A — Atomicity
A transaction is all-or-nothing. If any part fails, the entire transaction rolls back as if it never happened.
C — Consistency
A transaction can only move the database from one valid state to another, never violating constraints or rules.
I — Isolation
Concurrent transactions don't interfere with each other — each one behaves as if it ran alone.
D — Durability
Once a transaction commits, it survives a crash, power loss, or restart — it's permanently saved.
The Bank Transfer Example
The classic way to understand ACID is a money transfer between two accounts:
- Atomicity: if the second UPDATE fails (e.g. account B doesn't exist), the first UPDATE is rolled back too — money is never deducted without being credited somewhere.
- Consistency: a constraint like
balance >= 0ensures the transfer is rejected if it would overdraw account A. - Isolation: if another transaction reads account A's balance while this transfer is mid-flight, it sees either the balance before or after the transfer — never a half-updated value.
- Durability: once COMMIT returns successfully, the new balances are on disk — even if the server loses power one millisecond later.
Isolation Levels and the Problems They Prevent
| 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 another transaction's uncommitted changes.
- Non-repeatable read: re-running the same query twice in one transaction gives different results because another transaction committed in between.
- Phantom read: a range query returns a different set of rows the second time, because rows were inserted/deleted in between.
⚠️ Higher Isolation = Lower Concurrency
Serializable isolation gives the strongest guarantees but forces transactions to effectively run one at a time when they conflict, hurting throughput. Most production systems default to Read Committed or Repeatable Read as a practical middle ground.
ACID vs BASE
| Aspect | ACID | BASE |
|---|---|---|
| Stands for | Atomicity, Consistency, Isolation, Durability | Basically Available, Soft state, Eventually consistent |
| Consistency model | Strong — always consistent | Eventual — converges over time |
| Typical systems | PostgreSQL, MySQL, Oracle, SQL Server | Cassandra, DynamoDB, early MongoDB |
| Optimised for | Correctness, financial/transactional data | Availability, partition tolerance, scale |
| Trade-off | Harder to scale horizontally | Temporary inconsistency is acceptable |
This trade-off connects directly to the CAP theorem — under a network partition, a system must choose between consistency and availability, and ACID systems generally lean toward consistency.
💡 Most NoSQL Databases Now Offer Partial ACID
MongoDB has supported multi-document ACID transactions since version 4.0. The SQL vs NoSQL line is no longer "ACID vs no ACID" — it's more about data model fit, scale requirements, and operational complexity.
How We Research and Update This Guide
We test the underlying formula or workflow, compare outputs with reliable references, and revise examples whenever the page content changes.
- The workflow or formula is tested directly in the tool and compared against independent reference examples.
- Examples are kept practical so readers can verify the result without hidden assumptions.
- Pages are revised whenever the interface, calculation flow, or surrounding guidance materially changes.
Frequently Asked Questions — ACID Properties
ACID stands for Atomicity, Consistency, Isolation, and Durability — four guarantees a database transaction system provides to ensure data stays correct even when failures, crashes, or concurrent access happen. Most relational databases (PostgreSQL, MySQL with InnoDB, Oracle, SQL Server) are built around these guarantees.
Atomicity means a transaction is all-or-nothing — every statement inside it either all succeed or all roll back together. Consistency means the database moves from one valid state to another valid state, respecting all constraints, triggers, and rules. Atomicity is about the transaction boundary; consistency is about never violating the rules of your data model.
Even fast transactions can overlap under load. Isolation controls what one transaction can see of another transaction's uncommitted changes. Without proper isolation, you can get problems like dirty reads (seeing uncommitted data), non-repeatable reads (the same query returns different results twice), and phantom reads (new rows appearing mid-transaction).
Many NoSQL databases historically relaxed ACID guarantees in favour of availability and partition tolerance, following the BASE model (Basically Available, Soft state, Eventually consistent) instead. However, modern NoSQL databases like MongoDB (since 4.0) now support multi-document ACID transactions, blurring the old SQL vs NoSQL line.
A bank transfer is the textbook example: debiting ₹500 from Account A and crediting ₹500 to Account B must happen together. Atomicity ensures both happen or neither does. Consistency ensures the total money in the system stays the same. Isolation ensures another transaction reading balances mid-transfer does not see a half-finished state. Durability ensures the transfer survives a server crash the instant after it commits.
From weakest to strongest: Read Uncommitted (allows dirty reads), Read Committed (default in PostgreSQL and SQL Server — no dirty reads), Repeatable Read (default in MySQL InnoDB — no dirty or non-repeatable reads), and Serializable (strongest — transactions behave as if executed one at a time, preventing phantom reads too, at the cost of concurrency).