Database transaction isolation

import { Pool, PoolClient } from 'pg';

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 20,
  idleTimeoutMillis: 30000,
});

export enum IsolationLevel {
  READ_COMMITTED = 'READ COMMITTED',
  REPEATABLE_READ = 'REPEATABLE READ',
  SERIALIZABLE = 'SERIALIZABLE',
}

export async function executeTransaction<T>(
  isolation: IsolationLevel,
  handler: (client: PoolClient) => Promise<T>
): Promise<T> {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    await client.query(`SET TRANSACTION ISOLATION LEVEL ${isolation}`);
    
    const result = await handler(client);
    await client.query('COMMIT');
    return result;
  } catch (error) {
    await client.query('ROLLBACK');
    throw error;
  } finally {
    client.release();
  }
}

async function withRetry<T>(
  isolation: IsolationLevel,
  handler: (client: PoolClient) => Promise<T>,
  maxRetries = 3,
  baseDelay = 50
): Promise<T> {
  for (let attempt = 1; attempt <= maxRetries; attempt++) {
    try {
      return await executeTransaction(isolation, handler);
    } catch (error: any) {
      const isSerializationError = 
        error

.code === '40001' || error.message?.includes('could not serialize');

  if (!isSerializationError || attempt === maxRetries) {
    throw error;
  }
  
  const delay = baseDelay * Math.pow(2, attempt - 1) + Math.random() * 50;
  await new Promise(res => setTimeout(res, delay));
}

} throw new Error('Unreachable'); }

### Step 3: Connection Pool Isolation Hygiene
Connection pooling reuses connections. If a transaction fails to reset isolation, subsequent queries inherit stale settings. Explicitly reset after commit/rollback.

```typescript
// Inside executeTransaction finally block, add:
await client.query('SET TRANSACTION ISOLATION LEVEL READ COMMITTED');

Step 4: Architecture Decisions & Rationale

• Explicit over implicit: ORM defaults vary by driver version and database. Explicit SET TRANSACTION ISOLATION LEVEL guarantees deterministic behavior across environments.
• Retry over abort: Serialization failures are expected under high concurrency. Exponential backoff with jitter prevents thundering herd retries and aligns with database conflict resolution.
• MVCC awareness: PostgreSQL uses MVCC for REPEATABLE READ. No gap locks. Phantom reads are impossible, but write conflicts trigger serialization failures. MySQL InnoDB uses next-key locks for REPEATABLE READ. Read queries block on conflicting writes. Architecture must match engine semantics.
• Connection affinity: Isolation settings are connection-scoped. Never share transactional connections across request handlers. The pool.connect() / client.release() pattern enforces boundary integrity.

Pitfall Guide

1. Assuming ORM defaults match database defaults
   Prisma defaults to the database driver's isolation level. TypeORM inherits from pg or mysql2. If your CI uses SQLite and production uses PostgreSQL, isolation behavior diverges. Always override explicitly.

2. Using SERIALIZABLE without retry logic
   Serializable transactions fail under concurrent write patterns. Without retry, production systems experience 10-30% request failure rates during peak load. Retry is mandatory, not optional.

3. Ignoring MVCC vs. lock-based differences
   PostgreSQL REPEATABLE READ uses snapshot isolation. MySQL REPEATABLE READ uses gap locks. The same isolation level name produces different lock footprints. Cross-engine migrations require isolation re-evaluation, not just syntax translation.

4. Mixing isolation levels in the same connection pool
   If one request sets SERIALIZABLE and fails without resetting, the next request inherits it. This silently degrades throughput. Always reset to baseline in finally blocks.

5. Not handling 40001 serialization errors
   PostgreSQL returns SQLSTATE 40001 for serialization failures. Catching generic Error masks the root cause. Explicit error code checking enables targeted retry strategies.

6. Testing concurrency with sequential scripts
   Unit tests and basic integration tests run single-threaded. They never trigger phantom reads, non-repeatable reads, or serialization failures. Concurrency bugs only surface under load. Use tools like pg_isolation_test or custom concurrent test harnesses.

7. Overlooking application-level validation gaps
   Isolation prevents database-level anomalies. It does not enforce business rules. Example: REPEATABLE READ prevents non-repeatable reads, but if two transactions read the same inventory count and both decrement, you get negative stock without a CHECK constraint or SELECT ... FOR UPDATE. Isolation is necessary but insufficient for domain correctness.

Production Best Practices:

• Map isolation levels to anomaly tolerance, not safety aspirations.
• Pair READ COMMITTED with explicit row locks for critical updates.
• Use REPEATABLE READ for read-heavy reporting or snapshot consistency.
• Reserve SERIALIZABLE for financial settlement, audit trails, or strict consistency requirements.
• Monitor pg_stat_activity or information_schema.innodb_trx for lock waits and serialization failures.
• Align connection pool size with isolation overhead. Higher isolation requires smaller pools to prevent lock queue saturation.

Production Bundle

Action Checklist

• Audit current ORM/driver isolation defaults across all environments
• Replace implicit transaction blocks with explicit SET TRANSACTION ISOLATION LEVEL
• Implement serialization failure retry with exponential backoff and jitter
• Add isolation reset in transaction finally blocks to prevent pool leakage
• Map business anomaly tolerance to isolation selection matrix
• Instrument lock wait metrics and serialization failure rates in observability stack
• Replace sequential concurrency tests with parallel execution harnesses
• Validate row-level locking strategy against isolation level semantics

Decision Matrix

Scenario	Recommended Approach	Why	Cost Impact
Inventory decrement with concurrent orders	READ COMMITTED + SELECT FOR UPDATE	Prevents double-spend without serializing reads	Low (minimal lock overhead)
Financial settlement ledger	SERIALIZABLE with retry	Guarantees total ordering, prevents phantom/non-repeatable reads	Medium (retry latency, lower throughput)
Dashboard analytics with snapshot consistency	REPEATABLE READ	Stable read view across query batch, no write conflicts expected	Low-Medium (snapshot memory)
High-throughput event ingestion	READ COMMITTED	Anomalies tolerable, throughput prioritized, idempotency handles duplicates	Low (baseline performance)
Cross-database migration (Postgres → MySQL)	Explicit isolation mapping + gap lock audit	InnoDB next-key locks change concurrency behavior	Medium (engineering audit, testing)

Configuration Template

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

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: parseInt(process.env.DB_POOL_MAX || '20', 10),
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 5000,
});

export const ISOLATION_RESET = 'SET TRANSACTION ISOLATION LEVEL READ COMMITTED';

export async function runTransaction<T>(
  isolation: 'READ COMMITTED' | 'REPEATABLE READ' | 'SERIALIZABLE',
  fn: (client: PoolClient) => Promise<T>,
  retries = 3,
  baseDelay = 50
): Promise<T> {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    await client.query(`SET TRANSACTION ISOLATION LEVEL ${isolation}`);
    
    const result = await fn(client);
    await client.query('COMMIT');
    return result;
  } catch (error: any) {
    await client.query('ROLLBACK');
    
    const isSerialization = error.code === '40001' || error.message?.includes('serialize');
    if (isSerialization && retries > 0) {
      const delay = baseDelay * Math.pow(2, 3 - retries) + Math.random() * 50;
      await new Promise(res => setTimeout(res, delay));
      return runTransaction(isolation, fn, retries - 1, baseDelay);
    }
    throw error;
  } finally {
    await client.query(ISOLATION_RESET);
    client.release();
  }
}

// Usage
// await runTransaction('SERIALIZABLE', async (client) => {
//   await client.query('UPDATE accounts SET balance = balance - 100 WHERE id = $1', [1]);
//   await client.query('UPDATE accounts SET balance = balance + 100 WHERE id = $2', [2]);
// });

Quick Start Guide

1. Install dependencies: npm i pg @types/pg
2. Set environment variable: DATABASE_URL=postgresql://user:pass@localhost:5432/dbname
3. Replace existing transaction calls with runTransaction() using the isolation level matching your anomaly tolerance
4. Add observability: Instrument 40001 error counts and lock wait times in your metrics pipeline
5. Validate under load: Run concurrent write tests (e.g., 50 parallel transactions updating the same row) to confirm retry logic and isolation behavior match expectations