Cancel a Long Running Database Query From Go Cleanly

A report query that takes 40 seconds when the table is large, a lock wait that never ends, a join that the optimizer got wrong after a statistics change: any of these will hold a goroutine, a database connection and server resources for as long as the query runs. When the caller has already given up, all of that is waste, and under load the waste turns into an outage because the connection pool fills with queries nobody is waiting for. Go has a clean mechanism for stopping them, and it works only when each layer cooperates.

This article shows how context cancellation reaches the database, demonstrates the difference between a driver that honors it and one that doesn't, and covers the server-side limits that back it up. It is part of the concurrency patterns cluster, applies the timeout pattern to databases, and relies on context cancellation. The demo drivers run in-process, so the examples need no database. Everything ran on Go 1.25.6.

The calls to use

database/sql has a Context variant for every operation that touches the server: db.QueryContext, db.QueryRowContext, db.ExecContext, db.PrepareContext, db.BeginTx(ctx, opts) and db.PingContext. The plain versions (Query, Exec) use context.Background(), which never ends. A rule that costs nothing to follow: in server code, never call the non-context variants.

What the context does depends on where it ends:

• If it ends before the call starts, database/sql returns the context error without touching the database.
• If it ends while waiting for a connection from the pool, the wait is abandoned and the error returned.
• If it ends during execution, the driver is asked to interrupt the query, if it can. The result is context.Canceled or context.DeadlineExceeded from the call.
• If it ends while reading rows, the Rows is closed and rows.Err() reports it.
• For BeginTx, cancelling the context rolls the transaction back.

That last point makes a context on a transaction a safety net: a request that dies mid-transaction cannot leave it open.

A demo with two kinds of driver

The standard library defines the driver interface, so a small fake can show the behaviors without a database. The first driver implements driver.QueryerContext, the optional interface that lets database/sql pass the context down, and it stops waiting when the context ends. The second implements only the old driver.Queryer, which has no context parameter, like drivers written before Go 1.8 and some still maintained today:

package main

import (
	"context"
	"database/sql"
	"database/sql/driver"
	"fmt"
	"io"
	"time"
)

const queryTime = 500 * time.Millisecond // how long the "server" takes

type rows struct{}

func (rows) Columns() []string              { return []string{"n"} }
func (rows) Close() error                   { return nil }
func (rows) Next(dest []driver.Value) error { return io.EOF }

type conn struct{ honorsContext bool }

func (c *conn) Prepare(string) (driver.Stmt, error) { return nil, driver.ErrSkip }
func (c *conn) Close() error                        { return nil }
func (c *conn) Begin() (driver.Tx, error)           { return nil, driver.ErrSkip }

// Query is the pre-context interface: it cannot be interrupted.
func (c *conn) Query(string, []driver.Value) (driver.Rows, error) {
	time.Sleep(queryTime)
	return rows{}, nil
}

// contextConn adds QueryContext, which watches the context the way real drivers do.
type contextConn struct{ conn }

func (c *contextConn) QueryContext(ctx context.Context, _ string, _ []driver.NamedValue) (driver.Rows, error) {
	select {
	case <-time.After(queryTime):
		return rows{}, nil
	case <-ctx.Done():
		// A real driver would send a cancel request to the server here.
		return nil, ctx.Err()
	}
}

type modernDriver struct{}

func (modernDriver) Open(string) (driver.Conn, error) { return &contextConn{}, nil }

type legacyDriver struct{}

func (legacyDriver) Open(string) (driver.Conn, error) { return &conn{}, nil }

func try(name string) {
	db, err := sql.Open(name, "")
	if err != nil {
		panic(err)
	}
	defer db.Close()

	ctx, cancel := context.WithTimeout(context.Background(), 100*time.Millisecond)
	defer cancel()
	start := time.Now()
	r, err := db.QueryContext(ctx, "SELECT slow()")
	if err == nil {
		r.Close()
	}
	fmt.Printf("%-7s driver: returned after %v, error: %v\n", name, time.Since(start).Round(50*time.Millisecond), err)
}

func main() {
	sql.Register("modern", modernDriver{})
	sql.Register("legacy", legacyDriver{})
	try("modern")
	try("legacy")
}

modern  driver: returned after 100ms, error: context deadline exceeded
legacy  driver: returned after 500ms, error: <nil>

With a 100 ms deadline against a 500 ms query, the context-aware driver returned in about 100 ms with context deadline exceeded. The legacy driver took the full 500 ms, and then returned successfully, since the context had already expired but the call had completed; the deadline was effectively ignored. database/sql can check the context before it starts the legacy call, but it cannot stop a blocked function.

Real drivers differ in the details, and the details matter. The PostgreSQL drivers (pgx, and lib/pq via the cancel protocol) open a second connection and send a cancel request for the running backend, which stops the query on the server. The go-sql-driver/mysql driver watches the context and, when it ends, closes the connection; the server notices the closed socket and aborts when it next checks, which is usually prompt but not instantaneous for a long scan. In both cases the connection is discarded from the pool rather than reused, since its state after an interrupted statement is uncertain. Check the changelog of your driver for how it handles cancellation, and test it: start a deliberately slow query (pg_sleep(30), SELECT SLEEP(30)), cancel the context, and watch the server's process list to confirm the query disappears.

Use the request context

The most useful context to pass is the one the caller already has. In an HTTP handler, r.Context() ends when the client disconnects or the server shuts down; hand it to the query and abandoned requests stop consuming the database:

func (s *Server) report(w http.ResponseWriter, r *http.Request) {
	ctx, cancel := context.WithTimeout(r.Context(), 5*time.Second) // never longer than the caller waits
	defer cancel()

	rows, err := s.db.QueryContext(ctx, reportSQL, r.PathValue("id"))
	if errors.Is(err, context.DeadlineExceeded) {
		http.Error(w, "report took too long", http.StatusGatewayTimeout)
		return
	}
	if err != nil {
		http.Error(w, "internal error", http.StatusInternalServerError)
		return
	}
	defer rows.Close()
	// ... stream rows ...
}

Deriving a timeout from r.Context() gives the earlier of the two limits: the client leaving, or five seconds. Mapping the error to a 504 instead of a 500 tells the operator, and the client, what happened (mapping errors to status codes). Do not reuse a cancelled request context for cleanup that still has to work, such as logging the failure to the database; use a new context for that.

Why you still need a server-side limit

Client-side cancellation depends on the network, the driver and the server noticing, and none of the three is guaranteed. A process that crashes, a network partition, a connection pool bug or a driver that ignores the context all leave a query running on the server with nobody waiting. The belt-and-braces answer is a limit enforced by the database itself:

• PostgreSQL: statement_timeout aborts any statement that runs longer than the limit. Set it per role (ALTER ROLE app SET statement_timeout = '10s'), per session (SET statement_timeout = '10s') or in the connection string's options. lock_timeout bounds waiting for locks, and idle_in_transaction_session_timeout ends transactions a client forgot to commit.
• MySQL: max_execution_time (milliseconds) limits SELECT statements, set globally, per session, or per query with an optimizer hint such as SELECT /*+ MAX_EXECUTION_TIME(2000) */ .... It does not apply to writes. innodb_lock_wait_timeout bounds lock waits, and a stuck statement can be ended manually with KILL QUERY <id>.

A limit set here is the last line of defence. The context is the polite request; the server timeout is the one that works even when the client has vanished. Set both, with the server limit a little above the longest legitimate client deadline so the client's error arrives first and the message is the more informative one. The details of choosing the numbers are in put a timeout on every blocking call, and the plumbing to tune the pool that these queries occupy is in the connection pool article of the databases series.

A checklist

Every query in server code uses a Context method. The context derives from the request's. There is a deadline, either at the edge of the request or on the specific call. The error is checked with errors.Is(err, context.DeadlineExceeded) and mapped to something meaningful. The driver's cancellation behavior has been tested against a real database at least once. And the database has a statement timeout configured for the application's role. The authoritative description of how contexts flow through the package is in the database/sql documentation, the driver contract in database/sql/driver, and the server settings in the PostgreSQL runtime configuration reference and the MySQL optimizer hints reference.