GORM Context Timeouts and Cancellation in Real Requests

A database call without a deadline is a promise to wait forever. The query may be slow because a lock is held, because the plan is bad, or because the network dropped. If the HTTP client already gave up, the work is wasted, and every stuck request holds a goroutine and a pooled connection until the pool runs out. GORM takes a context.Context on every operation through WithContext. This article runs a query that sleeps for three seconds under several contexts on PostgreSQL 18.6 and reports what the client and the server did.

It continues from the GORM tutorial and transactions. The language level background is in context with timeouts and deadlines, and the driver level version of this experiment is pgx context cancellation and cancelling a database query.

The lab

The program calls pg_sleep(3) in seven ways. Time is reported as "under 400 ms" so the output does not depend on the machine.

package main

import (
	"context"
	"errors"
	"fmt"
	"log"
	"strings"
	"time"

	"gorm.io/driver/postgres"
	"gorm.io/gorm"
	"gorm.io/gorm/logger"
)

func must(err error) {
	if err != nil {
		log.SetFlags(0)
		log.Fatal(err)
	}
}

// connect recreates a schema and returns a connection whose search_path points at it.
func connect(schema string, cfg *gorm.Config) *gorm.DB {
	base := "host=127.0.0.1 port=55432 user=postgres password=secret dbname=lab sslmode=disable"
	admin, err := gorm.Open(postgres.Open(base), &gorm.Config{Logger: logger.Discard})
	must(err)
	must(admin.Exec("drop schema if exists " + schema + " cascade").Error)
	must(admin.Exec("create schema " + schema).Error)
	if cfg == nil {
		cfg = &gorm.Config{Logger: logger.Discard}
	}
	db, err := gorm.Open(postgres.Open(base+" search_path="+schema), cfg)
	must(err)
	return db
}

func elapsedMS(start time.Time) string {
	if d := time.Since(start); d < 400*time.Millisecond {
		return "under 400 ms"
	}
	return "over 400 ms"
}

func running(db *gorm.DB) int64 {
	var n int64
	db.Raw("select count(*) from pg_stat_activity where datname = 'lab' and query like '%pg_sleep(3)%' and state = 'active' and pid <> pg_backend_pid()").Scan(&n)
	return n
}

func main() {
	db := connect("l0720", nil)

	// 1. A deadline on a slow query.
	ctx, cancel := context.WithTimeout(context.Background(), 100*time.Millisecond)
	defer cancel()
	start := time.Now()
	err := db.WithContext(ctx).Exec("select pg_sleep(3)").Error
	fmt.Println("1. deadline:", errors.Is(err, context.DeadlineExceeded), "|", err, "|", elapsedMS(start))

	// 2. The server stopped the query too, not just the client.
	time.Sleep(200 * time.Millisecond)
	fmt.Println("2. pg_sleep(3) still running on the server:", running(db))

	// 3. Cancelling by hand.
	ctx, cancel = context.WithCancel(context.Background())
	go func() { time.Sleep(50 * time.Millisecond); cancel() }()
	start = time.Now()
	err = db.WithContext(ctx).Exec("select pg_sleep(3)").Error
	fmt.Println("3. cancel:", errors.Is(err, context.Canceled), "|", elapsedMS(start))

	// 4. A transaction under a deadline rolls back.
	db.Exec("create table t (n int)")
	ctx, cancel = context.WithTimeout(context.Background(), 150*time.Millisecond)
	defer cancel()
	err = db.WithContext(ctx).Transaction(func(tx *gorm.DB) error {
		tx.Exec("insert into t values (1)")
		return tx.Exec("select pg_sleep(3)").Error
	})
	var n int64
	db.Table("t").Count(&n)
	fmt.Println("4. transaction:", errors.Is(err, context.DeadlineExceeded), "rows kept:", n)

	// 5. A context is not remembered: the next call without one runs unbounded.
	scoped := db.WithContext(ctx) // already expired
	err = scoped.Exec("select 1").Error
	fmt.Println("5. reusing an expired ctx:", errors.Is(err, context.DeadlineExceeded))
	fmt.Println("   fresh session ok:", db.Exec("select 1").Error == nil)

	// 6. A server side limit as a second layer, scoped to one transaction with SET LOCAL.
	err = db.Transaction(func(tx *gorm.DB) error {
		tx.Exec("set local statement_timeout = '100ms'")
		return tx.Exec("select pg_sleep(3)").Error
	})
	fmt.Println("6. statement_timeout:", strings.Contains(err.Error(), "57014"), "|", strings.Split(err.Error(), " (")[0])

	// 7. First with a context on a fast query is unaffected.
	ctx, cancel = context.WithTimeout(context.Background(), time.Second)
	defer cancel()
	fmt.Println("7. fast query under 1s deadline:", db.WithContext(ctx).Exec("select 1").Error == nil)
}

1. deadline: true | timeout: context deadline exceeded | under 400 ms
2. pg_sleep(3) still running on the server: 0
3. cancel: true | under 400 ms
4. transaction: true rows kept: 0
5. reusing an expired ctx: true
   fresh session ok: true
6. statement_timeout: true | ERROR: canceling statement due to statement timeout
7. fast query under 1s deadline: true

A deadline stops the query

Block 1 created a context with a 100 ms timeout and ran db.WithContext(ctx).Exec("select pg_sleep(3)"). The error came back after about 100 ms: timeout: context deadline exceeded, and errors.Is(err, context.DeadlineExceeded) was true. GORM passes the context to database/sql, which passes it to the driver, and the driver aborts the wait.

Match the error with errors.Is. The text depends on the driver, and a handler should map DeadlineExceeded to a 504 or a 503 with Retry-After, and context.Canceled (the client went away) to nothing at all, since nobody is listening.

The server stops too

Giving up on the client side would not help if PostgreSQL kept running the query. Block 2 waits 200 ms and then asks pg_stat_activity how many connections are actively running pg_sleep(3). The answer is 0. With pgx, a cancelled context makes the driver send a cancel request to the server on a second connection, and the backend ends the statement. That is the behaviour that makes timeouts worth setting: the database gets its resources back.

One caveat applies. Cancel requests are best effort. A backend stuck in an uninterruptible state, or one inside a long C function that does not check for interrupts, can take longer to stop. Treat the context as the way to stop waiting, and the server side statement timeout below as the way to guarantee the work ends.

Manual cancellation

Block 3 cancels from another goroutine after 50 ms, standing in for a client disconnect or a shutdown signal. The call returned context.Canceled in under 400 ms. In an HTTP server, r.Context() is cancelled when the client closes the connection, so passing it down gets this behaviour for free, as described in request context cancellation.

Transactions and deadlines

Block 4 runs db.WithContext(ctx).Transaction(...) with a 150 ms context. Inside, it inserts a row and then calls pg_sleep(3). The deadline expired during the sleep, the transaction was rolled back, and a count afterwards found rows kept: 0. The insert, which succeeded on its own, was undone with the failure.

The context goes on the transaction handle, so every statement inside it shares the same deadline. If you want a deadline for the whole unit of work, set it once at the top. If a particular statement needs its own tighter limit, derive a child context for it with a shorter timeout and use tx.WithContext(child). A child cannot outlive its parent.

A context belongs to a request

Block 5 shows a mistake that is easy to make. The scoped variable holds db.WithContext(ctx) where ctx has already expired. Using it for a new statement failed with DeadlineExceeded straight away. A fresh call on the original db worked. WithContext returns a new session bound to that context; it does not change db.

Two rules follow:

1. Never store a handle that carries a request context. A struct field holding db.WithContext(r.Context()) is dead after the request. Store the root db, and call WithContext inside each method with the context the caller passed in.
2. Pass the context as the first parameter of every store method. func (s *Store) Get(ctx context.Context, id int64). This is the same convention the standard library uses, and it is how deadlines reach the database.

A related trap: a context that is already expired when the call starts makes the call fail without trying the query. In a background job started from a request, create a new context for the job (or use context.WithoutCancel(ctx) to keep values but drop cancellation) so the work does not die with the response.

Server side limits as a second layer

Client contexts only work when the code remembers them. PostgreSQL has its own limits that apply to all callers. Block 6 runs a transaction, executes SET LOCAL statement_timeout = '100ms' and then pg_sleep(3). The server ended the statement itself with canceling statement due to statement timeout and SQLSTATE 57014.

The options, in increasing breadth:

• SET LOCAL statement_timeout inside a transaction, for one unit of work.
• ALTER ROLE app SET statement_timeout = '5s', for every connection of an application role. This is the safest default for a web service, because it caps the damage of any query that lacks a context.
• idle_in_transaction_session_timeout, which ends a session that opened a transaction and went quiet. It protects against the forgotten Begin in the transactions article.
• lock_timeout, which bounds the time spent waiting for a lock separately from the time spent running.

Do not set a global statement_timeout on a role that runs migrations or batch jobs; a legitimate five minute index build would be cancelled. Use separate roles.

A pooler changes the picture. Behind PgBouncer in transaction mode, SET without LOCAL leaks to the next client of that server connection. SET LOCAL inside a transaction does not, which is why the lab uses it.

Choosing the numbers

A timeout is a decision about what a slow answer is worth. A workable starting set for a web API:

Measure your own p99 first. A timeout below the real p99 turns slow requests into errors. A timeout far above it protects nothing.

Checklist

• Use db.WithContext(ctx) on every call, with the request's context.
• Derive deadlines with context.WithTimeout and call cancel with defer.
• Compare with errors.Is(err, context.DeadlineExceeded) and context.Canceled.
• Never store a session made from a request context.
• Set statement_timeout and idle_in_transaction_session_timeout on the application role, and SET LOCAL for per transaction limits.

The WithContext method is documented in the GORM context guide, and the server side settings in the PostgreSQL client connection defaults.