Serializable — Isolation Levels | OneNoughtOne

0/105

Database Management SystemsIsolation Levels

Isolation Levels

LevelIntermediate

Duration90 mins

TopicIsolation Levels

4 / 5

Serializable

Complete Isolation: The Gold Standard

If concurrent transactions make you anxious—if you want absolute certainty that no concurrency anomaly of any kind is possible— Serializable isolation is your answer. At this level, the database guarantees that the outcome of concurrent transactions is identical to running them one after another in some serial order. Serializable represents the theoretical ideal: perfect consistency, no anomalies, complete predictability. But perfection comes at a price. Understanding when that price is worth paying—and when it isn't—is key to effective database engineering.

What You Will Learn

By the end of this page, you will understand Serializable isolation's complete guarantee, the two primary implementation strategies (strict two-phase locking with predicate locks vs Serializable Snapshot Isolation), why this level has the highest performance cost, and when the consistency guarantees justify that cost.

Formal Definition of Serializable

Serializable is the highest isolation level in the SQL standard. It provides the ultimate guarantee: the execution of concurrent transactions is equivalent to some serial execution of those transactions. Definition (Informal): If you run transactions T1 and T2 concurrently at the Serializable level, the result will be the same as if you had run either: T1 first, then T2 T2 first, then T1 The database doesn't promise which serial order—just that the outcome matches some serial order. SQL Standard Definition: Serializable prevents all phenomena defined by the standard: Prevents Dirty Reads : Uncommitted data is invisible Prevents Non-Repeatable Reads : Rows remain stable within a transaction Prevents Phantom Reads : No new rows appear in repeated range queries

Complete Anomaly Prevention at Serializable
Anomaly Type	Read Uncommitted	Read Committed	Repeatable Read	Serializable
Dirty Read	❌ Possible	✅ Prevented	✅ Prevented	✅ Prevented
Non-Repeatable Read	❌ Possible	❌ Possible	✅ Prevented	✅ Prevented
Phantom Read	❌ Possible	❌ Possible	❌ Possible*	✅ Prevented
Write Skew	❌ Possible	❌ Possible	❌ Possible	✅ Prevented

* Note : SQL standard permits phantoms at RR, but many implementations prevent them. Beyond the SQL Standard: Write Skew: Serializable also prevents write skew, an anomaly not explicitly mentioned in the SQL standard but absolutely possible at lower isolation levels. Write skew occurs when two transactions read the same data and then make different modifications based on what they read, resulting in a state that violates application invariants.

set_isolation_serializable.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
-- Setting Serializable isolation level in various databases
 
-- SQL Server
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
    -- All reads acquire range locks, preventing all anomalies
    SELECT * FROM Accounts WHERE balance > 1000;
    -- Range lock on "balance > 1000" prevents any changes/inserts to this range
COMMIT;
 
-- PostgreSQL
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- or: BEGIN ISOLATION LEVEL SERIALIZABLE;
BEGIN;
    -- Uses Serializable Snapshot Isolation (SSI)
    -- No blocking, but conflicts cause transaction abort
    SELECT * FROM accounts WHERE balance > 1000;
COMMIT;
 
-- Oracle
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- Oracle's implementation is snapshot-based
-- But requires careful handling of write conflicts
 
-- MySQL/InnoDB
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;
    -- Converts all SELECT to SELECT ... FOR SHARE (previously LOCK IN SHARE MODE)
    -- Gap locks prevent phantom inserts
    SELECT * FROM accounts WHERE balance > 1000;
    -- Implicit: FOR SHARE
COMMIT;

Serializability ≠ Serial Execution

Serializable isolation doesn't mean transactions actually run one at a time. Transactions can execute concurrently—they just produce results equivalent to some serial ordering. The database uses sophisticated algorithms (locks or SSI) to enforce this equivalence while allowing concurrency.

The Write Skew Anomaly

Write skew is perhaps the most insidious concurrency anomaly because it can occur even at Repeatable Read in some implementations. It involves two transactions reading the same data and making non-conflicting writes that together violate a constraint. Classic Example: On-Call Doctors Consider a hospital rule: at least one doctor must always be on call. Both Dr. Alice and Dr. Bob are currently on call. Each wants to take themselves off call, but only if the other is still on call.

Timeline: Write Skew Leading to Constraint Violation
Time	Transaction A (Alice)	Transaction B (Bob)	Constraint Status
T1	BEGIN	BEGIN	Alice=ON, Bob=ON ✅
T2	Check: Is Bob on call? → Yes		Alice=ON, Bob=ON ✅
T3		Check: Is Alice on call? → Yes	Alice=ON, Bob=ON ✅
T4	Set Alice = OFF (Bob is on, it's safe)		Alice=OFF, Bob=ON ✅
T5		Set Bob = OFF (Alice is on, it's safe)	Alice=OFF, Bob=OFF ❌
T6	COMMIT	COMMIT	Constraint violated!

write_skew_demo.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
-- Write Skew Demonstration
-- This can occur at Repeatable Read but NOT at Serializable
 
-- Setup
CREATE TABLE OnCallDoctors (
    doctor_id VARCHAR(10) PRIMARY KEY,
    is_on_call BOOLEAN NOT NULL
);
INSERT INTO OnCallDoctors VALUES ('Alice', true), ('Bob', true);
-- Constraint: COUNT(*) WHERE is_on_call = true >= 1
 
-- Transaction A (Alice wants to go off-call)
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
    -- Check if anyone else is on call
    SELECT COUNT(*) FROM OnCallDoctors WHERE is_on_call = true AND doctor_id <> 'Alice';
    -- Returns: 1 (Bob is on call)
    
    -- Safe to go off-call
    UPDATE OnCallDoctors SET is_on_call = false WHERE doctor_id = 'Alice';
COMMIT;
 
-- Transaction B (Bob wants to go off-call) - running concurrently
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
    -- Check if anyone else is on call
    SELECT COUNT(*) FROM OnCallDoctors WHERE is_on_call = true AND doctor_id <> 'Bob';
    -- Returns: 1 (Alice is on call - per B's snapshot)
    
    -- Safe to go off-call (or so B thinks)
    UPDATE OnCallDoctors SET is_on_call = false WHERE doctor_id = 'Bob';
COMMIT;
 
-- Result: Both Alice and Bob are off-call, constraint violated!
-- Each transaction's logic was correct individually, but together they broke the invariant
 
-- At SERIALIZABLE, one of these transactions would abort:
-- PostgreSQL SSI: Detects rw-conflict and aborts one transaction
-- SQL Server: Predicate locks would cause one transaction to block/abort

Write Skew Is Common in Practice

Write skew isn't just a theoretical concern. Real-world examples include: double-booking meeting rooms, overdrawing accounts across linked cards, exceeding inventory in multi-warehouse systems, and bypassing rate limits through parallel requests. Any invariant spanning multiple rows is susceptible.

Locking Implementation: Predicate Locking

The traditional approach to Serializable isolation uses Strict Two-Phase Locking (S2PL) enhanced with predicate locks or range locks. While normal locks protect specific rows, predicate locks protect entire conditions—preventing not just modifications to existing rows but also insertions of new rows that would match a query. How Predicate Locking Works: When you execute SELECT * WHERE condition, the database locks not just the matching rows but the entire predicate space Any INSERT or UPDATE that would create a row matching condition is blocked This prevents phantom reads completely—no new rows can appear in your result set

predicate_locking.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
-- SQL Server Serializable with Predicate/Range Locks
 
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
    -- This query establishes a range lock on salary BETWEEN 50000 AND 100000
    SELECT employee_id, name, salary 
    FROM Employees 
    WHERE salary BETWEEN 50000 AND 100000;
    -- Returns: 45 employees
    
    -- Range lock covers:
    -- 1. All existing rows with salary in [50000, 100000]
    -- 2. The "gaps" where new rows could be inserted
    
    -- Long processing...
    WAITFOR DELAY '00:00:30';
    
    -- Re-query: guaranteed same 45 employees
    SELECT COUNT(*) FROM Employees WHERE salary BETWEEN 50000 AND 100000;
    -- Returns: 45 (phantoms prevented)
COMMIT;
 
-- Concurrent Transaction (during first transaction's delay)
BEGIN TRANSACTION;
    -- Try to insert an employee with salary in the locked range
    INSERT INTO Employees (name, salary) VALUES ('New Hire', 75000);
    -- ⏳ BLOCKED! Waiting for first transaction's range lock
    
    -- Try to update an existing employee to fall into the range
    UPDATE Employees SET salary = 80000 WHERE employee_id = 999;
    -- ⏳ BLOCKED! Would violate the predicate lock
    
    -- Only operations outside the locked range can proceed
    INSERT INTO Employees (name, salary) VALUES ('Executive', 150000);
    -- ✅ Proceeds - 150000 is outside [50000, 100000]
COMMIT;

Index Range Locking: In practice, databases implement predicate locking through index range locks. They lock ranges of index entries rather than trying to evaluate arbitrary predicates. This is efficient when queries use indexed columns but may be overly broad for complex conditions.

Predicate Lock Implementation Strategies
Strategy	Precision	Overhead	Used By
Index Range Locks	Good on indexed cols	Moderate	SQL Server
Next-Key Locks	Record + gap before	Moderate	MySQL InnoDB
Table Locks (fallback)	Very coarse	High contention	When no suitable index

Missing Indexes = Table Locks

If a Serializable query can't use an index, the database may lock the entire table. A query like 'SELECT * FROM orders WHERE YEAR(order_date) = 2024' that can't use an index might lock all of 'orders', severely impacting concurrency. Always ensure Serializable queries have supporting indexes.

Serializable Snapshot Isolation (SSI)

PostgreSQL revolutionized Serializable isolation with Serializable Snapshot Isolation (SSI) —an approach that provides true serializability without the locking overhead of traditional methods. SSI is built on top of Snapshot Isolation (similar to Repeatable Read) with additional tracking to detect and abort transactions that would violate serializability. How SSI Works: Transactions use snapshot isolation—reads never block The database tracks read-write dependencies between concurrent transactions If a cycle is detected in the dependency graph \to Serializability would be violated One transaction in the cycle is aborted (serialization failure) The aborted transaction must retry

Converting Mermaid diagram...

ssi_behavior.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
-- PostgreSQL SSI in Action
 
-- Setup
CREATE TABLE account (
    id INTEGER PRIMARY KEY,
    balance DECIMAL(10,2) NOT NULL
);
INSERT INTO account VALUES (1, 500), (2, 500);
-- Invariant: total balance = 1000
 
-- Transaction 1: Move money from account 1 to 2
BEGIN ISOLATION LEVEL SERIALIZABLE;
    SELECT balance FROM account WHERE id = 1;  -- Reads 500
    -- rw-dependency: T1 read row 1
    
    -- T2 commits between here and T1's update
    
    UPDATE account SET balance = balance - 100 WHERE id = 1;
    UPDATE account SET balance = balance + 100 WHERE id = 2;
COMMIT;
-- May fail with: ERROR: could not serialize access due to read/write dependencies
 
-- Transaction 2: Move money from account 2 to 1 (concurrent)
BEGIN ISOLATION LEVEL SERIALIZABLE;
    SELECT balance FROM account WHERE id = 2;  -- Reads 500
    -- rw-dependency: T2 read row 2
    
    UPDATE account SET balance = balance - 200 WHERE id = 2;
    UPDATE account SET balance = balance + 200 WHERE id = 1;
COMMIT;
 
-- SSI detects the cycle:
-- T1 reads row 1, T2 writes row 1 → rw-antidependency T1→T2
-- T2 reads row 2, T1 writes row 2 → rw-antidependency T2→T1
-- Cycle: T1→T2→T1 → one must abort
 
-- Key difference from locking:
-- Neither transaction blocks the other during execution
-- Conflict is detected at commit time
-- Failed transaction must be retried by application

SSI Advantages Over Locking

•No Read Blocking: Readers never wait for writers (snapshot isolation foundation)
•No Writer Blocking on Readers: Writers proceed without waiting for read locks
•Reduced Deadlocks: Classic lock-based deadlocks are eliminated
•Better Read Performance: Queries execute at snapshot speed, not lock-acquisition speed
•Optimistic Approach: Conflicts are detected, not prevented—better for low-conflict workloads

SSI Requires Retry Logic

SSI's optimistic approach means transactions can fail at commit time. Applications MUST implement retry logic. Unlike locking (which waits), SSI aborts conflicting transactions immediately, expecting the application to retry. Test your retry logic with concurrent load.

MySQL's Serializable Implementation

MySQL/InnoDB implements Serializable by essentially converting all reads to locking reads. When you execute a SELECT at Serializable level, InnoDB implicitly converts it to SELECT ... FOR SHARE (or LOCK IN SHARE MODE in older syntax). MySQL Serializable Behavior: All SELECT statements acquire shared locks (not just use snapshots) Gap locking (from Repeatable Read) is still active Combined, this provides serializable behavior through extensive locking

mysql_serializable.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
-- MySQL InnoDB Serializable Behavior
 
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;
    -- This simple SELECT actually acquires S locks!
    SELECT * FROM products WHERE category = 'Electronics';
    -- Equivalent to:
    -- SELECT * FROM products WHERE category = 'Electronics' FOR SHARE;
    
    -- All matched rows are S-locked
    -- Gaps are gap-locked (preventing phantom inserts)
    
    -- This means: ANY concurrent transaction trying to UPDATE
    -- or DELETE these rows will BLOCK
COMMIT;
 
-- Concurrent transaction (will block)
START TRANSACTION;
    -- Attempts to acquire X lock on a row Select'd by first transaction
    UPDATE products SET price = price * 1.1 WHERE product_id = 100;
    -- ⏳ BLOCKED waiting for S lock to release
COMMIT;
 
-- To see the locks MySQL is taking:
SELECT * FROM performance_schema.data_locks;
 
-- You can also see waiting locks:
SELECT * FROM performance_schema.data_lock_waits;
 
-- The key implication: Serializable in MySQL means MORE blocking
-- than lower levels, because reads now compete with writes

MySQL: Repeatable Read vs Serializable
Aspect	Repeatable Read (Default)	Serializable
SELECT behavior	Consistent read (MVCC)	Locking read (FOR SHARE)
Read locks acquired	No	Yes (S locks)
Gap locks	Yes (for locking reads)	Yes (for all reads)
Readers block writers	No	Yes
Writers block readers	Only locking reads	All reads
Concurrency	Higher	Lower

autocommit Interaction

With autocommit=1 (default), each statement is its own transaction. In Serializable mode, each autocommit SELECT acquires and immediately releases locks—minimal impact. But with autocommit=0 or explicit transactions, locks accumulate until commit, potentially causing significant blocking.

Performance Impact of Serializable

Serializable isolation provides maximum consistency at maximum cost. Understanding the performance implications is critical for deciding when this level is justified. Performance Costs by Implementation:

Serializable Performance Characteristics
Implementation	Blocking	Abort Rate	Lock Overhead	Best For
S2PL + Predicate Locks	High	Low (conflicts wait)	High	Low contention, write-heavy
SSI (PostgreSQL)	None	Can be high	Moderate tracking	Read-heavy, low conflict
Locking Reads (MySQL)	Very High	Moderate	High	Short transactions

serializable_performance.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
-- Measuring Serializable overhead (PostgreSQL)
 
-- Test 1: Read operations throughput
-- Setup: Create a table with 100,000 rows
 
-- At Read Committed:
BEGIN;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
    EXPLAIN ANALYZE SELECT * FROM large_table WHERE category = 'A';
    -- Time: ~50ms, no additional overhead
COMMIT;
 
-- At Serializable:
BEGIN ISOLATION LEVEL SERIALIZABLE;
    EXPLAIN ANALYZE SELECT * FROM large_table WHERE category = 'A';
    -- Time: ~55ms, slight overhead for dependency tracking
COMMIT;
-- SSI overhead is low for individual queries
 
-- Test 2: High-contention write scenario
-- Multiple concurrent updates to overlapping rows
 
-- At Serializable, expect:
-- - More transactions aborted due to serialization failures
-- - Need for retry logic
-- - Overall lower throughput under contention
 
-- Monitoring SSI aborts in PostgreSQL:
SELECT 
    datname,
    conflicts,
    confl_tablespace,
    confl_snapshot
FROM pg_stat_database_conflicts
WHERE datname = current_database();
 
-- Check for serialization failures in pg_stat_user_tables:
SELECT 
    relname,
    n_tup_ins,
    n_tup_upd,
    n_tup_del
FROM pg_stat_user_tables;

Performance Considerations

•Throughput Reduction: Under contention, throughput can drop 50-90% compared to Read Committed
•Latency Increase: Locking systems have higher P99 latency due to wait times
•Retry Overhead: SSI systems require application-level retry logic adding complexity
•Lock Escalation: Long transactions may escalate locks, blocking entire tables
•Deadlock Increase: More aggressive locking means more deadlock possibilities
•Index Requirements: Poor indexing causes table-level locks, devastating concurrency

Don't Use Serializable as Default

There's a tempting argument: 'Just use Serializable everywhere for safety.' In practice, this devastates performance. Most applications don't need Serializable semantics, and paying the cost for operations that don't require it wastes resources and causes unnecessary blocking.

When to Use Serializable Isolation

Given its performance cost, Serializable should be reserved for scenarios where absolute correctness is non-negotiable and the application invariants cannot be expressed through database constraints alone. Ideal Candidates for Serializable:

Appropriate Use Cases

•Financial Transactions with Complex Invariants: Transfers where total balances must be preserved across multiple accounts
•Resource Allocation with Shared Constraints: Booking systems where total capacity must not be exceeded
•Write Skew-Prone Operations: Any operation where two transactions reading overlapping data make decisions that together violate constraints
•Audit-Critical Operations: When you must prove the outcome could have happened in some serial order
•Rare, Critical Operations: Low-frequency operations where correctness matters more than throughput

serializable_use_cases.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
-- Use Case 1: Financial Transfer with Total Balance Invariant
-- Total across all accounts in a group must stay constant
 
BEGIN ISOLATION LEVEL SERIALIZABLE;
    -- Read source and destination balances
    SELECT balance FROM accounts WHERE id IN (@src, @dst);
    
    -- Verify sufficient funds
    IF (SELECT balance FROM accounts WHERE id = @src) < @amount THEN
        RAISE EXCEPTION 'Insufficient funds';
    END IF;
    
    -- Execute transfer
    UPDATE accounts SET balance = balance - @amount WHERE id = @src;
    UPDATE accounts SET balance = balance + @amount WHERE id = @dst;
COMMIT;
-- Serializable prevents: another transaction from simultaneously 
-- reading/modifying these accounts in a way that breaks the invariant
 
-- Use Case 2: Meeting Room Double-Booking Prevention
BEGIN ISOLATION LEVEL SERIALIZABLE;
    -- Check if room is available
    SELECT COUNT(*) FROM room_bookings
    WHERE room_id = @room 
    AND start_time < @end_time 
    AND end_time > @start_time;
    
    -- If available, book it
    IF count = 0 THEN
        INSERT INTO room_bookings (room_id, start_time, end_time, user_id)
        VALUES (@room, @start_time, @end_time, @user);
    ELSE
        RAISE EXCEPTION 'Room not available';
    END IF;
COMMIT;
-- Without Serializable, two concurrent requests could both see 
-- count=0 and both insert bookings for the same slot
 
-- Use Case 3: Inventory Allocation Across Warehouses
BEGIN ISOLATION LEVEL SERIALIZABLE;
    -- Check total available across all warehouses
    SELECT SUM(quantity) AS total 
    FROM warehouse_inventory 
    WHERE product_id = @product;
    
    IF total >= @order_qty THEN
        -- Allocate from warehouses (complex logic here)
        UPDATE warehouse_inventory 
        SET quantity = quantity - allocation
        WHERE product_id = @product AND warehouse_id = @chosen_warehouse;
    END IF;
COMMIT;
-- Serializable ensures total inventory check is consistent
-- with the allocation that follows

Alternative: Explicit Locking

Before reaching for Serializable, consider SELECT ... FOR UPDATE. Explicitly locking the rows you intend to modify often achieves the same protection with less overhead. Serializable is for when you can't predict which rows need locking until after the reads.

Database Management SystemsIsolation Levels

Isolation Levels

LevelIntermediate

Duration90 mins

TopicIsolation Levels

4 / 5

Serializable

Complete Isolation: The Gold Standard

If concurrent transactions make you anxious—if you want absolute certainty that no concurrency anomaly of any kind is possible— Serializable isolation is your answer. At this level, the database guarantees that the outcome of concurrent transactions is identical to running them one after another in some serial order. Serializable represents the theoretical ideal: perfect consistency, no anomalies, complete predictability. But perfection comes at a price. Understanding when that price is worth paying—and when it isn't—is key to effective database engineering.

What You Will Learn

By the end of this page, you will understand Serializable isolation's complete guarantee, the two primary implementation strategies (strict two-phase locking with predicate locks vs Serializable Snapshot Isolation), why this level has the highest performance cost, and when the consistency guarantees justify that cost.

Formal Definition of Serializable

Serializable is the highest isolation level in the SQL standard. It provides the ultimate guarantee: the execution of concurrent transactions is equivalent to some serial execution of those transactions. Definition (Informal): If you run transactions T1 and T2 concurrently at the Serializable level, the result will be the same as if you had run either: T1 first, then T2 T2 first, then T1 The database doesn't promise which serial order—just that the outcome matches some serial order. SQL Standard Definition: Serializable prevents all phenomena defined by the standard: Prevents Dirty Reads : Uncommitted data is invisible Prevents Non-Repeatable Reads : Rows remain stable within a transaction Prevents Phantom Reads : No new rows appear in repeated range queries

Complete Anomaly Prevention at Serializable
Anomaly Type	Read Uncommitted	Read Committed	Repeatable Read	Serializable
Dirty Read	❌ Possible	✅ Prevented	✅ Prevented	✅ Prevented
Non-Repeatable Read	❌ Possible	❌ Possible	✅ Prevented	✅ Prevented
Phantom Read	❌ Possible	❌ Possible	❌ Possible*	✅ Prevented
Write Skew	❌ Possible	❌ Possible	❌ Possible	✅ Prevented

* Note : SQL standard permits phantoms at RR, but many implementations prevent them. Beyond the SQL Standard: Write Skew: Serializable also prevents write skew, an anomaly not explicitly mentioned in the SQL standard but absolutely possible at lower isolation levels. Write skew occurs when two transactions read the same data and then make different modifications based on what they read, resulting in a state that violates application invariants.

set_isolation_serializable.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
-- Setting Serializable isolation level in various databases
 
-- SQL Server
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
    -- All reads acquire range locks, preventing all anomalies
    SELECT * FROM Accounts WHERE balance > 1000;
    -- Range lock on "balance > 1000" prevents any changes/inserts to this range
COMMIT;
 
-- PostgreSQL
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- or: BEGIN ISOLATION LEVEL SERIALIZABLE;
BEGIN;
    -- Uses Serializable Snapshot Isolation (SSI)
    -- No blocking, but conflicts cause transaction abort
    SELECT * FROM accounts WHERE balance > 1000;
COMMIT;
 
-- Oracle
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- Oracle's implementation is snapshot-based
-- But requires careful handling of write conflicts
 
-- MySQL/InnoDB
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;
    -- Converts all SELECT to SELECT ... FOR SHARE (previously LOCK IN SHARE MODE)
    -- Gap locks prevent phantom inserts
    SELECT * FROM accounts WHERE balance > 1000;
    -- Implicit: FOR SHARE
COMMIT;

Serializability ≠ Serial Execution

Serializable isolation doesn't mean transactions actually run one at a time. Transactions can execute concurrently—they just produce results equivalent to some serial ordering. The database uses sophisticated algorithms (locks or SSI) to enforce this equivalence while allowing concurrency.

The Write Skew Anomaly

Write skew is perhaps the most insidious concurrency anomaly because it can occur even at Repeatable Read in some implementations. It involves two transactions reading the same data and making non-conflicting writes that together violate a constraint. Classic Example: On-Call Doctors Consider a hospital rule: at least one doctor must always be on call. Both Dr. Alice and Dr. Bob are currently on call. Each wants to take themselves off call, but only if the other is still on call.

Timeline: Write Skew Leading to Constraint Violation
Time	Transaction A (Alice)	Transaction B (Bob)	Constraint Status
T1	BEGIN	BEGIN	Alice=ON, Bob=ON ✅
T2	Check: Is Bob on call? → Yes		Alice=ON, Bob=ON ✅
T3		Check: Is Alice on call? → Yes	Alice=ON, Bob=ON ✅
T4	Set Alice = OFF (Bob is on, it's safe)		Alice=OFF, Bob=ON ✅
T5		Set Bob = OFF (Alice is on, it's safe)	Alice=OFF, Bob=OFF ❌
T6	COMMIT	COMMIT	Constraint violated!

write_skew_demo.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
-- Write Skew Demonstration
-- This can occur at Repeatable Read but NOT at Serializable
 
-- Setup
CREATE TABLE OnCallDoctors (
    doctor_id VARCHAR(10) PRIMARY KEY,
    is_on_call BOOLEAN NOT NULL
);
INSERT INTO OnCallDoctors VALUES ('Alice', true), ('Bob', true);
-- Constraint: COUNT(*) WHERE is_on_call = true >= 1
 
-- Transaction A (Alice wants to go off-call)
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
    -- Check if anyone else is on call
    SELECT COUNT(*) FROM OnCallDoctors WHERE is_on_call = true AND doctor_id <> 'Alice';
    -- Returns: 1 (Bob is on call)
    
    -- Safe to go off-call
    UPDATE OnCallDoctors SET is_on_call = false WHERE doctor_id = 'Alice';
COMMIT;
 
-- Transaction B (Bob wants to go off-call) - running concurrently
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
    -- Check if anyone else is on call
    SELECT COUNT(*) FROM OnCallDoctors WHERE is_on_call = true AND doctor_id <> 'Bob';
    -- Returns: 1 (Alice is on call - per B's snapshot)
    
    -- Safe to go off-call (or so B thinks)
    UPDATE OnCallDoctors SET is_on_call = false WHERE doctor_id = 'Bob';
COMMIT;
 
-- Result: Both Alice and Bob are off-call, constraint violated!
-- Each transaction's logic was correct individually, but together they broke the invariant
 
-- At SERIALIZABLE, one of these transactions would abort:
-- PostgreSQL SSI: Detects rw-conflict and aborts one transaction
-- SQL Server: Predicate locks would cause one transaction to block/abort

Write Skew Is Common in Practice

Write skew isn't just a theoretical concern. Real-world examples include: double-booking meeting rooms, overdrawing accounts across linked cards, exceeding inventory in multi-warehouse systems, and bypassing rate limits through parallel requests. Any invariant spanning multiple rows is susceptible.

Locking Implementation: Predicate Locking

The traditional approach to Serializable isolation uses Strict Two-Phase Locking (S2PL) enhanced with predicate locks or range locks. While normal locks protect specific rows, predicate locks protect entire conditions—preventing not just modifications to existing rows but also insertions of new rows that would match a query. How Predicate Locking Works: When you execute SELECT * WHERE condition, the database locks not just the matching rows but the entire predicate space Any INSERT or UPDATE that would create a row matching condition is blocked This prevents phantom reads completely—no new rows can appear in your result set

predicate_locking.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
-- SQL Server Serializable with Predicate/Range Locks
 
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
    -- This query establishes a range lock on salary BETWEEN 50000 AND 100000
    SELECT employee_id, name, salary 
    FROM Employees 
    WHERE salary BETWEEN 50000 AND 100000;
    -- Returns: 45 employees
    
    -- Range lock covers:
    -- 1. All existing rows with salary in [50000, 100000]
    -- 2. The "gaps" where new rows could be inserted
    
    -- Long processing...
    WAITFOR DELAY '00:00:30';
    
    -- Re-query: guaranteed same 45 employees
    SELECT COUNT(*) FROM Employees WHERE salary BETWEEN 50000 AND 100000;
    -- Returns: 45 (phantoms prevented)
COMMIT;
 
-- Concurrent Transaction (during first transaction's delay)
BEGIN TRANSACTION;
    -- Try to insert an employee with salary in the locked range
    INSERT INTO Employees (name, salary) VALUES ('New Hire', 75000);
    -- ⏳ BLOCKED! Waiting for first transaction's range lock
    
    -- Try to update an existing employee to fall into the range
    UPDATE Employees SET salary = 80000 WHERE employee_id = 999;
    -- ⏳ BLOCKED! Would violate the predicate lock
    
    -- Only operations outside the locked range can proceed
    INSERT INTO Employees (name, salary) VALUES ('Executive', 150000);
    -- ✅ Proceeds - 150000 is outside [50000, 100000]
COMMIT;

Index Range Locking: In practice, databases implement predicate locking through index range locks. They lock ranges of index entries rather than trying to evaluate arbitrary predicates. This is efficient when queries use indexed columns but may be overly broad for complex conditions.

Predicate Lock Implementation Strategies
Strategy	Precision	Overhead	Used By
Index Range Locks	Good on indexed cols	Moderate	SQL Server
Next-Key Locks	Record + gap before	Moderate	MySQL InnoDB
Table Locks (fallback)	Very coarse	High contention	When no suitable index

Missing Indexes = Table Locks

If a Serializable query can't use an index, the database may lock the entire table. A query like 'SELECT * FROM orders WHERE YEAR(order_date) = 2024' that can't use an index might lock all of 'orders', severely impacting concurrency. Always ensure Serializable queries have supporting indexes.

Serializable Snapshot Isolation (SSI)

PostgreSQL revolutionized Serializable isolation with Serializable Snapshot Isolation (SSI) —an approach that provides true serializability without the locking overhead of traditional methods. SSI is built on top of Snapshot Isolation (similar to Repeatable Read) with additional tracking to detect and abort transactions that would violate serializability. How SSI Works: Transactions use snapshot isolation—reads never block The database tracks read-write dependencies between concurrent transactions If a cycle is detected in the dependency graph \to Serializability would be violated One transaction in the cycle is aborted (serialization failure) The aborted transaction must retry

Converting Mermaid diagram...

ssi_behavior.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
-- PostgreSQL SSI in Action
 
-- Setup
CREATE TABLE account (
    id INTEGER PRIMARY KEY,
    balance DECIMAL(10,2) NOT NULL
);
INSERT INTO account VALUES (1, 500), (2, 500);
-- Invariant: total balance = 1000
 
-- Transaction 1: Move money from account 1 to 2
BEGIN ISOLATION LEVEL SERIALIZABLE;
    SELECT balance FROM account WHERE id = 1;  -- Reads 500
    -- rw-dependency: T1 read row 1
    
    -- T2 commits between here and T1's update
    
    UPDATE account SET balance = balance - 100 WHERE id = 1;
    UPDATE account SET balance = balance + 100 WHERE id = 2;
COMMIT;
-- May fail with: ERROR: could not serialize access due to read/write dependencies
 
-- Transaction 2: Move money from account 2 to 1 (concurrent)
BEGIN ISOLATION LEVEL SERIALIZABLE;
    SELECT balance FROM account WHERE id = 2;  -- Reads 500
    -- rw-dependency: T2 read row 2
    
    UPDATE account SET balance = balance - 200 WHERE id = 2;
    UPDATE account SET balance = balance + 200 WHERE id = 1;
COMMIT;
 
-- SSI detects the cycle:
-- T1 reads row 1, T2 writes row 1 → rw-antidependency T1→T2
-- T2 reads row 2, T1 writes row 2 → rw-antidependency T2→T1
-- Cycle: T1→T2→T1 → one must abort
 
-- Key difference from locking:
-- Neither transaction blocks the other during execution
-- Conflict is detected at commit time
-- Failed transaction must be retried by application

SSI Advantages Over Locking

•No Read Blocking: Readers never wait for writers (snapshot isolation foundation)
•No Writer Blocking on Readers: Writers proceed without waiting for read locks
•Reduced Deadlocks: Classic lock-based deadlocks are eliminated
•Better Read Performance: Queries execute at snapshot speed, not lock-acquisition speed
•Optimistic Approach: Conflicts are detected, not prevented—better for low-conflict workloads

SSI Requires Retry Logic

SSI's optimistic approach means transactions can fail at commit time. Applications MUST implement retry logic. Unlike locking (which waits), SSI aborts conflicting transactions immediately, expecting the application to retry. Test your retry logic with concurrent load.

MySQL's Serializable Implementation

MySQL/InnoDB implements Serializable by essentially converting all reads to locking reads. When you execute a SELECT at Serializable level, InnoDB implicitly converts it to SELECT ... FOR SHARE (or LOCK IN SHARE MODE in older syntax). MySQL Serializable Behavior: All SELECT statements acquire shared locks (not just use snapshots) Gap locking (from Repeatable Read) is still active Combined, this provides serializable behavior through extensive locking

mysql_serializable.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
-- MySQL InnoDB Serializable Behavior
 
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;
    -- This simple SELECT actually acquires S locks!
    SELECT * FROM products WHERE category = 'Electronics';
    -- Equivalent to:
    -- SELECT * FROM products WHERE category = 'Electronics' FOR SHARE;
    
    -- All matched rows are S-locked
    -- Gaps are gap-locked (preventing phantom inserts)
    
    -- This means: ANY concurrent transaction trying to UPDATE
    -- or DELETE these rows will BLOCK
COMMIT;
 
-- Concurrent transaction (will block)
START TRANSACTION;
    -- Attempts to acquire X lock on a row Select'd by first transaction
    UPDATE products SET price = price * 1.1 WHERE product_id = 100;
    -- ⏳ BLOCKED waiting for S lock to release
COMMIT;
 
-- To see the locks MySQL is taking:
SELECT * FROM performance_schema.data_locks;
 
-- You can also see waiting locks:
SELECT * FROM performance_schema.data_lock_waits;
 
-- The key implication: Serializable in MySQL means MORE blocking
-- than lower levels, because reads now compete with writes

MySQL: Repeatable Read vs Serializable
Aspect	Repeatable Read (Default)	Serializable
SELECT behavior	Consistent read (MVCC)	Locking read (FOR SHARE)
Read locks acquired	No	Yes (S locks)
Gap locks	Yes (for locking reads)	Yes (for all reads)
Readers block writers	No	Yes
Writers block readers	Only locking reads	All reads
Concurrency	Higher	Lower

autocommit Interaction

With autocommit=1 (default), each statement is its own transaction. In Serializable mode, each autocommit SELECT acquires and immediately releases locks—minimal impact. But with autocommit=0 or explicit transactions, locks accumulate until commit, potentially causing significant blocking.

Performance Impact of Serializable

Serializable isolation provides maximum consistency at maximum cost. Understanding the performance implications is critical for deciding when this level is justified. Performance Costs by Implementation:

Serializable Performance Characteristics
Implementation	Blocking	Abort Rate	Lock Overhead	Best For
S2PL + Predicate Locks	High	Low (conflicts wait)	High	Low contention, write-heavy
SSI (PostgreSQL)	None	Can be high	Moderate tracking	Read-heavy, low conflict
Locking Reads (MySQL)	Very High	Moderate	High	Short transactions

serializable_performance.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
-- Measuring Serializable overhead (PostgreSQL)
 
-- Test 1: Read operations throughput
-- Setup: Create a table with 100,000 rows
 
-- At Read Committed:
BEGIN;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
    EXPLAIN ANALYZE SELECT * FROM large_table WHERE category = 'A';
    -- Time: ~50ms, no additional overhead
COMMIT;
 
-- At Serializable:
BEGIN ISOLATION LEVEL SERIALIZABLE;
    EXPLAIN ANALYZE SELECT * FROM large_table WHERE category = 'A';
    -- Time: ~55ms, slight overhead for dependency tracking
COMMIT;
-- SSI overhead is low for individual queries
 
-- Test 2: High-contention write scenario
-- Multiple concurrent updates to overlapping rows
 
-- At Serializable, expect:
-- - More transactions aborted due to serialization failures
-- - Need for retry logic
-- - Overall lower throughput under contention
 
-- Monitoring SSI aborts in PostgreSQL:
SELECT 
    datname,
    conflicts,
    confl_tablespace,
    confl_snapshot
FROM pg_stat_database_conflicts
WHERE datname = current_database();
 
-- Check for serialization failures in pg_stat_user_tables:
SELECT 
    relname,
    n_tup_ins,
    n_tup_upd,
    n_tup_del
FROM pg_stat_user_tables;

Performance Considerations

•Throughput Reduction: Under contention, throughput can drop 50-90% compared to Read Committed
•Latency Increase: Locking systems have higher P99 latency due to wait times
•Retry Overhead: SSI systems require application-level retry logic adding complexity
•Lock Escalation: Long transactions may escalate locks, blocking entire tables
•Deadlock Increase: More aggressive locking means more deadlock possibilities
•Index Requirements: Poor indexing causes table-level locks, devastating concurrency

Don't Use Serializable as Default

There's a tempting argument: 'Just use Serializable everywhere for safety.' In practice, this devastates performance. Most applications don't need Serializable semantics, and paying the cost for operations that don't require it wastes resources and causes unnecessary blocking.

When to Use Serializable Isolation

Given its performance cost, Serializable should be reserved for scenarios where absolute correctness is non-negotiable and the application invariants cannot be expressed through database constraints alone. Ideal Candidates for Serializable:

Appropriate Use Cases

•Financial Transactions with Complex Invariants: Transfers where total balances must be preserved across multiple accounts
•Resource Allocation with Shared Constraints: Booking systems where total capacity must not be exceeded
•Write Skew-Prone Operations: Any operation where two transactions reading overlapping data make decisions that together violate constraints
•Audit-Critical Operations: When you must prove the outcome could have happened in some serial order
•Rare, Critical Operations: Low-frequency operations where correctness matters more than throughput

serializable_use_cases.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
-- Use Case 1: Financial Transfer with Total Balance Invariant
-- Total across all accounts in a group must stay constant
 
BEGIN ISOLATION LEVEL SERIALIZABLE;
    -- Read source and destination balances
    SELECT balance FROM accounts WHERE id IN (@src, @dst);
    
    -- Verify sufficient funds
    IF (SELECT balance FROM accounts WHERE id = @src) < @amount THEN
        RAISE EXCEPTION 'Insufficient funds';
    END IF;
    
    -- Execute transfer
    UPDATE accounts SET balance = balance - @amount WHERE id = @src;
    UPDATE accounts SET balance = balance + @amount WHERE id = @dst;
COMMIT;
-- Serializable prevents: another transaction from simultaneously 
-- reading/modifying these accounts in a way that breaks the invariant
 
-- Use Case 2: Meeting Room Double-Booking Prevention
BEGIN ISOLATION LEVEL SERIALIZABLE;
    -- Check if room is available
    SELECT COUNT(*) FROM room_bookings
    WHERE room_id = @room 
    AND start_time < @end_time 
    AND end_time > @start_time;
    
    -- If available, book it
    IF count = 0 THEN
        INSERT INTO room_bookings (room_id, start_time, end_time, user_id)
        VALUES (@room, @start_time, @end_time, @user);
    ELSE
        RAISE EXCEPTION 'Room not available';
    END IF;
COMMIT;
-- Without Serializable, two concurrent requests could both see 
-- count=0 and both insert bookings for the same slot
 
-- Use Case 3: Inventory Allocation Across Warehouses
BEGIN ISOLATION LEVEL SERIALIZABLE;
    -- Check total available across all warehouses
    SELECT SUM(quantity) AS total 
    FROM warehouse_inventory 
    WHERE product_id = @product;
    
    IF total >= @order_qty THEN
        -- Allocate from warehouses (complex logic here)
        UPDATE warehouse_inventory 
        SET quantity = quantity - allocation
        WHERE product_id = @product AND warehouse_id = @chosen_warehouse;
    END IF;
COMMIT;
-- Serializable ensures total inventory check is consistent
-- with the allocation that follows

Alternative: Explicit Locking

Before reaching for Serializable, consider SELECT ... FOR UPDATE. Explicitly locking the rows you intend to modify often achieves the same protection with less overhead. Serializable is for when you can't predict which rows need locking until after the reads.

Serializable

Complete Isolation: The Gold Standard

Formal Definition of Serializable

The Write Skew Anomaly

Locking Implementation: Predicate Locking

Serializable Snapshot Isolation (SSI)

MySQL's Serializable Implementation

Performance Impact of Serializable

When to Use Serializable Isolation

There’s more to understand.

Serializable

Complete Isolation: The Gold Standard

Formal Definition of Serializable

The Write Skew Anomaly

Locking Implementation: Predicate Locking

Serializable Snapshot Isolation (SSI)

MySQL's Serializable Implementation

Performance Impact of Serializable

When to Use Serializable Isolation

There’s more to understand.