ACID Properties

1 Atomicity - All or Nothing

Either every statement in the transaction succeeds or the DB rolls back to its previous state.

BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

If any statement fails, the DBMS issues a ROLLBACK and none of the updates persist.

2 Consistency - Valid States Only

The transaction must bring the database from one valid state to another, respecting constraints and business rules.

BEGIN TRANSACTION;
IF (SELECT balance FROM accounts WHERE id = 1) >= 100 THEN
    UPDATE accounts SET balance = balance - 100 WHERE id = 1;
    UPDATE accounts SET balance = balance + 100 WHERE id = 2;
    COMMIT;
ELSE
    ROLLBACK;
END IF;

3 Durability - Changes Stick

Once committed, the effects survive power loss or crashes. DBMSes journal/redo logs to ensure committed data can be replayed after restarts.

4 Isolation - Controlled Overlap

Concurrent transactions should not interfere. Isolation levels balance data freshness vs. anomalies.

Isolation anomalies

Isolation Levels

Level	Guarantees	Allows	Typical Use
Read Uncommitted	Minimal	Dirty reads	Rare; debugging
Read Committed	No dirty reads	Non-repeatable reads, phantom reads	Default in many RDBMS
Repeatable Read	Stable rows within txn	Phantom reads	Financial/reporting
Serializable	Full serial equivalence	Reduced concurrency	Strict correctness

Examples

Repeatable Read

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
SELECT stock FROM products WHERE id = 1; -- locks row
SELECT stock FROM products WHERE id = 1; -- same result
COMMIT;

Read Committed

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN;
SELECT stock FROM products WHERE id = 1; -- latest committed value
-- Another transaction commits an update here
SELECT stock FROM products WHERE id = 1; -- sees new value
COMMIT;

Read Uncommitted

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
BEGIN;
SELECT stock FROM products WHERE id = 1; -- may read uncommitted changes
COMMIT;

Serializable

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;
SELECT stock FROM products WHERE id = 1; -- blocks concurrent writers
UPDATE products SET stock = stock - 1 WHERE id = 1;
COMMIT;

Putting it Together

Property	Tagline	Enforced By
Atomicity	All-or-nothing	Logging, undo segments
Consistency	Valid states only	Constraints, application logic
Isolation	Controlled concurrency	Locks, MVCC, isolation levels
Durability	Survives crashes	Write-ahead logging, replication