Canceling MySQL Queries in Go

5 min readOct 28, 2018

There are many occasions when you want to cancel the execution of a MySQL query. With the advent of Go 1.8 (2017), the database/sql package made it possible. This was a long sought-after feature because it meant being able to create a search box in the front-end. On every keypress, you could retrigger the expensive search query and cancel the prior one to reduce the load on your database. Or so I thought. It turned out to be a massive lie.

This is even more problematic with write operations because your application may believe and expect that the canceled operation does not change the state of your database.

In this article, I will show you an approach to canceling a read in order to save unnecessary CPU and memory usage on your database. I will also show you how to cancel a write operation so your database state remains intact.

If you are a beginner in Go, then I recommend reading this article to understand how context cancelation works.

I’ve created a package that embodies my approach. It can be found here.

Connection Pooling

Go uses a built-in connection pool. This means that when you attempt to query the database, you are not creating a brand-new connection each time and disconnecting upon completion.

Instead, the pool contains a finite number of connections to the database (The default is infinite but you should configure it to an appropriate number using SetMaxOpenConns). The number of active connections can fluctuate based on how many queries you are attempting to execute in any given time frame, but the pool will ensure it never exceeds the maximum configured number.

When you attempt to query, the pool will provide a connection and upon completion, it will be returned to the pool for another future query. If all the connections are currently in use, then the query is blocked until one becomes available.

// Creating a database pool
pool, err := sql.Open("mysql", "user:password@/dbname")// Configure the pool
pool.SetMaxOpenConns()
pool.SetMaxIdleConns()
pool.SetConnMaxLifetime(d time.Duration)// Execute a query
rows, err := pool.QueryContext(ctx, stmt)

It should be noted that although the API provides a Close( ) method for closing the pool, for almost all intents and purposes it should never be used.

The issue

Sometimes you want to cancel a query because its results are no longer required, or because it is too slow and using up too much CPU/memory on your database server.

In order to do that, you would cancel the context that you provided:

// ctx can be canceled
ctx, cancelFunc := context.WithCancel(context.Background())result, err := pool.ExecContext(ctx, stmt)ORrows, err := pool.QueryContext(ctx, stmt)

Upon cancelation, the command will return with either a context.Canceled or context.DeadlineExceeded error.

Anyone familiar with Go API design would quite reasonably expect that the execution on the database side has also been canceled. For the popular Postgres driver, that is in fact what happens.

Sadly if you are using MySQL, this is not the case. From Go’s point of view, the query is canceled and it will not wait for the result of the query. But from MySQL’s point of view, the query will continue until it eventually completes or loops forever.

Canceling a read operation

The key to canceling a read operation is to obtain an exclusive connection from the pool.

// Obtain an exclusive connection
conn, err := pool.Conn(ctx)
defer conn.Close() // Return the connection back to the pool// Perform your read operation
rows, err := conn.QueryContext(ctx, stmt)

Once you have an exclusive connection, you can determine the connection’s connection_id which can be used later to kill the operation safely.

// kill is used to kill a running query.
func kill(pool *sql.DB, connectionID string) {
   pool.Exec("KILL QUERY ?", connectionID)
}

The KILL command must be called from another connection which is why we grab another connection from the pool. For ultimate robustness, it is recommended you use an independent connection pool purely for this purpose. This pool can have 1 connection. This is because there is always the possibility that all connections in your primary connection pool are stuck in long-lasting queries making kills impossible.

// Obtain an exclusive connection
conn, err := pool.Conn(ctx)
defer conn.Close() // Return the connection back to the pool// Determine the connection's connection_id
var connectionID string
connStmt := `SELECT CONNECTION_ID()`err = conn.QueryRowContext(ctx, connStmt).Scan(&connectionID)
if err != nil {
   return err
}// Perform your read operation
rows, err := conn.QueryContext(ctx, stmt)
if err != nil {
   if err == context.Canceled || err == context.DeadlineExceeded {
      kill(pool, connectionID)
   }
   return err
}

It must be stressed that this approach must only be used when you know you have a potentially time-consuming operation and you want it to be cancelable. Do not use it for ordinary queries which take a few milliseconds. Even if you want to cancel them, by the time you send the kill signal, they will be finished already.

Canceling a write operation

Special precaution must be taken with write operations because they change the state of the database. If your application cancels a write operation, it may proceed under the assumption that no state has changed. Behind the scenes, MySQL may have completed the write query much to your chagrin.

The key is to always use transactions. If you want to cancel the write query, then again send the kill signal. This time it will also rollback the transaction.

Out of the box, the MySQL driver already gives you an exclusive connection when you create a transaction. However, the below example will proceed in the same spirit as the above example.

// Obtain an exclusive connection
conn, err := pool.Conn(ctx)
defer conn.Close() // Return the connection back to the pool// Determine the connection's connection_id
var connectionID string
connStmt := `SELECT CONNECTION_ID()`err = conn.QueryRowContext(ctx, connStmt).Scan(&connectionID)
if err != nil {
   return err
}// Perform the write operation
tx, err := conn.BeginTx(ctx, nil)_, err = tx.ExecContext(ctx, stmt)
if err != nil {
   if err == context.Canceled || err == context.DeadlineExceeded {
      kill(pool, connectionID)
   }
   return tx.Rollback()
}tx.Commit()

It may be worth a try creating a transaction directly from the pool instead. Upon context cancelation, you can call tx.Rollback() . That should reduce the boilerplate above but I haven’t tested that approach. My suspicion is that it won’t work.

Final Notes

The above examples call defer conn.Close() immediately after obtaining the exclusive connection. This is not necessarily best practice — particularly if you are doing post-processing of the returned results within the same function. It may be more prudent to call conn.Close() after you have performed your query because it is imperative that you return the connection back to the pool ASAP. The kill (Go function) must also be called before the conn.Close() to guarantee that the connection is not calling some other query. We don’t want to inadvertently kill a totally independent query.

It is essential that you fully understand the ambit of the kill command based on different types of queries. See here: https://dev.mysql.com/doc/refman/8.0/en/kill.html

Finally, I have created a package to make this process automated. It should drastically reduce the boilerplate code in the examples. It can be found here: https://github.com/rocketlaunchr/mysql-go