Transactions

err := conn.Transaction(ctx, func(tx *db.DB) error {
    if _, err := tx.Update("accounts").Decrement("balance", 50).Where("id", 1).Exec(ctx); err != nil {
        return err // rolls back
    }
    _, err := tx.Update("accounts").Increment("balance", 50).Where("id", 2).Exec(ctx)
    return err // nil commits
})

Rules

Situation	Result
Callback returns nil	Commit
Callback returns an error	Rollback, the error is returned
Callback panics	Rollback, then the panic is re-raised
runtime.Goexit in the callback	Handled safely

• Use tx, not conn, inside the function. tx is bound to the transaction. Statements run on conn are outside it.
• A connection that cannot begin transactions returns db.ErrNoTransaction.

Nested transactions (savepoints)

A nested Transaction call uses a savepoint (savepoint sp_1 ... rollback to savepoint / release savepoint). An inner failure can be undone without aborting the outer transaction.

err := conn.Transaction(ctx, func(tx *db.DB) error {
    // ... main work ...

    if err := tx.Transaction(ctx, func(inner *db.DB) error {
        return optionalStep(inner) // failure rolls back to the savepoint only
    }); err != nil {
        log.Println("optional step skipped:", err)
    }
    return nil // outer commits
})

A db.New(sqlTx, dialect) built on an existing *sql.Tx also uses savepoints.

Isolation and read-only

err := conn.TransactionOpts(ctx,
    &sql.TxOptions{Isolation: sql.LevelSerializable, ReadOnly: true},
    func(tx *db.DB) error {
        // ...
        return nil
    },
)

Options are ignored for nested savepoints.

Locks

Row locks (LockForUpdate, SharedLock) belong inside a transaction:

err := conn.Transaction(ctx, func(tx *db.DB) error {
    var acc Account
    if err := tx.Select().From("accounts").Where("id", 1).LockForUpdate().First(ctx, &acc); err != nil {
        return err
    }
    // ... safe to modify acc's row ...
    return nil
})

SQLite ignores row locks. See Select Queries.

Tips

• Keep transactions short. Do not call remote services inside the callback.
• Pair UpdateOrInsert with a transaction and a unique index, or prefer Upsert.
• Multi-statement changes that must stay consistent belong in a Transaction. This is a data-integrity rule, see Security.