Posted on

InnoDB lock timeout before query execution

I found this yesterday while tracking down a locking issue for a client. They had a connection time out on a lock, but before it times out, SHOW PROCESSLIST had the status ‘statistics’ so it wasn’t actually executing the query yet. So, what was it doing and why did it time out there?

The answer actually was remarkably simple, but I did have to take a peek in the MySQL server source code (horay for Open Source!) The server sets the thd_proc_info to ‘statistics’ when calling the join optimiser, that’s the part of the optimiser that works out the best join order.

A lesser known feature of the join optimiser is that if it works out that only one row can match (lookup on primary or unique key field), it’ll try to retrieve the row. If there’s no match, there’s an “impossible WHERE clause” and essentially the entire query is optimised away and 0 rows returned.

If there is a match, all references to columns in that table can be replaced with the values that were just retrieved, turning them into constants. After all, there’s only one row matching! This can optimise away tables, removing the need to execute some or all joins. You don’t actually see this directly if you do EXPLAIN EXTENDED and then SHOW WARNINGS, because that output is from the parse tree which does not know about the join structure. But what you will see there is that the columns were replaced with constants, so from that you can deduce what’s going on.

For a query
EXPLAIN EXTENDED SELECT name from Country where code='AUS'
you would see access type const in the explain (which is the indicator for this optimisation), and SHOW WARNINGS brings up
select 'Australia' AS `name` from `world`.`country` where 1
so you see the WHERE clause is gone completely and the name column in the SELECT has been fed the value Australia so it’s all constants.

Back to the original issue… if another connection holds an exclusive (write) lock on that particular row, the join optimiser will be waiting for the lock to be released and that’s how you can actually get a lock timeout at this stage. It’s a perfectly normal thing to happen.