Intense-Visions Isolation Level Selection
Selecting the right isolation level requires matching the workload's correctness requirements against the performance cost and retry complexity of stricter levels.
When to Use
- Starting a new feature with transaction requirements
- Optimizing transaction throughput by downgrading unnecessarily strict isolation
- Debugging serialization failures that seem unnecessary
- Deciding between isolation level upgrades and application-level locking
- Reviewing transaction design during code review
Instructions
Key Concepts
Decision Framework
1. Read Committed (default -- use for most workloads)
CRUD operations, form submissions, content management. Low overhead, no retry logic needed. Works for 90% of web application transactions. Only unsafe when business logic depends on re-reading the same rows within a transaction.
2. Repeatable Read
Reporting queries that must see a consistent snapshot, balance calculations, read-heavy analytics within a transaction. Adds the cost of potential serialization failures on write conflicts. Requires retry logic.
3. Serializable
Financial transfers, inventory management with complex constraints, any workflow where write skew would violate business invariants. Requires retry logic for every transaction. Expect ~5-15% throughput reduction.
4. Alternative: Explicit Locking
SELECT FOR UPDATE within Read Committed can prevent specific races without the full overhead of Serializable. See db-pessimistic-locking. Preferred when only a few rows need protection.
Decision Table
| Workload Type | Recommended Level | Retry Required | Notes |
|---|---|---|---|
| Simple CRUD | Read Committed | No | Default, sufficient for most |
| Form submit / save | Read Committed | No | Single write, no re-reads |
| Report / analytics | Repeatable Read | Yes | Consistent snapshot needed |
| Balance calculation | Repeatable Read | Yes | Must see consistent totals |
| Financial transfer | Serializable | Yes | Write skew would lose money |
| Inventory decrement | RC + FOR UPDATE | No | Targeted lock, less overhead |
| Queue processing | RC + SKIP LOCKED | No | See db-pessimistic-locking |
Worked Example
E-commerce checkout -- comparing two approaches for preventing overselling:
Approach A: Serializable Isolation
BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT stock FROM products WHERE id = 42; -- returns 3
-- Application logic: verify stock >= requested quantity
UPDATE products SET stock = stock - 1 WHERE id = 42;
INSERT INTO orders (product_id, quantity) VALUES (42, 1);
COMMIT;
-- May raise: ERROR: could not serialize access
-- Application must retry entire transaction
Approach B: Read Committed with SELECT FOR UPDATE
BEGIN; -- Read Committed (default)
SELECT stock FROM products WHERE id = 42 FOR UPDATE; -- returns 3, row locked
-- Other transactions block here until we commit
UPDATE products SET stock = stock - 1 WHERE id = 42;
INSERT INTO orders (product_id, quantity) VALUES (42, 1);
COMMIT;
-- No serialization error possible -- lock prevents conflict
Trade-off analysis: Approach B is preferred for this workload. The lock targets only the inventory row, other products are unaffected. Approach A applies serializable checking to the entire transaction including the orders insert, which is unnecessary overhead. Approach B also eliminates the need for retry logic.
Anti-Patterns
Setting Serializable as the database default. All transactions pay the serialization cost, even simple reads. Set isolation per-transaction where needed instead.
Choosing isolation level per-table instead of per-transaction. Isolation applies to the entire transaction, not individual tables or queries. There is no way to use Serializable for one table and Read Committed for another within the same transaction.
Upgrading isolation to "fix bugs" without understanding the root cause. The bug may be in application logic, not a concurrency anomaly. Diagnose first using
db-read-phenomenabefore changing isolation levels.No retry loop for Repeatable Read or Serializable. These levels raise serialization errors by design. The application must catch
SQLSTATE 40001and retry the entire transaction with exponential backoff.
PostgreSQL Specifics
- Set session default:
SET default_transaction_isolation = 'read committed'; - Per-transaction override:
BEGIN ISOLATION LEVEL SERIALIZABLE; - Prevent snapshot-holding:
idle_in_transaction_session_timeout = '30s'kills idle transactions - Monitor serialization failures:
pg_stat_database.conflictsand application-level error counters - Read-only Serializable with
DEFERRABLE: zero-abort reporting queries
Details
Advanced Topics
Retry loop pattern:
-- Pseudocode for serializable retry
max_retries = 3
for attempt in range(max_retries):
try:
BEGIN ISOLATION LEVEL SERIALIZABLE;
-- ... transaction logic ...
COMMIT;
break -- success
except SerializationFailure:
-- SQLSTATE 40001
ROLLBACK;
sleep(random_backoff(attempt)) -- jitter: 10ms * 2^attempt
else:
raise TooManyRetries()
Read-only Serializable with DEFERRABLE: For reporting queries that need a consistent snapshot without any risk of abort, use BEGIN ISOLATION LEVEL SERIALIZABLE READ ONLY DEFERRABLE;. PostgreSQL may delay the start slightly to find a safe snapshot, but the transaction will never abort.
Connection pool implications: Transaction-level isolation means the pool must not reuse connections mid-transaction. Configure pool to use transaction-level pooling (e.g., PgBouncer pool_mode = transaction), and ensure isolation level is set within the transaction, not at the session level.
Engine Differences
MySQL defaults to Repeatable Read, so applications migrating from MySQL to PostgreSQL may experience different behavior at the default isolation level. MySQL's gap locking provides some phantom protection that PostgreSQL handles differently via MVCC.
MySQL Serializable uses shared locks on all SELECT statements (pessimistic, blocking). PostgreSQL SSI is optimistic (abort-on-conflict, no blocking). This means MySQL Serializable has lower throughput but fewer retries, while PostgreSQL Serializable has higher throughput but requires retry logic.
Real-World Case Studies
A SaaS platform ran all transactions at Serializable, experiencing a 15% serialization failure rate during peak hours. Profiling showed 80% of transactions were simple CRUD (user profile updates, content edits) that only needed Read Committed. Selectively downgrading CRUD transactions to Read Committed while keeping Serializable for billing and subscription workflows reduced the failure rate from 15% to 0.3% and improved p99 latency by 40%.
Source
- PostgreSQL Transaction Isolation
- Kleppmann, M. "Designing Data-Intensive Applications" (2017), Chapter 7
Process
- Identify the correctness requirements: which read phenomena would violate business invariants?
- Select the minimum isolation level from the decision table, or use explicit locking for targeted protection.
- Implement retry logic for any transaction using Repeatable Read or Serializable, and monitor serialization failure rates.
Harness Integration
- Type: knowledge -- this skill is a reference document, not a procedural workflow.
- No tools or state -- consumed as context by other skills and agents.
- related_skills: db-isolation-levels, db-read-phenomena, db-acid-properties, db-optimistic-locking, db-pessimistic-locking, prisma-transactions, drizzle-transactions
Success Criteria
- Isolation level chosen matches actual workload requirements (not over- or under-specified)
- Retry logic present for every Repeatable Read and Serializable transaction
- Serialization failure rates monitored and below acceptable thresholds