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.
Transaction with Context
The Go adapter provides adapter.Transaction(ctx, fn) with a transaction function taking a standard *sql.Tx:
package main
import (
"context"
"database/sql"
"fmt"
"github.com/an5ORM/an5Adapters/golang"
)
func runTransaction(ctx context.Context, adapter *an5adapters.An5Adapter) error {
return adapter.Transaction(ctx, func(tx *sql.Tx) error {
// Execute queries within the active transaction
_, err := tx.ExecContext(ctx, "UPDATE accounts SET balance = balance - $1 WHERE id = $2", 100, "acc-1")
if err != nil {
return err // Returning an error automatically triggers ROLLBACK
}
_, err = tx.ExecContext(ctx, "UPDATE accounts SET balance = balance + $1 WHERE id = $2", 100, "acc-2")
if err != nil {
return err
}
return nil // Returning nil triggers COMMIT
})
}
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