MySQL锁杂谈

【说明】

1.MySQL版本5.7.37
2.事务隔离级别 REPEATABLE-READ
3.表结构

Create Table: CREATE TABLE `isolation_innodb` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  `money` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1
1
2
3
4
5
6

4.表数据

【验证锁等待/事务等待情况】

1.开启事务A , 在事务A中执行 select * from isolation_innodb where id <=13 for update 语句, 并未提交事务 .
2.开启事务B , 在事务B中执行 select * from isolation_innodb where id =10 for update 语句, 此时会被阻塞 .

为了能够观察锁的等待情况, 需要设置 set innodb_lock_wait_timeout=60,让等待时间稍长一些

3.执行以下SQL

SELECT  r.trx_id waiting_trx_id
       ,r.trx_mysql_thread_id waiting_thread
       ,r.trx_query waiting_query
       ,b.trx_id blocking_trx_id
       ,b.trx_mysql_thread_id blocking_thread
       ,b.trx_query blocking_query
FROM information_schema.innodb_lock_waits w
INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;
1
2
3
4
5
6
7
8
9

输出结果如下

3.1 事务A(515608)阻塞了事务B(515609), 即线程11阻塞了线程12, 线程ID与show processlist 命令显示的ID对应.
3.2 waiting_query表示被阻塞事务B的SQL
3.3 blocking_query表示阻塞事务B(515609)的SQL, 一般情况都是NULL, 因为此SQL大概率已经在事务515608中执行过了.

执行 show processlist 结果如下图

甚至可以不使用上面的关联语句, 而直接使用以下SQL
SELECT * FROM sys.innodb_lock_waits

输出结果如下

关于查看事务与锁的相关表和语句如下 :

1.查看事务执行情况
SELECT * FROM information_schema.innodb_trx;

2.查看锁的占用情况 (8.0版本之前)
SELECT * FROM information_schema.innodb_locks;
(8.0版本之后)
SELECT * FROM performance_schema.data_locks;

3.查看锁的等待情况 (8.0版本之前)
SELECT * FROM information_schema.innodb_lock_waits;
(8.0版本之后)
SELECT * FROM performance_schema.data_lock_waits;

set global innodb_status_output_locks=on;
show engine innodb status;

【查看InnoDB锁】

还是以上测试的环境, 通过 show engine innodb status 语句查看事务A加锁的情况

即事务A在isolation_innodb表上加了一把IX锁, 因为RR隔离级别默认在记录上会加Next-Key Lock锁, 如上输出所示, 事务A将 负无穷到id=20的这段区间锁住了.

既然 select * from isolation_innodb where id <=13 for update 为何要一直锁住范围到id=20呢?

查看事务B加锁的情况

事务B欲通过 select * from isolation_innodb where id=10 for update 语句给id=10的记录加X锁, 但是由于事务A已经将负无穷到id=20的这段区间锁住了, 因此事务B被阻塞.
即便是一条 insert into isolation_innodb values(19,‘F’,2000) 语句, 由于id=19在负无穷到id=20的这段区间, 因此也会被阻塞.

【非索引字段】
事务1
select * from isolation_innodb where name=‘C’ for update; 或 delete from isolation_innodb where name=‘C’; 由于name字段上没有索引, 因此会在主键索引上, 负无穷到正无穷加锁.

事务2
insert into isolation_innodb values(39,‘G’,2000) 会被阻塞

【主键索引字段加锁,锁定多条数据】
事务1
delete from isolation_innodb where id<=20;

游标会逐条判断, 当id=27时才不满足id<=20, 因此加锁范围是负无穷到id=27

事务2
insert into isolation_innodb values(15,‘G’,2000);
insert into isolation_innodb values(25,‘G’,2000); 都会被阻塞

【非聚簇索引字段加锁,锁定多条数据】
表结构

Create Table: CREATE TABLE `t_good` (
  `id` int(11) NOT NULL,
  `good_no` varchar(20) DEFAULT NULL,
  `unit` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_good_no` (`good_no`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1
2
3
4
5
6
7

表数据





在事务A中执行 delete from t_good where good_no=‘G0003’ 语句, 那么会在主键索引的id=17上加一把记录锁,
在二级索引 idx_good_no 的 (‘G0002’,‘G0003’] 和 (‘G0003’,‘G0004’)范围加锁, 只有这样的话, 才可以防止幻读, 因此执行以下语句均会被阻塞

insert into t_good values(27,‘G00021’,‘mil’);
insert into t_good values(20,‘G00031’,‘mil’);
insert into t_good values(27,‘G00031’,‘mil’);
delete from t_good where id=17;

再看一点, 由于在good_no='G0004’记录上并没有加锁, 因此执行 insert into t_good values(22,‘G0004’,‘mil’) 插入操作也是可以的, 而且 idx_good_no 索引只是一个普通索引, 并不是唯一索引, 因此good_no='G0004’的记录是可以重复的.但是执行 insert into t_good values(19,‘G0004’,‘mil’) 就会被阻塞, 因为二级索引的结构类似(good_no,id), 在good_no的 (‘G0002’,‘G0003’] 和 (‘G0003’,‘G0004’)范围加锁, 同时也意味着锁住了id在 (15,17] 和 (17,21)的范围. 准确的加锁范围如下图

红色箭头表示不允许插入的数据, 绿色箭头表示允许插入的数据.

https://www.yuque.com/infuq/others/ivmss0

锁是加在索引上的

REPEATABLE-READ 隔离级别下的当前读通过加锁的方式解决幻读