MySQL Bugs: #72561: Add P_S metrics on GET

Bug #72561	Add P_S metrics on GET_LOCK() usage
Submitted:	7 May 2014 18:14	Modified:	23 Oct 2015 9:44
Reporter:	Simon Mudd (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Performance Schema	Severity:	S4 (Feature request)
Version:	5.7	OS:	Any
Assigned to:	Marc ALFF	CPU Architecture:	Any
Tags:	GET_LOCK, locking, performance_schema

Description:
GET_LOCK(), http://dev.mysql.com/doc/refman/5.7/en/miscellaneous-functions.html#function_get-lock, is a useful function for obtaining exclusive locks to resources inside the database.

However, which it's easy to use it's hard to see how it's used, who holds which locks nor is there any information on for example the average number of locks that are outstanding or the average or maximum amount of time that locks have been held.

Debugging problems related to GET_LOCK() usage is quite hard because of this.

How to repeat:
Use GET_LOCK() in different connections and then try to see who holds the lock (as the thread may be doing something else), or find statistics on the different locks that MySQL manages.

Suggested fix:
Expose information via performance_schema on GET_LOCK usage.

I can think of:
current_get_locks: names, and threads waiting or holding locks, and timing information for these locks
historic_get_lock_statistics: showing totals since statistics restart (table truncation) collected from the current lock information.

This information would aid developer trying to determine why issues are occurring and the cause.

This feature is now implemented in MySQL 5.7

GET_LOCK uses metadata locks with an OBJECT_TYPE of USER LEVEL LOCK

See http://dev.mysql.com/doc/refman/5.7/en/metadata-locks-table.html for details,
the table performance_schema.metadata_locks displays who owns / who waits user level locks.

Note, you need to enable the metadata lock instrument:

UPDATE setup_instruments 
       SET ENABLED = 'YES' 
 WHERE NAME = 'wait/lock/metadata/sql/mdl';

Then it's pretty easy to see current locks, including those that are pending, with:

mysql> select object_name, lock_status, owner_thread_id from metadata_locks where object_type = 'user level lock';
+---------------+-------------+-----------------+
| object_name   | lock_status | owner_thread_id |
+---------------+-------------+-----------------+
| somelock      | GRANTED     |              23 |
| someotherlock | GRANTED     |              23 |
| somelock      | PENDING     |              24 |
+---------------+-------------+-----------------+