Long-running transactions

Optimize provides actionable recommendations to help you identify and resolve performance issues caused by long-running transactions.

Long-running transactions can negatively impact scalability and resilience by locking resources and holding database connections for extended periods. Below is a common example of a problematic long-running transaction:

// Example: A single massive transaction performing multiple steps
await prisma.$transaction(async (prisma) => {
  const order = await prisma.order.create({
    data: {
      /* ... */
    },
  });
  await prisma.user.update({
    where: { id: userId },
    data: { balance: { decrement: order.total } },
  });
  await prisma.shipping.create({ data: { orderId: order.id /* ... */ } });
  // Additional dependent operations
});

What is the problem?

Long-running transactions can cause several critical issues that harm the performance and reliability of your application:

• Database locks: Long transactions hold locks on rows, tables, or other resources, preventing access by other queries. This leads to contention and blocking, which can significantly disrupt concurrent operations.

• Connection tie-ups: Transactions occupy database connections for their entire duration. With a limited connection pool, this can quickly exhaust available connections, resulting in application-wide slowdowns or failures.

• Increased contention: As locks accumulate and connections are tied up, other transactions queue up, creating bottlenecks, higher latency, and reduced throughput.

• Scalability challenges: Inefficiencies caused by long transactions are magnified in high-traffic systems, limiting the system’s ability to scale effectively.

• Fragility: When a long transaction fails or times out, all intermediate progress is lost. This is especially problematic in workflows with multiple dependent steps, as recovering from partial failures becomes complex and error-prone.

• Debugging difficulties: Troubleshooting long-running transactions is challenging due to their multiple steps and potential failures caused by timeouts, deadlocks, or unexpected dependencies.