Transactions & ACID

A transaction is a unit of work that is either fully completed or fully rolled back. ACID properties: Atomicity (all-or-nothing), Consistency (valid state transitions), Isolation (concurrent transactions don't interfere), Durability (committed data survives crashes). Isolation levels trade correctness for performance: Read Uncommitted → Read Committed → Repeatable Read → Serializable.

Key Concepts

ACID Properties

Property	Meaning	Mechanism
Atomicity	All operations succeed or all fail	Transaction log, rollback
Consistency	Database moves between valid states	Constraints, triggers
Isolation	Concurrent transactions don't interfere	Locks, MVCC
Durability	Committed data is permanent	Write-Ahead Log (WAL)

Deep Dive: Isolation Levels

Level	Dirty Read	Non-Repeatable Read	Phantom Read	Performance
Read Uncommitted	✅ possible	✅ possible	✅ possible	Fastest
Read Committed	❌ prevented	✅ possible	✅ possible	Fast
Repeatable Read	❌ prevented	❌ prevented	✅ possible	Moderate
Serializable	❌ prevented	❌ prevented	❌ prevented	Slowest

Dirty Read: Reading uncommitted changes from another transaction.

Tx1: UPDATE salary SET amount = 5000 WHERE id = 1 (not committed)
Tx2: SELECT amount FROM salary WHERE id = 1 → sees 5000 (dirty!)
Tx1: ROLLBACK → amount is still 3000
Tx2 used incorrect value 5000

Non-Repeatable Read: Same query returns different values.

Tx1: SELECT amount FROM salary WHERE id = 1 → 3000
Tx2: UPDATE salary SET amount = 5000 WHERE id = 1; COMMIT
Tx1: SELECT amount FROM salary WHERE id = 1 → 5000 (different!)

Phantom Read: Same query returns different number of rows.

Tx1: SELECT * FROM users WHERE age > 25 → 10 rows
Tx2: INSERT INTO users (age) VALUES (30); COMMIT
Tx1: SELECT * FROM users WHERE age > 25 → 11 rows (phantom!)

Default levels: - PostgreSQL: Read Committed - MySQL (InnoDB): Repeatable Read - Oracle: Read Committed

Deep Dive: MVCC (Multi-Version Concurrency Control)

MVCC allows readers and writers to not block each other by keeping multiple versions of data.

How it works: - Each row has a version/timestamp - Readers see a consistent snapshot (their transaction's start time) - Writers create new versions instead of modifying in-place - Old versions are cleaned up by vacuum/garbage collection

Transaction 100 starts → sees snapshot at time 100
Transaction 101 updates row X → creates version 101 of X
Transaction 100 reads row X → still sees version 100 (snapshot)
Transaction 101 commits
Transaction 100 reads row X → still sees version 100 (consistent!)

Used by: PostgreSQL, MySQL (InnoDB), Oracle

Benefit: High concurrency — reads don't block writes, writes don't block reads.

Deep Dive: Write-Ahead Log (WAL)

Durability mechanism: Changes are written to a log before being applied to the database.

1. Transaction modifies data
2. Changes written to WAL (sequential write — fast)
3. Transaction committed (WAL flushed to disk)
4. Actual data pages updated later (checkpoint)

On crash recovery: 1. Read WAL from last checkpoint 2. Redo committed transactions 3. Undo uncommitted transactions 4. Database is back to consistent state

Why sequential writes? Random disk writes are slow. WAL converts them to sequential writes, which are much faster.

Deep Dive: Locking Strategies

Pessimistic Locking: Lock the row before reading/modifying.

-- Lock row until transaction ends
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;

// JPA
@Lock(LockModeType.PESSIMISTIC_WRITE)
@Query("SELECT a FROM Account a WHERE a.id = :id")
Account findByIdForUpdate(@Param("id") Long id);

Optimistic Locking: Don't lock — detect conflicts at commit.

@Entity
public class Account {
    @Version
    private Long version;  // Auto-incremented on update
}
// If version changed between read and write → OptimisticLockException

	Pessimistic	Optimistic
Mechanism	Database locks	Version column
Conflicts	Prevented	Detected at commit
Performance	Lower (locks held)	Higher (no locks)
Best for	High contention	Low contention

Common Interview Questions

• Explain each ACID property with an example.
• What are isolation levels? What problems does each prevent?
• What is a dirty read? Non-repeatable read? Phantom read?
• What is MVCC? How does it improve concurrency?
• What is the Write-Ahead Log?
• What is the difference between pessimistic and optimistic locking?
• How does @Version work in JPA?
• What is the default isolation level in PostgreSQL/MySQL?
• What happens if two transactions update the same row concurrently?
• How would you implement a money transfer between two accounts?