A Complete Guide to Database Transactions and Isolation Levels
T1: read ------ update ------ commit
T2: read ------ read ------ commit
Isolation level decides:
- can T2 see uncommitted data?
- can T2 read different values twice?
- can new rows appear between reads?Transaction isolation becomes much easier to understand when you think of it not as abstract theory, but as a question of what is allowed to happen between two overlapping transactions. Dirty Read, Non-Repeatable Read, and Phantom Read are all differences in what can be observed during that overlapping time window.
ACID Properties
| Property | Description |
|---|---|
| Atomicity | A transaction either succeeds completely or fails completely |
| Consistency | Data integrity is preserved before and after the transaction |
| Isolation | Concurrent transactions do not interfere with each other |
| Durability | Committed data is stored permanently |
Problems by Isolation Level
| Isolation Level | Dirty Read | Non-Repeatable Read | Phantom Read |
|---|---|---|---|
| READ UNCOMMITTED | Yes | Yes | Yes |
| READ COMMITTED | No | Yes | Yes |
| REPEATABLE READ | No | No | Yes |
| SERIALIZABLE | No | No | No |
MySQL InnoDB default: REPEATABLE READ
Dirty Read
-- Transaction A
BEGIN;
UPDATE accounts SET balance = 0 WHERE id = 1;
-- Not committed yet
-- Transaction B (READ UNCOMMITTED)
SELECT balance FROM accounts WHERE id = 1;
-- Reads 0 -> if A rolls back, this was a wrong valueNon-Repeatable Read
-- Transaction A
BEGIN;
SELECT balance FROM accounts WHERE id = 1; -- reads 1000
-- Transaction B
UPDATE accounts SET balance = 2000 WHERE id = 1;
COMMIT;
-- Transaction A (read again in the same transaction)
SELECT balance FROM accounts WHERE id = 1; -- reads 2000 (changed!)Phantom Read
-- Transaction A
BEGIN;
SELECT * FROM orders WHERE amount > 1000; -- 3 rows
-- Transaction B
INSERT INTO orders (amount) VALUES (5000);
COMMIT;
-- Transaction A
SELECT * FROM orders WHERE amount > 1000; -- 4 rows! (phantom row appears)Configuring MySQL Isolation Levels
-- Session level
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- Global level
SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- Check the current isolation level
SELECT @@transaction_isolation;Optimistic vs. Pessimistic Locking
-- Pessimistic lock (SELECT FOR UPDATE)
BEGIN;
SELECT * FROM inventory WHERE id = 1 FOR UPDATE;
UPDATE inventory SET stock = stock - 1 WHERE id = 1;
COMMIT;
-- Optimistic lock (version column)
UPDATE products
SET stock = stock - 1, version = version + 1
WHERE id = 1 AND version = 5;
-- if affected rows = 0, conflict happened -> retryWhat Gets Hard in Production
- Transaction isolation is not an academic setting; it directly shapes correctness, latency, and contention under concurrency.
- Teams often choose an isolation level by default and then debug anomalies without a shared model of what is allowed.
- Higher isolation can protect correctness while quietly reducing throughput if access patterns are not designed for it.
Architecture Decisions That Matter
- Start from the business invariant you must protect, not from the prestige of stronger isolation.
- Design read-modify-write paths to minimize conflict windows.
- Use locking, retries, and idempotency policies alongside isolation choices.
Practical Example
The right question is usually “what anomaly is unacceptable here?”
inventory reservation
need to prevent oversell
may require row lock or optimistic retry depending on workloadAnti-Patterns to Avoid
- Using the default isolation level without knowing what anomalies remain possible.
- Raising isolation globally when the hotspot exists in only a few workflows.
- Ignoring retry design after introducing stricter concurrency control.
Operational Checklist
- Document invariants for money, inventory, and ordering workflows.
- Load-test contention scenarios, not just happy-path latency.
- Measure deadlock and retry frequency.
- Review lock scope and transaction length.
Final Judgment
Isolation level is a correctness budget decision. The best level is the lowest one that still protects the invariants you actually care about.
Continue Reading
Related posts
MySQL Index Optimization Strategies — Query Analysis with EXPLAIN
This post explains how MySQL indexes work and how to optimize them using EXPLAIN analysis. It covers composite indexes, covering indexes, and index hints with practical examples.
🗄️ DatabaseDatabase Partitioning Strategies — Sharding, Horizontal/Vertical Splitting
This post summarizes partitioning strategies for managing large-scale data efficiently. It covers MySQL partitions, horizontal and vertical splitting, and the concepts and implementation approaches behind sharding.
📈 TrendsPostgreSQL 18 Trends: What Actually Matters in Practice
PostgreSQL 18 is more than an upgrade headline. AIO, skip scan, better post-upgrade recovery, OAuth, and generated columns all point to a release focused on operational cost reduction.
🚀 DevOpsKubernetes Advanced Operations — HPA, Resource Management, and Pod Scheduling
This article explains Kubernetes operations not as a collection of settings but from the perspective of resource placement and resilience. It covers when and how to use requests/limits, HPA, affinity, taints, PDBs, and probes in real environments.
Next Path