↓Skip to main content

Database ACID properties — what they mean and why they matter

·5 mins
Databases
Table of Contents

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 Reuter1.

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.

Event

Transaction begins to transfer $10 from A to B

$10 debited from A

❌ Fail to credit $10 to B

🔄 Transaction rolled back

Balances are read

Person A's Balance

$100

$90 (pending)

$90 (pending)

$100

$100

Person B's Balance

$50

$50

$50

$50

$50

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.

Event

Transaction begins to transfer $10 from A to B

$10 debited from A

$10 credited to B

✅ Transaction committed

Transaction begins to transfer $60 from B to C

$60 debited from B

$60 credited to C

✅ Transaction committed

Balances are read

Person A's Balance

$100

$90 (pending)

$90 (pending)

$90

$90

$90

$90

$90

$90

Person B's Balance

$50

$50

$60 (pending)

$60

$60

$0 (pending)

$0

$0

$0

Person C's Balance

$0

$0

$0

$0

$0

$0

$60 (pending)

$60

$60

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 #

Event

Start

✅ Transaction TX committed to transfer $10 from A to B

✅ Transaction TY committed to transfer $60 from B to C

Balances are read

Person A's Balance

$100

$90

$90

$90

Person B's Balance

$50

$60

$0

$0

Person C's Balance

$0

$0

$60

$60

TY happens before TX #

Event

Start

✅ Transaction TY committed to transfer $60 from B to C

✅ Transaction TX committed to transfer $10 from A to B

Balances are read

Person A's Balance

$100

$100

$90

$90

Person B's Balance

$50

$-10

$0

$0

Person C's Balance

$0

$60

$60

$60

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.

Event

Transaction begins to transfer $10 from A to B

$10 debited from A

$10 credited to B

✅ Transaction committed

❌ Failure in the database occurs

Balances are read

Person A's Balance

$100

$90 (pending)

$90 (pending)

$90

$90

$90

Person B's Balance

$50

$50

$60 (pending)

$60

$60

$60

(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).

  1. Theo Haerder and Andreas Reuter. 1983. Principles of transaction-oriented database recovery. ACM Comput. Surv. 15, 4 (December 1983), 287–317. https://doi.org/10.1145/289.291 ↩︎

↑