MySQL Bugs: #65139: Waiting for a table level lock does not count as slow query

Bug #65139	Waiting for a table level lock does not count as slow query
Submitted:	27 Apr 2012 22:12	Modified:	28 Apr 2012 7:15
Reporter:	Hartmut Holzgraefe	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Logging	Severity:	S4 (Feature request)
Version:	5.5.21	OS:	Linux
Assigned to:		CPU Architecture:	Any

Description:
A query waiting for a table locked by another connection/session using LOCK TABLES does not show up in the slow query lock unless it also accumulates enough wall clock time during actual execution after it has left the lock waiting phase.

So when doing the following in two sessions:

S1: LOCK TABLES t1 WRITE;
S2:   SELECT * FROM t1;
--  wait e.g. 10s with a slow log limit of 1s
S1: UNLOCK TABLES;

the query from session #2 will not show up in the slow log here

only when rewriting the query as 
  
  SELECT SLEEP(10) FROM t1;

it will show up in the slow log with "Query_time: 20, Lock_time: 10"

The funny part is that the slow log explicitly lists the lock wait time but that it does not seem to account towards the slow query threshold

How to repeat:
see above

Suggested fix:
Include lock wait time into slow query calculations

5.2.5. The Slow Query Log

"The time to acquire the initial table locks is not counted as execution time."

As current behavior is intended and explained in the manual, http://dev.mysql.com/doc/refman/5.5/en/slow-query-log.html, we can speak only about a new feature request here.

OK, so it is really documented behavior ... but why is Lock_time explicitly listed int the slow query log output then even though lock wait time alone would never trigger a slow log entry anyway?

Please allow users to change the behavior according to the original request.

We need the ability to make MySQL count time waiting on a lock as part of the query time. This will help us troubleshoot what queries were blocked for long periods of time. It would be helpful. To implement it properly, queries should also have Lock Waiting time as part of the report.

Why are we asking for this? Developers aren't always great at adding the ability to sense locks to their code. As a DBA, I need help giving them what they need. Subsequent RFE on the way...