ACID Properties

Overview

ACID stands for Atomicity, Consistency, Isolation, and Durability, the four guarantees that make transactions trustworthy. Atomicity means all-or-nothing, consistency keeps the database following its rules, isolation stops concurrent transactions from corrupting each other, and durability ensures committed data survives a crash. These properties are why relational databases are a safe place for critical data like payments.

Syntax / Usage

You rely on ACID by using transactions; the database enforces the guarantees. Isolation behavior can be tuned with isolation levels.

-- Atomicity + Durability: after COMMIT, both updates are permanent
BEGIN;
UPDATE accounts SET balance = balance - 50 WHERE id = 1;
UPDATE accounts SET balance = balance + 50 WHERE id = 2;
COMMIT;

-- Isolation: control how transactions see concurrent changes
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Examples

Consistency is enforced by constraints even inside a transaction:

BEGIN;
-- Fails if it would make balance negative and a CHECK forbids it
UPDATE accounts SET balance = balance - 999 WHERE id = 1;
COMMIT;  -- rejected, leaving data valid

A stricter isolation level prevents two transfers from reading stale balances:

BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM accounts WHERE id = 1;
UPDATE accounts SET balance = balance - 50 WHERE id = 1;
COMMIT;

Common Mistakes

• Assuming individual statements outside a transaction are still atomic across multiple rows
• Ignoring isolation levels and then being surprised by dirty or non-repeatable reads
• Believing durability protects against bad application logic; it only protects committed data
• Expecting the same strict ACID guarantees from every NoSQL store by default
• Using the lowest isolation level for speed without understanding the concurrency risks

See Also

databases-transactions databases-relational-basics databases-sql-vs-nosql