Isolation Levels

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

-- Setting Read Committed isolation level in various databases
 
-- SQL Server (explicitly, though it's the default)
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRANSACTION;
    -- All reads see only committed data
    SELECT * FROM Accounts WHERE account_id = 'ACC001';
    -- If another transaction is updating this row but hasn't committed,
    -- this SELECT will WAIT (in locking mode) or see prior version (in RCSI)
COMMIT;
 
-- PostgreSQL (default behavior)
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- or simply BEGIN; (Read Committed is PostgreSQL's default)
BEGIN;
    SELECT * FROM accounts WHERE account_id = 'ACC001';
    -- PostgreSQL MVCC: sees last committed version, never waits
COMMIT;
 
-- Oracle (default behavior)
-- No explicit statement needed; Oracle defaults to Read Committed
-- Every query sees a snapshot of committed data as of query start
 
-- MySQL/InnoDB
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- or per-transaction:
START TRANSACTION;
    -- InnoDB uses MVCC, similar to PostgreSQL
COMMIT;

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

-- Locking behavior under Read Committed (SQL Server without RCSI)
 
-- Session 1: Writer
BEGIN TRANSACTION;
    -- Acquires X lock on row
    UPDATE Accounts SET balance = 500 WHERE account_id = 'ACC001';
    -- X lock held until commit/rollback
    
    -- Simulate long-running operation
    WAITFOR DELAY '00:00:30';
COMMIT;
-- X lock released on commit
 
-- Session 2: Reader (runs while Session 1 is active)
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRANSACTION;
    SELECT balance FROM Accounts WHERE account_id = 'ACC001';
    -- ⏳ BLOCKED! Waits for Session 1 to commit or rollback
    -- This query hangs until Session 1's transaction completes
    -- Then returns the COMMITTED value (either new or original)
COMMIT;
 
-- Key difference from Read Uncommitted:
-- Read Uncommitted: Returns 500 immediately (dirty read)
-- Read Committed (locking): WAITS until commit, then returns final value

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

-- MVCC behavior under Read Committed (PostgreSQL example)
 
-- Session 1: Writer
BEGIN;
    -- Creates new version, old version still visible to readers
    UPDATE accounts SET balance = 500 WHERE account_id = 'ACC001';
    -- No blocking occurs - old version remains visible
    
    -- Long processing...
    SELECT pg_sleep(30);
    
COMMIT;
-- New version now visible to new reads
 
-- Session 2: Reader (runs while Session 1 is active)
BEGIN;
    -- Immediately returns! No waiting!
    SELECT balance FROM accounts WHERE account_id = 'ACC001';
    -- Returns: 1000 (the committed version from before Session 1's update)
    
    -- After Session 1 commits, same query returns different result:
    SELECT balance FROM accounts WHERE account_id = 'ACC001';
    -- Returns: 500 (now this version is committed)
    -- This is a non-repeatable read, which is allowed at Read Committed
COMMIT;
 
-- PostgreSQL version visibility uses:
-- xmin: Transaction ID that created the row version
-- xmax: Transaction ID that deleted/updated (created next version)
-- Visibility rules check if xmin's transaction committed before our query started

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

-- Enabling Read Committed Snapshot Isolation in SQL Server
 
-- Step 1: Check current setting
SELECT name, is_read_committed_snapshot_on
FROM sys.databases
WHERE name = 'YourDatabase';
 
-- Step 2: Enable RCSI (requires exclusive database access or single_user mode)
-- In production, typically done during maintenance window
ALTER DATABASE YourDatabase SET READ_COMMITTED_SNAPSHOT ON;
 
-- Alternative: Enable with controlled connection termination
ALTER DATABASE YourDatabase SET READ_COMMITTED_SNAPSHOT ON 
WITH ROLLBACK AFTER 60 SECONDS;
 
-- Step 3: Verify the change
SELECT is_read_committed_snapshot_on 
FROM sys.databases 
WHERE name = 'YourDatabase';
-- Should return 1
 
-- Now all Read Committed transactions automatically use row versioning
-- No application code changes required!
 
-- To disable (if needed):
ALTER DATABASE YourDatabase SET READ_COMMITTED_SNAPSHOT OFF;
 
-- Monitor tempdb usage for row versions
SELECT 
    SUM(version_store_reserved_page_count) * 8.0 / 1024 AS VersionStoreSizeMB
FROM sys.dm_db_file_space_usage;

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

-- Non-Repeatable Read demonstration at Read Committed
 
-- Transaction A: Calculating order total with discount
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRANSACTION;
    
    -- First read: get base price
    DECLARE @BasePrice DECIMAL(10,2);
    SELECT @BasePrice = unit_price 
    FROM Products 
    WHERE product_id = 'P100';
    -- @BasePrice = $100.00
    
    -- Some processing time...
    WAITFOR DELAY '00:00:05';
    
    -- Second read: verify price for discount calculation
    DECLARE @VerifyPrice DECIMAL(10,2);
    SELECT @VerifyPrice = unit_price 
    FROM Products 
    WHERE product_id = 'P100';
    -- @VerifyPrice = $120.00  (different! Another txn committed)
    
    -- Inconsistency: used $100 for initial calc, $120 for verification
    -- If we compare: @BasePrice <> @VerifyPrice (inconsistent within transaction)
    
    -- This could cause incorrect discount applications
    IF @BasePrice >= 100 AND @VerifyPrice < 100
        -- Logic error: conditions based on different snapshots
        PRINT 'Discount eligible based on old price, not new?';
        
COMMIT;
 
-- Concurrent Transaction B (runs during A's processing delay)
BEGIN TRANSACTION;
    UPDATE Products SET unit_price = 120.00 WHERE product_id = 'P100';
COMMIT;

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

-- Phantom Read demonstration at Read Committed
 
-- Transaction A: Calculating department salary totals
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRANSACTION;
    
    -- First query: sum salaries for Engineering
    SELECT SUM(salary) AS total_salary, COUNT(*) AS employee_count
    FROM Employees
    WHERE department = 'Engineering';
    -- Returns: total_salary = $500,000, employee_count = 5
    
    -- Business logic: budget planning based on current headcount
    WAITFOR DELAY '00:00:05';
    
    -- Second query: same query, same transaction
    SELECT SUM(salary) AS total_salary, COUNT(*) AS employee_count
    FROM Employees
    WHERE department = 'Engineering';
    -- Returns: total_salary = $650,000, employee_count = 7
    -- Two new employees appeared (phantoms)!
    
    -- The transaction's calculations are now inconsistent
    -- Initial budget was based on 5 employees, but now there are 7
    
COMMIT;
 
-- Concurrent Transaction B (runs during A's delay)
BEGIN TRANSACTION;
    INSERT INTO Employees (employee_id, name, department, salary)
    VALUES 
        ('E106', 'Alice', 'Engineering', 75000),
        ('E107', 'Bob', 'Engineering', 75000);
COMMIT;

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

-- Pattern 1: Simple CRUD - Read Committed is perfect
-- Each operation is independent, no multi-step consistency needed
BEGIN TRANSACTION;
    UPDATE Users SET last_login = GETDATE() WHERE user_id = @UserId;
COMMIT;
 
-- Pattern 2: Displaying current product information
-- User sees latest committed price, which is exactly what we want
BEGIN TRANSACTION;
    SELECT product_name, price, stock_quantity 
    FROM Products 
    WHERE product_id = @ProductId;
COMMIT;
 
-- Pattern 3: Job queue processing with optimistic claim
-- Worker claims a job atomically; Read Committed handles this well
BEGIN TRANSACTION;
    -- Claim next available job
    UPDATE TOP(1) JobQueue
    SET status = 'PROCESSING', 
        worker_id = @WorkerId,
        claimed_at = GETDATE()
    OUTPUT INSERTED.*
    WHERE status = 'PENDING'
    ORDER BY created_at;
COMMIT;
 
-- Pattern 4: Inserting independent records
-- No relationship to other data that might change
BEGIN TRANSACTION;
    INSERT INTO AuditLog (event_type, user_id, details, timestamp)
    VALUES (@EventType, @UserId, @Details, GETDATE());
COMMIT;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

-- PostgreSQL Specifics
-- Uses xmin/xmax version visibility
-- Old versions cleaned up by VACUUM process
-- Very efficient for read-heavy workloads
SHOW default_transaction_isolation;  -- Shows 'read committed'
 
-- Oracle Specifics 
-- Uses System Change Numbers (SCN) for versioning
-- Old versions stored in UNDO tablespace
-- ORA-01555 "snapshot too old" if undo is exhausted
SELECT * FROM v$transaction;  -- View active transactions
 
-- SQL Server Specifics
-- Traditional mode uses shared locks for reads
-- RCSI mode uses tempdb for row versions
-- RCSI can be enabled transparently for existing apps
SELECT * FROM sys.dm_tran_version_store_space_usage;  -- Version store usage
 
-- MySQL/InnoDB Specifics
-- Default is REPEATABLE READ (unusual!)
-- Must explicitly set READ COMMITTED if desired
-- Uses redo log and undo log for versioning
SHOW VARIABLES LIKE 'transaction_isolation';
SET GLOBAL transaction_isolation = 'READ-COMMITTED';