In database/sql, what happens when a *sql.Stmt is executed on a connection it was not prepared on?

## One Go handle, many server-side statements A prepared statement is not a portable object. It lives inside one session on the database server, and a session in Go terms is one pooled connection. But `*sql.Stmt` is documented as usable for the lifetime of the `*sql.DB` it came from, and a `*sql.DB` hands out whatever connection is free. Those two facts can only be reconciled one way, and that way is re-preparation. When you call `stmt.ExecContext` or `stmt.QueryContext`, `database/sql` borrows a connection. Then: 1. If the handle already has a server-side statement on that connection, it executes it. One round trip. 2. If it does not, `database/sql` prepares the statement on that connection first, records the pairing, and then executes. Two round trips. Step 2 is invisible from your code. There is no error, no callback, no flag. The handle simply grows a new attachment. ## Why the connection changes underneath you Several ordinary things move a statement onto a new connection: - **Concurrency.** A `*sql.Stmt` is safe for concurrent use; if two goroutines execute it at the same time, they are on two different connections by definition. - **The first connection is busy** with someone else's query when your execution arrives. - **Connections come and go.** A long-running process does not keep the same physical connections all day; as old ones are retired and new ones opened, the handle attaches to the new ones on first use. So the steady-state server-side footprint of a service is roughly *number of long-lived statements* multiplied by *number of live connections*, not the number of `*sql.Stmt` values in the Go heap. For a background job holding four statement handles against a pool of ten connections, that is up to forty parsed statements on the server, all legitimate. ## What Close does about it `stmt.Close()` is the counterpart: it drops the statement on every connection the handle attached to, not just the one it started on. That is why a single deferred `Close` is enough no matter how far the handle spread. ## The bound case: Tx and Conn The re-preparation behaviour belongs to statements prepared on a `*sql.DB`. Two other origins behave the opposite way: - `tx.PrepareContext` on a `*sql.Tx` - `conn.PrepareContext` on a `*sql.Conn` A statement from either is bound to that one underlying connection forever. It cannot migrate, because the whole point of a `*sql.Tx` or a `*sql.Conn` is that it pins one session. When the transaction commits or rolls back, or the `*sql.Conn` is closed, the statement stops working and further calls return an error rather than silently re-preparing somewhere else. That asymmetry is worth stating explicitly in an interview, because it is where the two halves of prepared-statement lifetime meet: `*sql.DB` gives you a durable handle over a shifting set of connections, `*sql.Tx` and `*sql.Conn` give you a handle that is exactly as long-lived as the thing it was prepared on. ## Performance consequences The re-preparation is cheap in the steady state and awkward at the edges. **Steady state.** A handful of statements against a stable set of connections converges quickly: after a warm-up, nearly every execution is one round trip and you get the reuse you prepared for. **Churn.** If connections are short-lived relative to how often the statement runs, the handle spends a large fraction of its executions on the two-round-trip path — the same prepare-then-execute you were trying to avoid. At that point the explicit preparation is paying for itself less and less, and a plain parameterised `ExecContext` is competitive and simpler. **Server memory.** Each attachment is a parsed statement and its plan, held per session. Multiply by statements and by connections. This is normally small and bounded; it becomes a problem only when the count is unbounded, which means handles that were never closed rather than the re-preparation itself. ## What it is not A `*sql.Stmt` does not hold a connection out of the pool between calls. It holds statement attachments *on* connections. Each execution borrows a connection, runs, and returns it. That is precisely why one handle can serve many goroutines at once — and why its footprint on the server is a function of how many connections it has touched rather than how often it has run.