How do you manage database transactions correctly in Go, including error handling?

Question

Accepted Answer

func (r *UserRepository) CreateWithProfile(ctx context.Context, user *User, profile *Profile) error {
    tx, err := r.db.BeginTx(ctx, nil)
    if err != nil {
        return fmt.Errorf("begin tx: %w", err)
    }
    // Deferred rollback is a no-op after Commit — safe safety net
    defer tx.Rollback()

// Insert user
    _, err = tx.ExecContext(ctx,
        "INSERT INTO users (id, name, email) VALUES ($1, $2, $3)",
        user.ID, user.Name, user.Email,
    )
    if err != nil {
        return fmt.Errorf("insert user: %w", err) // rollback triggered by defer
    }

// Insert profile
    _, err = tx.ExecContext(ctx,
        "INSERT INTO profiles (user_id, bio) VALUES ($1, $2)",
        user.ID, profile.Bio,
    )
    if err != nil {
        return fmt.Errorf("insert profile: %w", err)
    }

// Commit — after this, defer Rollback is a no-op
    if err := tx.Commit(); err != nil {
        return fmt.Errorf("commit: %w", err)
    }
    return nil
}

// Unit of work pattern — pass tx as interface
type DBTX interface {
    ExecContext(context.Context, string, ...interface{}) (sql.Result, error)
    QueryContext(context.Context, string, ...interface{}) (*sql.Rows, error)
    QueryRowContext(context.Context, string, ...interface{}) *sql.Row
}

func (r *UserRepository) FindByID(ctx context.Context, db DBTX, id string) (*User, error) {
    // works with both *sql.DB and *sql.Tx
    row := db.QueryRowContext(ctx, "SELECT id, name FROM users WHERE id = $1", id)
    // ...
} Defer Rollback immediately after BeginTx — ensures rollback on any error path Pass tx as a DBTX interface to repository functions for unit-of-work reuse Keep transactions short — long-running transactions hold locks and cause contention Handle serialization failures (pgcode 40001) with retry logic for high-contention scenarios Use savepoints for nested transaction-like behavior within a single transaction

How do you manage database transactions correctly in Go, including error handling?

Begin a transaction, defer a rollback immediately, execute queries with error checking, and commit at the end. The deferred rollback is a no-op if Commit has already been called.

How do you manage database transactions correctly in Go, including error handling?

Begin a transaction, defer a rollback immediately, execute queries with error checking, and commit at the end. The deferred rollback is a no-op if Commit has already been called.

Scenario Questions

Follow-up Questions