# Transactions

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

## 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:

```csharp
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:

```csharp
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.

```csharp
// 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:

```csharp
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:

```csharp
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.