I recently finished solving an issue where an
a particular row was timing out due to not being able to obtain a lock on that
row. For example, I was seeing the error:
At a high level, an error like this occurs when some transaction is holding on to a lock for longer than a specified interval (the lock timeout amount) while another transaction is trying to obtain that lock. It’s possible that it’s just a long-running query, but problems like this are usually due to a deadlock. The process required to solve this problem was a bit nuanced to figure out, so I’ve documented how to deal with this issue if it ever happens to you.
Finding Long-Running Queries
From the MySQL client, run:
This will list all connections which are currently executing a query, and list
the query as well. If any of these queries has a time column with a value
larger than 50 seconds (though to be honest, any query taking more than a
couple of seconds warrants a quick check to make sure it makes sense), then
you’ve probably found the problem. Look at the
SQL being executed to see if
it makes sense for this query to take a long time. If you don’t find anything
in this list, then chances are you just have a transaction that finished but
didn’t release its lock (or something of that nature). In either case, execute:
This will show you a list of currently running transactions at the bottom of the output. If on multiple different executions you see any transactions which have a lock on something, for example:
From the above output, you should decide if killing it is necessary, or if you can manually terminate the transaction via the client that initiated it.
Killing a Query
Once you’ve identified the culprit, killing the transaction is a piece of cake.
Using the thread ID from the output of
SHOW ENGINE INNODB STATUS, simply
KILL 6793410 from the client, and the thread will be no more.
Note that this is simply a last resort once deadlocks have been detected. Usually problems like this are indicative of a deeper problem (a race condition on your client, for example), and you should investigate to make sure you prevent the problem from ever happening again.