Transactions
an5 ORM supports transactions to ensure data consistency when performing multiple related operations.
Sqlserver note:
SQL Server supports full ACID transactions. Nested transactions use SAVE TRANSACTION savepoints.
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
- Keep transactions short - Minimize the time table rows are locked to avoid blocking concurrent queries.
- Handle errors properly - Always handle potential deadlock or timeout exceptions in application code.
- Consistent ordering - Always update tables and records in a deterministic order across all endpoints to prevent database deadlocks.
- Choose the appropriate isolation level - Tailor the transaction isolation according to concurrency and consistency requirements.
Next Steps
- Advanced Queries - Filter, sort, and paginate data
- Relations & Joins - Model relational data integrity