One of Golang Context’s usage is to provide control over timeouts and cancelation. We can use this to control the timeout of SQL query execution and cancel it. This article will show you how to do that.
We use a timeout to make our resource usage more efficient. In SQL we can do this by limiting our query execution time. We can also cancel running query execution that is no longer used. Example case: our HTTP server query to the database to serve a client request. The query execution is still running, but the client already canceled the request. By default, the query will run until the result is produced. But since the client already disconnected, we can cancel the query because the result is no longer needed. By doing this, we released some resources and increased efficiency.
Query Without Go Context
Query without Context will be executed until we got the result. This can bad because if the execution took too long, the client most likely already timeout / canceled the request.
We will use query SELECT sleep(15) to simulate a long-running query. The Go code will be:
var db *sql.DB
func main() {
dtbs, err := sql.Open("mysql", "myuser:mypassword@/jajaldoang")
if err != nil {
panic(err.Error()) // just for example
}
db = dtbs
http.HandleFunc("/get_something", myHandler)
println("listening..")
http.ListenAndServe(":5005", nil)
}
func myHandler(w http.ResponseWriter, r *http.Request) {
_, err := db.Query("SELECT sleep(15)")
if err != nil {
log.Println("error query: " + err.Error())
w.Write([]byte("something's wrong: " + err.Error()))
return
}
w.Write([]byte("success"))
}When myHandler is called, we can use show full processlist; to see the running query.
The process will be there until 15 seconds is elapsed.
How to Use Go Context in SQL Query
We will use the function QueryContext to execute a query with context. This function accepts context.Context as the first argument. If the context is canceled or timed out, the query execution will be stopped. If the query is INSERT or UPDATE you can use function ExecContext.
func myHandler(w http.ResponseWriter, r *http.Request) {
ctx, cancel := context.WithTimeout(context.Background(), 3*time.Second)
defer cancel()
_, err := db.QueryContext(ctx, "SELECT sleep(15)")In the example, we create a context with a timeout of 3 seconds. The context is derived from the background context. After 3 seconds the context will be timeout and canceling the query right away. Let’s do a test.
Send a request to the API
curl -w '\n%{time_total}' http://localhost:5005/get_something
something's wrong: context deadline exceeded
3.017303
You see that we got the response in around 3 seconds. Now let’s see the processlist in mysql.
The process is gone in the list without waiting for the result of the query SELECT sleep(15).
How to Use Context from http.Request
In the previous example, we created a new context from the background context. Background context is an empty context. It has no deadline and no cancelation. So when we created a context with a timeout from background context, the only cancelation it has is our timeout.
What if before 3 seconds, the client disconnects or cancel the request. We can get this event from the Context of http.Request. To do this, we should create a new context from request.Context() instead of background context.
func myHandler(w http.ResponseWriter, r *http.Request) {
ctx, cancel := context.WithTimeout(r.Context(), 3*time.Second)
defer cancel()
_, err := db.QueryContext(ctx, "SELECT sleep(15)")Let’s send a request and cancel it before 3 seconds. We can cancel it by using CTRL + C if we use curl. The query will be stopped and return error context canceled. With this context, the query will stop when timeout elapsed or when the HTTP request canceled, whichever comes first.
We can also use middleware to make the code reusable.
Conclusion
Executing query with context gives you control over execution timeout and cancelation. This is a good thing because we can use the resource more efficiently. This also can prevent long-running queries from piling up and exhausting our resources. Context timeout not only can be used for SQL execution but also any other use cases.