Right, let’s talk transactions. This is where we stop playing in the sandbox and start building castles, with all the attendant risk of them collapsing into a pile of mud. A transaction bundles multiple SQL operations into a single, all-or-nothing unit of work. It’s the database’s way of saying, “I promise I will do all of this, or absolutely none of it. There is no in-between.”

Think of it like ordering a burger and fries. You don’t want a system where the kitchen charges you for the burger, makes it, but then burns the fries and just… doesn’t give you any. That’s a failed state. The transaction is you placing the entire order. The restaurant either commits to making both items successfully, or they roll the whole thing back and you get nothing (and hopefully your money back). The atomicity—the “all-or-nothing” property—is the entire point.

The Basic Flow: Begin, Commit, Rollback

In database/sql, you don’t just start executing queries willy-nilly. You have to explicitly begin a transaction to get a Tx object. This object is your handle for everything transaction-related; it’s your way of saying “Hey, everything I do from now on is part of this one unit.”

db, err := sql.Open("mysql", dataSourceName)
if err != nil {
    log.Fatal(err)
}
// Deferring db.Close() is good practice, but let's assume it's here.

// Begin the transaction
tx, err := db.Begin()
if err != nil {
    log.Fatal(err)
}

// Now use 'tx' for all subsequent operations, not 'db'
_, err = tx.Exec("UPDATE accounts SET balance = balance - 100 WHERE user_id = 1")
if err != nil {
    // If something goes wrong, we roll back the entire transaction
    tx.Rollback()
    log.Fatal(err)
}

_, err = tx.Exec("UPDATE accounts SET balance = balance + 100 WHERE user_id = 2")
if err != nil {
    tx.Rollback()
    log.Fatal(err)
}

// If we got here, both updates succeeded. Let's make it permanent.
err = tx.Commit()
if err != nil {
    log.Fatal(err)
}

Notice the pattern? The moment you get a tx object, you’re on the hook. You must call either Commit() or Rollback() before releasing the connection back to the pool. If you don’t, you’re leaking resources and holding onto a database connection until the garbage collector eventually figures it out. This is a classic beginner mistake that can tank your application’s performance under load.

The Pit of Despair: Forgetting to Rollback

This is so important it needs its own subheading. The code above is correct but verbose. A missed error path means a leaked transaction. The idiomatic, safer way to handle this in Go is to defer the rollback immediately after beginning the transaction.

tx, err := db.Begin()
if err != nil {
    log.Fatal(err)
}
// This is your safety net. Defer this RIGHT AFTER you check the Begin error.
defer tx.Rollback()

// ... do your various Execs and Querys ...

// If Commit() is successful, it will make the changes permanent.
// More importantly, if Commit() is successful, it makes the subsequent Rollback() a no-op.
// This is a key detail! It's safe to call Rollback after a successful Commit.
err = tx.Commit()
if err != nil {
    log.Fatal(err)
}
// The deferred tx.Rollback() runs now, but since we Committed, it does nothing.

Why is this brilliant? It ensures that no matter how many error paths you have or how complex your function gets—whether you return early or panic()—the transaction will always be rolled back if you don’t explicitly commit it. It cleanly handles the failure cases without repetitive code. This is a non-negotiable best practice.

Isolation Levels and Context

Transactions have something called isolation levels (Read Uncommitted, Read Committed, Repeatable Read, Serializable) which control how your transaction views changes made by other concurrent transactions. This is a deep, fascinating, and horrifying topic about locking and consistency. The database/sql API, in its infinite wisdom, provides a way to set this via db.BeginTx(), which takes a context and options.

// For operations that should be timeout-able, or to set an isolation level
ctx := context.Background()
opts := &sql.TxOptions{
    Isolation: sql.LevelRepeatableRead,
    ReadOnly:  false,
}
tx, err := db.BeginTx(ctx, opts)
if err != nil {
    log.Fatal(err)
}
defer tx.Rollback()

// ... use tx with context-aware methods ...
_, err = tx.ExecContext(ctx, "UPDATE ...")

A word of caution: isolation levels are not always implemented consistently across different database drivers. You can’t just set LevelSerializable on a MySQL driver and expect the same behavior as on a Postgres driver. You have to read your specific database’s documentation. It’s one of the rough edges of the abstraction.

The Curious Case of Deferred Constraints

Here’s a fun one that trips up everyone coming from other ORMs. Some databases, like SQLite and PostgreSQL, support DEFERRABLE constraints. This means you can violate a foreign key or uniqueness constraint during the transaction, as long as you fix it by the time you commit. This is incredibly useful for updating related tables in any order.

However, the database/sql package itself is blissfully unaware of this database-specific feature. It doesn’t magically make your constraints deferrable. You have to execute a command inside the transaction to tell the database to defer constraint checks.

tx, err := db.Begin()
defer tx.Rollback()

// For PostgreSQL, you must explicitly defer constraint checking
_, err = tx.Exec("SET CONSTRAINTS ALL DEFERRED")
if err != nil {
    log.Fatal(err)
}

// Now you can run your INSERTs and UPDATEs in any order, even if they
// temporarily violate constraints, as long as it's all consistent at commit.
err = tx.Commit()

If you don’t do this and you try to update tables in a “wrong” order, your transaction will fail mid-way with a constraint violation, even though your final state would have been perfectly valid. It’s a sharp edge, but it’s not the library’s fault—it’s just giving you raw access to the power of the underlying database. You have to know how to use it.