How to Avoid: Multi-step Writes Without a Transaction

What Are Multi-step Writes Without a Transaction?

Multi-step writes without a transaction occur when AI-generated code performs several related database operations as independent statements instead of wrapping them in an atomic transaction block. The code looks correct—each write succeeds in isolation—but if any step fails, your database is left in an inconsistent state with no automatic rollback.

This is one of the most common logic bugs in AI-generated code because the failure mode is invisible during normal operation. The code works perfectly when everything succeeds. The problem only surfaces when a network timeout, constraint violation, or server crash interrupts the sequence mid-execution.

Why AI Produces Non-Atomic Writes

AI code generation models have a systematic blind spot for atomicity requirements. Several factors contribute to this pattern:

Training Data Reflects Happy Paths

Most code examples in training datasets show operations that work. Tutorial code, Stack Overflow answers, and documentation samples rarely include comprehensive error handling or transaction logic because they're demonstrating specific functionality, not production resilience.

Sequential Logic Appears Correct

When you ask AI to "create a user and set up their account," it produces logically sequential code:

// AI-generated: looks correct, breaks on failure
const user = await db.users.create({ email, name });
await db.accounts.create({ userId: user.id, balance: 0 });
await db.preferences.create({ userId: user.id, theme: 'default' });
await sendWelcomeEmail(user.email);

Each line does what it should. The AI has correctly understood the business logic. What it hasn't considered is what happens when db.preferences.create throws an error—you now have a user and account with no preferences, and the welcome email never sent.

Atomicity Requirements Are Implicit

The need for a transaction is a business rule, not a syntactic one. AI can't infer from "create user and account" that these operations must succeed or fail together. Unless you explicitly state atomicity requirements, the model has no signal that a transaction is needed.

Context Window Limitations

Even when earlier code in the same file uses transactions, AI may not maintain that pattern consistently. Each generation is influenced by the immediate prompt context, and established patterns can drift across a long file or multiple generation requests.

The Real-World Consequences

Non-atomic writes cause problems that are difficult to diagnose and expensive to fix:

Orphaned Records

A user exists but their required related records don't. Your application assumes certain relationships exist and throws errors or behaves unexpectedly when they're missing.

Financial Inconsistencies

An order is recorded but inventory isn't decremented. A payment is logged but the subscription isn't activated. A refund is issued but the credit balance isn't restored. These mismatches often require manual database intervention to resolve.

Cascade Failures

Application code that depends on data consistency starts failing in unpredictable ways. A missing preference record causes a null reference exception in a completely different part of your system, making the root cause hard to trace.

Silent Data Corruption

The worst outcome: partial writes that don't cause immediate errors but slowly corrupt your data integrity. You might not discover the problem until months later during an audit or migration.

How to Detect Non-Atomic Writes

Manual Code Review Signals

Look for these patterns in AI-generated code:

• Multiple INSERT, UPDATE, or DELETE statements in the same function without transaction boundaries
• ORM methods like .save(), .create(), or .update() called multiple times on related models
• Database operations mixed with external API calls (email, payment processors) without compensation logic
• Keywords like await db. appearing multiple times in sequence

Automated Detection

Search your codebase for functions that match this pattern:

# Find files with multiple sequential database operations
grep -l "await.*\.create\|await.*\.insert\|await.*\.update" src/**/*.ts | \
  xargs grep -c "await.*db\." | \
  grep -v ":1$"  # Files with more than one db operation

Then manually review each match for proper transaction wrapping.

Review Checklist

When reviewing AI-generated database code, ask:

1. Are there multiple write operations in this function?
2. Do these operations need to succeed or fail together?
3. What state does the database end up in if step 2 of 4 fails?
4. Is there explicit transaction handling with rollback on error?

The Correct Pattern

Basic Transaction Structure

Wrap all-or-nothing operations in explicit transaction blocks:

// Correct: atomic operation with rollback
async function createUserWithAccount(email, name) {
  const transaction = await db.transaction();
  
  try {
    const user = await transaction.users.create({ email, name });
    await transaction.accounts.create({ userId: user.id, balance: 0 });
    await transaction.preferences.create({ userId: user.id, theme: 'default' });
    
    await transaction.commit();
    
    // External calls AFTER commit - can't roll back email anyway
    await sendWelcomeEmail(user.email);
    
    return user;
  } catch (error) {
    await transaction.rollback();
    throw error;
  }
}

ORM-Specific Patterns

Different ORMs have different transaction APIs. Here are correct patterns for common tools:

Prisma:

const result = await prisma.$transaction(async (tx) => {
  const user = await tx.user.create({ data: { email, name } });
  const account = await tx.account.create({ 
    data: { userId: user.id, balance: 0 } 
  });
  return { user, account };
});

Knex:

const result = await knex.transaction(async (trx) => {
  const [userId] = await trx('users').insert({ email, name });
  await trx('accounts').insert({ user_id: userId, balance: 0 });
  return userId;
});

Sequelize:

const result = await sequelize.transaction(async (t) => {
  const user = await User.create({ email, name }, { transaction: t });
  await Account.create({ userId: user.id, balance: 0 }, { transaction: t });
  return user;
});

Handling External Side Effects

External operations (emails, webhooks, third-party APIs) can't be rolled back. Structure your code to perform them after the transaction commits:

async function processOrder(orderId, items) {
  let order;
  
  // Database operations in transaction
  const transaction = await db.transaction();
  try {
    order = await transaction.orders.update(orderId, { status: 'processing' });
    for (const item of items) {
      await transaction.inventory.decrement(item.productId, item.quantity);
    }
    await transaction.commit();
  } catch (error) {
    await transaction.rollback();
    throw error;
  }
  
  // External calls after commit succeeds
  // If these fail, the database state is still correct
  await notifyWarehouse(order);
  await sendOrderConfirmation(order.customerEmail);
}

Idempotency for Retries

When operations might be retried (webhooks, queue processors), combine transactions with idempotency checks:

async function handlePaymentWebhook(event) {
  const transaction = await db.transaction();
  
  try {
    // Check if already processed
    const existing = await transaction.processedEvents.findOne({ 
      eventId: event.id 
    });
    if (existing) {
      await transaction.rollback();
      return { status: 'already_processed' };
    }
    
    // Process the event atomically
    await transaction.processedEvents.create({ eventId: event.id });
    await transaction.subscriptions.update(
      event.data.subscriptionId,
      { status: 'active' }
    );
    
    await transaction.commit();
    return { status: 'processed' };
  } catch (error) {
    await transaction.rollback();
    throw error;
  }
}

Prompting AI for Correct Transactions

When generating database code with AI, explicitly specify atomicity requirements:

Weak prompt:

Create a function to register a new user with an account and preferences.

Strong prompt:

Create a function to register a new user with an account and preferences. All database operations must be wrapped in a single transaction that rolls back completely if any step fails. External operations like sending emails should happen after the transaction commits.

Even with good prompts, verify the output. AI may still produce non-atomic code or implement transactions incorrectly.

Integrating Transaction Checks Into Your Workflow

Pre-Commit Hooks

Add linting rules that flag multiple database operations without transaction wrappers. Tools like ESLint can be configured with custom rules to catch common patterns.

Code Review Checklists

Include "Are multi-table writes wrapped in transactions?" as a standard review item for any PR touching database code.

Automated Verification

Tools like Fairy Scout can analyze AI-generated code for logic bugs including non-atomic writes, surfacing issues before they reach production.

When Transactions Aren't Enough

Some scenarios require additional patterns beyond database transactions:

Distributed Systems

When operations span multiple services or databases, you need saga patterns or two-phase commits rather than single-database transactions.

Long-Running Operations

Transactions that hold locks for extended periods cause contention. Consider breaking into smaller transactions with application-level compensation logic.

Performance-Critical Paths

Transaction overhead matters at scale. Profile your actual workload before assuming transactions are too expensive—but also consider eventual consistency patterns for truly high-throughput scenarios.

Building Reliable AI-Generated Code

Multi-step writes without a transaction represent a class of AI code generation bugs that are invisible in the happy path but catastrophic in production. The fix is straightforward—wrap related operations in transaction blocks—but detecting the problem requires deliberate review.

This pattern is one of many logic bugs that AI consistently produces. Others include missing null handling, floating point for money, and access granted before payment confirmation. Each follows the same pattern: code that looks correct and works in testing but fails under real-world conditions.

Production-grade AI code requires systematic verification. Whether through manual review, automated tooling, or expert oversight through platforms like Fairy, the gap between "code that runs" and "code that's reliable" needs active management.