Transactions

an5 ORM supports transactions to ensure data consistency when performing multiple related operations.

Postgresql note: PostgreSQL uses standard ACID transactions with BEGIN, COMMIT, and ROLLBACK. Nested transactions leverage SAVEPOINT.

Scoped Transaction in C#

The .NET adapter provides adapter.Transaction<T>() to execute atomic blocks with automatic commit and rollback:

adapter.Transaction<int>(tx =>
{
    var userTable = adapter.Table<User>("User");
    var orderTable = adapter.Table<Order>("Order");

    userTable.Create(new User {
        Id = Guid.NewGuid().ToString(),
        Email = "dotnet-dev@example.com",
        Name = "Dev"
    });

    orderTable.Create(new Order {
        Id = Guid.NewGuid().ToString(),
        Total = 250
    });

    return 0; // Commits on normal exit
});

Error Handling and Rollback

If an unhandled exception occurs inside the delegate, the transaction aborts and changes are reverted:

try
{
    adapter.Transaction<int>(tx =>
    {
        userTable.Create(new User { Email = "fail@example.com" });
        throw new InvalidOperationException("Abort operation");
    });
}
catch (InvalidOperationException ex)
{
    Console.WriteLine($"Transaction rolled back: {ex.Message}");
}

Best Practices

  1. Keep transactions short - Minimize the time table rows are locked to avoid blocking concurrent queries.
  2. Handle errors properly - Always handle potential deadlock or timeout exceptions in application code.
  3. Consistent ordering - Always update tables and records in a deterministic order across all endpoints to prevent database deadlocks.
  4. Choose the appropriate isolation level - Tailor the transaction isolation according to concurrency and consistency requirements.

Next Steps