Isolation: Concurrent… — ACID Properties: The… | OneNoughtOne

0/278

Database Management SystemsACID Properties

ACID Properties: The Foundation of Reliable Transactions

LevelIntermediate

Duration60 mins

TopicACID Properties

3 / 5

Isolation: Concurrent Transactions in Harmony

When Two Transactions Meet

Two bank customers access their shared family account at the exact same moment. One is depositing $500; the other is withdrawing $300. The account has $1,000. What should the final balance be? Mathematically, the answer is obvious: $1,000 + $500 - $300 = $1,200. But in a database system, without proper controls, the following disaster could occur: Transaction A reads balance: $1,000 Transaction B reads balance: $1,000 Transaction B writes new balance: $1,000 + $500 = $1,500 Transaction A writes new balance: $1,000 - $300 = $700 Final balance: $700. The $500 deposit vanished because Transaction A overwrote it with stale data. This is the lost update problem—one of several concurrency anomalies that isolation prevents.

What You Will Learn

By the end of this page, you will understand how databases manage concurrent transactions, the spectrum of concurrency anomalies that can occur, and how isolation levels trade correctness for performance. You'll learn to choose the right isolation level for your use case and recognize when stronger guarantees are necessary.

Defining Isolation: The Illusion of Serialization

Isolation is the ACID property that defines how and when the effects of one transaction become visible to other concurrent transactions. At its strongest, isolation creates the illusion that each transaction executes in complete solitude, as if no other transactions exist. Formal Definition: A database system exhibits isolation when the result of executing concurrent transactions is the same as if those transactions had been executed serially—one after another, in some order. This property is also called serializability : concurrent execution produces results equivalent to some serial ordering. The database doesn't actually run transactions one at a time (that would be too slow), but it ensures that the observable outcomes match a possible serial execution.

Serializable ≠ Serial

Serializability doesn't mean transactions run sequentially—it means they APPEAR to run sequentially. In reality, operations interleave for performance. The database uses locking, timestamps, or validation to ensure that despite this interleaving, no anomalies occur that couldn't happen in a serial execution.

Why Isolation is Challenging: Concurrency is essential for database performance. A system serving thousands of users cannot make each one wait for every other transaction to complete. But concurrency introduces the possibility of interference: One transaction reads data that another is in the process of modifying Two transactions modify the same data based on a shared initial read A transaction makes decisions based on data that another transaction then changes Query results change between repeated executions within the same transaction Isolation mechanisms prevent these interferences—or at least define which ones are acceptable for a given workload.

Converting Mermaid diagram...

Concurrency Anomalies: What Can Go Wrong

Before understanding isolation levels, we must understand the problems they prevent. These are the concurrency anomalies —situations where concurrent execution produces results that could not occur in any serial execution.

The Five Classic Anomalies

•Dirty Read — Transaction A reads data written by Transaction B before B commits. If B rolls back, A has read data that never officially existed.
•Non-Repeatable Read — Transaction A reads a row, Transaction B modifies and commits that row, then A reads again and gets different data. The same query produces different results within one transaction.
•Phantom Read — Transaction A queries a range that returns N rows. Transaction B inserts a new row matching the query's criteria. A repeats the query and gets N+1 rows. New rows 'appear' mid-transaction.
•Lost Update — Two transactions read the same data, then both update it based on their reads. The second write overwrites the first without incorporating its changes.
•Write Skew — Two transactions read overlapping data, then make disjoint updates that together violate an invariant that neither individually violates.

Detailed Example: Dirty Read

dirty-read-example.txt
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
Timeline showing a dirty read:
 
Time    Transaction A                    Transaction B
─────   ─────────────────────────────    ─────────────────────────────
t1      BEGIN                            
t2                                       BEGIN
t3                                       UPDATE accounts 
                                         SET balance = balance + 1000
                                         WHERE account_id = 'A'
                                         -- Balance now 2000 (uncommitted)
t4      SELECT balance FROM accounts
        WHERE account_id = 'A'
        -- DIRTY READ: sees 2000 (uncommitted data)
t5      -- Makes business decision based on 2000
t6                                       ROLLBACK
                                         -- Balance reverts to 1000
t7      -- Transaction A's decision was based on
        -- data that never existed!
 
Result: A acted on phantom data. Depending on the action, this could cause
real-world problems (approving a loan, shipping goods, etc.)

Detailed Example: Lost Update

lost-update-example.txt
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
Timeline showing a lost update:
 
Initial: account balance = 1000
 
Time    Transaction A                    Transaction B
─────   ─────────────────────────────    ─────────────────────────────
t1      BEGIN                            BEGIN
t2      SELECT balance FROM accounts     
        WHERE account_id = 'A'
        -- Reads 1000
t3                                       SELECT balance FROM accounts
                                         WHERE account_id = 'A'
                                         -- Reads 1000
t4      UPDATE accounts 
        SET balance = 1000 - 300 = 700
        WHERE account_id = 'A'
t5      COMMIT
        -- Balance is 700
t6                                       UPDATE accounts 
                                         SET balance = 1000 + 500 = 1500
                                         WHERE account_id = 'A'
t7                                       COMMIT
                                         -- Balance is 1500
 
Expected: 1000 - 300 + 500 = 1200
Actual: 1500 (Transaction A's withdrawal was lost)
 
The $300 withdrawal simply vanished because B overwrote A's changes
using a stale read of the original balance.

Write Skew: The Subtle Killer

Write skew is particularly insidious because each individual transaction appears correct. Example: A hospital has two on-call doctors. The invariant is 'at least one doctor must remain on call.' Two transactions simultaneously check that two doctors are on call, then each removes one doctor. Individually correct; together, zero doctors on call. Write skew violates cross-row invariants.

Isolation Levels: Trading Correctness for Performance

Full serializability prevents all anomalies but comes with significant performance costs (more locking, more aborted transactions, less concurrency). Most databases offer multiple isolation levels, each allowing certain anomalies in exchange for better performance. The SQL standard defines four isolation levels, from weakest to strongest:

SQL Standard Isolation Levels and Anomaly Prevention
Isolation Level	Dirty Read	Non-Repeatable Read	Phantom Read	Lost Update	Write Skew
Read Uncommitted	Possible	Possible	Possible	Possible	Possible
Read Committed	Prevented	Possible	Possible	Possible	Possible
Repeatable Read	Prevented	Prevented	Possible*	Prevented	Possible
Serializable	Prevented	Prevented	Prevented	Prevented	Prevented

Implementation Varies by Database

The SQL standard defines these levels by which anomalies they prevent, but database implementations vary. PostgreSQL's Repeatable Read actually prevents phantom reads. MySQL's Repeatable Read uses gap locking that also prevents many phantoms. Oracle doesn't have a true Read Uncommitted. Always test your specific database's behavior.

Level 1: Read Uncommitted The Wild West of isolation. A transaction can see uncommitted changes from other transactions. Almost never used in practice because dirty reads cause unpredictable behavior. Use case: Some read-only analytics on transactional tables where approximate results are acceptable and stale/uncommitted data won't cause harm. Level 2: Read Committed The most common default. A transaction only sees data committed before each statement begins. Each statement gets a fresh view of committed data, but that view can change between statements in the same transaction. Use case: General-purpose OLTP workloads where individual statements need current data but cross-statement consistency isn't critical.

read-committed-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
-- Read Committed behavior example
-- Transaction A's view can change between statements
 
-- Transaction A                          -- Transaction B
BEGIN;                                    
SET TRANSACTION ISOLATION LEVEL 
READ COMMITTED;
 
SELECT balance FROM accounts              
WHERE account_id = 1;
-- Returns: 1000
                                          BEGIN;
                                          UPDATE accounts 
                                          SET balance = 1500
                                          WHERE account_id = 1;
                                          COMMIT;
                                          
SELECT balance FROM accounts
WHERE account_id = 1;
-- Returns: 1500 (different!)
-- This is allowed at Read Committed
 
COMMIT;

Level 3: Repeatable Read A transaction sees a consistent snapshot of committed data as of the transaction's start time (in snapshot-based implementations) or holds locks on all read data (in lock-based implementations). The same query returns the same results throughout the transaction. Use case: Reporting that requires consistent totals across multiple queries; multi-statement operations where intermediate decisions depend on earlier reads. Level 4: Serializable The gold standard. Transactions behave as if executed serially. All anomalies are prevented. In practice, databases achieve this through strict two-phase locking, serializable snapshot isolation, or optimistic concurrency control with validation. Use case: Any operation where correctness is paramount: financial transactions, inventory management, anything involving invariants across multiple rows or tables.

Choosing an Isolation Level: Practical Guidelines

Selecting the appropriate isolation level requires understanding your application's correctness requirements and performance constraints. Here's a decision framework:

Decision Framework for Isolation Levels

•Start with Serializable — Begin with the strongest guarantee and only weaken if you have a specific, measured performance need. Premature optimization with weaker isolation causes bugs that are nightmares to diagnose.
•Identify Invariants — What constraints must your transactions maintain? If invariants span multiple rows or require multiple read-modify-write cycles, you likely need Repeatable Read or Serializable.
•Measure Contention — Profile your workload. How often do transactions touch the same data? High contention means isolation level choice significantly impacts throughput. Low contention means stronger isolation has minimal cost.
•Understand Read Patterns — If a transaction only reads (no writes), many anomalies become irrelevant. Read-only transactions can often use Read Committed without issues.
•Test Under Load — Anomalies often appear only under concurrent load. Single-threaded testing won't reveal isolation bugs. Use tools like jepsen or custom concurrent test harnesses.

When Read Committed Is Sufficient

•Single-statement transactions
•Read-then-write uses SELECT FOR UPDATE
•No cross-statement consistency needed
•Application handles retry on conflicts
•Display-only queries (stale OK)

When Serializable Is Necessary

•Financial transactions
•Multi-row invariant enforcement
•Aggregate calculations over live data
•Complex workflows with multiple checks
•High-stakes or regulated domains

SELECT FOR UPDATE: Your Best Friend

Many applications can achieve correctness at Read Committed by using SELECT ... FOR UPDATE. This locks the selected rows, preventing other transactions from modifying them until you commit. It's a surgical way to get strong isolation where needed without the overhead of Serializable everywhere.

select-for-update.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- Using SELECT FOR UPDATE to prevent lost updates at Read Committed
 
-- Without FOR UPDATE: vulnerable to lost update
BEGIN;
SELECT balance FROM accounts WHERE account_id = 1;  -- Gets 1000
-- Another transaction could modify this row right now!
UPDATE accounts SET balance = balance - 300 WHERE account_id = 1;
COMMIT;
 
-- With FOR UPDATE: protected from concurrent modification
BEGIN;
SELECT balance FROM accounts WHERE account_id = 1 FOR UPDATE;
-- Row is now locked; other transactions must wait
-- Safe to read-modify-write
UPDATE accounts SET balance = balance - 300 WHERE account_id = 1;
COMMIT;
-- Lock released; other transactions can proceed

How Databases Implement Isolation

Databases use various mechanisms to enforce isolation. Understanding these mechanisms helps explain performance characteristics and failure modes.

Major Isolation Implementation Strategies

•Two-Phase Locking (2PL) — Transactions acquire locks before accessing data. Locks are held until commit (strict 2PL). Prevents conflicts through mutual exclusion. Used traditionally by Oracle, SQL Server, MySQL (InnoDB).
•Multi-Version Concurrency Control (MVCC) — The database maintains multiple versions of each row. Readers see a consistent snapshot without blocking writers. Writers create new versions. Used by PostgreSQL, MySQL (InnoDB for reads), Oracle (for reads).
•Optimistic Concurrency Control (OCC) — Transactions proceed without locks, recording what they read. At commit time, the database validates that no conflicts occurred. If conflicts are detected, the transaction aborts and must retry.
•Serializable Snapshot Isolation (SSI) — An advanced form of MVCC that tracks dependencies between concurrent transactions and aborts any that would create cycles (indicating non-serializable behavior). Used by PostgreSQL for Serializable level.

Isolation Mechanisms Comparison
Mechanism	Readers Block Writers?	Writers Block Readers?	Abort Rate	Best For
Two-Phase Locking	Yes (for writes)	Yes	Low (waits instead)	Write-heavy, low contention
MVCC (Read Committed)	No	No	N/A	Read-heavy, tolerates non-repeatable reads
MVCC (Snapshot)	No	No	Medium (on conflict)	Mixed workloads, consistent reads
SSI (Serializable)	No	No	Higher (on write conflicts)	Correctness-critical, moderate contention

MVCC in Detail: In MVCC systems like PostgreSQL: Each row has hidden columns: xmin (transaction that created this version), xmax (transaction that deleted/replaced this version) When a row is updated, the old version is marked with xmax = current transaction, and a new version is created A transaction can only see versions where: xmin was committed before this transaction started (or is this transaction) xmax is null or was not committed before this transaction started This allows readers and writers to proceed simultaneously without blocking

MVCC and Bloat

MVCC creates multiple versions of rows. Old versions that no transaction needs anymore must be cleaned up (vacuumed in PostgreSQL, purged in MySQL). If cleanup can't keep up with update rate, table bloat occurs. This is a trade-off for the concurrency benefits MVCC provides.

Handling Serialization Failures

When using Serializable isolation (or Repeatable Read in some databases), the database may detect that concurrent transactions cannot be serialized without anomalies. Rather than allowing the anomaly, it aborts one of the transactions with a serialization failure . This is not an error—it's the isolation mechanism working correctly. The application must be prepared to retry the transaction.

retry-serialization-failure.py
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
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
import psycopg2
from psycopg2 import errors
import time
 
MAX_RETRIES = 3
RETRY_DELAY_MS = 100
 
def execute_with_retry(operation_func):
    """
    Execute a database operation with automatic retry on serialization failure.
    Essential for Serializable isolation level.
    """
    retries = 0
    
    while retries < MAX_RETRIES:
        conn = get_db_connection()
        try:
            conn.set_isolation_level(
                psycopg2.extensions.ISOLATION_LEVEL_SERIALIZABLE
            )
            
            result = operation_func(conn)
            conn.commit()
            return result
            
        except errors.SerializationFailure as e:
            # Transaction was aborted due to concurrent conflict
            # This is expected behavior at Serializable level
            conn.rollback()
            retries += 1
            
            if retries < MAX_RETRIES:
                # Exponential backoff with jitter
                delay = RETRY_DELAY_MS * (2 ** retries) + random.uniform(0, 50)
                logging.info(f"Serialization failure, retry {retries}/{MAX_RETRIES} "
                           f"after {delay}ms")
                time.sleep(delay / 1000)
            else:
                logging.error("Max retries exceeded for serializable transaction")
                raise
                
        except Exception as e:
            conn.rollback()
            raise
            
        finally:
            conn.close()
    
    raise Exception("Transaction failed after max retries")
 
 
# Example usage
def transfer_funds(conn, from_account, to_account, amount):
    cursor = conn.cursor()
    
    # At Serializable, we don't need FOR UPDATE—the database
    # ensures serializable behavior automatically
    cursor.execute(
        "SELECT balance FROM accounts WHERE account_id = %s",
        (from_account,)
    )
    from_balance = cursor.fetchone()[0]
    
    if from_balance < amount:
        raise InsufficientFundsError()
    
    cursor.execute(
        "UPDATE accounts SET balance = balance - %s WHERE account_id = %s",
        (amount, from_account)
    )
    cursor.execute(
        "UPDATE accounts SET balance = balance + %s WHERE account_id = %s",
        (amount, to_account)
    )
    
    return True
 
# Run with automatic retry
execute_with_retry(
    lambda conn: transfer_funds(conn, 'savings', 'checking', 500)
)

Serialization Failures Are a Feature

Don't fear serialization failures—embrace them. They mean the database is correctly protecting you from anomalies. Design your application to expect them (idempotent operations, retry logic). A few retries are far cheaper than debugging data corruption from concurrency bugs.

Best Practices for Handling Serialization Failures

•Use exponential backoff — Don't retry immediately; give other transactions time to complete. Add jitter to prevent synchronized retries.
•Keep transactions short — Longer transactions have more time to conflict. Minimize the window between first read and commit.
•Make operations idempotent — If a transaction is retried, applying it twice should produce the same result as applying it once. Use unique request IDs to detect duplicates.
•Log serialization failures — They're expected but monitoring their rate helps identify contention hotspots.
•Consider transaction ordering — If transactions consistently access the same rows, accessing them in the same order reduces conflict likelihood.

Isolation Across Database Systems

Different database systems implement isolation levels with varying behavior. Understanding these differences is essential for portable applications.

Isolation Level Behavior Across Database Systems
Database	Default Level	Serializable Implementation	Notable Behavior
PostgreSQL	Read Committed	SSI (MVCC-based, may abort)	Repeatable Read prevents phantoms; Serializable is true serializable
MySQL (InnoDB)	Repeatable Read	Gap locking + traditional locking	Repeatable Read uses consistent snapshot; may have some phantom prevention
Oracle	Read Committed	Requires manual locking (SELECT FOR UPDATE)	No true Serializable; 'Serializable' is actually snapshot isolation
SQL Server	Read Committed	Lock-based or snapshot (configurable)	Has READ_COMMITTED_SNAPSHOT option for MVCC-like behavior
SQLite	Serializable	Database-level locking	Simple model; all transactions are effectively serializable

Oracle's 'Serializable' Isn't

Oracle's Serializable isolation level is actually Snapshot Isolation, which allows write skew anomalies. If you need true serializability in Oracle, you must use explicit locking (SELECT FOR UPDATE) on all related rows. This is a common source of bugs when porting applications.

set-isolation-level.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
-- PostgreSQL: Set isolation level for a transaction
BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- All operations in this transaction use Serializable isolation
COMMIT;
 
-- Or set per-session default
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL SERIALIZABLE;
 
-- MySQL: Set isolation level
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- Or set globally/session
SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
 
-- SQL Server: Set isolation level
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- Or use the SNAPSHOT option (requires database-level config)
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
 
-- Oracle: "Serializable" (actually snapshot)
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Database Management SystemsACID Properties

ACID Properties: The Foundation of Reliable Transactions

LevelIntermediate

Duration60 mins

TopicACID Properties

3 / 5

Isolation: Concurrent Transactions in Harmony

When Two Transactions Meet

Two bank customers access their shared family account at the exact same moment. One is depositing $500; the other is withdrawing $300. The account has $1,000. What should the final balance be? Mathematically, the answer is obvious: $1,000 + $500 - $300 = $1,200. But in a database system, without proper controls, the following disaster could occur: Transaction A reads balance: $1,000 Transaction B reads balance: $1,000 Transaction B writes new balance: $1,000 + $500 = $1,500 Transaction A writes new balance: $1,000 - $300 = $700 Final balance: $700. The $500 deposit vanished because Transaction A overwrote it with stale data. This is the lost update problem—one of several concurrency anomalies that isolation prevents.

What You Will Learn

By the end of this page, you will understand how databases manage concurrent transactions, the spectrum of concurrency anomalies that can occur, and how isolation levels trade correctness for performance. You'll learn to choose the right isolation level for your use case and recognize when stronger guarantees are necessary.

Defining Isolation: The Illusion of Serialization

Isolation is the ACID property that defines how and when the effects of one transaction become visible to other concurrent transactions. At its strongest, isolation creates the illusion that each transaction executes in complete solitude, as if no other transactions exist. Formal Definition: A database system exhibits isolation when the result of executing concurrent transactions is the same as if those transactions had been executed serially—one after another, in some order. This property is also called serializability : concurrent execution produces results equivalent to some serial ordering. The database doesn't actually run transactions one at a time (that would be too slow), but it ensures that the observable outcomes match a possible serial execution.

Serializable ≠ Serial

Serializability doesn't mean transactions run sequentially—it means they APPEAR to run sequentially. In reality, operations interleave for performance. The database uses locking, timestamps, or validation to ensure that despite this interleaving, no anomalies occur that couldn't happen in a serial execution.

Why Isolation is Challenging: Concurrency is essential for database performance. A system serving thousands of users cannot make each one wait for every other transaction to complete. But concurrency introduces the possibility of interference: One transaction reads data that another is in the process of modifying Two transactions modify the same data based on a shared initial read A transaction makes decisions based on data that another transaction then changes Query results change between repeated executions within the same transaction Isolation mechanisms prevent these interferences—or at least define which ones are acceptable for a given workload.

Converting Mermaid diagram...

Concurrency Anomalies: What Can Go Wrong

Before understanding isolation levels, we must understand the problems they prevent. These are the concurrency anomalies —situations where concurrent execution produces results that could not occur in any serial execution.

The Five Classic Anomalies

•Dirty Read — Transaction A reads data written by Transaction B before B commits. If B rolls back, A has read data that never officially existed.
•Non-Repeatable Read — Transaction A reads a row, Transaction B modifies and commits that row, then A reads again and gets different data. The same query produces different results within one transaction.
•Phantom Read — Transaction A queries a range that returns N rows. Transaction B inserts a new row matching the query's criteria. A repeats the query and gets N+1 rows. New rows 'appear' mid-transaction.
•Lost Update — Two transactions read the same data, then both update it based on their reads. The second write overwrites the first without incorporating its changes.
•Write Skew — Two transactions read overlapping data, then make disjoint updates that together violate an invariant that neither individually violates.

Detailed Example: Dirty Read

dirty-read-example.txt
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
Timeline showing a dirty read:
 
Time    Transaction A                    Transaction B
─────   ─────────────────────────────    ─────────────────────────────
t1      BEGIN                            
t2                                       BEGIN
t3                                       UPDATE accounts 
                                         SET balance = balance + 1000
                                         WHERE account_id = 'A'
                                         -- Balance now 2000 (uncommitted)
t4      SELECT balance FROM accounts
        WHERE account_id = 'A'
        -- DIRTY READ: sees 2000 (uncommitted data)
t5      -- Makes business decision based on 2000
t6                                       ROLLBACK
                                         -- Balance reverts to 1000
t7      -- Transaction A's decision was based on
        -- data that never existed!
 
Result: A acted on phantom data. Depending on the action, this could cause
real-world problems (approving a loan, shipping goods, etc.)

Detailed Example: Lost Update

lost-update-example.txt
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
Timeline showing a lost update:
 
Initial: account balance = 1000
 
Time    Transaction A                    Transaction B
─────   ─────────────────────────────    ─────────────────────────────
t1      BEGIN                            BEGIN
t2      SELECT balance FROM accounts     
        WHERE account_id = 'A'
        -- Reads 1000
t3                                       SELECT balance FROM accounts
                                         WHERE account_id = 'A'
                                         -- Reads 1000
t4      UPDATE accounts 
        SET balance = 1000 - 300 = 700
        WHERE account_id = 'A'
t5      COMMIT
        -- Balance is 700
t6                                       UPDATE accounts 
                                         SET balance = 1000 + 500 = 1500
                                         WHERE account_id = 'A'
t7                                       COMMIT
                                         -- Balance is 1500
 
Expected: 1000 - 300 + 500 = 1200
Actual: 1500 (Transaction A's withdrawal was lost)
 
The $300 withdrawal simply vanished because B overwrote A's changes
using a stale read of the original balance.

Write Skew: The Subtle Killer

Write skew is particularly insidious because each individual transaction appears correct. Example: A hospital has two on-call doctors. The invariant is 'at least one doctor must remain on call.' Two transactions simultaneously check that two doctors are on call, then each removes one doctor. Individually correct; together, zero doctors on call. Write skew violates cross-row invariants.

Isolation Levels: Trading Correctness for Performance

Full serializability prevents all anomalies but comes with significant performance costs (more locking, more aborted transactions, less concurrency). Most databases offer multiple isolation levels, each allowing certain anomalies in exchange for better performance. The SQL standard defines four isolation levels, from weakest to strongest:

SQL Standard Isolation Levels and Anomaly Prevention
Isolation Level	Dirty Read	Non-Repeatable Read	Phantom Read	Lost Update	Write Skew
Read Uncommitted	Possible	Possible	Possible	Possible	Possible
Read Committed	Prevented	Possible	Possible	Possible	Possible
Repeatable Read	Prevented	Prevented	Possible*	Prevented	Possible
Serializable	Prevented	Prevented	Prevented	Prevented	Prevented

Implementation Varies by Database

The SQL standard defines these levels by which anomalies they prevent, but database implementations vary. PostgreSQL's Repeatable Read actually prevents phantom reads. MySQL's Repeatable Read uses gap locking that also prevents many phantoms. Oracle doesn't have a true Read Uncommitted. Always test your specific database's behavior.

Level 1: Read Uncommitted The Wild West of isolation. A transaction can see uncommitted changes from other transactions. Almost never used in practice because dirty reads cause unpredictable behavior. Use case: Some read-only analytics on transactional tables where approximate results are acceptable and stale/uncommitted data won't cause harm. Level 2: Read Committed The most common default. A transaction only sees data committed before each statement begins. Each statement gets a fresh view of committed data, but that view can change between statements in the same transaction. Use case: General-purpose OLTP workloads where individual statements need current data but cross-statement consistency isn't critical.

read-committed-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
-- Read Committed behavior example
-- Transaction A's view can change between statements
 
-- Transaction A                          -- Transaction B
BEGIN;                                    
SET TRANSACTION ISOLATION LEVEL 
READ COMMITTED;
 
SELECT balance FROM accounts              
WHERE account_id = 1;
-- Returns: 1000
                                          BEGIN;
                                          UPDATE accounts 
                                          SET balance = 1500
                                          WHERE account_id = 1;
                                          COMMIT;
                                          
SELECT balance FROM accounts
WHERE account_id = 1;
-- Returns: 1500 (different!)
-- This is allowed at Read Committed
 
COMMIT;

Level 3: Repeatable Read A transaction sees a consistent snapshot of committed data as of the transaction's start time (in snapshot-based implementations) or holds locks on all read data (in lock-based implementations). The same query returns the same results throughout the transaction. Use case: Reporting that requires consistent totals across multiple queries; multi-statement operations where intermediate decisions depend on earlier reads. Level 4: Serializable The gold standard. Transactions behave as if executed serially. All anomalies are prevented. In practice, databases achieve this through strict two-phase locking, serializable snapshot isolation, or optimistic concurrency control with validation. Use case: Any operation where correctness is paramount: financial transactions, inventory management, anything involving invariants across multiple rows or tables.

Choosing an Isolation Level: Practical Guidelines

Selecting the appropriate isolation level requires understanding your application's correctness requirements and performance constraints. Here's a decision framework:

Decision Framework for Isolation Levels

•Start with Serializable — Begin with the strongest guarantee and only weaken if you have a specific, measured performance need. Premature optimization with weaker isolation causes bugs that are nightmares to diagnose.
•Identify Invariants — What constraints must your transactions maintain? If invariants span multiple rows or require multiple read-modify-write cycles, you likely need Repeatable Read or Serializable.
•Measure Contention — Profile your workload. How often do transactions touch the same data? High contention means isolation level choice significantly impacts throughput. Low contention means stronger isolation has minimal cost.
•Understand Read Patterns — If a transaction only reads (no writes), many anomalies become irrelevant. Read-only transactions can often use Read Committed without issues.
•Test Under Load — Anomalies often appear only under concurrent load. Single-threaded testing won't reveal isolation bugs. Use tools like jepsen or custom concurrent test harnesses.

When Read Committed Is Sufficient

•Single-statement transactions
•Read-then-write uses SELECT FOR UPDATE
•No cross-statement consistency needed
•Application handles retry on conflicts
•Display-only queries (stale OK)

When Serializable Is Necessary

•Financial transactions
•Multi-row invariant enforcement
•Aggregate calculations over live data
•Complex workflows with multiple checks
•High-stakes or regulated domains

SELECT FOR UPDATE: Your Best Friend

Many applications can achieve correctness at Read Committed by using SELECT ... FOR UPDATE. This locks the selected rows, preventing other transactions from modifying them until you commit. It's a surgical way to get strong isolation where needed without the overhead of Serializable everywhere.

select-for-update.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- Using SELECT FOR UPDATE to prevent lost updates at Read Committed
 
-- Without FOR UPDATE: vulnerable to lost update
BEGIN;
SELECT balance FROM accounts WHERE account_id = 1;  -- Gets 1000
-- Another transaction could modify this row right now!
UPDATE accounts SET balance = balance - 300 WHERE account_id = 1;
COMMIT;
 
-- With FOR UPDATE: protected from concurrent modification
BEGIN;
SELECT balance FROM accounts WHERE account_id = 1 FOR UPDATE;
-- Row is now locked; other transactions must wait
-- Safe to read-modify-write
UPDATE accounts SET balance = balance - 300 WHERE account_id = 1;
COMMIT;
-- Lock released; other transactions can proceed

How Databases Implement Isolation

Databases use various mechanisms to enforce isolation. Understanding these mechanisms helps explain performance characteristics and failure modes.

Major Isolation Implementation Strategies

•Two-Phase Locking (2PL) — Transactions acquire locks before accessing data. Locks are held until commit (strict 2PL). Prevents conflicts through mutual exclusion. Used traditionally by Oracle, SQL Server, MySQL (InnoDB).
•Multi-Version Concurrency Control (MVCC) — The database maintains multiple versions of each row. Readers see a consistent snapshot without blocking writers. Writers create new versions. Used by PostgreSQL, MySQL (InnoDB for reads), Oracle (for reads).
•Optimistic Concurrency Control (OCC) — Transactions proceed without locks, recording what they read. At commit time, the database validates that no conflicts occurred. If conflicts are detected, the transaction aborts and must retry.
•Serializable Snapshot Isolation (SSI) — An advanced form of MVCC that tracks dependencies between concurrent transactions and aborts any that would create cycles (indicating non-serializable behavior). Used by PostgreSQL for Serializable level.

Isolation Mechanisms Comparison
Mechanism	Readers Block Writers?	Writers Block Readers?	Abort Rate	Best For
Two-Phase Locking	Yes (for writes)	Yes	Low (waits instead)	Write-heavy, low contention
MVCC (Read Committed)	No	No	N/A	Read-heavy, tolerates non-repeatable reads
MVCC (Snapshot)	No	No	Medium (on conflict)	Mixed workloads, consistent reads
SSI (Serializable)	No	No	Higher (on write conflicts)	Correctness-critical, moderate contention

MVCC in Detail: In MVCC systems like PostgreSQL: Each row has hidden columns: xmin (transaction that created this version), xmax (transaction that deleted/replaced this version) When a row is updated, the old version is marked with xmax = current transaction, and a new version is created A transaction can only see versions where: xmin was committed before this transaction started (or is this transaction) xmax is null or was not committed before this transaction started This allows readers and writers to proceed simultaneously without blocking

MVCC and Bloat

MVCC creates multiple versions of rows. Old versions that no transaction needs anymore must be cleaned up (vacuumed in PostgreSQL, purged in MySQL). If cleanup can't keep up with update rate, table bloat occurs. This is a trade-off for the concurrency benefits MVCC provides.

Handling Serialization Failures

When using Serializable isolation (or Repeatable Read in some databases), the database may detect that concurrent transactions cannot be serialized without anomalies. Rather than allowing the anomaly, it aborts one of the transactions with a serialization failure . This is not an error—it's the isolation mechanism working correctly. The application must be prepared to retry the transaction.

retry-serialization-failure.py
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
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
import psycopg2
from psycopg2 import errors
import time
 
MAX_RETRIES = 3
RETRY_DELAY_MS = 100
 
def execute_with_retry(operation_func):
    """
    Execute a database operation with automatic retry on serialization failure.
    Essential for Serializable isolation level.
    """
    retries = 0
    
    while retries < MAX_RETRIES:
        conn = get_db_connection()
        try:
            conn.set_isolation_level(
                psycopg2.extensions.ISOLATION_LEVEL_SERIALIZABLE
            )
            
            result = operation_func(conn)
            conn.commit()
            return result
            
        except errors.SerializationFailure as e:
            # Transaction was aborted due to concurrent conflict
            # This is expected behavior at Serializable level
            conn.rollback()
            retries += 1
            
            if retries < MAX_RETRIES:
                # Exponential backoff with jitter
                delay = RETRY_DELAY_MS * (2 ** retries) + random.uniform(0, 50)
                logging.info(f"Serialization failure, retry {retries}/{MAX_RETRIES} "
                           f"after {delay}ms")
                time.sleep(delay / 1000)
            else:
                logging.error("Max retries exceeded for serializable transaction")
                raise
                
        except Exception as e:
            conn.rollback()
            raise
            
        finally:
            conn.close()
    
    raise Exception("Transaction failed after max retries")
 
 
# Example usage
def transfer_funds(conn, from_account, to_account, amount):
    cursor = conn.cursor()
    
    # At Serializable, we don't need FOR UPDATE—the database
    # ensures serializable behavior automatically
    cursor.execute(
        "SELECT balance FROM accounts WHERE account_id = %s",
        (from_account,)
    )
    from_balance = cursor.fetchone()[0]
    
    if from_balance < amount:
        raise InsufficientFundsError()
    
    cursor.execute(
        "UPDATE accounts SET balance = balance - %s WHERE account_id = %s",
        (amount, from_account)
    )
    cursor.execute(
        "UPDATE accounts SET balance = balance + %s WHERE account_id = %s",
        (amount, to_account)
    )
    
    return True
 
# Run with automatic retry
execute_with_retry(
    lambda conn: transfer_funds(conn, 'savings', 'checking', 500)
)

Serialization Failures Are a Feature

Don't fear serialization failures—embrace them. They mean the database is correctly protecting you from anomalies. Design your application to expect them (idempotent operations, retry logic). A few retries are far cheaper than debugging data corruption from concurrency bugs.

Best Practices for Handling Serialization Failures

•Use exponential backoff — Don't retry immediately; give other transactions time to complete. Add jitter to prevent synchronized retries.
•Keep transactions short — Longer transactions have more time to conflict. Minimize the window between first read and commit.
•Make operations idempotent — If a transaction is retried, applying it twice should produce the same result as applying it once. Use unique request IDs to detect duplicates.
•Log serialization failures — They're expected but monitoring their rate helps identify contention hotspots.
•Consider transaction ordering — If transactions consistently access the same rows, accessing them in the same order reduces conflict likelihood.

Isolation Across Database Systems

Different database systems implement isolation levels with varying behavior. Understanding these differences is essential for portable applications.

Isolation Level Behavior Across Database Systems
Database	Default Level	Serializable Implementation	Notable Behavior
PostgreSQL	Read Committed	SSI (MVCC-based, may abort)	Repeatable Read prevents phantoms; Serializable is true serializable
MySQL (InnoDB)	Repeatable Read	Gap locking + traditional locking	Repeatable Read uses consistent snapshot; may have some phantom prevention
Oracle	Read Committed	Requires manual locking (SELECT FOR UPDATE)	No true Serializable; 'Serializable' is actually snapshot isolation
SQL Server	Read Committed	Lock-based or snapshot (configurable)	Has READ_COMMITTED_SNAPSHOT option for MVCC-like behavior
SQLite	Serializable	Database-level locking	Simple model; all transactions are effectively serializable

Oracle's 'Serializable' Isn't

Oracle's Serializable isolation level is actually Snapshot Isolation, which allows write skew anomalies. If you need true serializability in Oracle, you must use explicit locking (SELECT FOR UPDATE) on all related rows. This is a common source of bugs when porting applications.

set-isolation-level.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
-- PostgreSQL: Set isolation level for a transaction
BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- All operations in this transaction use Serializable isolation
COMMIT;
 
-- Or set per-session default
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL SERIALIZABLE;
 
-- MySQL: Set isolation level
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- Or set globally/session
SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
 
-- SQL Server: Set isolation level
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- Or use the SNAPSHOT option (requires database-level config)
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
 
-- Oracle: "Serializable" (actually snapshot)
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Isolation: Concurrent Transactions in Harmony

When Two Transactions Meet

Defining Isolation: The Illusion of Serialization

Concurrency Anomalies: What Can Go Wrong

Isolation Levels: Trading Correctness for Performance

Choosing an Isolation Level: Practical Guidelines

How Databases Implement Isolation

Handling Serialization Failures

Best Practices for Handling Serialization Failures

Isolation Across Database Systems

There’s more to understand.

Isolation: Concurrent Transactions in Harmony

When Two Transactions Meet

Defining Isolation: The Illusion of Serialization

Concurrency Anomalies: What Can Go Wrong

Isolation Levels: Trading Correctness for Performance

Choosing an Isolation Level: Practical Guidelines

How Databases Implement Isolation

Handling Serialization Failures

Best Practices for Handling Serialization Failures

Isolation Across Database Systems

There’s more to understand.