MySQL 行锁观察模式8.0
2022/9/6 2:22:49
本文主要是介绍MySQL 行锁观察模式8.0,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!
全新的MySQL 8.0新增了全新的锁观测方式,在performance_schema下新增了data_locks表和data_lock_waits表
mysql> show tables like '%data_lock%';+--------------------------------------------+| Tables_in_performance_schema (%data_lock%) |+--------------------------------------------+| data_lock_waits || data_locks || metadata_locks |+--------------------------------------------+3 rows in set (0.01 sec)
1.
data_locks表
mysql> show create table data_locks\G*************************** 1. row *************************** Table: data_locksCreate Table: CREATE TABLE `data_locks` ( `ENGINE` varchar(32) NOT NULL, `ENGINE_LOCK_ID` varchar(128) NOT NULL, `ENGINE_TRANSACTION_ID` bigint unsigned DEFAULT NULL, `THREAD_ID` bigint unsigned DEFAULT NULL, `EVENT_ID` bigint unsigned DEFAULT NULL, `OBJECT_SCHEMA` varchar(64) DEFAULT NULL, `OBJECT_NAME` varchar(64) DEFAULT NULL, `PARTITION_NAME` varchar(64) DEFAULT NULL, `SUBPARTITION_NAME` varchar(64) DEFAULT NULL, `INDEX_NAME` varchar(64) DEFAULT NULL, `OBJECT_INSTANCE_BEGIN` bigint unsigned NOT NULL, `LOCK_TYPE` varchar(32) NOT NULL, `LOCK_MODE` varchar(32) NOT NULL, `LOCK_STATUS` varchar(32) NOT NULL, `LOCK_DATA` varchar(8192) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL, PRIMARY KEY (`ENGINE_LOCK_ID`,`ENGINE`), KEY `ENGINE_TRANSACTION_ID` (`ENGINE_TRANSACTION_ID`,`ENGINE`), KEY `THREAD_ID` (`THREAD_ID`,`EVENT_ID`), KEY `OBJECT_SCHEMA` (`OBJECT_SCHEMA`,`OBJECT_NAME`,`PARTITION_NAME`,`SUBPARTITION_NAME`)) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci1 row in set (0.00 sec)
1.
说明:
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表
mysql> show create table data_lock_waits\G*************************** 1. row *************************** Table: data_lock_waitsCreate Table: CREATE TABLE `data_lock_waits` ( `ENGINE` varchar(32) NOT NULL, `REQUESTING_ENGINE_LOCK_ID` varchar(128) NOT NULL, `REQUESTING_ENGINE_TRANSACTION_ID` bigint unsigned DEFAULT NULL, `REQUESTING_THREAD_ID` bigint unsigned DEFAULT NULL, `REQUESTING_EVENT_ID` bigint unsigned DEFAULT NULL, `REQUESTING_OBJECT_INSTANCE_BEGIN` bigint unsigned NOT NULL, `BLOCKING_ENGINE_LOCK_ID` varchar(128) NOT NULL, `BLOCKING_ENGINE_TRANSACTION_ID` bigint unsigned DEFAULT NULL, `BLOCKING_THREAD_ID` bigint unsigned DEFAULT NULL, `BLOCKING_EVENT_ID` bigint unsigned DEFAULT NULL, `BLOCKING_OBJECT_INSTANCE_BEGIN` bigint unsigned NOT NULL, KEY `REQUESTING_ENGINE_LOCK_ID` (`REQUESTING_ENGINE_LOCK_ID`,`ENGINE`), KEY `BLOCKING_ENGINE_LOCK_ID` (`BLOCKING_ENGINE_LOCK_ID`,`ENGINE`), KEY `REQUESTING_ENGINE_TRANSACTION_ID` (`REQUESTING_ENGINE_TRANSACTION_ID`,`ENGINE`), KEY `BLOCKING_ENGINE_TRANSACTION_ID` (`BLOCKING_ENGINE_TRANSACTION_ID`,`ENGINE`), KEY `REQUESTING_THREAD_ID` (`REQUESTING_THREAD_ID`,`REQUESTING_EVENT_ID`), KEY `BLOCKING_THREAD_ID` (`BLOCKING_THREAD_ID`,`BLOCKING_EVENT_ID`)) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci1 row in set (0.00 sec)
1.
说明:
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表
mysql> select * from data_locks;+--------+------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+| 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 |+--------+------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+| INNODB | 2512491671792:1088:2512457467624 | 5036 | 445 | 47 | xucl | t1 | NULL | NULL | NULL | 2512457467624 | TABLE | IX | GRANTED | NULL || INNODB | 2512491671792:31:4:2:2512457464840 | 5036 | 445 | 47 | xucl | t1 | NULL | NULL | PRIMARY | 2512457464840 | RECORD | X,REC_NOT_GAP | WAITING | 1 || INNODB | 2512491670944:1088:2512457462648 | 5035 | 444 | 15 | xucl | t1 | NULL | NULL | NULL | 2512457462648 | TABLE | IX | GRANTED | NULL || INNODB | 2512491670944:31:4:2:2512457459864 | 5035 | 444 | 15 | xucl | t1 | NULL | NULL | PRIMARY | 2512457459864 | RECORD | X,REC_NOT_GAP | GRANTED | 1 |+--------+------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+4 rows in set (0.00 sec)mysql> select * from data_lock_waits;+--------+------------------------------------+----------------------------------+----------------------+---------------------+----------------------------------+------------------------------------+--------------------------------+--------------------+-------------------+--------------------------------+| 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 |+--------+------------------------------------+----------------------------------+----------------------+---------------------+----------------------------------+------------------------------------+--------------------------------+--------------------+-------------------+--------------------------------+| INNODB | 2512491671792:31:4:2:2512457464840 | 5036 | 445 | 47 | 2512457464840 | 2512491670944:31:4:2:2512457459864 | 5035 | 444 | 15 | 2512457459864 |+--------+------------------------------------+----------------------------------+----------------------+---------------------+----------------------------------+------------------------------------+--------------------------------+--------------------+-------------------+--------------------------------+1 row in set (0.00 sec)
1.
分析:
从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表
mysql> select * from data_locks;+--------+------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+| 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 |+--------+------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+| INNODB | 2512491671792:1088:2512457467624 | 5068 | 445 | 50 | xucl | t1 | NULL | NULL | NULL | 2512457467624 | TABLE | IX | GRANTED | NULL || INNODB | 2512491671792:31:5:5:2512457464840 | 5068 | 445 | 50 | xucl | t1 | NULL | NULL | idx_c1 | 2512457464840 | RECORD | X | WAITING | 'd', 4 || INNODB | 2512491670944:1088:2512457462648 | 5067 | 444 | 39 | xucl | t1 | NULL | NULL | NULL | 2512457462648 | TABLE | IX | GRANTED | NULL || INNODB | 2512491670944:31:5:5:2512457459864 | 5067 | 444 | 39 | xucl | t1 | NULL | NULL | idx_c1 | 2512457459864 | RECORD | X | GRANTED | 'd', 4 || INNODB | 2512491670944:31:4:7:2512457460208 | 5067 | 444 | 39 | xucl | t1 | NULL | NULL | PRIMARY | 2512457460208 | RECORD | X,REC_NOT_GAP | GRANTED | 4 || INNODB | 2512491670944:31:5:6:2512457460552 | 5067 | 444 | 39 | xucl | t1 | NULL | NULL | idx_c1 | 2512457460552 | RECORD | X,GAP | GRANTED | 'e', 5 |+--------+------------------------------------+-----------------------+-----------+----------+---------------+-------------+----------------+-------------------+------------+-----------------------+-----------+---------------+-------------+-----------+6 rows in set (0.00 sec)mysql> select * from data_lock_waits;+--------+------------------------------------+----------------------------------+----------------------+---------------------+----------------------------------+------------------------------------+--------------------------------+--------------------+-------------------+--------------------------------+| 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 |+--------+------------------------------------+----------------------------------+----------------------+---------------------+----------------------------------+------------------------------------+--------------------------------+--------------------+-------------------+--------------------------------+| INNODB | 2512491671792:31:5:5:2512457464840 | 5068 | 445 | 50 | 2512457464840 | 2512491670944:31:5:5:2512457459864 | 5067 | 444 | 39 | 2512457459864 |+--------+------------------------------------+----------------------------------+----------------------+---------------------+----------------------------------+------------------------------------+--------------------------------+--------------------+-------------------+--------------------------------+1 row in set (0.00 sec)
1.
分析:
从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,类型为排他类型
总结一下:
区别于之前的通过innodb_lock_waits的方式,即便没有产生锁等待,data_locks也能显示出已经加锁的行,另外隐式锁能够显示,这对于DBA分析锁来说无疑是非常有帮助的,相信有了这两张表的加持,DBA分析锁能够更加得心应手。
-----------------------------------
全新的MySQL 8.0行锁观测方式
https://blog.51cto.com/imysql/3182006
这篇关于MySQL 行锁观察模式8.0的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!
- 2024-11-04部署MySQL集群项目实战:新手入门教程
- 2024-11-04如何部署MySQL集群资料:新手入门指南
- 2024-11-02MySQL集群项目实战:新手入门指南
- 2024-11-02初学者指南:部署MySQL集群资料
- 2024-11-01部署MySQL集群教程:新手入门指南
- 2024-11-01如何部署MySQL集群:新手入门教程
- 2024-11-01部署MySQL集群学习:新手入门教程
- 2024-11-01部署MySQL集群入门:新手必读指南
- 2024-10-23BinLog入门:新手必读的MySQL二进制日志指南
- 2024-10-23Binlog入门:MySQL数据库的日志管理指南