Transactions, Prepared Statements & sql.Tx

Transactions group multiple statements into one atomic unit.

Prepared statements reuse parsed SQL for repeated parameters.

sql.Tx ties both together: begin with context, execute inside the boundary, then commit or roll back.

Summary

BeginTx(ctx, opts) starts a transaction with optional isolation level.

Commit persists changes; Rollback discards them - call one path after errors.

PrepareContext creates a Stmt; Tx.Stmt reuses it inside a transaction.

Defer Rollback after BeginTx so error paths do not leak open transactions.

Recipe

Quick-reference recipe card - copy-paste ready.

func Transfer(ctx context.Context, db *sql.DB, from, to int64, amount int) error {
    tx, err := db.BeginTx(ctx, &sql.TxOptions{Isolation: sql.LevelReadCommitted})
    if err != nil {
        return err
    }
    defer tx.Rollback()
 
    if _, err := tx.ExecContext(ctx,
        `UPDATE accounts SET balance = balance - $1 WHERE id = $2`, amount, from,
    ); err != nil {
        return err
    }
    if _, err := tx.ExecContext(ctx,
        `UPDATE accounts SET balance = balance + $1 WHERE id = $2`, amount, to,
    ); err != nil {
        return err
    }
    return tx.Commit()
}

When to reach for this:

• Multi-step writes that must succeed or fail together.
• Repeated inserts or lookups with the same SQL shape in a hot path.
• Serializable isolation when race conditions cannot be solved in application logic alone.

Working Example

package main
 
import (
    "context"
    "database/sql"
    "fmt"
 
    _ "github.com/mattn/go-sqlite3"
)
 
func main() {
    db, _ := sql.Open("sqlite3", ":memory:")
    defer db.Close()
    ctx := context.Background()
 
    _, _ = db.ExecContext(ctx, `CREATE TABLE ledger (
        id INTEGER PRIMARY KEY,
        balance INTEGER NOT NULL
    )`)
    _, _ = db.ExecContext(ctx, `INSERT INTO ledger (id, balance) VALUES (1, 100), (2, 0)`)
 
    stmt, err := db.PrepareContext(ctx, `UPDATE ledger SET balance = balance + ? WHERE id = ?`)
    if err != nil {
        panic(err)
    }
    defer stmt.Close()
 
    tx, err := db.BeginTx(ctx, nil)
    if err != nil {
        panic(err)
    }
    defer tx.Rollback()
 
    txStmt := tx.Stmt(stmt)
    if _, err := txStmt.ExecContext(ctx, -25, 1); err != nil {
        panic(err)
    }
    if _, err := txStmt.ExecContext(ctx, 25, 2); err != nil {
        panic(err)
    }
    fmt.Println(tx.Commit())
}

What this demonstrates:

• PrepareContext once, Tx.Stmt to bind the stmt to the active transaction.
• defer tx.Rollback() covers panic and error exits; Commit succeeds after work completes.
• Same ctx flows through begin, exec, and commit for cancellation.
• SQLite illustrates the pattern; Postgres and MySQL use the same database/sql API.

Deep Dive

How It Works

• BeginTx checks out a dedicated connection until commit or rollback.
• Statements on tx run on that same connection, preserving isolation guarantees.
• Stmt objects may cache server-side prepared plans depending on driver settings.
• After Commit, the transaction object is invalid; start a new BeginTx for more work.

Isolation Levels

Rollback Discipline

tx, err := db.BeginTx(ctx, nil)
if err != nil {
    return err
}
defer func() {
    _ = tx.Rollback() // no-op after successful Commit
}()

• Rollback after Commit returns sql.ErrTxDone - safe to ignore in defer.
• Return domain errors before commit; map sql.ErrTxDone only if you double-commit by mistake.
• Long transactions hold row locks - keep business logic outside the tx when possible.

Savepoints (driver-specific)

Some teams use raw SAVEPOINT SQL inside a tx for partial rollback.

Prefer fewer, shorter transactions over deep savepoint nesting unless the database team documents patterns.

Gotchas

• Forgot Rollback on error path - Connection stays in aborted state and leaks from pool. Fix: defer tx.Rollback() immediately after BeginTx.
• Using db.Exec inside an open tx for related writes - Statements run on different connections and break atomicity. Fix: only tx.ExecContext for transactional work.
• Prepared stmt from wrong tx - Executing stmt.Exec while a tx is open bypasses the transaction connection. Fix: tx.Stmt(stmt) or prepare on tx directly.
• Huge transaction batch jobs - One tx locks tables for minutes. Fix: chunk commits with idempotent offsets or staging tables.
• Serializable without retry - Conflicts surface as errors under load. Fix: retry on serialization failure with backoff.
• Closing Stmt while tx still running - Undefined behavior on some drivers. Fix: close stmts after tx completes or use short-lived prepare per request.

Alternatives

FAQs

Related

• Databases Basics - BeginTx starter example
• database/sql Connection Pooling & Timeouts - txs hold connections
• Migrations with golang-migrate & goose - schema change transactions
• GORM vs sqlx vs Raw database/sql - ORM transaction helpers
• Databases & Data Access Best Practices - tx length and rollback rules

Stack versions: This page was written for Go 1.26.x (Green Tea GC default, go fix modernizers - verify patch at build), chi (latest - verify at build), gin (latest - verify at build), echo (latest - verify at build), google.golang.org/grpc (latest - verify at build), sigs.k8s.io/controller-runtime (latest - verify at build), kubebuilder (latest - verify at build), tinygo (latest - verify board targets at build), wazero (latest - verify at build), and golangci-lint (latest - verify linter set at build).