Using Serializable for All Transactions (10x Slower)

-- Wrong: Use Serializable for all transactions

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

BEGIN;

SELECT * FROM orders WHERE customer_id = 100;

UPDATE orders SET status = 'shipped' WHERE order_id = 1;

COMMIT;

-- Serializable acquires range locks, blocks concurrent transactions

-- 10x slower than Read Committed

-- Correct: Use Read Committed for most transactions

SET TRANSACTION ISOLATION LEVEL READ COMMITTED; -- Default

BEGIN;

SELECT * FROM orders WHERE customer_id = 100;

UPDATE orders SET status = 'shipped' WHERE order_id = 1;

COMMIT;

-- Read Committed uses short locks, high throughput

-- Use Serializable only for critical operations

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

BEGIN;

-- Calculate account balance from transaction history

SELECT SUM(amount) FROM transactions WHERE account_id = 'A';

-- Must prevent phantom reads (new transactions during calculation)

COMMIT;

Serializable is strongest isolation but slowest performance. Use only for critical operations requiring full isolation (< 1% of transactions). Most applications use Read Committed (99% of transactions) - good balance between consistency and performance. Example: Stripe uses Read Committed for payment transactions (30K+ TPS), Serializable for financial calculations (< 1% of transactions). Trade-off: Serializable prevents all anomalies but reduces throughput by 10x. Choose isolation level based on consistency requirements vs performance needs.

Use Read Committed for most transactions (99%). Use Serializable only for critical operations requiring full isolation (< 1%). Monitor throughput and deadlock frequency.

High

Serializable is 10x slower than Read Committed. Acquires range locks, blocks concurrent transactions. High deadlock risk. Poor scalability (throughput drops from 1000 TPS to 100 TPS).

graph TB

subgraph "Serializable: Slow"

S1["SET ISOLATION LEVEL SERIALIZABLE"]

S2["BEGIN"]

S3["SELECT: Range lock acquired"]

S4["UPDATE: Range lock held"]

S5["Concurrent transactions blocked<br/>Wait for range lock"]

S6["COMMIT: 10x slower<br/>100 TPS"]

end

subgraph "Read Committed: Fast"

R1["SET ISOLATION LEVEL READ COMMITTED"]

R2["BEGIN"]

R3["SELECT: Short lock"]