The four properties of transactions - Atomicity, Consistency, Isolation, Durability

ACID Properties

ACID represents the four core properties of database transactions that guarantee reliable data operations.

Overview

Property	Description
Atomicity	All operations in a transaction succeed or all fail
Consistency	Transaction brings database from one valid state to another
Isolation	Concurrent transactions are isolated from each other
Durability	Committed transactions are permanently saved, even after system failure

Atomicity

Atomicity ensures that a transaction is an indivisible unit of work.

Implementation

Write-Ahead Logging (WAL):
┌─────────────────────────────────────────────────────────┐
│ 1. Before modifying data, write changes to log first    │
│ 2. After log is persisted, modify actual data           │
│ 3. If crash occurs, can recover or rollback from log    │
└─────────────────────────────────────────────────────────┘

Transaction execution flow:
                    ┌──────────────┐
                    │ BEGIN TRANS  │
                    └──────┬───────┘
                           │
                    ┌──────▼───────┐
                    │ Write to WAL │──────────────┐
                    └──────┬───────┘              │
                           │                      │
                    ┌──────▼───────┐              │
                    │ Modify Data  │              │ Crash Recovery
                    └──────┬───────┘              │ uses WAL to
                           │                      │ recover/rollback
                    ┌──────▼───────┐              │
                    │    COMMIT    │──────────────┘
                    └──────────────┘

Undo Log vs Redo Log

Undo Log (Rollback Log):
┌─────────────────────────────────────────────────────────┐
│ Records data before modification                        │
│ Purpose: Restore original data during rollback          │
│ Example: UPDATE accounts SET balance=900 WHERE id=1     │
│         Undo: balance was 1000                          │
└─────────────────────────────────────────────────────────┘

Redo Log:
┌─────────────────────────────────────────────────────────┐
│ Records data after modification                         │
│ Purpose: Replay committed transactions after crash      │
│ Example: UPDATE accounts SET balance=900 WHERE id=1     │
│         Redo: set balance to 900                        │
└─────────────────────────────────────────────────────────┘

Code Example

# Python pseudocode demonstrating atomicity
def transfer_money(from_account, to_account, amount):
    try:
        db.begin_transaction()

        # Check balance
        balance = db.query("SELECT balance FROM accounts WHERE id = ?", from_account)
        if balance < amount:
            raise InsufficientFundsError()

        # Deduct
        db.execute("UPDATE accounts SET balance = balance - ? WHERE id = ?",
                   amount, from_account)

        # Deposit
        db.execute("UPDATE accounts SET balance = balance + ? WHERE id = ?",
                   amount, to_account)

        # All successful, commit
        db.commit()
        return True

    except Exception as e:
        # Any error, rollback all operations
        db.rollback()
        raise e

Consistency

Consistency ensures transactions only transition the database from one valid state to another.

Consistency Constraints

-- Database-level constraints
CREATE TABLE accounts (
    id SERIAL PRIMARY KEY,
    balance DECIMAL(10, 2) NOT NULL,
    CONSTRAINT positive_balance CHECK (balance >= 0)  -- Balance cannot be negative
);

CREATE TABLE transfers (
    id SERIAL PRIMARY KEY,
    from_account INT REFERENCES accounts(id),
    to_account INT REFERENCES accounts(id),
    amount DECIMAL(10, 2) CHECK (amount > 0),         -- Amount must be positive
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Application-level consistency rule
-- Rule: Sum of all account balances must remain constant
-- Before transfer: 1000 + 500 = 1500
-- After transfer 100: 900 + 600 = 1500 ✓

Consistency Example

Before transfer (valid state):
┌─────────────────────────────────┐
│ Account A: $1000                │
│ Account B: $500                 │
│ Total: $1500                    │
└─────────────────────────────────┘

During transaction (temporarily inconsistent):
┌─────────────────────────────────┐
│ Account A: $900  (-$100)        │
│ Account B: $500  (pending)      │
│ Total: $1400 (temporarily)      │
│ This state is NOT visible!      │
└─────────────────────────────────┘

After transfer (valid state):
┌─────────────────────────────────┐
│ Account A: $900                 │
│ Account B: $600                 │
│ Total: $1500 ✓                  │
└─────────────────────────────────┘

Isolation

Isolation ensures concurrent transactions are isolated from each other; intermediate states are not visible to other transactions.

Concurrency Problems

Problem	Description
Dirty Read	Reading uncommitted data from other transactions
Non-repeatable Read	Two reads in same transaction return different results
Phantom Read	Same query returns different row sets
Lost Update	Two transactions update simultaneously, one overwrites the other

Dirty Read Example

Transaction A                    Transaction B
─────────────────────────────────────────────────────
BEGIN;                           BEGIN;

UPDATE accounts
SET balance = 900
WHERE id = 1;
                                 -- Reads uncommitted 900 (dirty read)
                                 SELECT balance FROM accounts
                                 WHERE id = 1;  -- Returns 900

ROLLBACK;                        -- A rolled back! B's data is invalid
                                 -- But B may have made decisions based on 900

Non-repeatable Read Example

Transaction A                    Transaction B
─────────────────────────────────────────────────────
BEGIN;                           BEGIN;

SELECT balance FROM accounts
WHERE id = 1;  -- Returns 1000

                                 UPDATE accounts
                                 SET balance = 900
                                 WHERE id = 1;
                                 COMMIT;

SELECT balance FROM accounts
WHERE id = 1;  -- Returns 900
-- Same transaction, two queries return different results!

Durability

Durability guarantees that once a transaction is committed, its results are permanently saved, even if the system crashes.

Implementation

Durability guarantee flow:
┌─────────────────────────────────────────────────────────┐
│ 1. Transaction changes written to WAL                   │
│ 2. WAL force-flushed to disk (fsync)                    │
│ 3. Return COMMIT success                                │
│ 4. Background async flush data pages to disk            │
└─────────────────────────────────────────────────────────┘

Crash recovery:
┌─────────────────────────────────────────────────────────┐
│ 1. Database scans WAL on startup                        │
│ 2. Replay redo log for committed but unflushed txns     │
│ 3. Apply undo log for uncommitted transactions          │
│ 4. Recover to consistent state                          │
└─────────────────────────────────────────────────────────┘

ACID in Practice

E-commerce Order Example

BEGIN;

-- 1. Check inventory (lock to prevent overselling)
SELECT stock FROM products WHERE id = 101 FOR UPDATE;
-- stock = 5

-- 2. Reduce inventory
UPDATE products SET stock = stock - 1 WHERE id = 101;

-- 3. Create order
INSERT INTO orders (user_id, total, status)
VALUES (1, 99.99, 'pending')
RETURNING id;
-- order_id = 12345

-- 4. Create order items
INSERT INTO order_items (order_id, product_id, quantity, price)
VALUES (12345, 101, 1, 99.99);

-- 5. Deduct user balance
UPDATE users SET balance = balance - 99.99 WHERE id = 1;

-- All successful, commit transaction
COMMIT;

-- A: Any step fails, all rollback
-- C: Inventory decrease = Order increase, consistent
-- I: Other transactions don't see intermediate state
-- D: After commit, order data persists even if crash

ACID vs BASE

ACID	BASE
Atomicity	Basically Available
Consistency	Soft state
Isolation	Eventually consistent
Durability

ACID: Suitable for finance, orders requiring strong consistency
BASE: Suitable for social, recommendations requiring high availability

Choice depends on business needs:
- Bank transfer → ACID
- Like count → BASE
- Inventory deduction → ACID
- Browsing history → BASE

ACID Properties

On this page