Transaction isolation levels

ries - 1); } throw error; } finally { client.release(); } }

### Step 3: Implement Deterministic Retry Logic
Serializable and Snapshot isolation levels return `40001` (PostgreSQL) or `ERROR 1213`/`1205` (MySQL) when write conflicts occur. Retry logic must be exponential, jittered, and bounded. Unbounded retries amplify lock contention. The wrapper above caps retries at 3, applies exponential backoff with jitter, and rolls back immediately on failure.

### Step 4: Instrument Lock Contention
Isolation configuration is useless without visibility. Query `pg_stat_activity`, `information_schema.innodb_trx`, or `sys.dm_tran_locks` depending on the engine. Track:
- `wait_event_type = 'Lock'`
- `lock_timeout` violations
- Serialization failure rate per minute
- Version chain length (MVCC)

### Step 5: Align Connection Pool Behavior
Connection pools reuse sessions. If isolation is set at session scope, subsequent transactions inherit unintended levels. Always set isolation inside the transaction block (as shown). Tag pool connections with `application_name` or `client_encoding` to trace isolation usage in monitoring dashboards.

### Architecture Decisions & Rationale
- **Per-transaction scoping over global/session:** Prevents pool bleeding, enables mixed isolation workloads on the same pool, and aligns with ACID boundaries.
- **Explicit `SET TRANSACTION` over ORM defaults:** ORMs frequently omit isolation configuration or apply engine defaults inconsistently. Explicit SQL guarantees deterministic behavior across PostgreSQL, MySQL, and SQL Server.
- **Retry on 40001/1213 over fallback to lower isolation:** Lowering isolation silently introduces anomalies. Retrying preserves consistency guarantees while allowing transient conflicts to resolve.
- **MVCC awareness:** In PostgreSQL and MySQL 8.0+, Read Committed and Repeatable Read use MVCC. Lock overhead comes from writers, not readers. This changes capacity planning: isolation impacts write throughput, not read scaling.

## Pitfall Guide

### 1. Relying on Database Defaults Without Validation
Engines ship with different defaults. PostgreSQL: Read Committed. MySQL/InnoDB: Repeatable Read. SQL Server: Read Committed. Assuming consistency without verifying defaults leads to silent anomalies in cross-database deployments or cloud migrations.

### 2. Connection Pool Isolation Bleeding
Setting isolation at the session level (`SET SESSION CHARACTERISTICS`) persists across transactions in pooled connections. Subsequent requests inherit unintended levels, causing unpredictable locking and serialization failures. Always configure inside `BEGIN`.

### 3. Treating Serializable as a Silver Bullet
Serializable prevents all anomalies but forces predicate locking or strict serialization queues. Under concurrent write patterns, it triggers cascading lock waits and `40001` failures. Throughput drops 40–70% while latency spikes. Use only when business logic requires strict serializability (e.g., double-entry accounting).

### 4. Ignoring MVCC Snapshot Semantics
MVCC systems avoid reader-writer blocking by maintaining version chains. Developers assume higher isolation means more locks, but in PostgreSQL/MySQL, Repeatable Read and Serializable often use snapshot visibility. The cost shifts to tuple visibility checks, vacuum pressure, and undo log retention. Tune `autovacuum` and `innodb_undo_log_truncate` accordingly.

### 5. Omitting Serialization Failure Handling
Applications that catch exceptions without distinguishing `40001` from constraint violations or network errors mask concurrency bugs. Serialization failures are expected under high contention. Failing to retry deterministically causes data loss or partial commits.

### 6. Assuming Isolation Eliminates Application-Level Race Conditions
Isolation controls database-level visibility. It does not prevent race conditions in application logic, cache invalidation, or external API calls. A transaction can be isolated while the surrounding code remains non-atomic. Use idempotency keys and outbox patterns for distributed consistency.

### 7. Neglecting Lock Timeouts
Default lock timeouts vary (PostgreSQL: infinite, MySQL: 50s, SQL Server: 0). Long-running transactions hold locks, blocking subsequent requests and triggering connection pool exhaustion. Always set `lock_timeout` (PostgreSQL) or `innodb_lock_wait_timeout` (MySQL) at the transaction level.

**Best Practices from Production:**
- Explicitly scope transactions to the minimum required operations.
- Use `SELECT ... FOR UPDATE SKIP LOCKED` for queue processing to avoid head-of-line blocking.
- Implement idempotency keys for retry-safe operations.
- Monitor serialization failure rate; if >0.5%, reconsider isolation or indexing.
- Tag connections with `application_name` for isolation tracing.
- Validate isolation behavior under load testing, not just unit tests.

## Production Bundle

### Action Checklist
- [ ] Audit current isolation configuration: Verify per-engine defaults and ORM behavior against business consistency requirements.
- [ ] Implement per-transaction isolation scoping: Replace session-level or global settings with explicit `SET TRANSACTION` inside transaction blocks.
- [ ] Add deterministic retry logic: Catch serialization failure codes (`40001`, `1213`, `1205`) and apply bounded exponential backoff with jitter.
- [ ] Configure lock timeouts: Set `lock_timeout` (PostgreSQL) or `innodb_lock_wait_timeout` (MySQL) to prevent connection pool starvation.
- [ ] Instrument concurrency metrics: Track serialization failure rate, lock wait events, and version chain length in observability dashboards.
- [ ] Validate under load: Run concurrency tests with realistic read/write ratios to verify isolation behavior before deployment.

### Decision Matrix

| Scenario | Recommended Approach | Why | Cost Impact |
|----------|---------------------|-----|-------------|
| Financial ledger writes | Serializable + explicit retry | Strict serializability required for double-entry integrity; retry handles transient conflicts | High CPU/IO for predicate locking; moderate infrastructure cost |
| Inventory reservation | Read Committed + `SELECT FOR UPDATE` | Prevents overselling while maintaining high write throughput; gap locks unnecessary | Low overhead; scales linearly with index quality |
| Analytics/rollup queries | Snapshot Isolation | Prevents write skew on aggregates without blocking writers; MVCC optimizes read performance | Moderate storage for version chains; low latency impact |
| User session/state updates | Repeatable Read | Ensures consistent reads within session scope; prevents non-repeatable reads during multi-step updates | Medium lock overhead; acceptable for low-concurrency state |
| Message queue processing | Read Committed + `SKIP LOCKED` | Avoids head-of-line blocking; multiple workers process distinct rows safely | Minimal overhead; maximizes consumer throughput |

### Configuration Template

```typescript
// db/transaction-manager.ts
import { Pool, PoolClient, QueryResult } from 'pg';

const pool = new Pool({
  host: process.env.DB_HOST,
  port: Number(process.env.DB_PORT || 5432),
  database: process.env.DB_NAME,
  user: process.env.DB_USER,
  password: process.env.DB_PASS,
  max: 25,
  idleTimeoutMillis: 20000,
  connectionTimeoutMillis: 3000,
  application_name: 'app-isolation-manager',
});

export type IsolationLevel = 'READ UNCOMMITTED' | 'READ COMMITTED' | 'REPEATABLE READ' | 'SERIALIZABLE';

interface TransactionConfig {
  isolation: IsolationLevel;
  lockTimeoutMs?: number;
  retries?: number;
  statementTimeoutMs?: number;
}

export async function executeTransaction<T>(
  config: TransactionConfig,
  operation: (client: PoolClient) => Promise<T>
): Promise<T> {
  const client = await pool.connect();
  const { isolation, lockTimeoutMs = 5000, retries = 3, statementTimeoutMs = 10000 } = config;

  try {
    await client.query('BEGIN');
    await client.query(`SET TRANSACTION ISOLATION LEVEL ${isolation}`);
    await client.query(`SET lock_timeout = '${lockTimeoutMs}ms'`);
    await client.query(`SET statement_timeout = '${statementTimeoutMs}ms'`);

    const result = await operation(client);
    await client.query('COMMIT');
    return result;
  } catch (error: any) {
    await client.query('ROLLBACK');

    if (error.code === '40001' && retries > 0) {
      const baseDelay = Math.pow(2, 3 - retries) * 100;
      const jitter = Math.random() * 80;
      await new Promise(resolve => setTimeout(resolve, baseDelay + jitter));
      return executeTransaction({ ...config, retries: retries - 1 }, operation);
    }
    throw error;
  } finally {
    client.release();
  }
}

// Usage example
export async function reserveInventory(itemId: string, quantity: number): Promise<boolean> {
  return executeTransaction(
    { isolation: 'READ COMMITTED', lockTimeoutMs: 3000 },
    async (client) => {
      const res = await client.query(
        `UPDATE inventory SET stock = stock - $1 WHERE item_id = $2 AND stock >= $1 RETURNING item_id`,
        [quantity, itemId]
      );
      return res.rowCount === 1;
    }
  );
}

Quick Start Guide

1. Install dependencies: npm install pg and configure environment variables for your database connection.
2. Replace your existing transaction wrapper with the executeTransaction template, passing the required isolation level and timeout configuration.
3. Update critical write paths to use explicit SELECT FOR UPDATE or UPDATE ... RETURNING patterns instead of read-then-write sequences.
4. Add serialization failure monitoring: track error.code === '40001' in your logging/metrics system and alert when the rate exceeds 0.5% of transactions.
5. Run a load test with concurrent writers to verify lock behavior, retry success rate, and throughput before promoting to production.