Transactions

Database transactions are a series of read/write operations that are guaranteed to either succeed or fail as a whole. In this chapter, we will cover:

• Interactive Transactions
• Catching Exceptions/Errors
• Isolation Levels
• Timeouts
• Manual Transactions

Interactive Transactions

Use the $transaction method to execute a closure within a transaction. If any of the operations within the closure fail, the entire transaction will be rolled back:

/// Transfer views from one post to another
transfer(int from, int to, int views) async {
  await prisma.$transaction((tx) async {
    // 1. Decrement views from the source post
    await tx.post.update(
      where: PostWhereUniqueInput(id: from),
      data: PrismaUnion.$1(
        PostUpdateInput(
          views: PrismaUnion.$2(
            IntFieldUpdateOperationsInput(decrement: views),
          ),
        ),
      ),
    );

    // 2. Increment views from the destination post
    await tx.post.update(
      where: PostWhereUniqueInput(id: to),
      data: PrismaUnion.$1(
        PostUpdateInput(
          views: PrismaUnion.$2(
            IntFieldUpdateOperationsInput(increment: views),
          ),
        ),
      ),
    );
  });
}

await transfer(1, 2, 10); // Transfer 10 views from post 1 to post 2
await transfer(2, 1, 10); // Transfer 10 views from post 2 to post 1

In the example above, you can try passing invalid data to transfer as any exception thrown will cause the transaction to be rolled back without affecting the data.

Catching Exceptions/Errors

You may sometimes want to catch exceptions or errors within a transaction. You can do so using a try/catch statement:

try {
  await prisma.$transaction((tx) async {
    // Code running in a transaction...
  });
} catch (e) {
  // Handle the rollback...
}

Isolation Levels

Prisma Dart client transactions support isolation levels (if the database supports them). By default, transactions use the database's default isolation level. To change the isolation level, use the isolationLevel parameter:

await prisma.$transaction(
  isolationLevel: TransactionIsolationLevel.serializable, 
  (tx) async {
    // Code running in a transaction...
  },
);

Supported database isolation level matrix:

Database	Read Uncommitted	Read Committed	Repeatable Read	Serializable	Snapshot
PostgreSQL	✅	✅	✅	✅	❌
MySQL	✅	✅	✅	✅	❌
SQL Server	✅	✅	✅	✅	✅
CockroachDB	❌	❌	❌	✅	❌
SQLite	❌	❌	❌	✅	❌

Default isolation levels:

PostgresSQL	MySQL	SQL Server	CockroachDB	SQLite
Read Committed	Repeatable Read	Read Committed	Serializable	Serializable

WARNING

The TransactionIsolationLevel enum exposes all the isolation levels supported by the Prisma Dart client. However, not all databases support all isolation levels. For example, SQLite only supports Serializable isolation level.

Database-specific information on isolation levels

See the following resources:

• PostgreSQL
• MySQL
• SQL Server
• CockroachDB
• SQLite

Timeouts

When you use interactive transactions, in order to avoid long waiting times, you can set the transaction running time through the following two parameters:

• maxWait - The maximum time the client waits for a transaction from the database, default 2 seconds
• timeout - The maximum time an interactive transaction can run before being canceled or rolled back, default 5 seconds

await prisma.$transaction(
  (tx) async {
    // Code running in a transaction...
  },
  maxWait: 5000, // Default is 2000
  timeout: 10000, // Default is 5000
);

WARNING

You should use the timeout parameter with caution, keeping a transaction open for a long time can harm database performance and may even lead to deadlock. Try to avoid performing network requests and slow-performing queries within transaction functions. We recommend getting in and out as soon as possible!

Manual Transactions

Prisma Dart Client supports manual transactions, which means you can perform any number of operations within a transaction and then decide whether to commit or rollback the transaction.

final tx = await prisma.$transaction.start(); 
try {
  // Delete all posts
  await tx.post.deleteMany();
  // Delete all users
  await tx.user.deleteMany();

  // Commit the transaction
  await tx.$transaction.commit(); 
} catch (e) {
  // Rollback the transaction
  await tx.$transaction.rollback(); 
}

Using $transaction.start() will return a new PrismaClient instance. All operations within the transaction should be performed on this instance. When you decide to commit or rollback the transaction, call $transaction.commit() or $transaction.rollback().