Why does a *sql.Stmt prepared on *sql.DB not run inside your *sql.Tx, and what does tx.Stmt do?

## A transaction is a connection In `database/sql`, `db.BeginTx(ctx, opts)` checks out exactly one connection from the pool and holds it for the whole life of the `*sql.Tx`. Every `tx.ExecContext`, `tx.QueryContext` and `tx.QueryRowContext` runs on that connection. The transaction is not a Go-level concept the package layers over your calls — it is a property of that one server session. This is the fact everything else follows from: **being inside `BeginTx` and `Commit` in your source code does not make a call part of the transaction. Running on the transaction's connection does.** ## What a DB-level statement does instead A `*sql.Stmt` from `db.PrepareContext` belongs to the `*sql.DB`. When you execute it, it asks the pool for a connection — any free one — prepares itself there if it has not already, and runs. If a transaction is open at that moment on another connection, the statement neither knows nor cares. The consequences are all bad and all quiet: - **The work is not in the transaction.** `tx.Rollback()` will not undo it. You get a partial write that survives a failure the rest of your code treated as atomic. - **It cannot see uncommitted rows.** A statement reading rows the transaction has just written will not find them, so logic that depends on read-your-writes silently takes the wrong branch. - **It can block on the transaction's own locks.** The transaction holds row locks on its connection. The statement, on a second connection, waits for them. They cannot be released until the transaction commits, and the transaction cannot proceed because the goroutine is blocked inside the statement's execution. That is a self-deadlock: one request, two connections, waiting on itself until a context deadline or the server's lock timeout cuts it loose. Under load, several requests doing this hold connections while stuck, and the stall spreads. None of this produces a compile error or a runtime warning. The types line up perfectly. ## tx.Stmt and tx.StmtContext `tx.Stmt(stmt)` and its context-taking form `tx.StmtContext(ctx, stmt)` take a statement you already prepared on the `*sql.DB` and return a transaction-specific `*sql.Stmt` — one that operates within the transaction, on its connection. Two details are worth knowing: **It may or may not cost a prepare.** If the parent handle already has a server-side statement on the connection the transaction happens to be holding, that one can be reused. If not, the statement is prepared on that connection, which is an extra round trip taken inside the transaction — while it holds its locks. **It closes itself.** The returned statement is closed when the transaction is committed or rolled back. You do not need to track it, and you must not keep using it afterwards. The parent handle is untouched and stays usable for the life of the `*sql.DB`. ```go tx, err := db.BeginTx(ctx, nil) if err != nil { return err } defer tx.Rollback() txStmt := tx.StmtContext(ctx, stmt) if _, err := txStmt.ExecContext(ctx, idx, id); err != nil { return err } return tx.Commit() ``` ## When to use which - **Statement used both inside and outside transactions:** keep one long-lived handle on the `*sql.DB` and convert it with `tx.StmtContext` where a transaction is in play. - **Statement used only inside transactions:** `tx.PrepareContext` prepares directly on the transaction's connection. It is bound there and dies with the transaction — no conversion, no parent handle, no chance of using the wrong one. The trade is that you prepare once per transaction, so it suits transactions that run the statement several times. - **Statement run once inside a transaction:** just call `tx.ExecContext(ctx, sql, args...)`. Preparing a statement to use it a single time inside one transaction adds a round trip and a lifetime for nothing. ## The design lesson The reason this bug is common is that a repository method with a `*sql.DB` on its struct compiles and works fine — until someone calls it from inside a transaction. The durable fix is structural: make functions that participate in a transaction take the transaction explicitly, so there is no reachable `*sql.DB` handle for them to use by accident, and the mistake becomes impossible rather than merely discouraged.