SQL 71 🛢️ Transaction Isolation Levels and Concurrency Anomalies
A database serves many clients at once. Two customers buy the last item in stock. A report runs while an order is being processed. An analyst sums account balances while a transfer moves money between them. These concurrent operations are the normal state of any production database, and without careful control they produce results that no single user ever intended. Transaction isolation levels exist to manage this tension. They define exactly how much one transaction can see of another’s uncommitted work, and what anomalies the database will prevent versus allow. The choice of isolation level is one of the highest-leverage decisions a database developer makes, because it directly trades correctness against concurrency, and the wrong choice produces bugs that appear only under load, only in production, and only intermittently.
Key point: Isolation levels are not a binary choice between “safe” and “fast.” They are a spectrum of specific guarantees, each preventing a precisely defined class of anomaly, and the correct level is the lowest one that your application’s correctness actually requires.
Why isolation levels exist
The concurrent access problem. A database is a shared resource. Multiple transactions read and write the same rows, often within milliseconds of each other. Without isolation rules, one transaction could read another’s half-finished changes, make a decision based on them, and produce a result that never logically existed. The classic example: two customers both see one item in stock, both place an order, and the warehouse has negative inventory . Isolation levels are the mechanism that prevents this.
The performance problem. The simplest way to prevent all concurrency anomalies is to serialize everything: run one transaction at a time, fully complete it, then start the next. This is perfectly correct and completely unusable for any real system. Isolation levels exist so that most transactions can run concurrently, and only the specific conflicts that would cause anomalies are blocked or detected. The lower the isolation level, the more concurrency the system allows, and the more anomalies the application must tolerate or handle itself .
The application logic problem. Many transactions are not single statements. They read a value, compute something in application code, then write a result back. These multi-step transactions are where isolation matters most. A single-statement UPDATE inventory SET qty = qty - 1 WHERE item_id = 42 is atomic and safe by itself. But SELECT qty FROM inventory WHERE item_id = 42 followed by application logic followed by an UPDATE creates a window where another transaction can change the data between the read and the write . Isolation levels determine how wide that window is.
The consistency-per-operation trade-off. A stronger isolation level gives each transaction a more consistent view of the database, but at the cost of more blocking, more retries, or more overhead. Read Uncommitted gives no consistency but maximum concurrency. Serializable gives full consistency but can force transactions to wait or abort. Every intermediate level is a specific point on this curve, and no single level is correct for all workloads .
The different implementations problem. The SQL standard defines four isolation levels by the anomalies they prevent, but it explicitly allows databases to implement stronger guarantees than the minimum. PostgreSQL’s Repeatable Read, for example, prevents phantom reads even though the standard permits them at that level . MySQL’s default Repeatable Read also prevents phantoms in many cases through gap locking . This means the same named isolation level can behave differently on different databases, and developers must understand their specific database’s behavior, not just the standard’s definitions .
The detection-vs-prevention trade-off. There are two fundamentally different strategies for handling concurrency conflicts. Pessimistic approaches prevent conflicts by locking data before it is used, forcing other transactions to wait. Optimistic approaches let transactions proceed without blocking, then detect at commit time whether a conflict occurred, aborting one transaction if necessary. Different isolation levels on different databases use different combinations of these strategies, and the choice affects deadlock rates, retry logic, and throughput characteristics .
a. The four SQL standard isolation levels
The SQL:1992 standard defines four isolation levels, each specified by which of three concurrency anomalies it must prevent. In increasing order of strictness: Read Uncommitted, Read Committed, Repeatable Read, and Serializable .
Read Uncommitted
The lowest level. A transaction can read data written by another transaction that has not yet committed. This is called a dirty read. If the writing transaction rolls back, the reading transaction has acted on data that never officially existed .
-- Transaction A (not yet committed)
UPDATE accounts SET balance = 0 WHERE account_id = 1;
-- Transaction B at READ UNCOMMITTED
SELECT balance FROM accounts WHERE account_id = 1;
-- Returns 0, even though A may still roll back.
-- If A rolls back, B made a decision on phantom data.Read Uncommitted is rarely appropriate for transactional workloads. Its legitimate use is approximate reporting: a dashboard showing rough order counts where being off by a few is acceptable, and where blocking the production write path for exact consistency is not .
Read Committed
The most common default (SQL Server, Oracle, PostgreSQL). A transaction only sees data committed before the statement began. Dirty reads are impossible. However, successive reads within the same transaction can see different data if another transaction commits between them, producing non-repeatable reads and phantom reads .
-- Transaction B at READ COMMITTED
SELECT balance FROM accounts WHERE account_id = 1; -- returns 1000
-- Transaction A commits: UPDATE accounts SET balance = 500 WHERE account_id = 1;
SELECT balance FROM accounts WHERE account_id = 1; -- returns 500
-- Same transaction, same query, different result. Non-repeatable read.Read Committed is the workhorse level. It prevents the worst anomaly (dirty reads) while allowing high concurrency. Most applications that do not perform multi-step read-then-write logic are safe here.
Repeatable Read
Rows read by a transaction cannot be changed by other transactions until the reading transaction completes. Non-repeatable reads are prevented. The transaction sees a stable snapshot of the rows it has read. However, new rows that match the query filter can still appear, so phantom reads remain possible in the standard definition .
-- Transaction B at REPEATABLE READ
SELECT balance FROM accounts WHERE account_id = 1; -- returns 1000
-- Transaction A attempts: UPDATE accounts SET balance = 500 WHERE account_id = 1;
-- A blocks (or is aborted at commit, depending on implementation).
SELECT balance FROM accounts WHERE account_id = 1; -- still returns 1000On PostgreSQL, Repeatable Read uses snapshot isolation: the entire transaction sees the database as of the transaction’s start, and a concurrent update that commits after the transaction began causes the transaction to abort with a serialization error rather than silently losing the update .
Serializable
The strictest level. The result of running transactions concurrently must be equivalent to running them in some serial order, one after another. All three anomalies are prevented. In lock-based implementations, range locks prevent inserts into queried ranges. In snapshot-based implementations like PostgreSQL, predicate locks track dependencies and abort transactions when a serialization anomaly would occur .
-- PostgreSQL SERIALIZABLE example
-- Transaction A: SELECT SUM(value) FROM mytab WHERE class = 1; -- result 30
-- Transaction A: INSERT INTO mytab (class, value) VALUES (2, 30);
-- Transaction B: SELECT SUM(value) FROM mytab WHERE class = 2; -- result 300
-- Transaction B: INSERT INTO mytab (class, value) VALUES (1, 300);
-- Both commit. No serial order produces both results.
-- PostgreSQL aborts one:
-- ERROR: could not serialize access due to read/write dependenciesThe cost is real: range locks cause blocking and deadlocks, while predicate-lock implementations add monitoring overhead and force application retry logic .
b. The three concurrency anomalies
The anomalies are the concrete failures that isolation levels exist to prevent. Each is a specific pattern of concurrent access that produces a result inconsistent with any serial execution.
Dirty read
A transaction reads data written by another transaction that has not committed. If the writer rolls back, the reader has seen data that never existed. This is the most severe anomaly because it can produce decisions based on entirely fictional state .
-- T1: UPDATE products SET price = 0 WHERE id = 1; -- not committed
-- T2 at READ UNCOMMITTED: SELECT price FROM products WHERE id = 1; -- reads 0
-- T1: ROLLBACK; -- price is still the original value
-- T2 made pricing decisions based on a price that was never real.Non-repeatable read
A transaction reads the same row twice and gets different values because another transaction committed a change between the reads. The reading transaction’s view of the row is not stable across its own lifetime .
-- T1: SELECT balance FROM accounts WHERE id = 1; -- 1000
-- T2: UPDATE accounts SET balance = 500 WHERE id = 1; COMMIT;
-- T1: SELECT balance FROM accounts WHERE id = 1; -- 500
-- T1 computed a budget based on 1000, then saw 500. Inconsistent.Phantom read
A transaction re-executes a query and sees new rows that were not present before, because another transaction inserted rows matching the query’s filter. The new rows are “phantoms” — they appeared between two executions of the same query within one transaction .
-- T1: SELECT COUNT(*) FROM orders WHERE status = 'pending'; -- 10
-- T2: INSERT INTO orders (status) VALUES ('pending'); COMMIT;
-- T1: SELECT COUNT(*) FROM orders WHERE status = 'pending'; -- 11
-- T1's count changed without T1 doing anything.Phantom reads are harder to prevent than non-repeatable reads because they require locking a range of values (or detecting equivalent read-write dependencies), not just the rows that were actually read .
Serialization anomaly
A serialization anomaly is not one of the three SQL-standard anomalies but the general condition that Serializable prevents: the concurrent execution of a set of transactions produces a result that no serial (one-at-a-time) execution of the same transactions could produce. The PostgreSQL example above (each transaction computing a sum and inserting based on it) is a serialization anomaly that Read Committed and even Repeatable Read would allow .
c. Implementation strategies: locking vs. versioning
The SQL standard defines behavior. Databases implement it differently. The two dominant strategies are lock-based and version-based concurrency control, and understanding which one your database uses is essential for predicting performance and failure modes.
Lock-based isolation
The database acquires shared locks on rows that a transaction reads and exclusive locks on rows it writes. Shared locks allow other readers but block writers. Exclusive locks block both. The isolation level determines how long read locks are held .
-- READ COMMITTED locking behavior (SQL Server default, before RCSI)
-- T1: SELECT * FROM products WHERE id = 1;
-- Takes shared lock, reads row, releases lock immediately.
-- Another transaction can update the row before T1 reads it again.
-- REPEATABLE READ locking behavior
-- T1: SELECT * FROM products WHERE id = 1;
-- Takes shared lock and holds it until T1 commits or rolls back.
-- Other transactions cannot update this row.Lock-based systems are predictable but prone to blocking and deadlocks. A long-running transaction at Repeatable Read can block writers for the entire duration of the transaction .
Row-versioning (MVCC) isolation
The database maintains multiple versions of each row. Readers see the version that was current when their statement (or transaction) started. Writers create new versions. Readers never block writers, and writers never block readers .
-- PostgreSQL MVCC at READ COMMITTED
-- T1: SELECT * FROM products WHERE id = 1;
-- Sees the version committed before the statement started.
-- T2: UPDATE products SET price = 99 WHERE id = 1; COMMIT;
-- Creates a new row version. Old version remains.
-- T1: SELECT * FROM products WHERE id = 1;
-- Sees the new version (Read Committed takes fresh snapshot per statement).PostgreSQL’s Repeatable Read and Serializable use MVCC. MySQL’s InnoDB uses MVCC for consistent reads at all isolation levels, with locking for writes . SQL Server offers Snapshot isolation as a versioning alternative to its default lock-based Read Committed .
The trade-off is storage and cleanup. Old row versions must be retained until no transaction needs them. Under long-running transactions or high write rates, version storage grows, and vacuum processes must clean up obsolete versions .
Hybrid approaches
MySQL’s InnoDB default is Repeatable Read with MVCC for plain SELECT statements and gap/next-key locks for locking reads and writes. A plain SELECT reads a snapshot; a SELECT ... FOR UPDATE locks index records and gaps to prevent phantom inserts . This hybrid gives consistent reads without blocking, while still preventing phantoms for locking operations.
SQL Server’s Read Committed Snapshot Isolation (RCSI) changes the default Read Committed to use row versioning. Every statement reads a snapshot as of the statement’s start, without taking shared locks. Writers still take exclusive locks. This eliminates reader-writer blocking while keeping the Read Committed semantics .
Complete Example Session
-- ============================================
-- PART 1: SETUP — THE SCENARIO
-- ============================================
-- A simple inventory table.
CREATE TABLE inventory (
item_id INT PRIMARY KEY,
item_name VARCHAR(100),
quantity INT NOT NULL CHECK (quantity >= 0)
);
INSERT INTO inventory VALUES (1, 'Widget', 10);
INSERT INTO inventory VALUES (2, 'Gadget', 5);
-- The business rule: quantity must never go below zero.
-- Two concurrent transactions will test this rule.
-- ============================================
-- PART 2: THE NORMAL CASE — NO CONCURRENCY
-- ============================================
-- Transaction A: buy 3 widgets. Safe.
BEGIN;
SELECT quantity FROM inventory WHERE item_id = 1; -- 10
-- Application logic: 10 >= 3, proceed.
UPDATE inventory SET quantity = quantity - 3 WHERE item_id = 1;
-- quantity is now 7.
COMMIT;
-- Transaction B: buy 8 widgets. Safe, single transaction.
BEGIN;
SELECT quantity FROM inventory WHERE item_id = 1; -- 7
-- Application logic: 7 < 8, reject.
ROLLBACK;
-- No change. Correct behavior.
-- ============================================
-- PART 3: THE RACE CONDITION — READ COMMITTED
-- ============================================
-- Transaction A and Transaction B both start.
-- Both read the same initial quantity.
-- T-A:
BEGIN;
SELECT quantity FROM inventory WHERE item_id = 1; -- 10
-- T-B (concurrent):
BEGIN;
SELECT quantity FROM inventory WHERE item_id = 1; -- 10 (same value!)
-- T-A: Application logic: 10 >= 8, proceed.
UPDATE inventory SET quantity = quantity - 8 WHERE item_id = 1;
COMMIT;
-- quantity is now 2.
-- T-B: Application logic: 10 >= 8, proceed.
UPDATE inventory SET quantity = quantity - 8 WHERE item_id = 1;
-- This UPDATE runs against the current value (2):
-- 2 - 8 = -6, which violates the CHECK constraint.
-- The CHECK constraint saves us here.
-- Without the CHECK constraint:
-- quantity would be -6. Oversold by 6 units.
-- This is the classic lost update / overselling problem.
-- ============================================
-- PART 4: PREVENTING THE RACE — REPEATABLE READ
-- ============================================
-- PostgreSQL Repeatable Read (snapshot isolation)
-- T-A:
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT quantity FROM inventory WHERE item_id = 1; -- 10
-- T-B (concurrent):
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT quantity FROM inventory WHERE item_id = 1; -- 10
-- T-A: Application logic: 10 >= 8, proceed.
UPDATE inventory SET quantity = quantity - 8 WHERE item_id = 1;
COMMIT;
-- quantity is now 2.
-- T-B: Application logic: 10 >= 8, proceed.
UPDATE inventory SET quantity = quantity - 8 WHERE item_id = 1;
-- PostgreSQL detects that T-A committed after T-B's snapshot.
-- ERROR: could not serialize access due to concurrent update
-- T-B must ROLLBACK and retry.
-- On retry, T-B reads quantity = 2, rejects the order.
-- No overselling. Correct behavior.
-- ============================================
-- PART 5: PREVENTING THE RACE — SERIALIZABLE
-- ============================================
-- PostgreSQL Serializable
-- T-A:
BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT quantity FROM inventory WHERE item_id = 1; -- 10
-- T-B:
BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT quantity FROM inventory WHERE item_id = 1; -- 10
-- T-A:
UPDATE inventory SET quantity = quantity - 8 WHERE item_id = 1;
COMMIT;
-- quantity is now 2.
-- T-B:
UPDATE inventory SET quantity = quantity - 8 WHERE item_id = 1;
-- ERROR: could not serialize access due to read/write dependencies
-- T-B must ROLLBACK and retry.
-- Same outcome as Repeatable Read in this case, but Serializable
-- also protects against phantom-based anomalies that Repeatable Read
-- would allow.
-- ============================================
-- PART 6: USING EXPLICIT LOCKS INSTEAD
-- ============================================
-- Sometimes the right answer is not a higher isolation level
-- but an explicit lock at the application's chokepoint.
-- T-A:
BEGIN;
SELECT quantity FROM inventory WHERE item_id = 1 FOR UPDATE;
-- Row is locked. T-B will block here.
-- quantity = 10. Proceed.
UPDATE inventory SET quantity = quantity - 8 WHERE item_id = 1;
COMMIT;
-- Lock released. quantity = 2.
-- T-B (was blocking on FOR UPDATE):
SELECT quantity FROM inventory WHERE item_id = 1 FOR UPDATE;
-- Now sees quantity = 2. Application logic: 2 < 8, reject.
ROLLBACK;
-- FOR UPDATE serializes access to this row without raising
-- the entire transaction's isolation level. This is often the
-- more surgical solution.
-- ============================================
-- PART 7: THE PHANTOM PROBLEM
-- ============================================
-- A different scenario where row locks are insufficient.
-- A reporting transaction sums pending orders.
-- T-Report at REPEATABLE READ:
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT SUM(amount) FROM orders WHERE status = 'pending';
-- Result: 5000
-- T-Insert commits a new pending order:
INSERT INTO orders (status, amount) VALUES ('pending', 1000);
COMMIT;
-- T-Report:
SELECT SUM(amount) FROM orders WHERE status = 'pending';
-- Result: 6000 in standard Repeatable Read (phantom row).
-- Result: still 5000 in PostgreSQL Repeatable Read (snapshot).
-- Result: still 5000 in MySQL Repeatable Read for plain SELECT (MVCC snapshot).
-- ============================================
-- PART 8: THE SERIALIZABLE PHANTOM GUARANTEE
-- ============================================
-- PostgreSQL Serializable
-- T-Report:
BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT SUM(amount) FROM orders WHERE status = 'pending';
-- Result: 5000. Predicate lock tracks this read.
-- T-Insert:
BEGIN ISOLATION LEVEL SERIALIZABLE;
INSERT INTO orders (status, amount) VALUES ('pending', 1000);
-- Insert proceeds (predicate locks don't block).
-- T-Insert: COMMIT;
-- T-Report: SELECT SUM(amount) FROM orders WHERE status = 'pending';
-- Result: still 5000 (snapshot).
-- T-Report: COMMIT;
-- ERROR: could not serialize access due to read/write dependencies
-- T-Report must retry. On retry, it sees the new order.
-- The serial order (T-Report before T-Insert) is not possible
-- because T-Insert's row was not visible to T-Report's sum.
-- ============================================
-- PART 9: SETTING ISOLATION LEVELS
-- ============================================
-- PostgreSQL: per-transaction
BEGIN ISOLATION LEVEL READ COMMITTED;
-- or
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- MySQL: session or global
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- SQL Server: per-connection
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
-- Note: MySQL does not allow SET TRANSACTION ISOLATION LEVEL
-- in the middle of an active transaction .
-- ============================================
-- PART 10: THE RETRY PATTERN
-- ============================================
-- Serializable transactions may abort. Applications must retry.
-- Pseudocode pattern:
--
-- while true:
-- BEGIN ISOLATION LEVEL SERIALIZABLE;
-- try:
-- ... application logic ...
-- COMMIT;
-- break; -- success
-- catch SerializationFailure:
-- ROLLBACK;
-- continue; -- retry with fresh snapshot
--
-- The retry must re-read all data from the beginning.
-- The second attempt sees the committed state that caused
-- the first attempt to fail, so it can proceed correctly.
-- Read-only transactions never need retry in PostgreSQL .The ten parts built from a simple inventory scenario through the three anomalies, three isolation levels, explicit locking, phantom reads, and the retry pattern that Serializable requires. The same database table and business rule thread through every part, showing how each isolation level changes the outcome of the identical concurrent operations.
Quick Reference
The Four Standard Isolation Levels
| Level | Dirty Read | Non-Repeatable Read | Phantom Read |
|---|---|---|---|
| Read Uncommitted | Possible | Possible | Possible |
| Read Committed | Not possible | Possible | Possible |
| Repeatable Read | Not possible | Not possible | Possible* |
| Serializable | Not possible | Not possible | Not possible |
*PostgreSQL and MySQL (MVCC) prevent phantoms at Repeatable Read for plain SELECT.
Default Isolation Levels by Database
| Database | Default | Row Versioning Option |
|---|---|---|
| PostgreSQL | Read Committed | Repeatable Read = Snapshot Isolation |
| MySQL (InnoDB) | Repeatable Read | MVCC for consistent reads |
| SQL Server | Read Committed | Snapshot, RCSI |
| Oracle | Read Committed | Serializable (snapshot-based) |
Lock Duration by Level
| Level | Read Lock Held | Write Lock Held |
|---|---|---|
| Read Uncommitted | Not taken | Until end of transaction |
| Read Committed | Released immediately | Until end of transaction |
| Repeatable Read | Until end of transaction | Until end of transaction |
| Serializable | Until end of transaction (range) | Until end of transaction |
Anomaly Summary
| Anomaly | Definition | Prevented By |
|---|---|---|
| Dirty read | Reading uncommitted data | Read Committed+ |
| Non-repeatable read | Row changes between reads | Repeatable Read+ |
| Phantom read | New rows appear in re-query | Serializable (standard) |
| Serialization anomaly | Non-serializable outcome | Serializable |
Best Practices
✅ Do This:
-- Use the lowest isolation level that satisfies correctness.
BEGIN ISOLATION LEVEL READ COMMITTED; -- ✅ Default for most work
-- Use explicit FOR UPDATE for specific chokepoints.
SELECT * FROM inventory WHERE item_id = 1 FOR UPDATE; -- ✅
-- Implement retry logic for Serializable.
while (serialization_failure) retry_transaction(); -- ✅
-- Set isolation level explicitly in code.
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; -- ✅
-- Keep transactions short to minimize lock duration.
BEGIN; UPDATE ...; COMMIT; -- ✅
-- Test concurrency behavior under load.
-- Write integration tests with parallel transactions. -- ✅❌ Don’t Do This:
-- Don't use Serializable everywhere.
BEGIN ISOLATION LEVEL SERIALIZABLE; -- ❌ Unnecessary blocking
-- Don't read-then-write without protection.
SELECT qty; /* compute */ UPDATE qty; -- ❌ Race condition
-- Don't hold transactions open for user input.
BEGIN; /* wait for HTTP request */ COMMIT; -- ❌ Locks held for seconds
-- Don't ignore serialization failures.
catch (SerializationFailure) { /* log and continue */ } -- ❌ Must retry
-- Don't assume isolation level names mean the same everywhere.
-- PostgreSQL Repeatable Read ≠ MySQL Repeatable Read -- ❌ Verify behavior
-- Don't mix locking and non-locking reads carelessly.
SELECT ...; SELECT ... FOR UPDATE; -- ❌ Inconsistent snapshotsCommon Pitfalls
| Pitfall | Why It Happens | Fix |
|---|---|---|
| Overselling inventory | Read-then-write without locking | Use FOR UPDATE or Serializable |
| Lost update | Two transactions update same row from stale read | Repeatable Read or explicit lock |
| Phantom rows in reports | Re-query sees new rows | Serializable or snapshot isolation |
| Deadlocks from range locks | Serializable locks large ranges | Retry logic; shorter transactions |
| Version store bloat | Long transactions hold old versions | Keep transactions short |
| Unexpected abort storms | Serializable detects dependencies | Retry with backoff; consider lower level |
| Isolation level ignored | SET inside transaction (MySQL) | Set before BEGIN |
| Cross-database portability | Same name, different behavior | Test on target database |
Real-World Examples
1. E-Commerce Inventory Reservation
BEGIN;
SELECT quantity FROM inventory WHERE item_id = 42 FOR UPDATE;
-- If quantity >= requested, UPDATE and COMMIT.
-- If not, ROLLBACK.2. Financial Transfer with Serializable
BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT balance FROM accounts WHERE id = 1;
-- Compute after deducting amount.
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT; -- May abort; retry on serialization failure.3. Reporting Snapshot
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT region, SUM(sales) FROM orders
WHERE created_at >= '2026-01-01' GROUP BY region;
-- All queries in this transaction see the same snapshot.
COMMIT;4. MySQL Session Isolation
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- Applies to all subsequent transactions in this connection.5. SQL Server Snapshot Isolation
ALTER DATABASE MyDb SET ALLOW_SNAPSHOT_ISOLATION ON;
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
-- Readers never block writers.6. PostgreSQL Retry Loop
-- Application-level pseudocode
for attempt in range(max_retries):
try:
cursor.execute("BEGIN ISOLATION LEVEL SERIALIZABLE")
# ... work ...
cursor.execute("COMMIT")
break
except SerializationFailure:
cursor.execute("ROLLBACK")7. Batch Insert with Gap Locking
BEGIN;
SELECT COUNT(*) FROM orders WHERE status = 'pending' FOR UPDATE;
-- Range locked. No new pending orders can be inserted.
-- Prevents phantom in the count.
COMMIT;8. Deadlock from Range Locks
-- T1: SELECT * FROM t WHERE id BETWEEN 1 AND 10 FOR UPDATE;
-- T2: SELECT * FROM t WHERE id BETWEEN 5 AND 15 FOR UPDATE;
-- Both block. Deadlock detected. One rolls back.9. Read Uncommitted Dashboard
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT COUNT(*) FROM orders WHERE status = 'processing';
-- Approximate count. No locks. May be slightly off.10. Detecting Isolation Level in PostgreSQL
SHOW transaction_isolation;
-- current_setting('transaction_isolation')Visual
The Anomaly Spectrum
┌──────────────────────────────────────────────────────────────┐
│ ANOMALY SPECTRUM │
│ │
│ READ UNCOMMITTED │
│ ├── Dirty read ✓ allowed │
│ ├── Non-repeatable ✓ allowed │
│ └── Phantom ✓ allowed │
│ │
│ READ COMMITTED │
│ ├── Dirty read ✗ prevented │
│ ├── Non-repeatable ✓ allowed │
│ └── Phantom ✓ allowed │
│ │
│ REPEATABLE READ │
│ ├── Dirty read ✗ prevented │
│ ├── Non-repeatable ✗ prevented │
│ └── Phantom ✓ allowed (standard) │
│ │
│ SERIALIZABLE │
│ └── All anomalies ✗ prevented │
│ │
└──────────────────────────────────────────────────────────────┘The Overselling Race
┌──────────────────────────────────────────────────────────────┐
│ THE OVERSELLING RACE │
│ │
│ Transaction A Transaction B │
│ ─────────────── ─────────────── │
│ │
│ SELECT qty = 10 │
│ SELECT qty = 10 │
│ (same snapshot) │
│ │
│ UPDATE qty = 10 - 8 = 2 │
│ COMMIT │
│ UPDATE qty = 10 - 8 = 2 │
│ (against current value) │
│ COMMIT │
│ │
│ RESULT: qty = -6? NO — CHECK constraint saves us. │
│ WITHOUT CONSTRAINT: Oversold by 6 units. │
│ │
└──────────────────────────────────────────────────────────────┘Locking vs. Versioning
┌──────────────────────────────────────────────────────────────┐
│ LOCKING vs. VERSIONING │
│ │
│ LOCK-BASED (Pessimistic) │
│ ┌────────┐ ┌────────┐ ┌────────┐ │
│ │ Reader │───▶│ Lock │───▶│ Writer │ │
│ │ │ │ (hold) │ │ waits │ │
│ └────────┘ └────────┘ └────────┘ │
│ Reader blocks writer. Predictable. Deadlock-prone. │
│ │
│ VERSION-BASED (Optimistic) │
│ ┌────────┐ ┌─────────────────┐ ┌────────┐ │
│ │ Reader │───▶│ Version Store │ │ Writer │ │
│ │ │ │ (old versions) │◀───│ creates│ │
│ └────────┘ └─────────────────┘ │ new ver│ │
│ Reader never blocks. Writer never blocks reader. │
│ Storage overhead. Cleanup needed. │
│ │
└──────────────────────────────────────────────────────────────┘The Retry Pattern
┌──────────────────────────────────────────────────────────────┐
│ SERIALIZABLE RETRY PATTERN │
│ │
│ ┌─────────┐ │
│ │ BEGIN │ │
│ │SERIALIZ-│ │
│ │ ABLE │ │
│ └────┬────┘ │
│ │ │
│ ▼ │
│ ┌─────────┐ ┌──────────────┐ ┌─────────┐ │
│ │ Read │───▶│ Application │───▶│ Write │ │
│ │ Data │ │ Logic │ │ Result │ │
│ └─────────┘ └──────────────┘ └────┬────┘ │
│ │ │
│ ▼ │
│ ┌─────────┐ │
│ │ COMMIT │ │
│ └────┬────┘ │
│ │ │
│ ┌────────────────────────────┴────┐ │
│ │ │ │
│ ▼ ▼ │
│ ┌─────────┐ ┌──────────┐ │
│ │ SUCCESS │ │SERIALIZ- │ │
│ │ │ │ ATION │ │
│ └─────────┘ │ FAILURE │ │
│ └────┬─────┘ │
│ │ │
│ ▼ │
│ ┌─────────┐ │
│ │ROLLBACK │ │
│ │ & RETRY │───────┐│
│ └─────────┘ ││
│ ││
│◀───────────────────────────────────────────────────────────┘│
└──────────────────────────────────────────────────────────────┘Summary
| Item | Value |
|---|---|
| Isolation | The “I” in ACID; controls visibility between concurrent transactions |
| Dirty read | Reading uncommitted data; prevented by Read Committed+ |
| Non-repeatable read | Row changes between reads; prevented by Repeatable Read+ |
| Phantom read | New rows appear on re-query; prevented by Serializable (standard) |
| Read Uncommitted | Lowest level; allows all anomalies; approximate reporting only |
| Read Committed | Default for most databases; prevents dirty reads |
| Repeatable Read | Stable snapshot of read rows; implementation varies by database |
| Serializable | Full serial equivalence; retry required on conflicts |
| MVCC | Multi-version concurrency control; readers don’t block writers |
| FOR UPDATE | Explicit row lock; surgical alternative to higher isolation |
| Retry pattern | Required for Serializable transactions; must re-read all data |
Key takeaways:
- Isolation levels are a trade-off spectrum. Higher levels prevent more anomalies but cost concurrency through blocking, aborts, or overhead. The correct level is the lowest one that satisfies your application’s correctness requirements.
- The three standard anomalies are precise. Dirty read (uncommitted data), non-repeatable read (changed row), phantom read (new row). Each isolation level prevents a specific subset.
- Implementation varies by database. PostgreSQL Repeatable Read prevents phantoms; standard does not require this. MySQL and SQL Server have different default levels and versioning options.
- Locking and versioning are different strategies. Locking blocks conflicts; versioning detects them. Each has performance characteristics that matter under load.
- Serializable requires retry logic. Transactions can abort with serialization failures and must be re-executed from the beginning. Applications must be designed for this.
- Explicit locks are often the better tool.
SELECT ... FOR UPDATEat the specific chokepoint is more surgical than raising the entire transaction’s isolation level. - Phantom prevention requires range awareness. Row locks cannot prevent new rows. Serializable uses range locks or predicate locks to detect insert conflicts.
- The default is usually right. Read Committed (or the database’s default) is correct for most single-statement and simple multi-statement transactions. Raise isolation only when you have a specific anomaly to prevent.
Remember: Transaction isolation is the discipline of choosing what concurrent operations may observe of each other. The SQL standard gives four named levels, but each database implements them with different strategies and sometimes stronger guarantees than required. The practical question is not “which level is best” but “what is the lowest level at which my application produces correct results under concurrency.” Answer that by understanding the specific anomalies that threaten your business logic, then selecting the level that prevents them, adding explicit locks where the cost of a higher level is not justified, and building retry logic wherever the database may abort a transaction to preserve correctness.
Stop using slow, ad-bloated tool sites! 🤮
🔎 Search “KandZ Tools” on Google to use many professional utilities for free.
KandZ.me is the ultimate minimalist hub for:
✅ Finance (Mortgage, Interest, Inflation)
✅ Tech (Base64, JSON, Dev Suite, IP)
✅ Health (BMI, BMR, TDEE)
✅ Productivity (Timer, Workspace, QR)
⚡️ Fast & Private
🔒 No data leaves your device
💎 100% Free
🔗 Use it now: https://tools.kandz.me
🔖 Bookmark it—you’ll need it later!