Re: Which blocks which?

[Ingo Strüwing]

Hi.

Should it? Indeed it would be possible to go through the list of open tables, find the table in question, check, which thread locked it, and get the command that the thread is executing.

But I doubt that this is implemented. At least I don't know a command that could do the above. So you seem to need to guess.

From your description I assume that the "blocked" state persists for some time. This would mean that the table is locked by a "slow query". You could turn on the "slow query log" (http://dev.mysql.com/doc/refman/4.1/en/slow-query-log.html). In the log file you could search for the command that works with the table in question.

Every command locks all tables it works with at the beginning of its execution and unlocks them at the end. However there are some execeptions with subqueries. They might release read locks before the end of the command.

Regards,
Ingo

Ingo Strüwing, Senior Software Developer - Storage Engines
MySQL AB, www.mysql.com