Database Transactions with Claude Code: Optimistic Locking, Deadlocks, and Saga Pattern (2026-03-11)

#claudecode #node #postgres #prisma

Wrong transaction usage leads to deadlocks, negative inventory, and duplicate orders. Claude Code generates safe transaction design automatically from your CLAUDE.md.

CLAUDE.md for Transaction Safety

# Database Transaction Rules
- Multi-table writes always wrapped in a transaction
- No I/O (HTTP calls, file reads) inside transactions
- Keep transactions as short as possible
- Low contention → optimistic locking (version number column)
- High contention → SELECT FOR UPDATE (pessimistic)
- Always acquire locks in the same table order
- SET statement_timeout = '5s' on all transactions
- External services (Stripe, email) → Saga pattern with compensation

Optimistic Locking (version column)

async function reserveStock(productId: string, quantity: number): Promise<void> {
  const MAX_RETRIES = 3;

  for (let attempt = 0; attempt < MAX_RETRIES; attempt++) {
    const product = await prisma.product.findUnique({
      where: { id: productId },
      select: { version: true, stock: true },
    });

    if (!product || product.stock < quantity) {
      throw new Error('Insufficient stock');
    }

    const updated = await prisma.product.updateMany({
      where: {
        id: productId,
        version: product.version,       // Conflict guard
        stock: { gte: quantity },
      },
      data: {
        stock: { decrement: quantity },
        version: { increment: 1 },
      },
    });

    if (updated.count > 0) return; // Success

    // Conflict detected — exponential backoff
    const delay = 100 * Math.pow(2, attempt); // 100ms, 200ms, 400ms
    await new Promise(resolve => setTimeout(resolve, delay));
  }

  throw new Error('Stock reservation failed after retries (conflict)');
}

Pessimistic Locking (SELECT FOR UPDATE)

async function transferBalance(
  fromId: string,
  toId: string,
  amount: number
): Promise<void> {
  // Always lock in consistent order (lower ID first) to prevent deadlocks
  const [first, second] = [fromId, toId].sort();

  await prisma.$transaction(
    async (tx) => {
      // Lock rows in deterministic order
      const accounts = await tx.$queryRaw<Account[]>`
        SELECT id, balance FROM accounts
        WHERE id IN (${first}, ${second})
        ORDER BY id
        FOR UPDATE
      `;

      const from = accounts.find(a => a.id === fromId)!;
      const to = accounts.find(a => a.id === toId)!;

      if (from.balance < amount) throw new Error('Insufficient balance');

      await tx.account.update({ where: { id: fromId }, data: { balance: { decrement: amount } } });
      await tx.account.update({ where: { id: toId }, data: { balance: { increment: amount } } });
    },
    { timeout: 5000 }
  );
}

Saga Pattern (external services + compensation)

async function createOrderSaga(
  userId: string,
  items: OrderItem[],
  paymentMethodId: string
): Promise<Order> {
  let paymentIntentId: string | null = null;

  try {
    // Step 1: Reserve inventory (DB)
    await reserveStock(items);

    // Step 2: Charge via Stripe (external — outside any DB transaction)
    const intent = await stripe.paymentIntents.create({
      amount: calculateTotal(items),
      currency: 'jpy',
      payment_method: paymentMethodId,
      confirm: true,
    });
    paymentIntentId = intent.id;

    // Step 3: Create order record (DB)
    const order = await prisma.order.create({
      data: {
        userId,
        items: { create: items },
        paymentIntentId,
        status: 'confirmed',
      },
    });

    return order;

  } catch (error) {
    // Compensation: undo in reverse order
    if (paymentIntentId) {
      await stripe.paymentIntents.cancel(paymentIntentId).catch(e =>
        console.error('Payment cancel failed (manual review needed):', e)
      );
    }

    await releaseStock(items).catch(e =>
      console.error('Stock release failed (manual review needed):', e)
    );

    throw error;
  }
}

Why Each Pattern Fits

Scenario	Pattern	Why
Product stock update	Optimistic	Low contention; retry is cheap
Balance transfer	Pessimistic FOR UPDATE	High contention; correctness critical
Order with payment	Saga	Stripe is external; can't include in DB transaction

Key Rules Claude Code Enforces from CLAUDE.md

No I/O inside $transaction: Stripe calls are always outside; only DB operations inside
Lock ordering: [fromId, toId].sort() ensures consistent order → no circular deadlocks
Short transactions: Reserve → confirm → done; no polling or waiting inside
Compensation in .catch(): Compensation failures are logged but don't throw; original error propagates

Summary

CLAUDE.md defines the constraints → Claude Code generates optimistic locking with version columns and exponential backoff, pessimistic SELECT FOR UPDATE with deterministic lock ordering, and Saga compensation for external service calls — all without manual transaction design.

Want Claude Code to generate safe, production-ready code from your specs?

Code Review Pack (¥980) — prompt collection for architecture review, security check, and refactoring → prompt-works.jp

DEV Community