全新的MySQL 8.0新增了全新的锁观测方式，在performance_schema下新增了data_locks表和data_lock_waits表

  1. mysql> show tables like '%data_lock%';
  2. +--------------------------------------------+
  3. | Tables_in_performance_schema (%data_lock%) |
  4. +--------------------------------------------+
  5. | data_lock_waits                            |
  6. | data_locks                                 |
  7. | metadata_locks                             |
  8. +--------------------------------------------+
  9. 3 rows in set (0.01 sec)

data_locks表

  1. mysql> show create table data_locks\G
  2. *************************** 1. row ***************************
  3.        Table: data_locks
  4. Create Table: CREATE TABLE `data_locks` (
  5.   `ENGINE` varchar(32) NOT NULL,
  6.   `ENGINE_LOCK_ID` varchar(128) NOT NULL,
  7.   `ENGINE_TRANSACTION_ID` bigint unsigned DEFAULT NULL,
  8.   `THREAD_ID` bigint unsigned DEFAULT NULL,
  9.   `EVENT_ID` bigint unsigned DEFAULT NULL,
  10.   `OBJECT_SCHEMA` varchar(64) DEFAULT NULL,
  11.   `OBJECT_NAME` varchar(64) DEFAULT NULL,
  12.   `PARTITION_NAME` varchar(64) DEFAULT NULL,
  13.   `SUBPARTITION_NAME` varchar(64) DEFAULT NULL,
  14.   `INDEX_NAME` varchar(64) DEFAULT NULL,
  15.   `OBJECT_INSTANCE_BEGIN` bigint unsigned NOT NULL,
  16.   `LOCK_TYPE` varchar(32) NOT NULL,
  17.   `LOCK_MODE` varchar(32) NOT NULL,
  18.   `LOCK_STATUS` varchar(32) NOT NULL,
  19.   `LOCK_DATA` varchar(8192) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  20.   PRIMARY KEY (`ENGINE_LOCK_ID`,`ENGINE`),
  21.   KEY `ENGINE_TRANSACTION_ID` (`ENGINE_TRANSACTION_ID`,`ENGINE`),
  22.   KEY `THREAD_ID` (`THREAD_ID`,`EVENT_ID`),
  23.   KEY `OBJECT_SCHEMA` (`OBJECT_SCHEMA`,`OBJECT_NAME`,`PARTITION_NAME`,`SUBPARTITION_NAME`)
  24. ) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
  25. 1 row in set (0.00 sec)

说明：

  • ENGINE：持有或请求锁定的存储引擎
  • ENGINE_LOCK_ID：存储引擎持有或请求的锁的ID，锁ID格式是内部的，随时可能更改。
  • ENGINE_TRANSACTION_ID：请求锁定的事务存储引擎内部ID，可以将其视为锁的所有者
  • THREAD_ID：对应事务的线程ID，如果需要获取更详细的信息，需要关联threads表的THREAD_ID
  • EVENT_ID：指明造成锁的EVENT_ID，THREAD_ID+EVENT_ID对应parent EVENT，可以在以下几张表内获得信息
    • events_waits_xx表查看等待事件
    • events_stages_xxx查看到了哪个阶段
    • events_statements_xx表查看对应的SQL语句
    • events_transactions_current对应查看事务信息
  • OBJECT_SCHEMA：对应锁表的schema名称
  • OBJECT_NAME：对应锁的表名
  • PARTITION_NAME：对应锁的分区名
  • SUBPARTITION_NAME：对应锁的子分区名
  • INDEX_NAME：锁对应的索引名称，InnoDB表不会为NULL
  • OBJECT_INSTANCE_BEGIN：锁对应的内存地址
  • LOCK_TYPE：对应的锁类型，对InnoDB而言，可为表锁或者行锁
  • LOCK_MODE：锁模式，对应值可能为S[,GAP], X[, GAP], IS[,GAP], IX[,GAP], AUTO_INC和UNKNOWN
  • LOCK_STATUS：锁状态，可能为GRANTED或者WAITING
  • LOCK_DATA：锁对应的数据，例如如果锁定的是主键，那么该列对应的就是加锁的主键值

data_lock_waits表

  1. mysql> show create table data_lock_waits\G
  2. *************************** 1. row ***************************
  3.        Table: data_lock_waits
  4. Create Table: CREATE TABLE `data_lock_waits` (
  5.   `ENGINE` varchar(32) NOT NULL,
  6.   `REQUESTING_ENGINE_LOCK_ID` varchar(128) NOT NULL,
  7.   `REQUESTING_ENGINE_TRANSACTION_ID` bigint unsigned DEFAULT NULL,
  8.   `REQUESTING_THREAD_ID` bigint unsigned DEFAULT NULL,
  9.   `REQUESTING_EVENT_ID` bigint unsigned DEFAULT NULL,
  10.   `REQUESTING_OBJECT_INSTANCE_BEGIN` bigint unsigned NOT NULL,
  11.   `BLOCKING_ENGINE_LOCK_ID` varchar(128) NOT NULL,
  12.   `BLOCKING_ENGINE_TRANSACTION_ID` bigint unsigned DEFAULT NULL,
  13.   `BLOCKING_THREAD_ID` bigint unsigned DEFAULT NULL,
  14.   `BLOCKING_EVENT_ID` bigint unsigned DEFAULT NULL,
  15.   `BLOCKING_OBJECT_INSTANCE_BEGIN` bigint unsigned NOT NULL,
  16.   KEY `REQUESTING_ENGINE_LOCK_ID` (`REQUESTING_ENGINE_LOCK_ID`,`ENGINE`),
  17.   KEY `BLOCKING_ENGINE_LOCK_ID` (`BLOCKING_ENGINE_LOCK_ID`,`ENGINE`),
  18.   KEY `REQUESTING_ENGINE_TRANSACTION_ID` (`REQUESTING_ENGINE_TRANSACTION_ID`,`ENGINE`),
  19.   KEY `BLOCKING_ENGINE_TRANSACTION_ID` (`BLOCKING_ENGINE_TRANSACTION_ID`,`ENGINE`),
  20.   KEY `REQUESTING_THREAD_ID` (`REQUESTING_THREAD_ID`,`REQUESTING_EVENT_ID`),
  21.   KEY `BLOCKING_THREAD_ID` (`BLOCKING_THREAD_ID`,`BLOCKING_EVENT_ID`)
  22. ) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
  23. 1 row in set (0.00 sec)

说明：

  • ENGINE：请求的锁的引擎
  • REQUESTING_ENGINE_LOCK_ID：请求的锁在存储引擎中的锁ID
  • REQUESTING_ENGINE_TRANSACTION_ID：请求锁的事务对应的事务ID
  • REQUESTING_THREAD_ID：请求锁的线程ID
  • REQUESTING_EVENT_ID：请求锁的EVENT ID
  • REQUESTING_OBJECT_INSTANCE_BEGIN：请求的锁的内存地址
  • BLOCKING_ENGINE_LOCK_ID：阻塞的锁的ID，对应data_locks表的ENGINE_LOCK_ID列
  • BLOCKING_ENGINE_TRANSACTION_ID：锁阻塞的事务ID
  • BLOCKING_THREAD_ID：锁阻塞的线程ID
  • BLOCKING_EVENT_ID：锁阻塞的EVENT ID
  • BLOCKING_OBJECT_INSTANCE_BEGIN：阻塞的锁内存地址

我们找几个案例来看一下：
主键|Lock_X

session1 session2
begin; begin;
update t1 set c1=’b’ where id=1;
update t1 set c1=’c’ where id=1;等待

查看data_locks表和data_lock_waits表

  1. mysql> select * from data_locks;
  2. +--------+------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+
  3. | ENGINE | ENGINE_LOCK_ID                     | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE     | LOCK_STATUS | LOCK_DATA |
  4. +--------+------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+
  5. | INNODB | 2512491671792:1088:2512457467624   |                  5036 |       445 |       47 | xucl          | t1          | NULL           | NULL              | NULL       |         2512457467624 | TABLE     | IX            | GRANTED     | NULL      |
  6. | INNODB | 2512491671792:31:4:2:2512457464840 |                  5036 |       445 |       47 | xucl          | t1          | NULL           | NULL              | PRIMARY    |         2512457464840 | RECORD    | X,REC_NOT_GAP | WAITING     | 1         |
  7. | INNODB | 2512491670944:1088:2512457462648   |                  5035 |       444 |       15 | xucl          | t1          | NULL           | NULL              | NULL       |         2512457462648 | TABLE     | IX            | GRANTED     | NULL      |
  8. | INNODB | 2512491670944:31:4:2:2512457459864 |                  5035 |       444 |       15 | xucl          | t1          | NULL           | NULL              | PRIMARY    |         2512457459864 | RECORD    | X,REC_NOT_GAP | GRANTED     | 1         |
  9. +--------+------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+
  10. 4 rows in set (0.00 sec)
  12. mysql> select * from data_lock_waits;
  13. +--------+------------------------------------+----------------------------------+----------------------+---------------------+----------------------------------+------------------------------------+--------------------------------+--------------------+-------------------+--------------------------------+
  14. | ENGINE | REQUESTING_ENGINE_LOCK_ID          | REQUESTING_ENGINE_TRANSACTION_ID | REQUESTING_THREAD_ID | REQUESTING_EVENT_ID | REQUESTING_OBJECT_INSTANCE_BEGIN | BLOCKING_ENGINE_LOCK_ID            | BLOCKING_ENGINE_TRANSACTION_ID | BLOCKING_THREAD_ID | BLOCKING_EVENT_ID | BLOCKING_OBJECT_INSTANCE_BEGIN |
  15. +--------+------------------------------------+----------------------------------+----------------------+---------------------+----------------------------------+------------------------------------+--------------------------------+--------------------+-------------------+--------------------------------+
  16. | INNODB | 2512491671792:31:4:2:2512457464840 |                             5036 |                  445 |                  47 |                    2512457464840 | 2512491670944:31:4:2:2512457459864 |                                                      5035 |                444 |                15 |                  2512457459864 |
  17. +--------+------------------------------------+----------------------------------+----------------------+---------------------+----------------------------------+------------------------------------+--------------------------------+--------------------+-------------------+--------------------------------+
  18. 1 row in set (0.00 sec)

分析：

  • 从data_locks表可以看出，线程ID为444的会话持有了xucl.t1表的id=1的主键排他记录锁，和表级别的IX锁
  • 结合data_lock_waits表可以看出，线程ID为445的会话等待xucl.t1表的主键上的排他记录锁

二级索引|next-key lock

session1 session2
begin; begin;
update c2=’a’ where c1=’d’;
update c2=’a’ where c1=’d’;等待

查看data_locks表和data_lock_waits表

  1. mysql> select * from data_locks;
  2. +--------+------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+
  3. | ENGINE | ENGINE_LOCK_ID                     | ENGINE_TRANSACTION_ID | THREAD_ID | EVENT_ID | OBJECT_SCHEMA | OBJECT_NAME | PARTITION_NAME | SUBPARTITION_NAME | INDEX_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_MODE     | LOCK_STATUS | LOCK_DATA |
  4. +--------+------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+
  5. | INNODB | 2512491671792:1088:2512457467624   |                  5068 |       445 |       50 | xucl          | t1          | NULL           | NULL              | NULL       |         2512457467624 | TABLE     | IX            | GRANTED     | NULL      |
  6. | INNODB | 2512491671792:31:5:5:2512457464840 |                  5068 |       445 |       50 | xucl          | t1          | NULL           | NULL              | idx_c1     |         2512457464840 | RECORD    | X             | WAITING     | 'd', 4    |
  7. | INNODB | 2512491670944:1088:2512457462648   |                  5067 |       444 |       39 | xucl          | t1          | NULL           | NULL              | NULL       |         2512457462648 | TABLE     | IX            | GRANTED     | NULL      |
  8. | INNODB | 2512491670944:31:5:5:2512457459864 |                  5067 |       444 |       39 | xucl          | t1          | NULL           | NULL              | idx_c1     |         2512457459864 | RECORD    | X             | GRANTED     | 'd', 4    |
  9. | INNODB | 2512491670944:31:4:7:2512457460208 |                  5067 |       444 |       39 | xucl          | t1          | NULL           | NULL              | PRIMARY    |         2512457460208 | RECORD    | X,REC_NOT_GAP | GRANTED     | 4         |
  10. | INNODB | 2512491670944:31:5:6:2512457460552 |                  5067 |       444 |       39 | xucl          | t1          | NULL           | NULL              | idx_c1     |         2512457460552 | RECORD    | X,GAP         | GRANTED     | 'e', 5    |
  11. +--------+------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+
  12. 6 rows in set (0.00 sec)
  14. mysql> select * from data_lock_waits;
  15. +--------+------------------------------------+----------------------------------+----------------------+---------------------+----------------------------------+------------------------------------+--------------------------------+--------------------+-------------------+--------------------------------+
  16. | ENGINE | REQUESTING_ENGINE_LOCK_ID          | REQUESTING_ENGINE_TRANSACTION_ID | REQUESTING_THREAD_ID | REQUESTING_EVENT_ID | REQUESTING_OBJECT_INSTANCE_BEGIN | BLOCKING_ENGINE_LOCK_ID            | BLOCKING_ENGINE_TRANSACTION_ID | BLOCKING_THREAD_ID | BLOCKING_EVENT_ID | BLOCKING_OBJECT_INSTANCE_BEGIN |
  17. +--------+------------------------------------+----------------------------------+----------------------+---------------------+----------------------------------+------------------------------------+--------------------------------+--------------------+-------------------+--------------------------------+
  18. | INNODB | 2512491671792:31:5:5:2512457464840 |                             5068 |                  445 |                  50 |                    2512457464840 | 2512491670944:31:5:5:2512457459864 |                                                        5067 |                444 |                39 |                  2512457459864 |
  19. +--------+------------------------------------+----------------------------------+----------------------+---------------------+----------------------------------+------------------------------------+--------------------------------+--------------------+-------------------+--------------------------------+
  20. 1 row in set (0.00 sec)

分析：

  • 从data_locks表可以看到，线程ID为444的会话持有的锁有
    • xucl.t1表上的IX锁
    • 表xucl.t1索引idx_c1上的’d’,4这条记录的next-key lock（这里LOCK_MODE只显示了X表示这是next-key lock）
    • 表xucl.t1索引idx_c1上的’e’,5这条记录的GAP lock（二级索引等值条件需要扫描到第一条不满足的记录，转换成GAP Lock）
    • 表xucl.t1索引主键索引上id=4这条记录的record Lock，类型为排他
  • 结合data_lock_waits表，可以看出
    • 等待的锁为xucl.t1表上索引idx_c1上的’d’,4这条记录的next-key lock，类型为排他类型