How to terminate database query using context in Go

One of Go’s best features is the ability to cancel database queries while they are still running via context.Context (as long as cancellation is supported by your database driver).

On the face of it, using this functionality appears to be quite simple. But once you get into the details, there’s a lot of nuance and a few surprises… Especially if you’re using this functionality as part of a web application or API.

So in this article I want to explain how to cancel a database query in a web application, what quirks and hard cases are important to keep in mind. I’ll try to provide an answer to questions you may encounter while working with all of these.

First and foremost, why would you want to cancel a database query? Two scenarios come to mind:

1. When a query taking a lot of time to complete than expected.
2. When you want to set a timeout for taking a lock.

Here i’ll explain, How can we take a lock with timeout using context in transactions.

To start a context-aware transaction, use the BeginTx() method provided by the database/sql package. A code example can be seen here.

It’s important to realise that the context you provide to BeginTx() affects the entire transaction. The queries in the transaction will be immediately rolled back in the event of a context timeout or cancellation.

It’s perfectly fine to pass the same context as a parameter for all the queries in the transaction, in which case it ensures that they all (as a whole) complete before any timeout/cancellation . Alternatively, if you want per-query timeouts you can create different child contexts with different timeouts for each in the queries in the transaction. But you must derive these child contexts from the context you passed to BeginTX(). Otherwise there is a risk that the BeginTX() context timeout/cancellation occurs and the automatic rollback happens, but your code still may try to execute the query with a still-live context. If that happened you would receive the error "sql: transaction has already been committed or rolled back".

Here the sample code can be referenced for taking a lock on a row with timeout of 5 seconds, so here this function will throw an error if it is not able to take a lock within 5 seconds:

import (
 "database/sql"
 "github.com/jinzhu/gorm"
 "golang.org/x/net/context"
 "time"
)

type SampleStruct struct {
 ID        uint `gorm:"primary_key"`
 CreatedAt time.Time
 UpdatedAt time.Time
 DeletedAt *time.Time `sql:"index"`
 Name      string
 Mobile    string
}

func QueryWithTimeout(ctx context.Context, db *gorm.DB) (err error) {
 ctx, cancel := context.WithTimeout(ctx, 5*time.Second)
 defer cancel()
 var sample SampleStruct
 tx := db.BeginTx(ctx, &sql.TxOptions{})
 err = tx.Set("gorm:query_option", "FOR UPDATE").Find(&sample, "name = Ankit").Error
 return
}

There are a few things about this that I’d like to emphasize and explain:

• The defer cancel() line is important because it ensures that the resources associated with our child context (the one with the timeout) will be released before the slowQuery() function returns. If we don't call cancel() it may cause a memory leak: the resources won't be released until either the parent r.Context() is canceled or the 5-second timeout is hit (whichever happens first).
• The timeout countdown begins from the moment that the child context is created using context.WithTimeout().