Transactions

an5 ORM supports transactions to ensure data consistency when performing multiple related operations.

Mysql note: MySQL (InnoDB engine) enforces full ACID compliance and row-level locking during active transactions.

Basic Transaction (Scoped)

Execute multiple database operations within a scoped callback. If any operation throws an error, all changes roll back automatically.

await db.$transaction(async (tx) => {
  const user = await tx.user.create({
    data: { email: 'john@example.com', name: 'John' }
  });
  
  const order = await tx.order.create({
    data: {
      userId: user.id,
      total: 100
    }
  });
});

Interactive Transactions ($begin, $commit, $rollback)

Use $begin() when you need manual control over transaction boundaries across asynchronous steps:

const tx = await db.$begin();

try {
  await tx.account.update({
    where: { id: senderId },
    data: { balance: { decrement: amount } }
  });

  await tx.account.update({
    where: { id: receiverId },
    data: { balance: { increment: amount } }
  });

  await tx.$commit();
} catch (error) {
  await tx.$rollback();
  throw error;
}

Nested Transactions

Inner $transaction blocks automatically map to savepoints on supported engines:

await db.$transaction(async (tx) => {
  const user = await tx.user.create({
    data: { email: 'john@example.com' }
  });
  
  await tx.$transaction(async (innerTx) => {
    await innerTx.order.create({
      data: { userId: user.id, total: 100 }
    });
  });
});

Best Practices

  1. Keep transactions short - Minimize the time table rows are locked to avoid blocking concurrent queries.
  2. Handle errors properly - Always handle potential deadlock or timeout exceptions in application code.
  3. Consistent ordering - Always update tables and records in a deterministic order across all endpoints to prevent database deadlocks.
  4. Choose the appropriate isolation level - Tailor the transaction isolation according to concurrency and consistency requirements.

Next Steps