ACID Properties Explained: Database Transactions

You're transferring money. The debit happens. Then your server crashes. Did the credit happen too?

Without ACID guarantees, you'd have to hope so. With them, either both happen or neither does.

What Is ACID?

ACID describes four properties that guarantee database transactions are processed reliably. It's not a new technology — it's a set of requirements that databases like PostgreSQL, MySQL, and Oracle implement.

ACID = Atomicity, Consistency, Isolation, Durability.

Atomicity: All or Nothing

A transaction either completes fully or not at all. No partial states.

BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;  -- Debit Alice
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;  -- Credit Bob
COMMIT;

If the second UPDATE fails (Bob's account doesn't exist), the database rolls back both changes. Alice keeps her $100. No money disappears.

Without atomicity: Alice loses $100, Bob gets nothing.

-- Explicit rollback on error
BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  
  -- Something goes wrong
  ROLLBACK;  -- First UPDATE is undone

Consistency: Rules Are Always Satisfied

A transaction moves the database from one valid state to another. It can't break your defined rules.

-- Define a constraint: balance can't go below 0
ALTER TABLE accounts ADD CONSTRAINT positive_balance CHECK (balance >= 0);
 
BEGIN;
  UPDATE accounts SET balance = balance - 500 WHERE id = 1;  -- Alice has $100
  -- This fails: CHECK constraint violated (balance would be -400)
  -- Transaction rolls back automatically
COMMIT;

Consistency means your data invariants are always true:

• Account balances stay non-negative
• Foreign keys are always valid
• Unique constraints are always respected

The database enforces this, not just your application code.

Isolation: Concurrent Transactions Don't Interfere

Multiple transactions running at the same time see consistent data, as if they ran one after another.

Without isolation, you get anomalies:

Dirty read: Transaction A reads data that Transaction B has modified but not committed yet. B then rolls back. A read data that never existed.

Non-repeatable read: Transaction A reads a row. Transaction B updates that row. A reads it again and gets a different value.

Phantom read: Transaction A queries rows matching a condition. Transaction B inserts a matching row. A runs the same query and gets a different count.

-- PostgreSQL isolation levels (increasing strictness)
 
-- READ COMMITTED (default): Only sees committed data. Prevents dirty reads.
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
 
-- REPEATABLE READ: Same row reads return same data within transaction. Prevents dirty + non-repeatable reads.
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
 
-- SERIALIZABLE: Fully isolated. Behaves as if transactions ran sequentially.
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Higher isolation = fewer anomalies = more locking = lower throughput. Most apps work fine with READ COMMITTED (default).

Use SERIALIZABLE for financial transactions where even phantom reads could cause problems (like double-spending attacks).

Durability: Committed Data Survives Crashes

Once a transaction commits, the data is permanently saved — even if the server crashes immediately after.

PostgreSQL achieves this with a Write-Ahead Log (WAL). Before writing to the actual data files, it writes the change to a log that's flushed to disk. On crash recovery, it replays the log.

BEGIN;
  INSERT INTO orders (user_id, total) VALUES (123, 100.00);
COMMIT;  -- At this point, data is on disk. Server crash won't lose it.

Without durability: you'd need to verify every write actually persisted.

A Complete Example

import psycopg2
 
def transfer_money(from_id: int, to_id: int, amount: float):
    conn = psycopg2.connect("postgresql://localhost/mydb")
    
    try:
        with conn:  # Context manager handles BEGIN/COMMIT/ROLLBACK
            with conn.cursor() as cur:
                # Check balance (read within transaction)
                cur.execute(
                    "SELECT balance FROM accounts WHERE id = %s FOR UPDATE",
                    (from_id,)
                )
                balance = cur.fetchone()[0]
                
                if balance < amount:
                    raise ValueError("Insufficient funds")
                
                # Debit
                cur.execute(
                    "UPDATE accounts SET balance = balance - %s WHERE id = %s",
                    (amount, from_id)
                )
                
                # Credit
                cur.execute(
                    "UPDATE accounts SET balance = balance + %s WHERE id = %s",
                    (amount, to_id)
                )
                
                # COMMIT happens automatically when context manager exits normally
                
    except Exception:
        # ROLLBACK happens automatically on exception
        raise
    finally:
        conn.close()

FOR UPDATE locks the row so no other transaction can modify it while you're reading and about to write.

ACID vs BASE

NoSQL databases often trade ACID for performance and scale. BASE is the opposite:

BASE = Basically Available, Soft state, Eventually consistent.

Cassandra returns a response immediately, even if it means returning slightly stale data. Eventually all nodes agree. That's fine for showing 1000 likes vs 999 likes. Not fine for bank balances.

When You Need ACID

Financial transactions: Money transfers, payments, ledger entries. Partial transactions cause real harm.

Inventory management: Overselling because two transactions both read "1 in stock" and both proceed.

User authentication: Creating a user + sending a welcome email + creating default settings all succeed, or none do.

Anywhere partial writes are catastrophic: Any multi-step operation where some steps completing without others would leave data in a broken state.

Transaction Pitfalls

Holding transactions too long: Long-running transactions hold locks. Other transactions wait. Throughput drops.

# Bad: holding transaction open while doing slow external work
with db.transaction():
    order = db.create_order(...)
    email_service.send_confirmation(order)  # Network call inside transaction!
    payment_service.charge(order)           # Another network call!
 
# Good: do DB work only inside transaction
order = db.create_order(...)  # Quick, inside transaction implicitly
# Then do external calls outside the transaction
email_service.send_confirmation(order)
payment_service.charge(order)

Unnecessary transactions: Wrapping every single query in an explicit transaction has overhead. Use transactions for groups of operations that must be atomic.

Too much isolation: SERIALIZABLE serializes transactions — much lower throughput. Use only when needed.

Key Takeaways

• Atomicity: all steps succeed or none do — no partial transactions
• Consistency: database constraints are always enforced
• Isolation: concurrent transactions don't see each other's partial work
• Durability: committed data survives crashes (WAL)
• Higher isolation levels prevent more anomalies but reduce throughput
• READ COMMITTED (default) is fine for most apps; use SERIALIZABLE for financial operations
• Keep transactions short — long transactions hold locks and hurt throughput
• NoSQL databases often trade ACID for availability and speed (BASE)

ACID is what lets you sleep at night when your app handles money. Use a database that supports it for anything where partial writes cause harm.

Related reading: Database Indexing Explained · CAP Theorem Explained · Database Sharding Explained