Transactions

What it is

db.transaction((tx) async { ... }) borrows one connection, runs BEGIN, runs your callback, then COMMITs if it returns or ROLLBACKs if it throws. The tx it passes is a TxDbContext: every query you run on it is part of the transaction.

final post = await db.transaction((tx) async {
  final author = await authors().insert({'name': 'Ada', 'email': 'ada@example.com'}).one(tx);
  return posts().insert({'title': 'Hello', 'author_id': author.id}).one(tx);
});

When to use it

Several writes that must all happen or none: an order and its line items, a transfer between two accounts.
Read-then-write logic that must not race (with row locks via raw SELECT ... FOR UPDATE, or a stricter isolation level).
Consistent reads across several queries (REPEATABLE READ or SERIALIZABLE).

When not to use it

A single statement. Every statement is already atomic; a transaction only adds round trips.
Long-running work such as HTTP calls or file processing inside the callback. The transaction holds a connection and its locks the whole time. Do the slow work first, then open the transaction for the writes.
Session state such as advisory locks that must outlive one transaction. Use withConnection.

How it works

Use `tx`, not `db`

Inside the callback, run everything on tx. A query on db.context() borrows a different connection: it isn’t part of the transaction, can’t see its uncommitted rows, and can deadlock if it waits on a row the transaction has locked. If every pool connection is busy with transactions that wait on such queries, the pool itself can stall.

The cleanest way to get this right is to make data-access functions take a DbContext:

Future<Author> createAuthor(DbContext db, String name, String email) =>
    authors().insert({'name': name, 'email': email}).one(db);

await createAuthor(db.context(), 'Ada', 'ada@example.com');          // its own statement
await db.transaction((tx) => createAuthor(tx, 'Ada', 'ada@example.com')); // part of a transaction

Errors abort the transaction

When a statement fails, Postgres marks the transaction as aborted and rejects every later statement with current transaction is aborted (PostgresInvalidTransactionStateException, SQLSTATE 25P02). Catching the error and carrying on doesn’t work. Either let it propagate (rolling back everything), or isolate the risky part in a savepoint.

Savepoints

tx.savepoint((sp) async { ... }) runs the callback inside SAVEPOINT. If it throws, only its work is rolled back and the outer transaction stays usable:

await db.transaction((tx) async {
  await orders().insert({'customer_id': customerId, 'total': total}).run(tx);
  try {
    await tx.savepoint((sp) => loyalty().insert({'customer_id': customerId}).run(sp));
  } on PostgresUniqueViolationException {
    // Already a member; keep the order.
  }
});

Nested `db.transaction` calls

If code that runs inside a transaction calls db.transaction(...) again (same Database, same connection name), the inner call becomes a savepoint of the outer transaction rather than a second transaction. The inner work commits only if the outer transaction commits. This is tracked through Dart zones, so it works across function boundaries without passing tx down, although passing a DbContext explicitly is clearer.

Isolation levels

Pass TransactionSettings from package:postgres for REPEATABLE READ, SERIALIZABLE, or read-only transactions. Serializable transactions can fail with SQLSTATE 40001 under contention and should be retried; gisila_orm reports that code as PostgresUnknownException, so check sqlState. See Errors.

Example

A transfer that locks both rows, checks the balance and writes, retried on serialization failures:

import 'package:postgres/postgres.dart' show IsolationLevel, TransactionSettings;

Future<void> transfer(Database db, int from, int to, int amount) async {
  for (var attempt = 1;; attempt++) {
    try {
      await db.transaction(
        (tx) async {
          final rows = await tx.execute(
            'SELECT balance FROM accounts WHERE id = \$1 FOR UPDATE',
            parameters: [from],
          );
          final balance = rows.first.toColumnMap()['balance'] as int;
          if (balance < amount) throw StateError('insufficient funds');
          await tx.execute('UPDATE accounts SET balance = balance - \$1 WHERE id = \$2',
              parameters: [amount, from]);
          await tx.execute('UPDATE accounts SET balance = balance + \$1 WHERE id = \$2',
              parameters: [amount, to]);
        },
        settings: TransactionSettings(isolationLevel: IsolationLevel.serializable),
      );
      return;
    } on PostgresException catch (e) {
      if (e.sqlState != '40001' || attempt == 3) rethrow;
    }
  }
}