29.12.13.1 data_locks 表
The data_locks
table shows data locks held and requested. For information about which lock requests are blocked by which held locks, see Section 29.12.13.2, “The data_lock_waits Table”.
Example data lock information:
mysql> SELECT * FROM performance_schema.data_locks\G
*************************** 1. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 139664434886512:1059:139664350547912
ENGINE_TRANSACTION_ID: 2569
THREAD_ID: 46
EVENT_ID: 12
OBJECT_SCHEMA: test
OBJECT_NAME: t1
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 139664350547912
LOCK_TYPE: TABLE
LOCK_MODE: IX
LOCK_STATUS: GRANTED
LOCK_DATA: NULL
*************************** 2. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 139664434886512:2:4:1:139664350544872
ENGINE_TRANSACTION_ID: 2569
THREAD_ID: 46
EVENT_ID: 12
OBJECT_SCHEMA: test
OBJECT_NAME: t1
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: GEN_CLUST_INDEX
OBJECT_INSTANCE_BEGIN: 139664350544872
LOCK_TYPE: RECORD
LOCK_MODE: X
LOCK_STATUS: GRANTED
LOCK_DATA: supremum pseudo-record
Unlike most Performance Schema data collection, there are no instruments for controlling whether data lock information is collected or system variables for controlling data lock table sizes. The Performance Schema collects information that is already available in the server, so there is no memory or CPU overhead to generate this information or need for parameters that control its collection.
Use the data_locks
table to help diagnose performance problems that occur during times of heavy concurrent load. For InnoDB
, see the discussion of this topic at Section 17.15.2, “InnoDB INFORMATION_SCHEMA Transaction and Locking Information”.
The data_locks
table has these columns:
-
ENGINE
The storage engine that holds or requested the lock.
-
ENGINE_LOCK_ID
The ID of the lock held or requested by the storage engine. Tuples of (
ENGINE_LOCK_ID
,ENGINE
) values are unique.Lock ID formats are internal and subject to change at any time. Applications should not rely on lock IDs having a particular format.
-
ENGINE_TRANSACTION_ID
The storage engine internal ID of the transaction that requested the lock. This can be considered the owner of the lock, although the lock might still be pending, not actually granted yet (
LOCK_STATUS='WAITING'
).If the transaction has not yet performed any write operation (is still considered read only), the column contains internal data that users should not try to interpret. Otherwise, the column is the transaction ID.
For
InnoDB
, to obtain details about the transaction, join this column with theTRX_ID
column of theINFORMATION_SCHEMA
INNODB_TRX
table. -
THREAD_ID
The thread ID of the session that created the lock. To obtain details about the thread, join this column with the
THREAD_ID
column of the Performance Schemathreads
table.THREAD_ID
can be used together withEVENT_ID
to determine the event during which the lock data structure was created in memory. (This event might have occurred before this particular lock request occurred, if the data structure is used to store multiple locks.) -
EVENT_ID
The Performance Schema event that caused the lock. Tuples of (
THREAD_ID
,EVENT_ID
) values implicitly identify a parent event in other Performance Schema tables:-
The parent wait event in the
events_waits_
tablesxxx
-
The parent stage event in the
events_stages_
tablesxxx
-
The parent statement event in the
events_statements_
tablesxxx
-
The parent transaction event in the
events_transactions_current
table
To obtain details about the parent event, join the
THREAD_ID
andEVENT_ID
columns with the columns of like name in the appropriate parent event table. See Section 29.19.2, “Obtaining Parent Event Information”. -
-
OBJECT_SCHEMA
The schema that contains the locked table.
-
OBJECT_NAME
The name of the locked table.
-
PARTITION_NAME
The name of the locked partition, if any;
NULL
otherwise. -
SUBPARTITION_NAME
The name of the locked subpartition, if any;
NULL
otherwise. -
INDEX_NAME
The name of the locked index, if any;
NULL
otherwise.In practice,
InnoDB
always creates an index (GEN_CLUST_INDEX
), soINDEX_NAME
is non-NULL
forInnoDB
tables. -
OBJECT_INSTANCE_BEGIN
The address in memory of the lock.
-
LOCK_TYPE
The type of lock.
The value is storage engine dependent. For
InnoDB
, permitted values areRECORD
for a row-level lock,TABLE
for a table-level lock. -
LOCK_MODE
How the lock is requested.
The value is storage engine dependent. For
InnoDB
, permitted values areS[,GAP]
,X[,GAP]
,IS[,GAP]
,IX[,GAP]
,AUTO_INC
, andUNKNOWN
. Lock modes other thanAUTO_INC
andUNKNOWN
indicate gap locks, if present. For information aboutS
,X
,IS
,IX
, and gap locks, refer to Section 17.7.1, “InnoDB Locking”. -
LOCK_STATUS
The status of the lock request.
The value is storage engine dependent. For
InnoDB
, permitted values areGRANTED
(lock is held) andWAITING
(lock is being waited for). -
LOCK_DATA
The data associated with the lock, if any. The value is storage engine dependent. For
InnoDB
, a value is shown if theLOCK_TYPE
isRECORD
, otherwise the value isNULL
. Primary key values of the locked record are shown for a lock placed on the primary key index. Secondary index values of the locked record are shown with primary key values appended for a lock placed on a secondary index. If there is no primary key,LOCK_DATA
shows either the key values of a selected unique index or the uniqueInnoDB
internal row ID number, according to the rules governingInnoDB
clustered index use (see Section 17.6.2.1, “Clustered and Secondary Indexes”).LOCK_DATA
reports “supremum pseudo-record” for a lock taken on a supremum pseudo-record. If the page containing the locked record is not in the buffer pool because it was written to disk while the lock was held,InnoDB
does not fetch the page from disk. Instead,LOCK_DATA
reportsNULL
.
The data_locks
table has these indexes:
-
Primary key on (
ENGINE_LOCK_ID
,ENGINE
) -
Index on (
ENGINE_TRANSACTION_ID
,ENGINE
) -
Index on (
THREAD_ID
,EVENT_ID
) -
Index on (
OBJECT_SCHEMA
,OBJECT_NAME
,PARTITION_NAME
,SUBPARTITION_NAME
)
TRUNCATE TABLE
is not permitted for the data_locks
table.