What Are Database Transactions? ACID Properties Explained for AI Coders

Why AI Coders Need to Understand Transactions

When AI generates code that writes to multiple tables, it often does them sequentially without a transaction:

// AI-generated code WITHOUT a transaction — dangerous
await db.order.create({ data: orderData })          // Step 1: succeeds
await db.inventory.update({ where: ..., data: ... }) // Step 2: FAILS (out of stock)
await db.payment.create({ data: paymentData })       // Step 3: never runs

// Result: An order exists in the database. No inventory was reduced.
// No payment was recorded. The state is inconsistent — an impossible situation.

With a transaction, if Step 2 fails, Step 1 is automatically reversed. The order never happened. The database stays consistent. If you're using Prisma as your ORM, it has built-in transaction support that makes this straightforward.

Transactions matter any time you have:

• Money movement (debit + credit must both succeed)
• Order creation (order + inventory reduction + payment record)
• User registration (user record + profile record + initial settings)
• Any write that depends on the success of a previous write

Real Scenario: Processing a Payment

What AI Generated (Prisma)

// Tested with Prisma 5.x + PostgreSQL 16

async function processPurchase(userId, productId, quantity, paymentDetails) {
  // prisma.$transaction() — everything inside is atomic
  // If ANY operation throws, ALL changes are rolled back automatically
  const result = await prisma.$transaction(async (tx) => {
    // Step 1: Check inventory (read inside transaction for consistency)
    const product = await tx.product.findUnique({
      where: { id: productId }
    })

    if (!product || product.stock < quantity) {
      throw new Error('Insufficient stock')  // Triggers automatic rollback
    }

    // Step 2: Create the order
    const order = await tx.order.create({
      data: {
        userId,
        productId,
        quantity,
        totalPrice: product.price * quantity,
        status: 'confirmed'
      }
    })

    // Step 3: Reduce inventory
    await tx.product.update({
      where: { id: productId },
      data: { stock: { decrement: quantity } }
    })

    // Step 4: Record payment
    const payment = await tx.payment.create({
      data: {
        orderId: order.id,
        amount: order.totalPrice,
        method: paymentDetails.method,
        status: 'completed'
      }
    })

    // If we reach here, all 4 steps succeeded → auto-commit
    return { order, payment }
  })

  return result
  // If any step threw, result is never returned — exception propagates
}

Equivalent in Raw SQL

-- Raw SQL transaction
BEGIN;  -- Start the transaction

-- Step 1: Lock the product row and check stock
SELECT stock FROM products WHERE id = $1 FOR UPDATE;
-- FOR UPDATE locks the row so concurrent transactions can't also reduce it

-- Step 2: Create order
INSERT INTO orders (user_id, product_id, quantity, total_price, status)
VALUES ($2, $1, $3, $4, 'confirmed')
RETURNING id;

-- Step 3: Reduce inventory
UPDATE products SET stock = stock - $3 WHERE id = $1;

-- Step 4: Record payment
INSERT INTO payments (order_id, amount, status) VALUES ($5, $4, 'completed');

COMMIT;  -- All changes now permanent

-- If anything above failed, you'd run ROLLBACK instead:
-- ROLLBACK;  -- Undoes everything since BEGIN

Understanding ACID Properties

A — Atomicity: All or Nothing

Every operation in a transaction is treated as a single indivisible unit. If the transaction fails at any point, the database state returns to exactly what it was before the transaction started — as if none of it ever happened.

The bank analogy: transferring $100 from Account A to Account B requires two operations (debit A, credit B). Atomicity guarantees you can never end up with a state where A was debited but B wasn't credited.

C — Consistency: Valid State Before and After

A transaction takes the database from one valid state to another valid state, never leaving it in a state that violates your defined rules. If you have a rule that account balances can't go negative, a transaction that would violate that rule is rejected entirely.

I — Isolation: Concurrent Transactions Don't Interfere

When two transactions run at the same time, they behave as if they ran one after the other. Imagine two customers both trying to buy the last 3 items in stock (there are 5 total). Without isolation, both see "5 in stock," both subtract 3, and you've sold 6 items you don't have. With isolation, the database makes sure one transaction finishes before the other reads the stock — the second customer correctly sees only 2 left and can't over-purchase.

You don't need to configure this yourself — your database handles it. When AI generates a transaction for you, isolation is built in.

D — Durability: Committed = Permanent

Once a transaction commits, the data is permanently saved — even if the server crashes immediately after. The database has internal mechanisms to ensure this. You don't need to know the details — just know that "committed" means "safe, period." This is why PostgreSQL and other production databases are trusted for financial and critical data.

The Prisma Batch Transaction (Simpler Case)

// When operations are independent (don't depend on each other's results):
const [updatedUser, newPost] = await prisma.$transaction([
  prisma.user.update({ where: { id: 1 }, data: { postCount: { increment: 1 } } }),
  prisma.post.create({ data: { title: 'New Post', authorId: 1 } })
])

// Both run in the same transaction — if either fails, both roll back
// Simpler than the async callback form, but can't use results of one in another

What AI Gets Wrong About Transactions

1. Missing Transactions Entirely

The most common mistake — AI generates sequential await calls without wrapping them in a transaction. Any failure leaves the database in a broken intermediate state.

Fix prompt: "Wrap these database operations in a Prisma transaction so they're atomic — if any step fails, all changes should roll back."

2. Long-Running Transactions

Transactions hold database locks for their entire duration. A transaction that calls an external API (Stripe, email service) while holding database locks can block other users for seconds.

// BAD: External API call inside transaction holds DB locks
await prisma.$transaction(async (tx) => {
  const order = await tx.order.create({ data: ... })
  await stripeChargeCard(order)   // This takes 200-2000ms — DB locked the whole time!
  await tx.payment.create({ data: ... })
})

// BETTER: Charge card outside transaction, record result inside
const order = await prisma.order.create({ data: ... })
const charge = await stripeChargeCard(order)  // Outside transaction — no lock held
await prisma.$transaction(async (tx) => {
  await tx.payment.create({ data: { ...charge } })
  await tx.order.update({ where: { id: order.id }, data: { status: 'paid' } })
})

3. Not Handling Transaction Errors

Transactions throw exceptions on failure — if you're not familiar with try/catch error handling, read that first. Here's the pattern:

// Transactions throw on rollback — always catch them
try {
  const result = await prisma.$transaction(async (tx) => {
    // ... operations
  })
} catch (error) {
  if (error.message === 'Insufficient stock') {
    return { success: false, reason: 'out_of_stock' }
  }
  throw error  // Re-throw unexpected errors
}

How to Debug Transaction Errors with AI

What to Learn Next

Frequently Asked Questions