WL#6657: PERFORMANCE_SCHEMA, DATA LOCKS
Affects: Server-8.0
—
Status: Complete
Instrument data locks in the performance schema. When transaction A is locking row R, and transaction B is waiting on this very same row, B is effectively blocked by A. The instrumentation is to expose in performance_schema tables: - which data is locked (R), - who owns the lock (A), - who waits for the data (B). User Documentation ================== * http://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-1.html * http://dev.mysql.com/doc/refman/8.0/en/data-locks-table.html * http://dev.mysql.com/doc/refman/8.0/en/data-lock-waits-table.html * http://dev.mysql.com/doc/refman/8.0/en/innodb-locks-table.html * http://dev.mysql.com/doc/refman/8.0/en/innodb-lock-waits-table.html * http://dev.mysql.com/doc/refman/8.0/en/sys-innodb-lock-waits.html * http://dev.mysql.com/doc/refman/8.0/en/innodb-information-schema-transactions.html
Requirements ============ (1) Table performance_schema.data_locks. ------------------------------------- Func-Req (1.1): A fresh MySQL installation of CURRENT-VERSION must create the table performance_schema.data_locks. Func-Req (1.2): An upgrade from PREVIOUS-VERSION to CURRENT-VERSION must create the table performance_schema.data_locks. Func-Req (1.3): Table performance_schema.data_locks is read-only, by users with the proper grants. Func-Req (1.4): For innodb, table performance_schema.data_locks displays locks as follows: 1.4.1 Column ENGINE = per the spec 1.4.2 Column ENGINE_LOCK_ID = per the spec 1.4.3 Column ENGINE_TRANSACTION_ID = per the spec 1.4.4 Column THREAD_ID = per the spec 1.4.5 Column EVENT_ID = per the spec 1.4.6 Column OBJECT_SCHEMA = per the spec 1.4.7 Column OBJECT_NAME = per the spec 1.4.8 Column PARTITION_NAME = per the spec 1.4.9 Column SUBPARTITION_NAME = per the spec 1.4.10 Column INDEX_NAME = per the spec 1.4.11 Column OBJECT_INSTANCE_BEGIN = per the spec 1.4.12 Column LOCK_TYPE = per the spec 1.4.13 Column LOCK_MODE = per the spec 1.4.14 Column LOCK_STATUS = per the spec 1.5.15 Column LOCK_DATA = per the spec 1.5.16 All locks are displayed 2) Table performance_schema.data_lock_waits. -------------------------------------------- Func-Req (2.1): A fresh MySQL installation of CURRENT-VERSION must create the table performance_schema.data_lock_waits. Func-Req (2.2): An upgrade from PREVIOUS-VERSION to CURRENT-VERSION must create the table performance_schema.data_lock_waits. Func-Req (2.3): Table performance_schema.data_lock_waits is read-only, by users with the proper grants. Func-Req (2.4): For innodb, table performance_schema.data_lock_waits displays locks as follows: 2.4.1 Column ENGINE = per the spec 2.4.2 Column REQUESTING_ENGINE_LOCK_ID = per the spec 2.4.3 Column REQUESTING_ENGINE_TRANSACTION_ID = per the spec 2.4.4 Column REQUESTING_THREAD_ID = per the spec 2.4.5 Column REQUESTING_EVENT_ID = per the spec 2.4.6 Column REQUESTING_OBJECT_INSTANCE_BEGIN = per the spec 2.4.7 Column BLOCKING_ENGINE_LOCK_ID = per the spec 2.4.8 Column BLOCKING_ENGINE_TRANSACTION_ID = per the spec 2.4.9 Column BLOCKING_THREAD_ID = per the spec 2.4.10 Column BLOCKING_EVENT_ID = per the spec 2.4.11 Column BLOCKING_OBJECT_INSTANCE_BEGIN = per the spec (3) View sys.innodb_lock_waits. ------------------------------- Func-Req (3.1): A fresh MySQL installation of CURRENT-VERSION must create the view sys.innodb_lock_waits with the new structure. Func-Req (3.2): An upgrade from PREVIOUS-VERSION to CURRENT-VERSION must upgrade the view sys.innodb_lock_waits to the new structure. (4) View sys.x$innodb_lock_waits. --------------------------------- Func-Req (4.1): A fresh MySQL installation of CURRENT-VERSION must create the view sys.x$innodb_lock_waits with the new structure. Func-Req (4.2): An upgrade from PREVIOUS-VERSION to CURRENT-VERSION must upgrade the view sys.x$innodb_lock_waits to the new structure.
TABLE performance_schema.data_locks =================================== New table. ---------- CREATE TABLE `data_locks` ( `ENGINE` varchar(32) NOT NULL, `ENGINE_LOCK_ID` varchar(128) NOT NULL, `ENGINE_TRANSACTION_ID` bigint(20) unsigned DEFAULT NULL, `THREAD_ID` bigint(20) unsigned DEFAULT NULL, `EVENT_ID` bigint(20) unsigned DEFAULT NULL, `OBJECT_SCHEMA` varchar(64) DEFAULT NULL, `OBJECT_NAME` varchar(64) DEFAULT NULL, `PARTITION_NAME` varchar(64) DEFAULT NULL, `SUBPARTITION_NAME` varchar(64) DEFAULT NULL, `INDEX_NAME` varchar(64) DEFAULT NULL, `OBJECT_INSTANCE_BEGIN` bigint(20) unsigned NOT NULL, `LOCK_TYPE` varchar(32) NOT NULL, `LOCK_MODE` varchar(32) NOT NULL, `LOCK_STATUS` varchar(32) NOT NULL, `LOCK_DATA` varchar(8192) CHARACTER SET utf8mb4 DEFAULT NULL ) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8 Privileges ---------- This table is read only, only SELECT is supported. A user must be granted the proper table privileges to perform an operation. Semantic -------- * ENGINE, ENGINE_LOCK_ID, ENGINE_TRANSACTION_ID: Table performance_schema.data_locks is storage engine agnostic, and designed to work with any storage engine that instruments data locks. (ENGINE, ENGINE_LOCK_ID) is unique. Should a storage engine named "MY_OTHER_ENGINE" provide data locks, these columns are intended to be populated as follows: - ENGINE = "MY_OTHER_ENGINE" - ENGINE_LOCK_ID = My other engine internal lock id, exposed as a string. The format of the engine lock id string is defined by the storage engine. - ENGINE_TRANSACTION_ID = My other engine internal transaction id, if any. Currently, only the INNODB storage engine is instrumented, so for innodb data locks: - ENGINE = "INNODB" - ENGINE_LOCK_ID = Innodb internal lock id, exposed as a string. The format of innodb locks is currently as follows: TABLE_LOCKS are currently exposed as "TRX_ID:TABLE_ID" RECORD_LOCKS are currently exposed as "TRX_ID:SPACE_ID:PAGE_ID:HEAP_ID" Both are internal, and can change at any time without notice. Users should not attempt to parse these fields and should consider them opaque. - ENGINE_TRANSACTION_ID = Innodb internal tx id. This column can be used to join with INFORMATION_SCHEMA.INNODB_TRX.TRX_ID * THREAD_ID: This column is the thread id for the session owning a lock. This can be used to join with table performance_schema.THREADS. * EVENT_ID: The performance schema event that caused the lock. (THREAD_ID, EVENT_ID) implicitly points to: - the parent wait event in performance_schema.events_waits_* - the parent stage event in performance_schema.events_stages_* - the parent statement event in performance_schema.events_statements_* - the parent transaction event in performance_schema.events_transactions_* * OBJECT_SCHEMA, OBJECT_NAME: The schema name and table name of the table locked. Note: Compared to column LOCK_TABLE in INFORMATION_SCHEMA.INNODB_LOCKS, the schema and table are in separate columns, which is easier to use. * PARTITION_NAME, SUBPARTITION_NAME: The partition and sub partition, if any. For innodb, this is a placeholder, it will be populated when WL#7881 is implemented. * INDEX_NAME: Name of the index locked, if any. * OBJECT_INSTANCE_BEGIN: Unique value, typically the address of the lock in memory. * LOCK_TYPE: The type of lock. This value is not an enum but a string, which can be set freely by different storage engines to reflect their implementation. For Innodb, values are the same as column INFORMATION_SCHEMA.INNODB_LOCKS.LOCK_TYPE in 5.7. http://dev.mysql.com/doc/refman/5.7/en/innodb-locks-table.html * LOCK_MODE: How the lock is requested. This value is not an enum but a string, which can be set freely by different storage engines to reflect their implementation. For Innodb, values are the same as column INFORMATION_SCHEMA.INNODB_LOCKS.LOCK_MODE in 5.7. http://dev.mysql.com/doc/refman/5.7/en/innodb-locks-table.html * LOCK_STATUS: The status of the lock request. This value is not an enum but a string, which can be set freely by different storage engines to reflect their implementation. For Innodb, values are "PENDING" and "GRANTED". * LOCK_DATA: Data associated with the lock, if any. This column does expose user data from the table. For Innodb, values are the same as column INFORMATION_SCHEMA.INNODB_LOCKS.LOCK_DATA in 5.7. http://dev.mysql.com/doc/refman/5.7/en/innodb-locks-table.html Behavior -------- Note that, for innodb, *ALL* the locks are reported in table performance_schema.data_locks. This is an improvement from existing table INFORMATION_SCHEMA.INNODB_LOCKS, which reports a lock only if another transaction is waiting on it. TABLE performance_schema.data_lock_waits ======================================== New table --------- CREATE TABLE `data_lock_waits` ( `ENGINE` varchar(32) NOT NULL, `REQUESTING_ENGINE_LOCK_ID` varchar(128) NOT NULL, `REQUESTING_ENGINE_TRANSACTION_ID` bigint(20) unsigned DEFAULT NULL, `REQUESTING_THREAD_ID` bigint(20) unsigned DEFAULT NULL, `REQUESTING_EVENT_ID` bigint(20) unsigned DEFAULT NULL, `REQUESTING_OBJECT_INSTANCE_BEGIN` bigint(20) unsigned NOT NULL, `BLOCKING_ENGINE_LOCK_ID` varchar(128) NOT NULL, `BLOCKING_ENGINE_TRANSACTION_ID` bigint(20) unsigned DEFAULT NULL, `BLOCKING_THREAD_ID` bigint(20) unsigned DEFAULT NULL, `BLOCKING_EVENT_ID` bigint(20) unsigned DEFAULT NULL, `BLOCKING_OBJECT_INSTANCE_BEGIN` bigint(20) unsigned NOT NULL ) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8 Privileges ---------- This table is read only, only SELECT is supported. A user must be granted the proper table privileges to perform an operation. Semantic -------- This table implements a many-to-many relationship between performance_schema.data_locks (requesting lock) and performance_schema.data_locks (blocking lock) Because it is N-N, a separate table is needed, similar to INFORMATION_SCHEMA.INNODB_LOCK_WAITS in 5.7 * ENGINE, REQUESTING_ENGINE_LOCK_ID: Storage engine name and internal lock id for the requested lock. Same as columns columns ENGINE, ENGINE_LOCK_ID in table DATA_LOCKS. * REQUESTING_ENGINE_TRANSACTION_ID: Storage engine internal transaction id for the requested lock. Same as columns column ENGINE_TRANSACTION_ID in table DATA_LOCKS. * REQUESTING_THREAD_ID: This column is the thread id for the session requesting a lock. Same as columns column THREAD_ID in table DATA_LOCKS. * REQUESTING_EVENT_ID: This column is the event id for the session requesting a lock. Same as columns column EVENT_ID in table DATA_LOCKS. * REQUESTING_OBJECT_INSTANCE_BEGIN: Unique value, typically the address of the lock in memory. Same as column OBJECT_INSTANCE_BEGIN in table DATA_LOCKS. * BLOCKING_ENGINE_LOCK_ID: Storage engine internal lock id for the blocking lock. Same as columns columns ENGINE_LOCK_ID in table DATA_LOCKS. * BLOCKING_THREAD_ID: This column is the thread id for the session owning the lock. Same as columns column THREAD_ID in table DATA_LOCKS. * BLOCKING_EVENT_ID: This column is the event id for the session owning the lock. Same as columns column EVENT_ID in table DATA_LOCKS. * BLOCKING_OBJECT_INSTANCE_BEGIN: Unique value, typically the address of the lock in memory. Same as column OBJECT_INSTANCE_BEGIN in table DATA_LOCKS. Note about performance_schema.setup_instruments =============================================== There is no instrument to enable / disable the instrumentation for data locks. The reason is that there is no data -- collection --, for which an instrument is desirable to control overhead, but data -- inspection -- of the internal innodb structures instead. TABLE INFORMATION_SCHEMA.INNODB_LOCKS ===================================== Note: This table is supported and deprecated in 5.7, see WL#9275 DEPRECATE INFORMATION_SCHEMA.INNODB_LOCKS IN 5.7 This table is removed in 5.8. This is an incompatible change. It is expected to affect very few tools or scripts, so the impact is expected to be low. TABLE INFORMATION_SCHEMA.INNODB_LOCK_WAITS ========================================== Note: This table is supported and deprecated in 5.7, see WL#9275 DEPRECATE INFORMATION_SCHEMA.INNODB_LOCKS IN 5.7 This table is removed in 5.8. This is an incompatible change. It is expected to affect very few tools or scripts, so the impact is expected to be low. VIEW sys.innodb_lock_waits ========================== Existing view. -------------- The view definition is changed as follows: Column locked_table is deleted. The following columns are added: - locked_table_schema - locked_table_name - locked_table_partition - locked_table_subpartition VIEW sys.x$innodb_lock_waits ============================ Existing view. -------------- Same structural changes as sys.innodb_lock_waits. Interface affected ================== sys schema ---------- The following sys views are changed: - innodb_lock_waits - x$innodb_lock_waits Information Schema ------------------- The following INFORMATION_SCHEMA tables are removed: - INNODB_LOCKS - INNODB_LOCK_WAITS Instrumentation --------------- A new performance schema interface is defined, used by storage engines like innodb to expose data locks.
A quick note about the implementation: The code pattern needs to inspect innodb structures upon P_S select, instead of feeding P_S with instruments call upon server execution, for overhead. This adds technical complexity to this task, but should avoid any runtime overhead.
Copyright (c) 2000, 2024, Oracle Corporation and/or its affiliates. All rights reserved.