MariaDB starting with 10.0.7

The metadata_lock_info plugin was added in MariaDB 10.0.7.

About

This plugin implements a new table, INFORMATION_SCHEMA.METADATA_LOCK_INFO. This table shows active metadata locks. The table will be empty if there are no active metadata locks.

Versions

VersionStatusIntroduced
0.1StableMariaDB 10.1.13
0.1GammaMariaDB 10.1.2

Installation

To install this plugin, use the INSTALL SONAME or INSTALL PLUGIN commands. For example:

INSTALL SONAME 'metadata_lock_info';

Example

SELECT * FROM information_schema.metadata_lock_info;  
+-----------+--------------------------+---------------+----------------------+-----------------+-------------+
| THREAD_ID | LOCK_MODE                | LOCK_DURATION | LOCK_TYPE            | TABLE_SCHEMA    | TABLE_NAME  |  
+-----------+--------------------------+---------------+----------------------+-----------------+-------------+
|        31 | MDL_INTENTION_EXCLUSIVE  | MDL_EXPLICIT  | Global read lock     |                 |             |  
|        31 | MDL_INTENTION_EXCLUSIVE  | MDL_EXPLICIT  | Commit lock          |                 |             |
|        31 | MDL_INTENTION_EXCLUSIVE  | MDL_EXPLICIT  | Schema metadata lock | dbname          |             |
|        31 | MDL_SHARED_NO_READ_WRITE | MDL_EXPLICIT  | Table metadata lock  | dbname          | exotics     |
+-----------+--------------------------+---------------+----------------------+-----------------+-------------+
4 rows in set (0.00 sec)
MariaDB [test]> SELECT 
CONCAT('Thread ',P.ID,' executing "',P.INFO,'" IS LOCKED BY Thread ',
M.THREAD_ID) WhoLocksWho 
FROM INFORMATION_SCHEMA.PROCESSLIST P,
INFORMATION_SCHEMA.METADATA_LOCK_INFO M 
WHERE LOCATE(lcase(LOCK_TYPE), lcase(STATE))>0;
+-----------------------------------------------------------------------------------+
| WhoLocksWho                                                                       |
+-----------------------------------------------------------------------------------+
| Thread 3 executing "INSERT INTO foo ( b ) VALUES ( 'FOO' )" IS LOCKED BY Thread 2 |
+-----------------------------------------------------------------------------------+
1 row in set (0.00 sec)

MariaDB [test]> SHOW PROCESSLIST;
+----+------+-----------+------+---------+------+------------------------------+----------------------------------------+----------+
| Id | User | Host      | db   | Command | Time | State                        | Info                                   | Progress |
+----+------+-----------+------+---------+------+------------------------------+----------------------------------------+----------+
|  2 | root | localhost | test | Sleep   |  123 |                              | NULL                                   |    0.000 |
|  3 | root | localhost | test | Query   |  103 | Waiting for global read lock | INSERT INTO foo ( b ) VALUES ( 'FOO' ) |    0.000 |
|  4 | root | localhost | test | Query   |    0 | init                         | SHOW PROCESSLIST                       |    0.000 |
+----+------+-----------+------+---------+------+------------------------------+----------------------------------------+----------+
3 rows in set (0.00 sec)

MariaDB [test]> 

See INFORMATION_SCHEMA.METADATA_LOCK_INFO for information on the table structure.

Comments

Comments loading...
Loading