An order that decrements stock without recording the order leaves the catalog inconsistent. Transactions are how Go programs make sure that pair of writes is all-or-nothing. This chapter covers db.Begin, the *sql.Tx value it returns, the defer-Rollback idiom that keeps transactions honest, isolation levels via BeginTx, and the common bugs that quietly bypass the whole mechanism.

A transaction is a group of SQL statements that the database treats as a single unit. Either all of them take effect, or none of them do. The four guarantees usually go by the acronym ACID:

The E-Commerce case is the classic one. Placing an order needs two writes: insert a row into orders, then decrement products.stock. If only the first succeeds, the customer paid for an order with no stock reservation. If only the second succeeds, the inventory shrinks but no one knows why. Wrapping both in a transaction means either both rows change or neither does.

Two tx.Exec calls, one tx.Commit, and the stock drops from 10 to 8. If either Exec had returned a non-nil error, the deferred Rollback would have erased the partial work before any other connection saw it. The next section explains why that defer line is the most important one in the function.

The transaction API on *sql.DB is small. You call db.Begin() to start one, and you get back an *sql.Tx. That *sql.Tx value has the same query methods as *sql.DB: Query, QueryRow, Exec, and Prepare. The difference is that anything you run on tx happens inside the transaction. Anything you run on the original db does not.

To finish the transaction, you call exactly one of two methods:

Both methods release the underlying connection back to the pool. After either one returns, the *sql.Tx is finished. Calling Commit or Rollback on an already-finished transaction returns sql.ErrTxDone. That sentinel error is what makes the defer idiom in the next section safe.

sql.ErrTxDone is the same value whether the first call was Commit or Rollback. The database/sql package uses it as a single signal for "this transaction is no longer active." Treat it as a benign indicator that someone (often a deferred Rollback) tried to finalize a transaction that was already finalized.

You must call Commit or Rollback on every transaction you start. Forgetting both leaks a connection from the pool, because the underlying driver keeps holding it open, waiting for the application to make up its mind. On SQLite this also blocks every other writer for the lifetime of the leaked transaction. The defer idiom in the next section exists to make this impossible to forget.

The thing that makes transaction code in Go feel sane is one line, placed immediately after the call to Begin:

Read that carefully. The defer tx.Rollback() schedules a rollback to run when the function returns, no matter how it returns. If any of the tx.Exec calls in the middle fail and the function returns early, the deferred rollback fires and undoes the partial work. If the function reaches tx.Commit() and commit succeeds, the deferred rollback still fires on the way out, but now the transaction is already done, so Rollback returns sql.ErrTxDone and does nothing. The database/sql package is deliberately designed so this is a no-op rather than an error you have to handle.

The result is that you only have to think about the happy path. Every error path is covered by the defer.

Two calls to recordPurchase. The first commits cleanly, dropping stock from 10 to 8. The second returns the "insufficient stock" error before reaching tx.Commit, so the deferred Rollback undoes the already-inserted order row. The final stock is 8, not -91, and there is no orphaned order. The defer did the recovery without any explicit branch in the function.

Without the defer, you would need to call tx.Rollback() at every error return, and miss one and you have a leaked transaction. With the defer, the rollback is unconditional and the commit either wins or doesn't.

The flow looks like this:

The diagram shows two terminal paths: success returns nil and the deferred Rollback finds an already-committed transaction (no-op), while failure returns an error and the deferred Rollback actually undoes the work. Both paths produce a consistent database.

Every *sql.Tx moves through a tiny state machine. It starts in an active state after Begin, and finishes in exactly one of two terminal states.

Active is the only state where tx.Exec, tx.Query, and friends do anything useful. Once the transaction moves to Committed or RolledBack, any further call on that *sql.Tx returns sql.ErrTxDone. The state machine is one-shot. You can't restart a transaction or undo a commit. To do more work, you call db.Begin again and get a fresh *sql.Tx.

Three things can move a transaction from Active to RolledBack:

db.Begin() is shorthand for the common case. The full version is db.BeginTx(ctx, opts), and it gives you two things Begin does not.

First, a context.Context that the transaction respects. If the context is canceled or its deadline expires while the transaction is still running, the driver aborts the transaction. This matters in HTTP handlers: if the client disconnects, the request context fires Done, and your transaction stops doing work instead of running to completion with no one to send the result to.

Second, a *sql.TxOptions value that lets you set the isolation level and mark the transaction read-only. Read-only mode lets the database optimize (or at least refuse writes early) and isolation levels control what concurrent transactions can see of each other.

The context goes into both BeginTx (which scopes the transaction itself) and QueryRowContext (which scopes the individual statement). If you pass the request context into BeginTx and the client disconnects, both the transaction and any in-flight statements are aborted together. Always use the Context variants on *sql.Tx once you've opened the transaction with BeginTx. Mixing tx.BeginTx(ctx) with tx.Query (no context) works, but the query won't observe the context and won't bail out on cancellation.

Isolation Levels

The isolation level controls what one transaction can see of another transaction's writes while both are running. The database/sql package defines a portable set of levels in sql.IsolationLevel. The driver maps these to whatever the underlying database supports, and some drivers reject levels they can't honor.

Three anomalies are worth knowing:

The levels available in sql.IsolationLevel, and which databases honor them, look like this:

SQLite is the simple case. Every transaction runs at serializable isolation because SQLite uses a single writer at a time. Postgres and MySQL give you a real choice, with trade-offs: stricter levels prevent more anomalies but increase the chance of conflicts (serialization failures on Postgres, lock waits on MySQL).

Picking a level is a per-workload decision. LevelReadCommitted is enough for most CRUD. LevelSerializable is what you want when correctness matters more than throughput, like the order-and-stock case where two concurrent orders could both read "stock = 1" and both try to decrement it. Read-only reporting queries can run at LevelRepeatableRead to get a stable snapshot of the database for the duration of the transaction.

If the driver doesn't support the requested level, BeginTx returns an error. That fast failure is preferable to silently downgrading: you don't want code that assumes serializable isolation to quietly run at read-committed.

This is the case the chapter has been building toward. A customer places an order for multiple items. The function must insert one row in orders, one row per item in order_items, and decrement the stock for each product. Every write happens in the same transaction. If any single step fails, every other step is rolled back.

The first order inserts an orders row, two order_items rows, and decrements both products' stock. The commit makes all four writes durable.

The second order is more interesting. The function inserts a second orders row, decrements product 1's stock from 8 to 7, inserts an order_items row for product 1, then tries to decrement product 2 by 100 and gets RowsAffected = 0. The function returns ErrOutOfStock. The deferred Rollback fires and undoes every write the second transaction made: the new orders row vanishes, the new order_items row vanishes, and product 1's stock returns to 8. The final counts confirm that exactly one order, two order items, and the original stock decrements survived.

That's the guarantee transactions give you. The second attempt is invisible. From the database's point of view, it never happened.

Most transaction bugs in Go come from one of a handful of mistakes. The symptoms vary, the fixes don't.

The first one is the most subtle, so it deserves a concrete example.

The INSERT runs inside the transaction. The UPDATE runs outside it, on a different connection from the pool, and commits on its own immediately. If the function later fails before reaching tx.Commit(), the deferred Rollback undoes the insert but the update is already committed. You end up with stock decremented but no order recorded. The variable name is db, not tx, and db is the package-level handle that has nothing to do with the current transaction. Any time you see db. inside a function that owns a transaction, treat it as a bug.

Sharing a *sql.Tx across goroutines is the other one that produces strange behavior. The database/sql package documents *sql.Tx as not safe for concurrent use. Two goroutines both calling tx.Exec on the same *sql.Tx race on the underlying connection. The driver may return a confusing error, or worse, the statements may interleave in ways that violate your invariants. A transaction belongs to exactly one goroutine for its entire lifetime. If you want concurrent work, use multiple goroutines each with their own *sql.Tx.

Finally, Go's database/sql package does not support nested transactions. Calling db.Begin() while another *sql.Tx is alive gives you a separate transaction on a separate connection. There's no parent-child relationship. The two transactions are isolated from each other (depending on the level), can deadlock with each other, and one committing has no effect on the other. If you need partial rollback within a larger transaction, use SQL SAVEPOINT:

Savepoints are a SQL feature, not a Go feature. The *sql.Tx doesn't know about them; you issue them as plain statements. They work on Postgres, MySQL, and SQLite, but the syntax varies slightly between databases.

A practical note on tx.Prepare. It exists, it returns a *sql.Stmt scoped to the transaction, and it behaves like db.Prepare except that calling it on a finished transaction returns sql.ErrTxDone. The only transaction-specific rule is that a *sql.Stmt you created with tx.Prepare becomes unusable as soon as the transaction commits or rolls back.

A single SQL statement is already atomic on every database that supports transactions. UPDATE products SET stock = stock - 1 WHERE id = 1 either completes fully or doesn't run at all. You don't need db.Begin to wrap it.

You need a transaction when you have multiple statements that must succeed or fail together, or when you need a consistent read across multiple queries (a transaction at LevelRepeatableRead or LevelSerializable gives you a stable view of the database for the whole transaction).

If your function does one Exec or one Query, skip the transaction. The pool-managed implicit transaction the driver wraps it in is enough.