import { Pool, PoolClient } from 'pg';

interface UserAccount {
  id: string;
  balance: number;
  version: number;
}

export class OptimisticConcurrencyService {
  constructor(private db: Pool) {}

  async updateBalance(accountId: string, amount: number): Promise<void> {
    const client = await this.db.connect();
    try {
      await client.query('BEGIN');

      // 1. Read current state
      const readResult = await client.query<UserAccount>(
        'SELECT id, balance, version FROM accounts WHERE id = $1 FOR SHARE',
        [accountId]
      );

      if (readResult.rows.length === 0) {
        throw new Error('Account not found');
      }

      const account = readResult.rows[0];
      const newBalance = account.balance + amount;

      // 2. Update with version check
      const updateResult = await client.query(
        `UPDATE accounts 
         SET balance = $1, version = version + 1 
         WHERE id = $2 AND version = $3`,
        [newBalance, accountId, account.version]
      );

      if (updateResult.rowCount === 0) {
        throw new Error('CONFLICT: Version mismatch');
      }

      await client.query('COMMIT');
    } catch (error) {
      await client.query('ROLLBACK');
      throw error;
    } finally {
      client.release();
    }
  }
}

export class PessimisticService {
  c

onstructor(private db: Pool) {}

async transferFunds(fromId: string, toId: string, amount: number): Promise<void> { // CRITICAL: Sort IDs to enforce lock ordering and prevent deadlocks const [firstId, secondId] = [fromId, toId].sort();

const client = await this.db.connect();
try {
  await client.query('BEGIN');

  // Acquire locks in sorted order
  await client.query('SELECT id FROM accounts WHERE id = $1 FOR UPDATE', [firstId]);
  await client.query('SELECT id FROM accounts WHERE id = $1 FOR UPDATE', [secondId]);

  // Perform updates
  await client.query(
    'UPDATE accounts SET balance = balance - $1 WHERE id = $2',
    [amount, fromId]
  );
  await client.query(
    'UPDATE accounts SET balance = balance + $1 WHERE id = $2',
    [amount, toId]
  );

  await client.query('COMMIT');
} catch (error) {
  await client.query('ROLLBACK');
  throw error;
} finally {
  client.release();
}

} }

**Architecture Rationale:**
*   **Lock Ordering:** Sorting keys before locking is mandatory in multi-row updates. Violating this causes deadlocks under contention.
*   **Lock Timeout:** Configure `lock_timeout` in the database to fail fast rather than hanging indefinitely.
*   Use when contention is high and retries are expensive or impossible (e.g., financial ledgers).

### 3. Compare-And-Swap (CAS) / Atomic Operations

For simple counters or flags, full transactions are overkill. Use atomic operations provided by the database.

**Implementation (PostgreSQL):**

```typescript
export class InventoryService {
  constructor(private db: Pool) {}

  async decrementStock(itemId: string, quantity: number): Promise<boolean> {
    const result = await this.db.query(
      `UPDATE inventory 
       SET quantity = quantity - $1 
       WHERE item_id = $2 AND quantity >= $1`,
      [quantity, itemId]
    );

    return result.rowCount > 0;
  }
}

Architecture Rationale:

• This pattern is lock-free and highly efficient.
• The WHERE clause acts as the CAS condition.
• Returns false if stock is insufficient, allowing the application to handle the failure without transaction overhead.

4. Queue-Based Serialization for Hot Keys

When a single key (e.g., a popular product's inventory) experiences extreme contention, even CAS operations can cause CPU spikes due to cache line bouncing. Offload updates to a queue to serialize writes.

Implementation Pattern:

1. Application publishes update intent to a message queue (e.g., Redis Stream, Kafka).
2. A single consumer processes updates sequentially.
3. Consumer applies updates to the database.
4. Application polls or subscribes for the result.

Architecture Rationale:

• Eliminates database lock contention by serializing at the application layer.
• Introduces latency but guarantees throughput for the hot key.
• Requires idempotency keys to handle consumer failures.

Pitfall Guide

1. N+1 Locking

Mistake: Acquiring locks row-by-row in a loop.

// BAD: High risk of deadlock and performance degradation
for (const id of ids) {
  await client.query('SELECT ... FOR UPDATE WHERE id = $1', [id]);
}

Best Practice: Batch locks using WHERE id IN (...) or sort IDs and lock in a single pass. N+1 locking increases the probability of deadlock cycles and holds connections longer.

2. Retry Storms in Optimistic Concurrency

Mistake: Retrying immediately upon conflict without backoff.

// BAD: Amplifies load during contention
if (conflict) { retry(); }

Best Practice: Implement exponential backoff with jitter. Jitter prevents synchronized retries from multiple clients, which creates thundering herds.

const delay = Math.min(1000 * Math.pow(2, attempt), maxDelay) + Math.random() * 100;
await new Promise(r => setTimeout(r, delay));

3. Inconsistent Lock Ordering

Mistake: Locking resources in arbitrary order based on input. Best Practice: Define a global lock ordering strategy (e.g., lexicographical sort of keys) and enforce it in all code paths. Deadlocks are often intermittent and disappear during debugging; enforce ordering to eliminate them deterministically.

4. Phantom Reads and Serialization Anomalies

Mistake: Assuming REPEATABLE READ prevents all anomalies. Best Practice: Understand isolation levels. REPEATABLE READ prevents non-repeatable reads but may allow phantom reads depending on the database. Use SERIALIZABLE isolation or explicit locking (FOR SHARE/FOR UPDATE) when range queries are involved. ORMs often default to READ COMMITTED, which is insufficient for complex concurrency logic.

5. ORM Caching Invalidations

Mistake: ORM caches entity state, and concurrent updates bypass the cache. Best Practice: When using OCC, ensure the ORM checks the version column. If the ORM caches the entity, a concurrent update may not invalidate the cache, leading to stale reads. Configure the ORM to bypass cache for concurrent operations or use explicit version checks in queries.

6. Lock Escalation Surprises

Mistake: Relying on row locks without considering database lock escalation. Best Practice: Some databases escalate row locks to table locks if the number of locks exceeds a threshold. Monitor lock escalation events. If escalation occurs, Pessimistic locking performance will degrade abruptly. Tune max_locks_per_transaction or redesign the schema to reduce lock count.

7. Distributed Lock Failures

Mistake: Using application-level locks (e.g., Mutex in memory) in distributed systems. Best Practice: In-memory locks do not work across processes. Use database-level locks or a distributed lock manager (e.g., Redis SETNX with TTL). Database locks are preferred as they integrate with transaction semantics.

Production Bundle

Action Checklist

• Audit Contention Hotspots: Identify keys with high write frequency using database monitoring (pg_stat_user_tables, innodb_row_lock_waits).
• Implement Versioning: Add version or updated_at columns to all tables requiring OCC. Update all UPDATE queries to include version checks.
• Enforce Lock Ordering: Review all multi-row update transactions and ensure keys are sorted before locking. Add linter rules to flag unsorted lock acquisitions.
• Add Retry Logic with Jitter: Wrap OCC operations in a retry decorator with exponential backoff and jitter. Limit max retries to prevent infinite loops.
• Configure Lock Timeouts: Set lock_timeout at the session or database level to fail fast on lock waits. Monitor lock wait events in dashboards.
• Test Concurrency: Use load testing tools (e.g., k6, Artillery) with concurrent requests targeting the same keys to verify patterns under contention.
• Monitor Isolation Levels: Verify that application sessions use the correct isolation level. Avoid relying on default levels that may not match consistency requirements.

Decision Matrix

Scenario	Recommended Approach	Why	Cost Impact
User Profile Update	Optimistic Concurrency	Low contention; high read ratio; retries are cheap.	Low
Inventory Decrement	Pessimistic Locking or CAS	High contention; strict consistency required; retries expensive.	Medium
Financial Ledger Entry	Pessimistic Locking + Serializable	Audit trail; zero tolerance for anomalies; regulatory compliance.	High
Leaderboard Score Update	Queue Serialization or Atomic CAS	Hot key; throughput priority; eventual consistency acceptable.	Low
Shopping Cart Checkout	Hybrid (Read-Optimistic / Write-Pessimistic)	Read-heavy cart; write-heavy checkout; balances performance and safety.	Medium

Configuration Template

TypeScript configuration for a concurrency manager with retry and timeout policies.

// concurrency.config.ts
export interface ConcurrencyConfig {
  optimistic: {
    maxRetries: number;
    baseDelayMs: number;
    maxDelayMs: number;
    jitterFactor: number; // 0 to 1
  };
  pessimistic: {
    lockTimeoutMs: number;
    deadlockRetryCount: number;
  };
  isolationLevel: 'READ COMMITTED' | 'REPEATABLE READ' | 'SERIALIZABLE';
}

export const defaultConfig: ConcurrencyConfig = {
  optimistic: {
    maxRetries: 3,
    baseDelayMs: 50,
    maxDelayMs: 1000,
    jitterFactor: 0.1,
  },
  pessimistic: {
    lockTimeoutMs: 5000,
    deadlockRetryCount: 1,
  },
  isolationLevel: 'REPEATABLE READ',
};

// Usage in service
export function withOptimisticRetry<T>(
  fn: () => Promise<T>,
  config: ConcurrencyConfig['optimistic'] = defaultConfig.optimistic
): Promise<T> {
  let attempt = 0;

  async function execute(): Promise<T> {
    try {
      return await fn();
    } catch (error) {
      if (isConflictError(error) && attempt < config.maxRetries) {
        attempt++;
        const delay = calculateDelay(attempt, config);
        await sleep(delay);
        return execute();
      }
      throw error;
    }
  }

  return execute();
}

function calculateDelay(attempt: number, config: ConcurrencyConfig['optimistic']): number {
  const exponential = Math.min(
    config.baseDelayMs * Math.pow(2, attempt),
    config.maxDelayMs
  );
  const jitter = Math.random() * config.jitterFactor * exponential;
  return exponential + jitter;
}

function isConflictError(error: unknown): boolean {
  // Check for version mismatch or serialization failure
  return error instanceof Error && 
    (error.message.includes('CONFLICT') || error.message.includes('serialization'));
}

Quick Start Guide

1. Add Version Column: Execute ALTER TABLE your_table ADD COLUMN version INTEGER DEFAULT 0; on all target tables.
2. Update Queries: Modify all UPDATE statements to include WHERE version = $current_version and increment version: SET version = version + 1.
3. Implement Retry Wrapper: Wrap service methods calling optimistic updates with the withOptimisticRetry function from the configuration template.
4. Verify Lock Ordering: Search codebase for FOR UPDATE clauses. Ensure any transaction locking multiple rows sorts keys before acquisition.
5. Load Test: Run a concurrent load test targeting a single key. Verify that OCC retries succeed and Pessimistic locking does not deadlock. Monitor database lock wait metrics.