CODEWITHSUNDEEP
mysqlgosqlx
Tyler S
Tyler S

Reputation: 21

How can I ensure that a query gets killed if its context is canceled?

I recently learned that Go's mysql driver doesn't kill running queries on context cancelation. That's a problem because my application can generate long running queries that lock the database (which is its own problem, but not what I'm focused on right now). I'd like to ensure that if a request is canceled, any running queries from that request get killed.

I've been using this function:

/*
GetConn() returns a connection to the database. It also launches a goroutine
that kills any running queries when the connection is closed.
*/
func GetConn(ctx context.Context) (*sqlx.Conn, error) {
    conn, err := db.Connx(ctx)
    if err != nil {
        return nil, err
    }
    var connId int64
    if err := conn.GetContext(ctx, &connId, `SELECT CONNECTION_ID()`); err != nil {
        return nil, err
    }
    go func() {
        // Wait for the context to be canceled, and kill the connection
        select {
        case <-ctx.Done():
            if err := conn.Close(); err != sql.ErrConnDone {
                db.Exec("KILL ?", connId)
            }
        }
    }()

    return conn, nil
}

That works, but my concern is what if the caller never cancels the context? The goroutine will just wait forever. I'm new to contexts and channels so is there a piece I'm missing? Or is there a better way to ensure queries get killed when a request is aborted?

Upvotes: 1

Views: 512

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562871

In fact, KILL doesn't really kill a query either. Not in the way might think. It doesn't interrupt the query immediately. It just sets a flag that the query should be terminated, and it's up to the MySQL Server to notice this flag eventually, and terminate the query at that time.

https://dev.mysql.com/doc/refman/8.0/en/kill.html says:

When you use KILL, a thread-specific kill flag is set for the thread. In most cases, it might take some time for the thread to die because the kill flag is checked only at specific intervals:

Refer to that manual page for the list of specific times MySQL checks the flag.

In practice, this works most of the time. But I've seen cases where a query execution gets stuck in a mode where it will take an excessively long time (if ever) for one of those checks to happen. I've had to resort to restarting the mysqld service forcibly at the operating system level to finally terminate a query.

So no, there's no 100% guaranteed way to terminate a query when the context is closed.

Upvotes: 0

Related Questions