How does context cancellation work with database/sql, including transactions and rows?

Use the *Context methods: QueryContext, ExecContext, QueryRowContext, BeginTx, PingContext, and Conn(ctx). When the context is canceled:

• A call waiting for a pool connection returns straight away with the context error.
• For a running query, database/sql tells the driver to cancel. For example, pgx/lib/pq send a Postgres cancel request, and MySQL may kill the query or close the connection. Whether the server actually stops depends on the driver.
• Rows tied to that context are closed automatically, and rows.Next() returns false with rows.Err() reporting the context error. Always check rows.Err().
• For BeginTx(ctx, ...): "If the context is canceled, the sql package will roll back the transaction. Tx.Commit will return an error."

func transfer(ctx context.Context, db *sql.DB, from, to int64, amt int) (err error) {
	tx, err := db.BeginTx(ctx, &sql.TxOptions{Isolation: sql.LevelSerializable})
	if err != nil {
		return err
	}
	defer func() {
		if err != nil {
			_ = tx.Rollback() // harmless if already rolled back
		}
	}()
	if _, err = tx.ExecContext(ctx, `UPDATE acct SET bal = bal - $1 WHERE id = $2`, amt, from); err != nil {
		return err
	}
	if _, err = tx.ExecContext(ctx, `UPDATE acct SET bal = bal + $1 WHERE id = $2`, amt, to); err != nil {
		return err
	}
	return tx.Commit()
}

Gotcha: if cancellation arrives while Commit is on the wire, the client may not know whether the commit happened. Critical operations need idempotency keys.