Skip to content

ACID Properties

Understand Atomicity, Consistency, Isolation, and Durability in database transactions.

What you'll learn

  • What ACID stands for and why each property matters
  • Atomicity — all or nothing
  • Consistency — valid state before and after
  • Isolation levels — Read Uncommitted to Serializable
  • Durability — committed data survives crashes

Concept

ACID Overview

ACID is a set of four properties that guarantee reliable processing of database transactions. They were defined by Andreas Reuter and Theo Härder in 1983 and remain the gold standard for transactional integrity. Relational databases like PostgreSQL and MySQL (with InnoDB) provide full ACID compliance; many NoSQL databases relax these properties for better scalability (the BASE model).

Atomicity

Atomicity means a transaction is an indivisible unit: either all its operations succeed, or none take effect. If you transfer money between accounts and the system crashes mid-transfer, the partial change is rolled back. There is no "half-applied" state. This is enforced by the transaction log (WAL — Write-Ahead Log).

Consistency

Consistency means a transaction takes the database from one valid state to another. All constraints (primary keys, foreign keys, checks, triggers) must be satisfied after the transaction. If a transaction would violate a constraint, it is rolled back. Consistency is partly enforced by the database (constraints) and partly by the application (business rules).

Isolation

Isolation determines how concurrent transactions interact. The SQL standard defines four isolation levels:

  • Read Uncommitted — can read uncommitted data (dirty reads); rarely used
  • Read Committed — only sees committed data; default in PostgreSQL
  • Repeatable Read — same query returns the same result within a transaction
  • Serializable — transactions behave as if executed one at a time; strongest but slowest

Higher isolation means more safety but less concurrency and performance.

Durability

Durability means once a transaction is committed, its changes survive system crashes, power failures, and restarts. This is achieved by writing committed changes to non-volatile storage (disk or SSD) before acknowledging the commit. The write-ahead log (WAL) ensures that on restart, the database can replay committed transactions and undo uncommitted ones.

Example

              -- Set isolation level for a transaction
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;

  -- Read a product's stock
  SELECT stock FROM products WHERE id = 42;

  -- Another transaction cannot modify this row at
  -- SERIALIZABLE level until we commit or rollback

  -- Decrement stock and create an order
  UPDATE products SET stock = stock - 1 WHERE id = 42 AND stock > 0;
  INSERT INTO orders (product_id, user_id, total)
    VALUES (42, 1, 19.99);

COMMIT;

-- Check current isolation level (PostgreSQL)
SHOW transaction_isolation;

-- Set default isolation level for the session
SET SESSION CHARACTERISTICS AS TRANSACTION
  ISOLATION LEVEL READ COMMITTED;
            

Setting SERIALIZABLE isolation for a critical read-then-write transaction, checking the current level, and setting a session default.

Try it

Common mistakes

The mistake

Using the default isolation level for critical read-then-write patterns

The fix

Read Committed (the default) allows non-repeatable reads. For check-then-act patterns (e.g. check stock then decrement), use REPEATABLE READ or SERIALIZABLE, or use SELECT ... FOR UPDATE.

The mistake

Assuming ACID means no concurrency issues

The fix

ACID guarantees per-transaction integrity, but concurrent transactions can still conflict. Choose the right isolation level and use explicit locks when needed.

Practice ACID Properties

3 exercises

Practice ACID Properties

Look up unfamiliar terms

A beginner-friendly glossary explains jargon in plain English — variables, functions, DOM, Promise, and more.

View glossary

Build real projects

Apply what you learned by building guided projects with starter code and solutions.

View projects

Decode error messages

Plain-English explanations of common errors — what they mean, why they happen, and how to fix them.

View errors