MySQL :: MySQL 8.0 Reference Manual :: 25.11.12.1 The data

MySQL 8.0 Reference Manual / ... / The data_locks Table

Pre-General Availability Draft: 2017-07-17

25.11.12.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 25.11.12.2, “The data_lock_waits Table”.

Example data lock information:

mysql> SELECT * FROM data_locks\G
*************************** 1. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 4140:74
ENGINE_TRANSACTION_ID: 4140
            THREAD_ID: 37
             EVENT_ID: 9
        OBJECT_SCHEMA: test
          OBJECT_NAME: t1
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140489308280888
            LOCK_TYPE: TABLE
            LOCK_MODE: IX
          LOCK_STATUS: GRANTED
            LOCK_DATA: NULL
*************************** 2. row ***************************
               ENGINE: INNODB
       ENGINE_LOCK_ID: 4140:66:5:1
ENGINE_TRANSACTION_ID: 4140
            THREAD_ID: 37
             EVENT_ID: 9
        OBJECT_SCHEMA: test
          OBJECT_NAME: t1
       PARTITION_NAME: NULL
    SUBPARTITION_NAME: NULL
           INDEX_NAME: GEN_CLUST_INDEX
OBJECT_INSTANCE_BEGIN: 140489320307736
            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 15.14.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.
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 that owns the lock. To obtain details about the thread, join this column with the THREAD_ID column of the Performance Schema threads table.
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 events_waits_xxx tables
- The parent stage event in events_stages_xxx tables
- The parent statement event in events_statements_xxx tables
- The parent transaction event in events_transactions_xxx tables
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.
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 15.5.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 PENDING (lock is being waited for).
LOCK_DATA:
The data associated with the lock, if any.
The value is storage engine dependent. For InnoDB, values are primary key values of the locked record if LOCK_TYPE is RECORD, otherwise NULL. This column contains the values of the primary key columns in the locked row, formatted as a valid SQL string (ready to be copied to SQL statements). If there is no primary key, LOCK_DATA is the unique InnoDB internal row ID number. If a gap lock is taken for key values or ranges above the largest value in the index, LOCK_DATA reports supremum pseudo-record. When the page containing the locked record is not in the buffer pool (in the case that it was paged out to disk while the lock was held), InnoDB does not fetch the page from disk, to avoid unnecessary disk operations. Instead, LOCK_DATA is set to 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.

PREV HOME UP NEXT

User Comments