Transactions
Wrap multiple operations in a database transaction. Use the ExecuteTransactionAsync unit-of-work scope, or manual WithTransaction() with isolation levels, savepoints, and the auto-rollback dispose pattern.
ExecuteTransactionAsync (recommended)
If you use the database context, the simplest way to run a transaction is ExecuteTransactionAsync. It opens a transaction, commits when the delegate returns, and rolls back if it throws. Every operation inside auto-enlists, and you never touch a connection or transaction object.
await db.ExecuteTransactionAsync(async d =>
{
await d.Orders.InsertAsync(order);
await d.LineItems.InsertAsync(item);
await d.Inventory.UpdateAsync(inventory);
// committed here; any exception above rolls the whole thing back
});A <TResult> overload returns a value, and both overloads accept an optional CancellationToken. Nested ExecuteTransactionAsync calls join the ambient transaction, and only the outermost commits. The scope also opens a parent TRANSACTION (postgresql) span so the commands nest under it in traces. See Diagnostics.
The rest of this page covers the lower-level manual API, which remains available when you manage connections yourself.
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. Inside an await using block, the transaction rolls back automatically when the scope exits unless CommitAsync() was already called. This removes 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 reads more concisely 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 pass the connection separately.
// Correct: the 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();WithConnection(conn) when a transaction is active on that connection does not enlist the operation in the transaction. The operation executes 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, so 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.