Concept
The ACID Properties
A transaction is a sequence of SQL queries executed as a single unit of work. Transactions must satisfy the ACID contract:
- Atomicity: All queries succeed, or all fail (rolled back). No partial commits.
- Consistency: Data updates must transition the database from one valid state to another, obeying all constraints/triggers.
- Isolation: Concurrent transactions execute without bleeding uncommitted data into each other.
- Durability: Once committed, updates survive server crashes or power failures.
Transaction Isolation Levels & Anomalies
To balance concurrency performance and correctness, SQL standards define four Isolation Levels, each defending against specific anomalies:
| Isolation Level | Dirty Read | Non-repeatable Read | Phantom Read |
|---|---|---|---|
| Read Uncommitted | Allowed | Allowed | Allowed |
| Read Committed | Prevented | Allowed | Allowed |
| Repeatable Read | Prevented | Prevented | Allowed |
| Serializable |
Read Committed (Default in Postgres/SQL Server)
This level prevents Dirty Reads. However, it still permits Non-repeatable Reads, where a transaction reads a row, another transaction modifies and commits it, and the first transaction reads it again, getting a different value:
BEGIN; SELECT balance FROM accounts WHERE id = 1; // Returns 5000
// no operations this step
T1 starts and reads the balance. In READ COMMITTED level, T1 can only read committed data, preventing dirty reads.
Repeatable Read (Default in MySQL InnoDB)
This level prevents both Dirty and Non-repeatable Reads. However, it permits Phantom Reads, where a transaction queries a range of rows, another transaction inserts a new matching row and commits, and the first transaction re-runs the query, seeing a 'phantom' row:
BEGIN; SELECT COUNT(*) FROM users WHERE active = true; // Returns 10
// no operations this step
T1 reads the active users range. REPEATABLE READ level locks existing individual records so they cannot change during T1's execution.
Serializable
The highest level. Transactions execute as if they ran sequentially (one after another), completely isolating database states. It utilizes range locks (or optimistic concurrency checks), which decreases concurrency throughput and can trigger serialization failures.
Common Mistakes
1. Assuming Read Committed prevents all concurrent anomalies
Read Committed is the default in many databases, but it does not prevent Non-repeatable or Phantom reads. If you write calculation queries (like inventory updates) across multiple steps, concurrency can modify the records in between, leading to calculation drift.
2. Not handling serialization failures
When running in SERIALIZABLE mode, PostgreSQL will fail transactions with a 40001 serialization error if it detects dependency loops. Your backend application must be written to catch these errors and retry the entire transaction block.
Best Practices
- Keep Transactions Small: Minimize the time write locks are held to avoid database blockages and deadlocks.
- Use SELECT FOR UPDATE: Under Read Committed, execute
SELECT ... FOR UPDATEto acquire write locks on read rows immediately, preventing concurrent updates from modifying them before commit. - Handle Deadlocks: Write transaction code blocks to execute writes in the same table order across your entire backend to prevent deadlocks (where T1 locks table A and waits for B, while T2 locks B and waits for A).
