Movatterモバイル変換

Thedata_lock_waits table implements a many-to-many relationship showing which data lock requests in thedata_locks table are blocked by which held data locks in thedata_locks table. Held locks indata_locks appear indata_lock_waits only if they block some lock request.

This information enables you to understand data lock dependencies between sessions. The table exposes not only which lock a session or transaction is waiting for, but which session or transaction currently holds that lock.

Example data lock wait information:

mysql> SELECT * FROM performance_schema.data_lock_waits\G*************************** 1. row ***************************                          ENGINE: INNODB       REQUESTING_ENGINE_LOCK_ID: 140211201964816:2:4:2:140211086465800REQUESTING_ENGINE_TRANSACTION_ID: 1555            REQUESTING_THREAD_ID: 47             REQUESTING_EVENT_ID: 5REQUESTING_OBJECT_INSTANCE_BEGIN: 140211086465800         BLOCKING_ENGINE_LOCK_ID: 140211201963888:2:4:2:140211086459880  BLOCKING_ENGINE_TRANSACTION_ID: 1554              BLOCKING_THREAD_ID: 46               BLOCKING_EVENT_ID: 12  BLOCKING_OBJECT_INSTANCE_BEGIN: 140211086459880

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_lock_waits 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”.

Because the columns in thedata_lock_waits table are similar to those in thedata_locks table, the column descriptions here are abbreviated. For more detailed column descriptions, seeSection 29.12.13.1, “The data_locks Table”.

Thedata_lock_waits table has these columns:

Thedata_lock_waits table has these indexes:

TRUNCATE TABLE is not permitted for thedata_lock_waits table.

The tables differ in the privileges required: TheINNODB_LOCK_WAITS table requires the globalPROCESS privilege. Thedata_lock_waits table requires the usual Performance Schema privilege ofSELECT on the table to be selected from.

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