mysql8.0锁观测模式分析

data_lock_waits | 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,

得到可能的死锁事务语句后，我们接下来模拟事务语句执行顺序，通过performance_schema.data_locks和performance_schema.data_lock_waits观察语句执行时持有/等待锁信息进行死锁验证。
data_locks之前已经说过，我们现在介绍data_lock_waits各字段的含义：

• REQUESTING_ENGINE_LOCK_ID: 等待获取锁的锁ID。

• REQUESTING_ENGINE_TRANSACTION_ID： 等待获取锁所在的事务ID。

• REQUESTING_THREAD_ID： 等待获取锁所在线程ID。

• BLOCKING_ENGINE_LOCK_ID： 导致阻塞的锁ID，对应data_locks表的ENGINE_LOCK_ID列。

• BLOCKING_ENGINE_TRANSACTION_ID：导致阻塞的事务ID。

• BLOCKING_THREAD_ID：导致阻塞的线程ID。

每一行可以理解为事务REQUESTING_ENGINE_TRANSACTION等待事务BLOCKING_ENGINE_TRANSACTION释放BLOCKING_ENGINE_LOCK锁，以便获取REQUESTING_ENGINE_LOCK锁。

使用存储过程构建临时的数据

delimiter ;;
create procedure idata()
begin
  START TRANSACTION;
  declare i int;
  set i=1;
  while(i<=100000)do
    insert into t values(i,i);
    set i=i+1;
  end while;
  commit;
end;;
delimiter;

delimiter ;;
create procedure idata()
begin
  declare i int;
  set i=1;
  START TRANSACTION;
  while(i<=100000)do
    insert into t values(i, i);
    set i=i+1;
  end while;
  commit;
end;;
delimiter ;
call idata();

常用来定位锁相关问题的表

schema_table_lock_waits

设置隔离级别

临时测试-使用会话隔离级别
永久测试-使用全局隔离级别—但是不针对当前会话， 需要重新建立链接才生效

查看MySQL隔离级别：

# 查看全局隔离级别
select @@global.transaction_isolation;
# 查看会话隔离级别
select @@session.transaction_isolation;
设置全局隔离级别：

set global transaction isolation level read uncommitted;
set global transaction isolation level read committed;
set global transaction isolation level repeatable read;
set global transaction isolation level serializable;

设置会话隔离级别：

set session transaction isolation level read uncommitted;
set session transaction isolation level read committed;
set session transaction isolation level repeatable read;
set session transaction isolation level serializable;

优秀文章

1: https://mp.weixin.qq.com/s/o-hRGAFYgqGxbG6h63Ukag（有锁相关的实验）