The scratchpad that saves your data
You are processing a payment. You deduct ten dollars from Alice's account. The database confirms the write. You try to add ten dollars to Bob's account. The connection drops. Alice is now nine dollars poorer, Bob has nothing, and your ledger is broken. A transaction groups these operations so they either both happen or neither happens. The database guarantees atomicity: the world sees the full change or no change at all.
Transactions are isolated scratchpads
Think of a transaction as a scratchpad. You write down every change you want to make on the scratchpad, but the database ignores the scratchpad until you hand it over and say "make it official." If you spill coffee on the scratchpad before handing it over, you can throw it away, and the database remains exactly as it was.
The scratchpad is isolated. Other users can't see your half-finished notes until you commit. This isolation prevents race conditions where one user reads data that is partially updated by another user. The database manages the isolation level, ensuring that concurrent transactions don't corrupt each other's work.
The minimal transaction pattern
Here is the canonical transaction pattern. Start the transaction, defer the rollback, run queries, and commit at the end.
// BeginTx starts a transaction. The context controls cancellation.
tx, err := db.BeginTx(ctx, nil)
if err != nil {
return err
}
// Defer rollback to handle panics or early returns.
// If Commit succeeds, Rollback becomes a no-op.
defer tx.Rollback()
// ExecContext runs a query within the transaction.
_, err = tx.ExecContext(ctx, "INSERT INTO users (name) VALUES (?)", "Alice")
if err != nil {
return err
}
// Commit finalizes the transaction.
// If this fails, the deferred Rollback cleans up.
return tx.Commit()
Defer rollback. Commit last. Never reuse a transaction.
How the defer safety net works
The defer tx.Rollback() line is the safety net. Go runs deferred functions when the surrounding function returns. If ExecContext returns an error, the function returns early, and the rollback runs. If Commit succeeds, the function returns, and the rollback runs too.
Calling Rollback on an already committed transaction is safe. The driver ignores the call and returns no error. This pattern means you never leak a transaction. You either commit, or you rollback. There is no middle ground.
The context.Context parameter is the first argument to BeginTx. This follows the Go convention: context always goes first, conventionally named ctx. The context allows the transaction to respect cancellation and deadlines. If the client disconnects or the request times out, the context cancels, and subsequent queries fail. The deferred rollback ensures the transaction closes even if the context cancellation triggers a panic.
Context is plumbing. Run it through every long-lived call site.
Realistic example: transferring money
Real code usually involves multiple steps. Here is a money transfer that checks balances, updates two rows, and logs the event.
// TransferMoney moves funds between accounts atomically.
func TransferMoney(db *sql.DB, ctx context.Context, from, to int, amount float64) error {
// BeginTx isolates the transfer from other operations.
tx, err := db.BeginTx(ctx, nil)
if err != nil {
return err
}
// Ensure cleanup on exit.
defer tx.Rollback()
// QueryRowContext reads the current balance.
var balance float64
err = tx.QueryRowContext(ctx, "SELECT balance FROM accounts WHERE id = ?", from).Scan(&balance)
if err != nil {
return err
}
// Validate business logic inside the transaction.
if balance < amount {
return fmt.Errorf("insufficient funds")
}
// Update both accounts using the transaction handle.
_, err = tx.ExecContext(ctx, "UPDATE accounts SET balance = balance - ? WHERE id = ?", amount, from)
if err != nil {
return err
}
_, err = tx.ExecContext(ctx, "UPDATE accounts SET balance = balance + ? WHERE id = ?", amount, to)
if err != nil {
return err
}
// Commit writes all changes to the database.
return tx.Commit()
}
The function takes *sql.DB, not *sql.Tx. The database handle manages the connection pool. You start a transaction from the pool, use it, and return the connection when the transaction ends. Passing *sql.DB keeps the API flexible. The caller provides the pool, and the function handles the transaction lifecycle.
The receiver name convention applies if this were a method. If TransferMoney were a method on a Service struct, the receiver would be (s *Service), not (this *Service) or (self *Service). One or two letters matching the type is the standard.
Pitfalls and compiler errors
The compiler catches type mismatches. If you pass db where a *sql.Tx is expected, you get cannot use db (variable of type *sql.DB) as *sql.Tx value in argument. This saves you from accidentally running a query outside the transaction.
Using db.ExecContext instead of tx.ExecContext compiles fine, but the query runs outside the transaction. The database sees the write immediately, breaking atomicity. The compiler cannot detect this logic error. You must review the code to ensure every query uses the transaction handle.
Reusing a transaction after commit causes a runtime panic or error. If you call tx.ExecContext after tx.Commit, the driver returns sql: transaction has already been committed or rolled back. The transaction object is dead after commit or rollback. Create a new transaction for the next batch of operations.
Goroutine leaks happen when the goroutine waits on a channel that never gets closed. Transactions don't use channels, but they do use connections. If you start a transaction and forget to commit or rollback, the connection stays locked. The connection pool exhausts, and the application hangs. The defer tx.Rollback() pattern prevents this leak. Always have a cancellation path.
The worst goroutine bug is the one that never logs. The worst transaction bug is the one that never rolls back.
Configuring isolation and read-only mode
BeginTx accepts sql.TxOptions to configure isolation levels and read-only behavior. Most of the time, nil is sufficient. The database uses its default isolation level, which is usually ReadCommitted or RepeatableRead.
// TxOptions configures isolation and read-only behavior.
opts := &sql.TxOptions{
Isolation: sql.LevelSerializable,
ReadOnly: false,
}
tx, err := db.BeginTx(ctx, opts)
if err != nil {
return err
}
Setting ReadOnly: true tells the database this transaction only reads data. Some databases optimize read-only transactions by skipping write locks or using snapshot isolation. This can improve performance for reporting queries. Use ReadOnly when you have a batch of reads that must see a consistent snapshot but never modify data.
Isolation levels control how transactions interact. LevelSerializable is the strictest level. It prevents phantom reads and ensures that concurrent transactions behave as if they ran one after another. This level can cause contention and deadlocks. Use it only when you need strict consistency. LevelDefault lets the database choose the best level for the workload.
ReadOnly transactions are faster. Use them for reports.
Error handling nuances
tx.Commit() can fail. A network error might occur after the database commits but before the acknowledgment reaches the driver. In this case, the transaction is committed, but Commit returns an error. You cannot rollback a committed transaction.
The driver usually distinguishes between commit errors that indicate failure and errors that indicate ambiguity. If Commit returns an error, check the error type. If the error is sql.ErrTxDone, the transaction is already done. If the error is a network error, the state might be ambiguous. Log the error and investigate. For most applications, returning the error to the caller is sufficient. The caller can retry the operation or alert the operator.
The if err != nil { return err } pattern is verbose by design. The community accepts the boilerplate because it makes the unhappy path visible. Every error is handled explicitly. There are no hidden failures.
When to use transactions
Use a transaction when you have dependent writes that must remain consistent. Use a single db.ExecContext call when the operation is a standalone write with no side effects. Use tx.QueryContext when you need to read data modified earlier in the same transaction. Use plain db.QueryContext for read-only operations where stale data is acceptable. Use sql.TxOptions with ReadOnly: true when you need a consistent snapshot for reporting without write locks. Use sql.LevelSerializable only when strict isolation is required and contention is low.
Trust the defer. The compiler enforces the type, but you enforce the logic.