sqltransaction

sqltransaction runs a function inside a database/sql transaction, committing on success and rolling back on failure with guarded rollback error handling.

-
Updated:

Table of contents

Part of nurago, a collection of independent Go packages for backend services.

import "github.com/tecnickcom/nurago/pkg/sqltransaction"

Runs your function inside a transaction. Return nil and it commits; return an error and it rolls back.

err := sqltransaction.Exec(ctx, db, func(ctx context.Context, tx *sql.Tx) error {
    if _, err := tx.ExecContext(ctx, q1); err != nil {
        return err
    }

    _, err := tx.ExecContext(ctx, q2)

    return err
})

The control flow it removes

Written by hand, a transaction is this:

tx, err := db.BeginTx(ctx, nil)
if err != nil {
    return err
}

if _, err := tx.ExecContext(ctx, q1); err != nil {
    _ = tx.Rollback()
    return err
}

if _, err := tx.ExecContext(ctx, q2); err != nil {
    _ = tx.Rollback()
    return err
}

return tx.Commit()

Every early return needs its own rollback, and adding a statement in the middle means remembering to add one more. A missed rollback holds the transaction open until the connection is reclaimed, taking its locks with it, which surfaces as unrelated queries timing out.

Inverting the control flow removes the possibility. There is one exit path, and the rollback is attached to it.

Rollback error handling is guarded

Three rules, and each exists because the naive version produces misleading errors:

Rollback is skipped after a successful commit. A deferred rollback that always fires returns sql.ErrTxDone on every successful transaction.

sql.ErrTxDone during rollback is ignored. The transaction was already finished, so there is nothing wrong to report.

A genuine rollback failure is joined with the current error, through errors.Join, rather than replacing it. You get both the reason the work failed and the fact that the rollback also failed, which usually indicates a connection problem.

Replacing the original error is the common bug here, and it is the worse one: you lose the actual cause and keep only the cleanup failure.

Isolation levels

ExecWithOptions takes *sql.TxOptions for the isolation level and read-only flag.

The default from Exec is the driver’s default, which for MySQL is REPEATABLE READ and for PostgreSQL is READ COMMITTED. Those differ in behaviour, so a service targeting both should be explicit rather than inheriting.

ReadOnly: true is worth using for read transactions. It lets the database optimise and prevents an accidental write.

Interface, not *sql.DB

Exec takes a DB interface with BeginTx, so a mock substitutes in tests without a database. The sqlxtransaction variant does the same for sqlx.

Nesting

database/sql has no nested transactions, and neither does this. Calling Exec inside an ExecFunc starts a second, independent transaction on a different connection, which will deadlock against the first if they touch the same rows.

Pass the *sql.Tx down instead of starting a new transaction deeper in the call stack.

Dependencies

This package reaches no external module: it uses only the Go standard library.