MariaDB starting with 10.0.7

The metadata_lock_info plugin was added in MariaDB 10.0.7.

The Information Schema METADATA_LOCK_INFO table is created by the metadata_lock_info plugin. It shows active metadata locks and user locks (the locks acquired with GET_LOCK()).

It has the following columns:

ColumnDescription
THREAD_ID
LOCK_MODEOne of MDL_INTENTION_EXCLUSIVE, MDL_SHARED, MDL_SHARED_HIGH_PRIO, MDL_SHARED_READ, MDL_SHARED_WRITE, MDL_SHARED_NO_WRITE, MDL_SHARED_NO_READ_WRITE or MDL_EXCLUSIVE.
LOCK_DURATIONOne of MDL_STATEMENT, MDL_TRANSACTION or MDL_EXPLICIT
LOCK_TYPEOne of Global read lock, Schema metadata lock, Table metadata lock, Stored function metadata lock, Stored procedure metadata lock, Trigger metadata lock, Event metadata lock, Commit lock or User lock.
TABLE_SCHEMA
TABLE_NAME

Examples

User lock:

SELECT GET_LOCK('abc',1000);
+----------------------+
| GET_LOCK('abc',1000) |
+----------------------+
|                    1 |
+----------------------+

SELECT * FROM information_schema.METADATA_LOCK_INFO;
+-----------+--------------------------+---------------+-----------+--------------+------------+
| THREAD_ID | LOCK_MODE                | LOCK_DURATION | LOCK_TYPE | TABLE_SCHEMA | TABLE_NAME |
+-----------+--------------------------+---------------+-----------+--------------+------------+
|        61 | MDL_SHARED_NO_READ_WRITE | MDL_EXPLICIT  | User lock | abc          |            |
+-----------+--------------------------+---------------+-----------+--------------+------------+

Table metadata lock:

START TRANSACTION;

INSERT INTO t VALUES (1,2);

SELECT * FROM information_schema.METADATA_LOCK_INFO \G
*************************** 1. row ***************************
    THREAD_ID: 4
    LOCK_MODE: MDL_SHARED_WRITE
LOCK_DURATION: MDL_TRANSACTION
    LOCK_TYPE: Table metadata lock
 TABLE_SCHEMA: test
   TABLE_NAME: t
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)

Comments

Comments loading...