Database Deadlock Detected Error

Ensure all transactions access tables and rows in the same consistent order.

// ❌ Deadlock prone
// Transaction 1: update A → update B
// Transaction 2: update B → update A

// ✅ Safe: always update in the same order
always update A first, then B

// In application code, sort IDs before updating
transaction(async (tx) => {
  const ids = [userId, otherId].sort() // Sort to ensure consistent order
  for (const id of ids) {
    await tx.users.update({ where: { id }, data: { ... } })
  }
})

Catch deadlock errors and retry the transaction.

import { Prisma } from "@prisma/client"

async function retryOnDeadlock(fn, maxRetries = 3) {
  for (let i = 0; i < maxRetries; i++) {
    try {
      return await fn()
    } catch (err) {
      if (err instanceof Prisma.PrismaClientKnownRequestError && err.code === "P2034") {
        console.log(`Deadlock detected, retrying (${i + 1}/${maxRetries})`)
        continue
      }
      throw err
    }
  }
}

Minimize the duration of transactions to reduce lock contention.

// ❌ Long transaction with external API call inside
await prisma.$transaction(async (tx) => {
  await tx.users.update(...)
  await fetch("https://slow-api.com/data") // 🔴 Holds lock while waiting!
  await tx.orders.update(...)
})

// ✅ Do external work outside the transaction
const data = await fetch("https://slow-api.com/data").then(r => r.json())
await prisma.$transaction(async (tx) => {
  await tx.users.update(...)
  await tx.orders.update({ ...data })
})