记录mysql的间隙锁误用

最新推荐文章于 2024-06-27 19:38:12 发布

大大大大哈士奇

最新推荐文章于 2024-06-27 19:38:12 发布

阅读量938

点赞数

分类专栏： mysql

本文链接：https://blog.csdn.net/BigHusky/article/details/98779024

版权

mysql 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

1.业务背景介绍
当前pay系统需要接收trade系统发来的支付成功消息之后会插入一条记录到fund_detail表中，但是该操作需要做幂等处理，即需要在高并发的情况下保证同一个订单只插入一条记录到数据库
在这里插入图片描述
由图可见，在插入该条数据的时候先去数据库查一把看是否存在该笔订单的收益记录，如果有就结束；没有就插入。该代码在rocketMq消息投递不重复的情况下是没有问题的，如果消息投递不能保证唯一性，那么该代码就会出问题。
于是就想着用mysql的悲观锁来解决


<select id="getFundAccountProfitInDetailListByUserId" resultMap="BaseResultMap">
select
<include refid="Base_Column_List"/>
from fund_detail t
where t.detail_type = 1 and t.is_deleted = 0
and t.user_id = #{userId}
and t.outer_biz_id = #{outerBizId}
for update
</select>

加入for update 后觉得美滋滋，然后提交代码到线上，结果悲剧了。。。。
发现该锁根本没有生效。
2.mysql相关知识
2.1搜索引擎

MyISAM(My Index Sequential Access Mode)索引循序存取法，MyISAM可压缩，读取效率高，所以经常把MyISAM用于slave层，或无写操作的表，供客户端去读取，而myisam在写库操作的时候会产生排他锁（表锁），即隔离级别为最高级别。不支持事物，也不支持传播特性。
Innodb读取效率没有MyISAM高，但它支持事物，隔离级别可设定。支持行锁，对更新操作更灵活。所以通常我们见到的表都是Innodb，即使在有从库的公司中，从库的索引甚至还是Innodb。

总结下来 Innodb相比与MyISAM增加了支持事务支持行锁两点，阿里云的rds默认用的是Innodb搜索引擎，所以下面咱们来围绕Innodb展开讲解

2.2 事务的特性

1.原子性：事务的不可分割，组成事务的各个逻辑单元不可分割。

2.一致性：事务执行的前后，数据完整性保持一致。

3.隔离性：事务执行不应该受到其他事务的干扰。

4.持久性：事务一旦结束，数据就持久化到数据库中。

2.3事务的隔离级别
1.脏读:
脏读是指在一个事务处理过程里读取了另一个未提交的事务中的数据。当一个事务正在多次修改某个数据，而在这个事务中这多次的修改都还未提交，这时一个并发的事务来访问该数据，就会造成两个事务得到的数据不一致。

例如：用户A向用户B转账100元，对应SQL命令如 update account set money=money+100 where name=’B’; (此时A通知B) update account set money=money - 100 where name=’A’;
　　当只执行第一条SQL时，A通知B查看账户，B发现确实钱已到账（此时即发生了脏读），而之后无论第二条SQL是否执行，只要该事务不提交，则所有操作都将回滚，那么当B以后再次查看账户时就会发现钱其实并没有转。

2.不可重复读:
　　不可重复读是指在对于数据库中的某个数据，一个事务范围内多次查询却返回了不同的数据值，这是由于在查询间隔，被另一个事务修改并提交了。
　　例如:事务T1在读取某一数据，而事务T2立马修改了这个数据并且提交事务给数据库，事务T1再次读取该数据就得到了不同的结果，发送了不可重复读。
　　不可重复读和脏读的区别是，脏读是某一事务读取了另一个事务未提交的脏数据，而不可重复读则是读取了前一事务提交的数据。
　　在某些情况下，不可重复读并不是问题，比如我们多次查询某个数据当然以最后查询得到的结果为主。但在另一些情况下就有可能发生问题，例如对于同一个数据A和B依次查询就可能不同，A和B就可能打起来了……

3.虚读(幻读):
　　幻读是事务非独立执行时发生的一种现象。例如事务T1对一个表中所有的行的某个数据项做了从“1”修改为“2”的操作，这时事务T2又对这个表中插入了一行数据项，而这个数据项的数值还是为“1”并且提交给数据库。而操作事务T1的用户如果再查看刚刚修改的数据，会发现还有一行没有修改，其实这行是从事务T2中添加的，就好像产生幻觉一样，这就是发生了幻读。

幻读和不可重复读都是读取了另一条已经提交的事务（这点就脏读不同），所不同的是不可重复读查询的都是同一个数据项，而幻读针对的是一批数据整体（比如数据的个数）

2.4设置事务的隔离级别
read uncommitted (读取未提交内容) ：脏读，不可重复读，虚读都有可能发生
read committed (读取提交内容) ：避免脏读。但是不可重复读和虚读是有可能发生
repeatable read (可重读) ：避免脏读和不可重复读，但是虚读有可能发生。
serializable(可串行化) ：避免脏读，不可重复读，虚读。

2.5锁
在这里插入图片描述

InnoDB默认支持的是行锁，但这并不代表InnoDB不支持表锁。在InnoDB中并不是在数据行上加锁，而是在对应的索引上加锁，这一点和oracle并不同，后者是在数据行上加锁的。

这种实现的特点是：只有通过索引条件检索数据的时候加的是行锁，否则加表锁！开销、加锁速度、死锁、粒度、并发性能：

表锁：开销小，加锁快；不会出现死锁；锁定力度大，发生锁冲突概率高，并发度最低

行锁：开销大，加锁慢；会出现死锁；锁定粒度小，发生锁冲突的概率低，并发度高

页锁：开销和加锁速度介于表锁和行锁之间；会出现死锁；锁定粒度介于表锁和行锁之间，并发度一般

2.5.1 共享锁Shared Locks（S锁）
（1）兼容性：加了S锁的记录，允许其他事务再加S锁，不允许其他事务再加X锁

（2）加锁方式：select…lock in share mode

2.5.2 排他锁Exclusive Locks（X锁）
（1）兼容性：加了X锁的记录，不允许其他事务再加S锁或者X锁

（2）加锁方式：select…for update

2.5.3 表锁：意向锁 Intention Locks，意向锁相互兼容

（1）表明“某个事务正在某些行持有了锁、或该事务准备去持有锁”

（2）意向锁的存在是为了协调行锁和表锁的关系，支持多粒度（表锁与行锁）的锁并存，。

（3）例子：事务A修改user表的记录r，会给记录r上一把行级的排他锁（X），同时会给user表上一把意向排他锁（IX），这时事务B要给user表上一个表级的排他锁就会被阻塞。意向锁通过这种方式实现了行锁和表锁共存且满足事务隔离性的要求。

2.5.4 行锁：记录锁(Record Locks)

（1）记录锁, 仅仅锁住索引记录的一行，在单条索引记录上加锁。
（2）record lock锁住的永远是索引，而非记录本身，即使该表上没有任何索引，那么innodb会在后台创建一个隐藏的聚集主键索引，那么锁住的就是这个隐藏的聚集主键索引。
所以说当一条sql没有走任何索引时，那么将会在每一条聚合索引后面加X锁，这个类似于表锁，但原理上和表锁应该是完全不同的。

2.5.5 行锁：间隙锁(Gap Locks)

（1）区间锁, 仅仅锁住一个索引区间（开区间，不包括双端端点）。
（2）在索引记录之间的间隙中加锁，或者是在某一条索引记录之前或者之后加锁，并不包括该索引记录本身。

比如在 1、2、3中，间隙锁的可能值有 (∞, 1)，(1, 2)，(2, ∞)，
（3）间隙锁可用于防止幻读，保证索引间的不会被插入数据

2.5.6 行锁：临键锁(Next-Key Locks)
（1）record lock + gap lock, 左开右闭区间。
（2）默认情况下，innodb使用next-key locks来锁定记录。select … for update
（3）但当查询的索引含有唯一属性的时候，Next-Key Lock 会进行优化，将其降级为Record Lock，即仅锁住索引本身，不是范围。
（4）Next-Key Lock在不同的场景中会退化:

2.5.7InnoDB行锁模式兼容性列表
在这里插入图片描述

3.普及完知识后验证一下
3.1创建表，price字段添加索引

CREATE TABLE `product` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `name` varchar(150) NOT NULL COMMENT '名称',
  `price` bigint(20) NOT NULL COMMENT '价格',
  `num` tinyint(2) NOT NULL COMMENT '数量',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `creator` varchar(50) DEFAULT '' COMMENT '创建者',
  `edit_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  `editor` varchar(50) DEFAULT '' COMMENT '修改者',
  `is_deleted` tinyint(1) NOT NULL COMMENT '是否删除',
  PRIMARY KEY (`id`),
  KEY `index_price` (`price`)
) ENGINE=InnoDB AUTO_INCREMENT=201322 DEFAULT CHARSET=utf8 COMMENT='测试gap';

1.对非索引字段来进行 for update 上锁，验证表锁
在这里插入图片描述
窗口1：

set autocommit=0;
select * from product where num= 1 for update;
在这里插入图片描述
窗口2：
select * from product where num= 5 for update;

可以看到窗口2被阻塞了

我们到窗口1看一下表是否被锁住，执行 show OPEN TABLES where In_use > 0;
在这里插入图片描述
可以看到product整张表被锁住了

我们再回到窗口1 执行select * from product where num= 6 for update;
在这里插入图片描述
还是查询不了，证明确实整张表被锁住了

2.对索引字段来进行 for update 上锁，验证record锁
窗口1：
select * from product where price = 188 for update;
在这里插入图片描述
窗口2：

select * from product where price = 288 for update;

select * from product where price = 299 for update;
在这里插入图片描述
可以看到搜其他行没啥问题，证明不是锁的表

那么窗口2继续

select * from product where price = 188 for update;
在这里插入图片描述
阻塞了，证明188这行被锁住了，窗口1验证

打印当前状态产生的innodb锁仅在有锁等待时打印
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
打印当前状态产生的innodb锁等待仅在有锁等待时打印
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
在这里插入图片描述
1894这个事务被锁住了，也就是窗口2的事务被锁住了，且用的是排他锁也就是行锁record锁

3.对索引字段来进行 for update 上锁，验证gap锁+next-key锁幻读
窗口1：

start transaction;
select * from product where price = 188 for update;
在这里插入图片描述
窗口2：
INSERT INTO product VALUES (‘5’, ‘安慕希’, ‘187’, ‘0’, ‘2019-08-05 17:05:19’, ‘’, ‘2019-08-05 17:05:42’, ‘’, ‘0’);

为啥插入price=187的数据被阻塞了？？？？？

可以看到窗口2加上了gap锁

窗口1 我们可以把数据的区间分为 (∞, 88)，(88, 188)，(188, 299) (299, 388)，而窗口1将这些区间都给锁住了，不允许其他事务在这个区间

进行增删改的x锁操作，同时188本身也被锁住了，也就是说窗口1锁住的区间是 gap锁+record锁 = next-key锁

我们再来窗口2验证下

INSERT INTO product VALUES (‘6’, ‘安慕希’, ‘188’, ‘0’, ‘2019-08-05 17:05:19’, ‘’, ‘2019-08-05 17:05:42’, ‘’, ‘0’);

INSERT INTO product VALUES (‘6’, ‘安慕希’, ‘189’, ‘0’, ‘2019-08-05 17:05:19’, ‘’, ‘2019-08-05 17:05:42’, ‘’, ‘0’);

INSERT INTO product VALUES (‘6’, ‘安慕希’, ‘400’, ‘0’, ‘2019-08-05 17:05:19’, ‘’, ‘2019-08-05 17:05:42’, ‘’, ‘0’);
在这里插入图片描述
可以看到只有400插入成功了

4.对索引字段来进行 for update 上锁，如果查询的条件为空，会怎么样？
窗口1：

select * from product where price = 200 for update;
在这里插入图片描述

窗口2：
select * from product where price = 200 for update;
在这里插入图片描述
窗口2查询没啥问题，看一下插入是否有问题，窗口2继续
INSERT INTO product VALUES (‘5’, ‘安慕希’, ‘187’, ‘0’, ‘2019-08-05 17:05:19’, ‘’, ‘2019-08-05 17:05:42’, ‘’, ‘0’);

OK 可以，继续

start transaction;
INSERT INTO product VALUES (‘5’, ‘安慕希’, ‘188’, ‘0’, ‘2019-08-05 17:05:19’, ‘’, ‘2019-08-05 17:05:42’, ‘’, ‘0’);
在这里插入图片描述
188被阻塞了，猜想被上了gap锁，然后去窗口1验证下

被上了gap锁锁住的范围 gap (188,200) (200,299) +record 200 = next-key ;

继续去窗口2验证
在这里插入图片描述
验证成功

4.线上失效原因分析
回到最开始的例子，为啥for update语句没有生效

1.入账代码没有添加事务

2.公司的rds默认隔离级别为 read commit,防止不了幻读

那么如果线上是rr级别呢，能成功吗？

答案是不会成功
窗口1：
start transaction;
select * from product where price = 200 for update;
在这里插入图片描述
窗口2：

start transaction;
select * from product where price = 200 for update;

INSERT INTO product VALUES (‘6’, ‘安慕希’, ‘200’, ‘0’, ‘2019-08-05 17:05:19’, ‘’, ‘2019-08-05 17:05:42’, ‘’, ‘0’);
在这里插入图片描述
窗口2并没有成功，由上面的知识我们得知，窗口1上了间隙锁

窗口1 也插入

INSERT INTO product VALUES (‘6’, ‘安慕希’, ‘200’, ‘0’, ‘2019-08-05 17:05:19’, ‘’, ‘2019-08-05 17:05:42’, ‘’, ‘0’);
在这里插入图片描述
直接死锁

总结：
对于select … for update，如果记录不存，则会获取当前行上Next-Key Lock + 下一区间的Gap Lock。
窗口1和窗口2都对相同区间加了间隙锁，间隙锁之间不会相互阻塞
间隙锁（无论是S还是X）只会阻塞insert操作

窗口1 insert into时会先获取插入意向间隙锁，这和上面窗口2的间隙锁是冲突的，会阻塞。
窗口2 insert into时同样也会先获取插入意向间隙锁，这时MySQL检测到死锁，回滚。
窗口2回滚后间隙锁撤销，窗口1继续执行，正常结束。

对于select from where for update操作，一定要保证where条件字段的值一定存在，不然可能会导致间隙锁锁定的范围变得很大，阻塞别的事务，也有可能会导致死锁

大大大大哈士奇

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
记录mysql的间隙锁误用

1.业务背景介绍当前pay系统需要接收trade系统发来的支付成功消息之后会插入一条记录到fund_detail表中，但是该操作需要做幂等处理，即需要在高并发的情况下保证同一个订单只插入一条记录到数据库由图可见，在插入该条数据的时候先去数据库查一把看是否存在该笔订单的收益记录，如果有就结束；没有就插入。该代码在rocketMq消息投递不重复的情况下是没有问题的，如果消息投递不能保证唯一性，那...
复制链接

扫一扫

专栏目录