# Transactions used for multi-step mutations

- **Pattern:** `ab-000906` (`database-design-operations.query-patterns.transactions-used`)
- **Severity:** high
- **Lifecycle:** active
- **Last modified:** 2026-04-18
- **Canonical URL:** https://auditbuffet.com/patterns/ab-000906
- **License:** CC-BY-4.0 — attribute to AuditBuffet Pattern Catalog (https://auditbuffet.com/patterns/ab-000906)

## Why it matters

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.

## Severity rationale

High because non-atomic multi-step writes produce partially committed data on failure, creating records that are inconsistent and cannot be automatically reconciled without manual intervention.

## Remediation

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.

```ts
// 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
})
```

```sql
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;
```

## Detection

- **ID:** `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:

  ```ts
  // 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 }),
  ])
  ```

  ```sql
  -- 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
  ```

---

## External references

- cwe CWE-362
- iso-25010 functional-suitability.functional-correctness

Taxons: data-integrity

HTML version: https://auditbuffet.com/patterns/ab-000906