The problem with partial updates
You are building a payment system. You deduct ten dollars from Alice's account. The network blinks. You add ten dollars to Bob's account. The server crashes before the second write finishes. Alice lost ten dollars. Bob got nothing. The money vanished into the void.
This happens when operations are independent. The database sees two separate writes. If the second one fails, the first one stays. You need a way to group operations so they succeed or fail together. That is what a transaction does.
Transactions are atomic units of work
A transaction wraps multiple database queries into a single atomic unit. Atomic means indivisible. Either every query in the transaction commits and becomes permanent, or none of them do. If anything goes wrong, the database rolls back to the state before the transaction started.
Think of a transaction like a checklist for a flight crew. The pilot checks the fuel, the doors, and the instruments. If any check fails, the flight is aborted. The plane stays on the ground. You never end up with fuel loaded but doors open. The checklist ensures the plane is either fully ready or not moving at all.
In Go, database/sql provides *sql.Tx to manage this. You start a transaction, run queries on the transaction object, and then commit or roll back. The database driver handles the protocol with the server to ensure atomicity.
The minimal transaction pattern
Here is the skeleton of a transaction: begin, defer rollback, do work, commit.
func createUser(db *sql.DB, ctx context.Context, name string) error {
// BeginTx starts a transaction and borrows a connection from the pool
tx, err := db.BeginTx(ctx, nil)
if err != nil {
return err
}
// Defer rollback to handle panics or early returns; safe to call after commit
defer tx.Rollback()
// ExecContext runs the query within the transaction scope
_, err = tx.ExecContext(ctx, "INSERT INTO users (name) VALUES ($1)", name)
if err != nil {
return err
}
// Commit finalizes changes; returns error if the database rejects the commit
return tx.Commit()
}
The defer tx.Rollback() line is the safety net. If ExecContext returns an error, the function returns early. The deferred rollback runs and undoes the insert. If Commit succeeds, the deferred rollback runs afterward. Most drivers make Rollback a no-op after a successful commit, so calling it twice is harmless.
Convention aside: context.Context always goes as the first parameter, named ctx. Functions that accept a context should pass it to BeginTx and ExecContext. This allows the caller to cancel the transaction if a deadline is exceeded.
How the database and driver cooperate
When you call db.BeginTx, the *sql.DB pool hands you a single underlying connection. That connection is locked to the transaction until you commit or roll back. You cannot use that connection for other queries while the transaction is active.
All queries you run on tx go through that same connection. The database server groups them into a transaction session. When you call tx.Commit, the driver sends a COMMIT command. The server writes the changes to disk and releases the connection back to the pool.
If you call tx.Rollback, the driver sends ROLLBACK. The server discards the changes and releases the connection. The *sql.DB pool tracks this. If your program crashes without committing or rolling back, the pool detects the abandoned connection and cleans it up, but it is better to manage the lifecycle explicitly.
The defer pattern works because Go guarantees deferred functions run when the surrounding function returns, regardless of how it returns. This covers normal returns, error returns, and panics. You do not need to sprinkle rollback calls at every exit point.
A realistic transfer function
Real code often needs to read data, check constraints, and update multiple rows. Here is a transfer function that moves money between accounts atomically.
func transferFunds(db *sql.DB, ctx context.Context, from, to int, amount float64) error {
tx, err := db.BeginTx(ctx, nil)
if err != nil {
return err
}
defer tx.Rollback()
// FOR UPDATE locks the row to prevent concurrent modifications
var balance float64
err = tx.QueryRowContext(ctx, "SELECT balance FROM accounts WHERE id = $1 FOR UPDATE", from).Scan(&balance)
if err != nil {
return err
}
// Check balance after acquiring the lock to avoid race conditions
if balance < amount {
return fmt.Errorf("insufficient funds")
}
// Deduct using arithmetic to avoid race on read-modify-write
_, err = tx.ExecContext(ctx, "UPDATE accounts SET balance = balance - $1 WHERE id = $2", amount, from)
if err != nil {
return err
}
// Add to receiver within the same transaction
_, err = tx.ExecContext(ctx, "UPDATE accounts SET balance = balance + $1 WHERE id = $2", amount, to)
if err != nil {
return err
}
return tx.Commit()
}
The FOR UPDATE clause locks the sender's row. Other transactions trying to read or write that row will wait until this transaction finishes. This prevents two transfers from reading the same balance simultaneously and both thinking there is enough money.
The UPDATE statements use balance - $1 instead of setting a calculated value. This lets the database handle the arithmetic atomically. Even if you read the balance earlier, the database ensures the update applies to the current value.
Convention aside: if err != nil { return err } is verbose by design. The community accepts the boilerplate because it makes the unhappy path visible. You cannot miss an error when it is on its own line.
Pitfalls, errors, and conventions
Transactions introduce specific failure modes. Knowing them prevents subtle bugs.
Using db instead of tx inside a transaction block compiles but breaks atomicity. The compiler does not stop you because *sql.DB and *sql.Tx both have ExecContext. If you call db.ExecContext while a transaction is open, the query runs outside the transaction. It commits immediately. The transaction later commits its own queries, but the stray query is already done. You end up with partial updates. Always use the tx variable for queries that belong to the transaction.
Passing the wrong type for transaction options triggers a compile error. If you try to pass a string for isolation level, the compiler rejects it with cannot use "serializable" as *sql.TxOptions value in argument. You must use &sql.TxOptions{Isolation: sql.LevelSerializable}.
Calling Commit twice returns a runtime error. The driver detects the state and returns sql: Transaction.Commit: transaction already committed. This usually happens if you commit, then a deferred rollback runs and somehow triggers a commit path, or if you restructure code and accidentally call commit in multiple branches. The defer tx.Rollback() pattern avoids this because rollback is safe after commit.
Ignoring the error from Commit is dangerous. Commit can fail if the database rejects the changes due to a constraint violation that was only checked at commit time, or if the connection drops. If you ignore the error, your code thinks the transaction succeeded when the database rolled it back. Always return the error from Commit.
Transactions hold connections. A long-running transaction ties up a connection from the pool. If your pool is small, other requests block waiting for a connection. Keep transactions short. Do not do heavy computation or network calls inside a transaction block. Fetch data, process it, then start the transaction for the writes.
Convention aside: Receiver names are usually one or two letters matching the type. If you wrap a transaction in a struct, use (t *TxWrapper) not (this *TxWrapper). The Go community prefers short, predictable names.
When to use transactions
Pick the right tool based on your data consistency needs.
Use db.BeginTx when you have multiple writes that must succeed or fail together. Use db.BeginTx when you need to read and update data with row-level locking to prevent races. Use db.BeginTx when you want to batch queries to reduce round-trips within a single connection.
Use db.Exec directly when a single query is sufficient and isolation does not matter. Use db.Exec when you are performing read-only operations that do not require consistency across multiple rows. Use db.Exec when you want the database connection to return to the pool immediately after the query.
Use pgx or a driver-specific API when you need advanced features like savepoints, advisory locks, or specific isolation levels not exposed by database/sql. Use pgx when you require higher performance and direct access to PostgreSQL extensions.
Use a single query with UPDATE ... RETURNING when the database can handle the logic in one statement. Use stored procedures when the transaction logic is complex and belongs in the database layer.
Transactions are all or nothing. If you skip the rollback, you leak connections. Check the error from Commit. A silent failure means your data is gone.