Keywords: MySQL | GET_LOCK | Lock Monitoring | Performance Schema | User-Level Lock
Abstract: This technical paper provides an in-depth analysis of the lock mechanism created by MySQL's GET_LOCK function and its monitoring techniques. Starting from MySQL 5.7, user-level locks can be monitored in real-time by enabling the mdl instrument in performance_schema. The article details configuration steps, query methods, and how to associate lock information with connection IDs through performance schema tables, offering database administrators a complete lock monitoring solution.
Overview of GET_LOCK Function Lock Mechanism
MySQL's GET_LOCK function provides a user-level locking mechanism that differs fundamentally from traditional table-level locks such as LOCK TABLES. This locking mechanism allows applications to implement distributed lock functionality at the database level, commonly used to coordinate access to shared resources by multiple processes or threads.
Performance Schema Monitoring Configuration
Starting from MySQL 5.7, locks created by GET_LOCK can be monitored through Performance Schema. First, the mdl (Metadata Lock) instrument must be enabled:
UPDATE performance_schema.setup_instruments
SET enabled = 'YES'
WHERE name = 'wait/lock/metadata/sql/mdl';This configuration is temporary and will be lost after server restart. For permanent enablement, add the following to the [mysqld] section of the MySQL configuration file my.cnf:
[mysqld]
performance_schema_instrument = 'wait/lock/metadata/sql/mdl=ON'After configuration, restart the MySQL service for the changes to take effect.
Lock Information Query and Analysis
Once monitoring is enabled, current lock information can be obtained by querying the performance_schema.metadata_locks table. User-level locks have an OBJECT_TYPE of 'USER LEVEL LOCK' and can be filtered using the following query:
SELECT * FROM performance_schema.metadata_locks
WHERE OBJECT_TYPE='USER LEVEL LOCK';The query results include several important fields: OBJECT_NAME displays the lock name, LOCK_TYPE identifies the lock type (typically EXCLUSIVE), LOCK_STATUS shows the lock status (GRANTED indicates the lock is granted), and OWNER_THREAD_ID records the thread ID holding the lock.
Lock and Connection Association
It is important to note that OWNER_THREAD_ID is not the connection ID and cannot be directly used with the KILL command. To obtain the corresponding connection ID, an association query through the performance_schema.threads table is required:
SELECT PROCESSLIST_ID FROM performance_schema.threads
WHERE THREAD_ID = [OWNER_THREAD_ID];Once the connection ID is obtained, the KILL command can be used to terminate the connection holding the lock.
Specific Lock Status Checking
In addition to viewing all lock information, MySQL provides the IS_USED_LOCK function to check the status of specific named locks:
SELECT IS_USED_LOCK('lock_name');If the lock is occupied, the function returns the connection ID holding the lock; otherwise, it returns NULL. This method is suitable for quickly checking the occupancy status of specific locks.
Comparison with Other Lock Monitoring Methods
Compared to traditional monitoring methods such as SHOW PROCESSLIST and SHOW ENGINE INNODB STATUS, Performance Schema provides more precise information about user-level locks. Traditional methods primarily focus on table-level and row-level locks and cannot provide detailed information about locks created by GET_LOCK.
Practical Application Scenarios
In practical applications, this monitoring mechanism is highly useful for debugging lock contention issues in distributed systems. By monitoring lock status in real-time, deadlocks or long-held locks can be promptly identified, improving system stability and performance.