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;