深入理解MySQL锁 （S、X、IS、IX）RC模式下解析

一、定义：

S锁：共享锁，行级锁，已加S锁的行，不允许其它事务再加X锁，但可以继续加S锁
X锁：排它锁，行级锁，已加X锁的行，不允行其它事务再加S或X锁

IS:意向共享锁，表级锁，已加S锁的表，肯定会有IS锁，反过来，有IS锁的表，不一定会有S锁
IX:意向排它锁，表级锁，已加X锁的表，肯定会有IX锁，反过来，有IX锁的表，不一定会有X锁

二、准备工作

我使用的MySQL版本为8.0.26
我使用的是MySQL Workbench 开两个连接，并确保隔离级别为RC,因这篇文章主要讲RC

所以请注意，后面讲产生的锁的情况如果没有特别标注，都是在RC隔离级别下。

先查看

select @@global.transaction_isolation,@@transaction_isolation;
复制

查看结果
REPEATABLE-READ REPEATABLE-READ

将当前session隔离级别改为 RC

set session transaction isolation level  read committed;
复制

三、测试数据

为了读者可复现，我这里先构造数据

  CREATE TABLE `test_store` (
  `tid` int(10) unsigned NOT NULL,
  `storeid` int(10) unsigned NOT NULL COMMENT '仓库ID',
  `storecode` varchar(50) NOT NULL COMMENT '仓库编号',
  PRIMARY KEY (`tid`,`storeid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into  test_store
select 1,2,'bb'
union all 
select 1,1,'AA';

CREATE TABLE `test_goodsbound` (
  `tid` int(10) unsigned NOT NULL,
  `goodsid` int(10) unsigned NOT NULL,
  `storeid` int(10) unsigned NOT NULL,
  PRIMARY KEY (`tid`,`goodsid`,`storeid`),
  KEY `fk_goodsbound_storeid` (`tid`,`storeid`),
  CONSTRAINT `fk_goodsbound_storeid` FOREIGN KEY (`tid`, `storeid`) REFERENCES `test_store` (`tid`, `storeid`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8; 

insert into test_goodsbound
select 1,1,1 union all
select 1,2,1 union all
select 1,3,1;

复制

四、产生锁的行为

什么行为会产生S锁?

1、主动 在select 后添 加 lock in share mode

begin;
  select 1 from test_goodsbound where  tid = 1   lock in share mode
复制

然后查看锁的情况，如下图

select * from performance_schema.data_locks;
复制

可以看到在相关的上加了S锁，表上也有了IS锁

2、对含有外键的表进行修改，会让外键对应的表加上S锁

参看上面的表结构，test_goodsbound 表存在一个外键 fk_goodsbound_storeid

begin;
  update test_goodsbound set goodsid =10 where  tid = 1  and  goodsid = 3
复制

然后查看锁的情况，如下图

select * from performance_schema.data_locks;
复制

看第4行数据，test_store表 的tid = 1 storeid = 1的行 添加了S锁、当然第一行也对 test_store表添加了 IS锁

外键只有insert,update表才会对外键数据所对应的主键表所在的行 增加 S锁。

delete 不会产生

begin;
  delete from test_goodsbound
复制

然后查看锁的情况，如下图

select * from performance_schema.data_locks;
复制

3、在RR上还有一次情况会产生S锁

先将改隔级别改为 RR

set session transaction isolation level  REPEATABLE READ 
复制

执行下列语句

INSERT INTO test_goodsbound2(tid,storeid,goodsid)
select tid,storeid,goodsid from
(
select tid,storeid,goodsid,sleep(10)  from test_goodsbound limit 100) as t 
复制

在另一个连接上查看锁的情况

select * from performance_schema.data_locks;
复制

隔10秒后，再执行

什么行为会产生X锁?

1、主动 在select 后添加 for update

begin;
select * from test_goodsbound where tid = 1 and goodsid = 1  for update 
复制

然后查看锁的情况，如下图

select * from performance_schema.data_locks;
复制

如果所涉及的查询或修改走了二级索引，则会连二级索引一起上锁。

explain 
select * from test_goodsbound where tid = 1 and storeid = 1  for update 
复制

可以看到 这个select语句，走了二级索引

begin;
select * from test_goodsbound where tid = 1 and storeid = 1  for update 
复制

然后查看锁的情况，如下图

select * from performance_schema.data_locks;
复制

会看到，主键索引与二级索引相关行上都添加了X锁

2、执行 insert,update,delete语句，对所涉及的行

begin;
delete tg from test_goodsbound as tg  force index(fk_goodsbound_storeid ) where tid = 1 and storeid = 1 
复制

我这里强制走了二级索引 fk_goodsbound_storeid
然后查看锁的情况，如下图

select * from performance_schema.data_locks;
复制

五、兼容与互斥

贴一个兼容表格

我对红框中的互斥做一个试验

先产生一个只有IS锁的 事务

begin;
select * from test_goodsbound where tid = 1 and storeid = 2  lock in share mode
复制

然后查看锁的情况，如下图

select * from performance_schema.data_locks;
复制

打开另一个连接，执行

 insert into test_goodsbound
values(1,1,2);

复制

再执行

delete from  test_goodsbound where tid = 1 and storeid = 2
复制

都是可以执行的。

我们再来检查一下，IX与X与S的互斥

begin;
select * from test_goodsbound where tid = 1 and storeid = 2   for update  
复制

然后查看锁的情况，如下图

select * from performance_schema.data_locks;
复制

同样，在另一个连接中去执行

打开另一个连接，执行

 insert into test_goodsbound
values(1,1,2);

复制

再执行

delete from  test_goodsbound where tid = 1 and storeid = 2
复制

都是可以执行的。

IX与表级S锁与X锁是互斥的，与行级S与X是不互斥的
IS与表级X锁是互斥的，与行级S与X是不互斥的

另外的互斥，我就不一一试验了。

如文章中有问题，也欢迎大家指正。谢谢。

欢迎大家关注我的微信公众号，分享MySQL相关知识