How to fix P2028: Transaction API error in Prisma

// ❌ WRONG - Using transaction after commit
const transaction = await prisma.$transaction(async (tx) => {
  await tx.user.create({ data: { name: 'Alice' } });
  await tx.post.create({ data: { title: 'Hello' } });
  return 'done';
});

// This will fail - transaction is already committed
await transaction.user.findMany(); // P2028 error

// ✅ CORRECT - Use the transaction only within the callback
const result = await prisma.$transaction(async (tx) => {
  const user = await tx.user.create({ data: { name: 'Alice' } });
  const post = await tx.post.create({ data: { title: 'Hello', authorId: user.id } });
  return { user, post };
});

// Use regular Prisma client after transaction
const users = await prisma.user.findMany(); // Works fine

// ❌ Problematic nested transactions
await prisma.$transaction(async (tx1) => {
  await tx1.user.create({ data: { name: 'Alice' } });

  // Nested transaction - may cause P2028
  await prisma.$transaction(async (tx2) => {
    await tx2.post.create({ data: { title: 'Hello' } });
  });
});

// ✅ Alternative: Use a single transaction
await prisma.$transaction(async (tx) => {
  await tx.user.create({ data: { name: 'Alice' } });
  await tx.post.create({ data: { title: 'Hello' } });
});

// ✅ For complex scenarios, use savepoints if supported by your database
// Note: Prisma doesn't natively support savepoints, you may need raw SQL

// Example: Checking transaction configuration
await prisma.$transaction(
  async (tx) => {
    // Your transaction operations
  },
  {
    isolationLevel: 'ReadCommitted', // or 'Serializable', 'RepeatableRead', 'ReadUncommitted'
    maxWait: 5000, // max wait for transaction lock
    timeout: 10000, // transaction timeout
  }
);

// If using MongoDB, ensure replica set is configured for transactions
// MongoDB requires replica set for transactions

async function executeWithRetry(operation: () => Promise<any>, maxRetries = 3) {
  for (let attempt = 1; attempt <= maxRetries; attempt++) {
    try {
      return await operation();
    } catch (error: any) {
      if (error.code === 'P2028' && attempt < maxRetries) {
        console.log(`Transaction failed (attempt ${attempt}/${maxRetries}), retrying...`);
        await new Promise(resolve => setTimeout(resolve, 100 * attempt)); // Exponential backoff
        continue;
      }
      throw error;
    }
  }
}

// Usage
await executeWithRetry(async () => {
  return await prisma.$transaction(async (tx) => {
    // Your transaction operations
  });
});

// Or with specific error handling
try {
  await prisma.$transaction(async (tx) => {
    // Your operations
  });
} catch (error: any) {
  if (error.code === 'P2028') {
    console.error('Transaction API error:', error.message);
    // Implement fallback logic or alert
  }
  throw error;
}

// Test file: transaction.test.ts
import { PrismaClient } from '@prisma/client';

const prisma = new PrismaClient();

describe('Transaction tests', () => {
  beforeEach(async () => {
    // Clean up before each test
    await prisma.post.deleteMany();
    await prisma.user.deleteMany();
  });

  test('Basic transaction works', async () => {
    const result = await prisma.$transaction(async (tx) => {
      const user = await tx.user.create({ data: { name: 'Test User' } });
      const post = await tx.post.create({
        data: { title: 'Test Post', authorId: user.id }
      });
      return { user, post };
    });

    expect(result.user.name).toBe('Test User');
    expect(result.post.title).toBe('Test Post');
  });

  test('Transaction rollback on error', async () => {
    try {
      await prisma.$transaction(async (tx) => {
        await tx.user.create({ data: { name: 'User 1' } });
        throw new Error('Simulated error');
        // This should not execute due to rollback
        await tx.user.create({ data: { name: 'User 2' } });
      });
    } catch (error) {
      // Expected error
    }

    // Verify rollback occurred
    const users = await prisma.user.findMany();
    expect(users.length).toBe(0); // No users should exist
  });
});

# Update Prisma and related packages
npm update @prisma/client @prisma/client

# Or for specific updates
npm install @prisma/client@latest @prisma/client@latest

# Generate Prisma client with updates
npx prisma generate

# Check for database driver updates
# For PostgreSQL:
npm update pg

# For MySQL:
npm update mysql2

# For SQLite:
npm update better-sqlite3

# For SQL Server:
npm update tedious