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:
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

[7 May 2014 18:14] Simon Mudd
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.
[23 Oct 2015 9:43] Marc ALFF
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.
[23 Oct 2015 9:54] Mark Leith
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 |