29.12.13.1 The data_locks Table

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 the TRX_ID column of the INFORMATION_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 Schema threads table.

  THREAD_ID can be used together with EVENT_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_xxx tables

  • The parent stage event in the events_stages_xxx tables

  • The parent statement event in the events_statements_xxx tables

  • The parent transaction event in the events_transactions_current table

  To obtain details about the parent event, join the THREAD_ID and EVENT_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), so INDEX_NAME is non-NULL for InnoDB 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 are RECORD 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 are S[,GAP], X[,GAP], IS[,GAP], IX[,GAP], AUTO_INC, and UNKNOWN. Lock modes other than AUTO_INC and UNKNOWN indicate gap locks, if present. For information about S, 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 are GRANTED (lock is held) and WAITING (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 the LOCK_TYPE is RECORD, otherwise the value is NULL. 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 unique InnoDB internal row ID number, according to the rules governing InnoDB 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 reports NULL.

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.

Note

Prior to MySQL 8.0.1, information similar to that in the Performance Schema data_locks table is available in the INFORMATION_SCHEMA.INNODB_LOCKS table, which provides information about each lock that an InnoDB transaction has requested but not yet acquired, and each lock held by a transaction that is blocking another transaction. INFORMATION_SCHEMA.INNODB_LOCKS is deprecated and is removed as of MySQL 8.0.1. data_locks should be used instead.

Differences between INNODB_LOCKS and data_locks:

• If a transaction holds a lock, INNODB_LOCKS displays the lock only if another transaction is waiting for it. data_locks displays the lock regardless of whether any transaction is waiting for it.

• The data_locks table has no columns corresponding to LOCK_SPACE, LOCK_PAGE, or LOCK_REC.

• The INNODB_LOCKS table requires the global PROCESS privilege. The data_locks table requires the usual Performance Schema privilege of SELECT on the table to be selected from.

The following table shows the mapping from INNODB_LOCKS columns to data_locks columns. Use this information to migrate applications from one table to the other.

Table 29.4 Mapping from INNODB_LOCKS to data_locks Columns

INNODB_LOCKS Column	data_locks Column
LOCK_ID	ENGINE_LOCK_ID
LOCK_TRX_ID	ENGINE_TRANSACTION_ID
LOCK_MODE	LOCK_MODE
LOCK_TYPE	LOCK_TYPE
LOCK_TABLE (combined schema/table names)	OBJECT_SCHEMA (schema name), OBJECT_NAME (table name)
LOCK_INDEX	INDEX_NAME
LOCK_SPACE	None
LOCK_PAGE	None
LOCK_REC	None
LOCK_DATA	LOCK_DATA