Documentation Home
MySQL 9.1 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 40.4Mb
PDF (A4) - 40.5Mb
Man Pages (TGZ) - 259.5Kb
Man Pages (Zip) - 366.7Kb
Info (Gzip) - 4.1Mb
Info (Zip) - 4.1Mb


29.12.13.1 The data_locks Table

Thedata_locks table shows data locks held and requested. For information about which lock requests are blocked by which held locks, seeSection 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:139664350547912ENGINE_TRANSACTION_ID: 2569            THREAD_ID: 46             EVENT_ID: 12        OBJECT_SCHEMA: test          OBJECT_NAME: t1       PARTITION_NAME: NULL    SUBPARTITION_NAME: NULL           INDEX_NAME: NULLOBJECT_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:139664350544872ENGINE_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_INDEXOBJECT_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 thedata_locks table to help diagnose performance problems that occur during times of heavy concurrent load. ForInnoDB, see the discussion of this topic atSection 17.15.2, “InnoDB INFORMATION_SCHEMA Transaction and Locking Information”.

Thedata_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.

    ForInnoDB, to obtain details about the transaction, join this column with theTRX_ID column of theINFORMATION_SCHEMAINNODB_TRX table.

  • THREAD_ID

    The thread ID of the session that created the lock. To obtain details about the thread, join this column with theTHREAD_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 theevents_waits_xxx tables

    • The parent stage event in theevents_stages_xxx tables

    • The parent statement event in theevents_statements_xxx tables

    • The parent transaction event in theevents_transactions_current table

    To obtain details about the parent event, join theTHREAD_ID andEVENT_ID columns with the columns of like name in the appropriate parent event table. SeeSection 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. ForInnoDB, 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. ForInnoDB, 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 toSection 17.7.1, “InnoDB Locking”.

  • LOCK_STATUS

    The status of the lock request.

    The value is storage engine dependent. ForInnoDB, 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. ForInnoDB, 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 (seeSection 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.

Thedata_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 thedata_locks table.