/DB

Transactions

Wrap multiple operations in a database transaction using WithTransaction() — isolation levels, savepoints, and the auto-rollback dispose pattern.

updated 3 May 20263 min readv0.1.82

Basic transaction

Start a transaction from an open connection, attach it to every builder with WithTransaction(), then commit on success or roll back on failure:

await using var conn = new NpgsqlConnection(connectionString);
await conn.OpenAsync();
await using var tx = await conn.BeginTransactionAsync();

try
{
    await order.Insert().WithTransaction(tx).ExcludeAutoFields().ExecuteAsync();
    await lineItem.Insert().WithTransaction(tx).ExcludeAutoFields().ExecuteAsync();
    await inventory.Update().WithTransaction(tx).ExecuteAsync();

    await tx.CommitAsync();
}
catch
{
    await tx.RollbackAsync();
    throw;
}

All operations share the same NpgsqlTransaction. Either all succeed (commit) or all are undone (rollback).

Auto-rollback with await using

NpgsqlTransaction implements IAsyncDisposable. When used in an await using block, the transaction is automatically rolled back when the scope exits unless CommitAsync() was already called. This eliminates the need for an explicit catch/rollback in many cases:

await using var conn = new NpgsqlConnection(connectionString);
await conn.OpenAsync();

await using (var tx = await conn.BeginTransactionAsync())
{
    await record.Insert().WithTransaction(tx).ExcludeAutoFields().ExecuteAsync();
    await audit.Insert().WithTransaction(tx).ExcludeAutoFields().ExecuteAsync();

    await tx.CommitAsync();
    // if CommitAsync is never reached (e.g. an exception above), dispose rolls back
}

This pattern is equivalent to the try/catch/rollback approach but is more concise when you want the exception to propagate naturally.

WithConnection vs WithTransaction

Every command builder accepts either WithConnection() or WithTransaction() — never both.

When you call WithTransaction(tx), the builder uses the connection that the transaction is already bound to. You do not need to pass the connection separately.

// Correct — transaction carries the connection implicitly
await user.Insert().WithTransaction(tx).ExcludeAutoFields().ExecuteAsync();

// Also correct — no transaction, pass the connection directly
await user.Insert().WithConnection(conn).ExcludeAutoFields().ExecuteAsync();
WARNING
Calling WithConnection(conn) when a transaction is active on that connection does not enlist the operation in the transaction. The operation will execute outside the transaction boundary and cannot be rolled back with it. Always use WithTransaction(tx) when atomicity is required.

Isolation levels

Pass an IsolationLevel to BeginTransactionAsync() to control PostgreSQL's concurrency behavior:

using System.Data;

await using var tx = await conn.BeginTransactionAsync(IsolationLevel.RepeatableRead);

PostgreSQL supports the following isolation levels:

Level PostgreSQL behavior
ReadCommitted Default. Each statement sees only committed rows at statement start.
RepeatableRead All statements in the transaction see the same snapshot of committed data.
Serializable Full serializability — transactions appear to execute one at a time.

ReadUncommitted is accepted by the API but PostgreSQL treats it as ReadCommitted.

Savepoints

PostgreSQL supports savepoints for partial rollbacks within a transaction. Use Npgsql's savepoint API directly — no extra abstractions are needed:

await using var tx = await conn.BeginTransactionAsync();

// Checkpoint before a risky bulk operation
await tx.SaveAsync("before_bulk");

try
{
    foreach (var item in items)
        await item.Insert().WithTransaction(tx).ExcludeAutoFields().ExecuteAsync();
}
catch
{
    // Roll back only to the savepoint, not the entire transaction
    await tx.RollbackAsync("before_bulk");
    // The transaction is still open — you can continue other work
}

await tx.CommitAsync();

Savepoints are useful when you want to attempt a batch operation optimistically and recover gracefully without aborting the surrounding transaction.

Thread safety

A single NpgsqlConnection (and its associated transaction) must not be used concurrently from multiple threads. Run one operation at a time per connection. For parallel workloads, open separate connections — each with its own transaction if needed.