ACID is one of the landmark concepts that has helped database systems succeed and become so useful. It was actually introduced nearly half a century ago by Theo Haerder and Andreas Reuter.
Transactions #
To understand the ACID properties, it’s important to understand what a transaction is first.
Fundamentally, databases contain data that a system will read and write from. The data may be frequently accessed and by many consumers. These accesses need to be safe and provide a reliable view of the data, especially for critical use cases like finance or medicine.
A transaction groups together a series of operations so that they appear as a single unit of work to the outside world. Only if all the operations succeed, the transaction is then “committed”, which finalizes and writes the data. Otherwise, the transaction is “rolled back”, undoing any operation steps that were performed.
In this way, a transaction serves as an incredibly useful abstraction. However, the database needs to guarantee a few things to be able to support transactions.
ACID properties #
The ACID properties make transactions possible by keeping the database in a consistent state.
Atomicity #
Either all operations in a group succeed or none succeed.
For example, if one step fails, the entire transaction fails. The database state is rolled back to what it was before the transaction.
%%{init: {'theme': 'neutral' }}%%
graph TD
direction LR
subgraph "Person B's Balance"
direction TB
B1["$50"]
B2["$50"]
B3["$50"]
B4["$50"]
B5["$50"]
end
subgraph "Person A's Balance"
direction TB
A1["$100"]
A2["$90 (pending)"]
A3["$90 (pending)"]
A4["$100"]
A5["$100"]
end
subgraph Event
direction TB
E1["Transaction begins to transfer $10 from A to B"]
E2["$10 debited from A"]
E3["❌ Fail to credit $10 to B"]
E4["🔄 Transaction rolled back"]
E5["Balances are read"]
end
A1 --> A2 --> A3 --> A4 --> A5
B1 --> B2 --> B3 --> B4 --> B5
E1 --> E2 --> E3 --> E4 --> E5
Consistency #
When a transaction successfully commits, that becomes the updated database state. When multiple transactions happen after each other, the database will apply each transaction to the state the previous transaction left it in.
%%{init: {'theme': 'neutral' }}%%
graph TD
direction LR
subgraph "Person C's Balance"
direction TB
C1["$0"]
C2["$0"]
C3["$0"]
C4["$0"]
C5["$0"]
C6["$0"]
C7["$60 (pending)"]
C8["$60"]
C9["$60"]
end
subgraph "Person B's Balance"
direction TB
B1["$50"]
B2["$50"]
B3["$60 (pending)"]
B4["$60"]
B5["$60"]
B6["$0 (pending)"]
B7["$0"]
B8["$0"]
B9["$0"]
end
subgraph "Person A's Balance"
direction TB
A1["$100"]
A2["$90 (pending)"]
A3["$90 (pending)"]
A4["$90"]
A5["$90"]
A6["$90"]
A7["$90"]
A8["$90"]
A9["$90"]
end
subgraph Event
direction TB
E1["Transaction begins to transfer $10 from A to B"]
E2["$10 debited from A"]
E3["$10 credited to B"]
E4["✅ Transaction committed"]
E5["Transaction begins to transfer $60 from B to C"]
E6["$60 debited from B"]
E7["$60 credited to C"]
E8["✅ Transaction committed"]
E9["Balances are read"]
end
E1 --> E2 --> E3 --> E4 --> E5 --> E6 --> E7 --> E8 --> E9
A1 --> A2 --> A3 --> A4 --> A5 --> A6 --> A7 --> A8 --> A9
B1 --> B2 --> B3 --> B4 --> B5 --> B6 --> B7 --> B8 --> B9
C1 --> C2 --> C3 --> C4 --> C5 --> C6 --> C7 --> C8 --> C9
Isolation #
When multiple transactions happen concurrently, the database will apply each transaction after the other. This does not necessarily guarantee a specific order of the transactions, but it does ensure all of the steps happen in order within each transaction.
Suppose there are two transactions, TX and TY.
- TX transfers $10 from A to B
- TY transfers $60 from B to C
TX happens before TY #
%%{init: {'theme': 'neutral' }}%%
graph TD
direction LR
subgraph "Person C's Balance"
direction TB
C1["$0"]
C2["$0"]
C3["$60"]
C4["$60"]
end
subgraph "Person B's Balance"
direction TB
B1["$50"]
B2["$60"]
B3["$0"]
B4["$0"]
end
subgraph "Person A's Balance"
direction TB
A1["$100"]
A2["$90"]
A3["$90"]
A4["$90"]
end
subgraph Event
direction TB
E1["Start"]
E2["✅ Transaction TX committed to transfer $10 from A to B"]
E3["✅ Transaction TY committed to transfer $60 from B to C"]
E4["Balances are read"]
end
E1 --> E2 --> E3 --> E4
A1 --> A2 --> A3 --> A4
B1 --> B2 --> B3 --> B4
C1 --> C2 --> C3 --> C4
TY happens before TX #
%%{init: {'theme': 'neutral' }}%%
graph TD
direction LR
subgraph "Person C's Balance"
direction TB
C1["$0"]
C2["$60"]
C3["$60"]
C4["$60"]
end
subgraph "Person B's Balance"
direction TB
B1["$50"]
B2["$-10"]
B3["$0"]
B4["$0"]
end
subgraph "Person A's Balance"
direction TB
A1["$100"]
A2["$100"]
A3["$90"]
A4["$90"]
end
subgraph Event
direction TB
E1["Start"]
E2["✅ Transaction TY committed to transfer $60 from B to C"]
E3["✅ Transaction TX committed to transfer $10 from A to B"]
E4["Balances are read"]
end
E1 --> E2 --> E3 --> E4
A1 --> A2 --> A3 --> A4
B1 --> B2 --> B3 --> B4
C1 --> C2 --> C3 --> C4
Durability #
This essentially means that a committed transaction is written in stone. After a transaction is committed, later failures in the database will never undo the change that this transaction made.
%%{init: {'theme': 'neutral' }}%%
graph TD
direction LR
subgraph "Person B's Balance"
direction TB
B1["$50"]
B2["$50"]
B3["$60 (pending)"]
B4["$60"]
B5["$60"]
B6["$60"]
end
subgraph "Person A's Balance"
direction TB
A1["$100"]
A2["$90 (pending)"]
A3["$90 (pending)"]
A4["$90"]
A5["$90"]
A6["$90"]
end
subgraph Event
direction TB
E1["Transaction begins to transfer $10 from A to B"]
E2["$10 debited from A"]
E3["$10 credited to B"]
E4["✅ Transaction committed"]
E5["❌ Failure in the database occurs"]
E6["Balances are read"]
end
A1 --> A2 --> A3 --> A4 --> A5 --> A6
B1 --> B2 --> B3 --> B4 --> B5 --> B6
E1 --> E2 --> E3 --> E4 --> E5 --> E6
(Though, another successful transaction can reverse the change made by this original transaction.)
Supported database systems #
The popular relational database systems are all ACID compliant: Postgres, MySQL, SQL Server, and Oracle.
Some non-relational databases, such as MongoDB (document database) and Neo4j (graph database), are also ACID compliant.
Not all database systems support ACID (or even transactions) though, like for example, Apache Cassandra (a NoSQL database).