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.

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

  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