Transactions used for multi-step mutations

Multi-step mutations without transactions are a silent data corruption risk. When an order creation writes the order row but then fails before writing the order items, the application has a phantom order with no line items — a business record that is inconsistent at the database level and requires manual cleanup. This is a time-of-check/time-of-use race condition (CWE-362) and a functional-correctness failure per ISO 25010: either all writes succeed atomically or none do. AI-generated code frequently sequences multiple prisma.create() calls without recognizing that network or database failures between them leave partial state.

Wrap every multi-table mutation in prisma.$transaction() (Prisma) or BEGIN/COMMIT (raw SQL). For Prisma, prefer the interactive transaction form when the subsequent queries depend on results from earlier ones.

// Prisma — interactive transaction (auto-rollbacks on throw)
const order = await prisma.$transaction(async (tx) => {
  const order = await tx.order.create({ data: { userId, total, status: 'pending' } })
  await tx.orderItem.createMany({
    data: items.map(item => ({ orderId: order.id, ...item }))
  })
  await tx.product.updateMany({
    where: { id: { in: items.map(i => i.productId) } },
    data: { stock: { decrement: 1 } }
  })
  return order
})

BEGIN;
  INSERT INTO orders (user_id, total, status) VALUES ($1, $2, 'pending') RETURNING id;
  INSERT INTO order_items (order_id, product_id, quantity) VALUES ($3, $4, $5);
  UPDATE products SET stock = stock - $5 WHERE id = $4;
COMMIT;

• ID: database-design-operations.query-patterns.transactions-used

• Severity: high

• What to look for: Identify all multi-step write operations in the codebase — places where two or more tables are written as part of one logical operation. Common examples: creating a user + their profile record, processing a payment + updating order status + decrementing inventory, transferring funds (debit one account + credit another), creating an order + order_items rows. For each such multi-step mutation, check whether it is wrapped in a transaction. In Prisma, look for prisma.$transaction([...]) or prisma.$transaction(async (tx) => { ... }). In raw SQL, look for BEGIN/COMMIT/ROLLBACK. In Knex, look for knex.transaction(). Check that error handling is present — a try/catch that triggers rollback on failure (or relies on the framework's automatic rollback).

• Pass criteria: All multi-step write operations that must be atomic are wrapped in transactions — count every multi-table write path and verify at least 100% use transaction wrappers. Error paths are handled (automatic rollback via the transaction wrapper, or explicit ROLLBACK in catch blocks). Partial writes cannot leave data in an inconsistent state.

• Fail criteria: Multiple related writes happen sequentially without transaction wrapping. Partial failure of step 2 in a 3-step write leaves data inconsistently modified. Creates/updates across multiple tables with no transaction protection.

• Skip (N/A) when: Application only performs single-table, single-row writes — no multi-step mutations (rare for a real application).

• Detail on fail: Specify the multi-step operation and location. Example: "In src/app/api/orders/route.ts: order row created and inventory decremented in two separate queries with no transaction — a failure between them would create an order with no inventory adjustment." or "User signup creates user + profile in two sequential prisma.create() calls without $transaction wrapper.".

• Remediation: Wrap multi-step mutations in transactions:

  // Prisma — interactive transaction (recommended for complex logic)
  const [order, _] = await prisma.$transaction(async (tx) => {
    // Create the order
    const order = await tx.order.create({
      data: { userId, total, status: 'pending' }
    })
  
    // Create order line items
    await tx.orderItem.createMany({
      data: items.map(item => ({ orderId: order.id, ...item }))
    })
  
    // Decrement inventory
    await tx.product.updateMany({
      where: { id: { in: items.map(i => i.productId) } },
      data: { stock: { decrement: 1 } }
    })
  
    return [order, null]
  })
  // If any operation throws, Prisma automatically rolls back
  
  // Prisma — batch transaction (for independent operations)
  await prisma.$transaction([
    prisma.user.create({ data: userInput }),
    prisma.profile.create({ data: profileInput }),
  ])
  

  -- Raw SQL transaction
  BEGIN;
    INSERT INTO orders (user_id, total, status) VALUES ($1, $2, 'pending') RETURNING id;
    INSERT INTO order_items (order_id, product_id, quantity) VALUES ($3, $4, $5);
    UPDATE products SET stock = stock - $5 WHERE id = $4;
  COMMIT;
  -- If any statement fails, ROLLBACK is triggered