Level Selection

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

-- Example: Applying the decision framework
 
-- Scenario 1: Dashboard showing approximate counts
-- Q1: Modifies data? NO
-- Q2: Accuracy required? APPROXIMATE OK
-- Decision: Read Uncommitted
SELECT COUNT(*) FROM orders WITH (NOLOCK) WHERE status = 'pending';
 
-- Scenario 2: User profile update
-- Q1: Modifies data? YES (UPDATE)
-- Q2: Decision based on read? NO (just SET these values)
-- Decision: Read Committed (default)
UPDATE users SET email = @NewEmail WHERE user_id = @UserId;
 
-- Scenario 3: Generate month-end financial report
-- Q1: Modifies data? NO
-- Q2: Accuracy required? EXACT
-- Q3: Multiple reads, need consistency? YES
-- Decision: Repeatable Read
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
    SELECT SUM(amount) FROM transactions WHERE month = 'December';
    SELECT AVG(amount) FROM transactions WHERE month = 'December';
    -- Both queries see exact same data
COMMIT;
 
-- Scenario 4: Balance transfer between accounts
-- Q1: Modifies data? YES
-- Q2: Decision based on read? YES (check balance first)
-- Q3: Multi-row invariant? YES (total balance must stay constant)
-- Decision: Serializable
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;
    SELECT balance FROM accounts WHERE id IN (@src, @dst);
    -- Decision: proceed only if source has sufficient funds
    UPDATE accounts SET balance = balance - @amt WHERE id = @src;
    UPDATE accounts SET balance = balance + @amt WHERE id = @dst;
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
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

-- Pattern: Queue Processing (claim work items)
-- Read Committed + Explicit Lock
BEGIN TRANSACTION;
    -- Claim next unclaimed item with row lock
    UPDATE TOP(1) work_queue
    SET status = 'processing', 
        worker_id = @WorkerId,
        claimed_at = GETDATE()
    OUTPUT INSERTED.*
    WHERE status = 'pending'
    ORDER BY created_at;
COMMIT;
-- The UPDATE is atomic; no explicit SELECT needed
 
-- Pattern: Resource Allocation (meeting room booking)
-- Option 1: Serializable
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;
    -- Check availability (predicate-locked range)
    IF NOT EXISTS (
        SELECT 1 FROM bookings 
        WHERE room_id = @room 
        AND start_time < @end AND end_time > @start
    )
    BEGIN
        INSERT INTO bookings (room_id, start_time, end_time, user_id)
        VALUES (@room, @start, @end, @user);
    END
COMMIT;
 
-- Option 2: Read Committed + Explicit Lock (often better)
BEGIN TRANSACTION;
    -- Lock the room record
    SELECT * FROM rooms WHERE room_id = @room FOR UPDATE;
    
    -- Now safe to check and insert
    IF NOT EXISTS (
        SELECT 1 FROM bookings 
        WHERE room_id = @room 
        AND start_time < @end AND end_time > @start
    )
    BEGIN
        INSERT INTO bookings (room_id, start_time, end_time, user_id)
        VALUES (@room, @start, @end, @user);
    END
COMMIT;
 
-- Pattern: Batch Insert with Parent Validation
-- Read Committed + Explicit Lock on parent
BEGIN TRANSACTION;
    -- Lock the order header (prevents concurrent modifications)
    SELECT * FROM orders WHERE order_id = @OrderId FOR UPDATE;
    
    -- Insert line items (parent is locked, so this is safe)
    INSERT INTO order_items (order_id, product_id, quantity, price)
    SELECT @OrderId, product_id, quantity, price
    FROM @ItemsTable;
    
    -- Update order total
    UPDATE orders 
    SET total = (SELECT SUM(quantity * price) FROM order_items WHERE order_id = @OrderId)
    WHERE order_id = @OrderId;
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
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
82
83
84
85
86
87
88
89

# Python application with mixed isolation levels
 
from enum import Enum
from contextlib import contextmanager
from typing import Optional
import psycopg2
 
class IsolationLevel(Enum):
    READ_UNCOMMITTED = "READ UNCOMMITTED"
    READ_COMMITTED = "READ COMMITTED"
    REPEATABLE_READ = "REPEATABLE READ"
    SERIALIZABLE = "SERIALIZABLE"
 
class TransactionManager:
    """Manages transactions with configurable isolation levels"""
    
    def __init__(self, connection_pool):
        self.pool = connection_pool
    
    @contextmanager
    def transaction(self, isolation: IsolationLevel = IsolationLevel.READ_COMMITTED):
        """Context manager for transactions with specified isolation"""
        conn = self.pool.getconn()
        try:
            # Set isolation level before starting transaction
            conn.set_isolation_level(
                psycopg2.extensions.ISOLATION_LEVEL_SERIALIZABLE 
                if isolation == IsolationLevel.SERIALIZABLE
                else psycopg2.extensions.ISOLATION_LEVEL_READ_COMMITTED
                # ... handle other levels
            )
            yield conn
            conn.commit()
        except Exception as e:
            conn.rollback()
            raise
        finally:
            self.pool.putconn(conn)
 
# Usage in application services
class OrderService:
    def __init__(self, tx_manager: TransactionManager):
        self.tx = tx_manager
    
    def get_order_count_approximate(self) -> int:
        """Dashboard metric - speed over accuracy"""
        with self.tx.transaction(IsolationLevel.READ_UNCOMMITTED) as conn:
            with conn.cursor() as cur:
                cur.execute("SELECT COUNT(*) FROM orders")
                return cur.fetchone()[0]
    
    def get_order_details(self, order_id: str) -> dict:
        """Standard order lookup - default isolation"""
        with self.tx.transaction(IsolationLevel.READ_COMMITTED) as conn:
            with conn.cursor() as cur:
                cur.execute("SELECT * FROM orders WHERE id = %s", (order_id,))
                return dict(cur.fetchone())
    
    def generate_monthly_report(self, month: str) -> dict:
        """Financial report - must be internally consistent"""
        with self.tx.transaction(IsolationLevel.REPEATABLE_READ) as conn:
            with conn.cursor() as cur:
                cur.execute("SELECT SUM(total) FROM orders WHERE month = %s", (month,))
                total = cur.fetchone()[0]
                cur.execute("SELECT COUNT(*) FROM orders WHERE month = %s", (month,))
                count = cur.fetchone()[0]
                # total and count are from same snapshot
                return {"total": total, "count": count, "average": total/count}
    
    def transfer_credits(self, from_user: str, to_user: str, amount: float):
        """Credit transfer - must maintain invariants"""
        with self.tx.transaction(IsolationLevel.SERIALIZABLE) as conn:
            with conn.cursor() as cur:
                cur.execute(
                    "SELECT balance FROM user_credits WHERE user_id = %s",
                    (from_user,)
                )
                balance = cur.fetchone()[0]
                if balance < amount:
                    raise ValueError("Insufficient credits")
                
                cur.execute(
                    "UPDATE user_credits SET balance = balance - %s WHERE user_id = %s",
                    (amount, from_user)
                )
                cur.execute(
                    "UPDATE user_credits SET balance = balance + %s WHERE user_id = %s",
                    (amount, to_user)
                )

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

-- Platform-specific considerations for level selection
 
-- PostgreSQL: Consider using SERIALIZABLE more freely
-- SSI has low overhead for read-heavy workloads
-- But ALWAYS implement retry logic
BEGIN ISOLATION LEVEL SERIALIZABLE;
    -- Efficient in PostgreSQL due to SSI
COMMIT;
 
-- MySQL: Remember RR is the default
-- If porting from PostgreSQL/Oracle, test for RR dependency
-- To match other databases' defaults:
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
 
-- SQL Server: Consider enabling RCSI
-- This changes Read Committed from locking to MVCC
-- Often the single biggest performance improvement
ALTER DATABASE MyApp SET READ_COMMITTED_SNAPSHOT ON;
-- Now Read Committed transactions don't block on writers
 
-- Oracle: No Read Uncommitted
-- If you need approximate reads, can't use isolation for this
-- Consider: querying V$ views or using FLASHBACK QUERY
SELECT * FROM orders AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '5' MINUTE);
 
-- Cross-platform considerations:
-- 1. Don't rely on Read Uncommitted being available (Oracle, Postgres-as-RC)
-- 2. Don't assume Repeatable Read prevents phantoms (SQL Server)
-- 3. Test Serializable retry paths on all target platforms
-- 4. Document platform-specific behaviors in code comments

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

-- PostgreSQL: Monitor serialization failures
SELECT 
    datname,
    conflicts,
    confl_snapshot,
    confl_lock
FROM pg_stat_database_conflicts
WHERE datname = current_database();
 
-- PostgreSQL: Check for long-running transactions holding snapshots
SELECT 
    pid,
    now() - xact_start AS duration,
    state,
    query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
AND now() - xact_start > interval '5 minutes'
ORDER BY xact_start;
 
-- SQL Server: Lock waits by isolation level
SELECT 
    t.name AS isolation_level,
    COUNT(*) AS wait_count,
    AVG(wait_time_ms) AS avg_wait_ms
FROM sys.dm_exec_requests r
JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id
JOIN sys.dm_exec_sql_text(r.sql_handle) t ON 1=1
WHERE r.wait_type LIKE 'LCK%'
GROUP BY s.transaction_isolation_level;
 
-- SQL Server: Deadlock information
SELECT 
    event_data.query('./data/value')[0].value('.', 'nvarchar(max)') as deadlock_graph
FROM sys.fn_xe_file_target_read_file('system_health*.xel', NULL, NULL, NULL)
CROSS APPLY (SELECT CAST(event_data AS XML)) AS T(event_data)
WHERE event_data.query('./name')[0].value('.', 'nvarchar(max)') = 'xml_deadlock_report';
 
-- MySQL: InnoDB lock waits
SELECT * FROM performance_schema.events_waits_summary_global_by_event_name
WHERE event_name LIKE 'wait/synch/mutex/innodb%'
ORDER BY sum_timer_wait DESC LIMIT 10;
 
-- MySQL: Recent deadlocks
SHOW ENGINE INNODB STATUS; -- Look for LATEST DETECTED DEADLOCK section

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

-- Anti-pattern #5: Long transaction at Serializable
-- ❌ Bad: Holding Serializable transaction while calling external API
BEGIN ISOLATION LEVEL SERIALIZABLE;
    SELECT * FROM accounts WHERE user_id = @UserId;
    -- ... call external payment gateway (takes 2-5 seconds) ...
    UPDATE accounts SET balance = balance - @Amount WHERE user_id = @UserId;
COMMIT;
-- Problem: High serialization failure rate, version bloat
 
-- ✅ Better: Minimize transaction scope
BEGIN ISOLATION LEVEL SERIALIZABLE;
    -- Only the database operations, keep it short
    SELECT balance FROM accounts WHERE user_id = @UserId;
    IF balance >= @Amount
        UPDATE accounts SET balance = balance - @Amount WHERE user_id = @UserId;
COMMIT;
-- External API call happens OUTSIDE the transaction
 
-- Anti-pattern #7: No documentation
-- ❌ Bad: No explanation
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
    SELECT * FROM inventory WHERE product_id = @ProductId;
    -- ... (why Repeatable Read? unclear)
COMMIT;
 
-- ✅ Better: Document the reasoning
/**
 * This report generation uses Repeatable Read because:
 * - Multiple queries must see consistent inventory state
 * - We compare current stock to pending orders (multi-query)
 * - Non-repeatable reads would cause inconsistent report
 * 
 * Note: Does NOT need Serializable because:
 * - This is read-only, no write skew possible
 * - All queries are point-in-time, no cross-row invariants modified
 */
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
    SELECT * FROM inventory WHERE product_id = @ProductId;
    SELECT SUM(quantity) FROM pending_orders WHERE product_id = @ProductId;
COMMIT;