MySQL Bugs: #1310: optionally add queries with lock times to slow

Bug #1310	optionally add queries with lock times to slow_query log
Submitted:	16 Sep 2003 13:54	Modified:	16 Feb 2006 12:28
Reporter:	Emery Ford	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Logging	Severity:	S4 (Feature request)
Version:	4.x	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
It would be nice to be able to see which queries accrue lock time in the slow_query log. Right now, a query can be waiting for a lock for a very long time and execute very quickly, thus not getting noticed that it was in fact very slow (as the result of another locking query).

How to repeat:
1. Set the long_query_time threshold to 2 seconds.
2. Execute a read SELECT query from a table which takes longer than 2 seconds. 
3. While this query is performing it's SELECT, INSERT a record into that same table.  
4. The INSERT will wait until there s no longer a read lock on the table before executing the INSERT.  
5. The SELECT query will get logged in the slow_query log; the INSERT, despite the variable length of time it needs to wait for the read lock to be freed, does not get logged in the slow_query log.

Thank you for a reasonable feature request. It is clearly needed to be able to catch such a long running queries. One of the possible (not best, although) ways is to write them into the slow query log.