Manage Cancellations and Database Timeouts with Golang

One of the great features of Go is that it’s possible to cancel database queries while they are still running via a context.Context instance (so long as cancellation is supported by your database driver).

On the face of it, using this functionality is quite straightforward (here’s a basic example). But once you start digging into the details there’s a lot of nuance and quite a few gotchas… especially if you are using this functionality in the context of a web application or API.

So in this post, I want to explain how to cancel database queries in a web application, what behavioral quirks and edge cases it is important to be aware of and try to provide answers to the questions you might have when working through all this.

But first, off, why would you want to cancel a database query? Two scenarios spring to mind:

1. When a query is taking a lot longer to complete than expected. If this happens, it suggests a problem — either with that particular query or your database or application more generally. In this scenario, you would probably want to cancel the query after a set period of time (so that resources are freed-up and the database connection is returned to the sql.DB connection pool for reuse), log an error for further investigation, and return a 500 Internal Server Error response to the client.
2. When a client goes away unexpectedly before the query completes. This could happen for several reasons, such as a user closing a browser tab or terminating a process. In this scenario, nothing has really gone ‘wrong’, but there is no client left to return a response to so you may as well cancel the query and free-up the resources.

Mimicking a long-running query with Golang

Let’s start with the first scenario. To demonstrate this, I’ll make a very basic web application with a handler that executes a SELECT pg_sleep(10) SQL query against a PostgreSQL database using the pq driver. The pg_sleep(10) function will make the query sleep for 10 seconds before returning, essentially mimicking a slow-running query.

package main

import (
    "database/sql"
    "fmt"
    "log"
    "net/http"

    _ "github.com/lib/pq"
)

var db *sql.DB

func slowQuery() error {
    _, err := db.Exec("SELECT pg_sleep(10)")
    return err
}

func main() {
    var err error

    db, err = sql.Open("postgres", "postgres://user:pa$$word@localhost/example_db")
    if err != nil {
        log.Fatal(err)
    }

    if err = db.Ping(); err != nil {
        log.Fatal(err)
    }

    mux := http.NewServeMux()
    mux.HandleFunc("/", exampleHandler)

    log.Print("Listening...")
    err = http.ListenAndServe(":5000", mux)
    if err != nil {
        log.Fatal(err)
    }
}

func exampleHandler(w http.ResponseWriter, r *http.Request) {
    err := slowQuery()
    if err != nil {
        serverError(w, err)
        return
    }

    fmt.Fprintln(w, "OK")
}

func serverError(w http.ResponseWriter, err error) {
    log.Printf("ERROR: %s", err.Error())
    http.Error(w, "Sorry, something went wrong", http.StatusInternalServerError)
}

If you were to run this code, then make a GET / request to the application you should find that the request hangs for 10 seconds before you finally get an "OK" response. Like so: