Information Schema METADATA_LOCK_INFO Table
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:
Column | Description |
---|---|
THREAD_ID | |
LOCK_MODE | One 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_DURATION | One of MDL_STATEMENT , MDL_TRANSACTION or MDL_EXPLICIT |
LOCK_TYPE | One 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
<<code lang=sql inline=false>>
¶
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) ¶ <</code>> ¶