Innodb 锁机制——一次插入慢查询的排查

慢查询日志中,发下有一条插入语句慢查询出现的概率比较高,一个简单插入需要消耗4-10s,很不寻常。附上插入语句,省略了一些字段

INSERT INTO `fc_pay_out_trade_log` (`out_trade_no`,`dateline`) VALUES ('191120093724940457',1574185044) [ RunTime:4.176669s ]
fc_pay_out_trade_log 表除了主键,out_trade_no字段为普通索引,没有其他索引。
mysql5.7版本,innodb引擎,默认RR级别。

业务流程:

1.生成订单流水id(out_trade_no),写入订单流水日志表fc_pay_out_trade_log,就是发送阻塞的语句
2.组合参数,调用微信统一下单接口->微信操作支付然后回调回调接口
3.回调接口全部放到一个事务中处理,有个操作是更新订单流水日志表fc_pay_out_trade_log对应的订单流水id的支付状态

UPDATE `fc_pay_out_trade_log` SET `pay_status`=1,`pay_time`=1574221674 WHERE `out_trade_no` = '191120194747307594' [ RunTime:0.000716s ]

订单流水out_trade_no生成规则

$out_trade_no = date(‘YmdHis’).rand(1000,9999);

对innodb锁机制比较了解的人应该发现了问题所在,这是因为update操作把innodb的间隙锁把fc_pay_out_trade_log表的部分区域锁住了,导致插入操作阻塞,必须等update操作的那个事务提交后才会释放这个间隙锁。

分析过程

1.前一个支付操作发生,支付成功后回调接口,执行上面的update语句,更新的条件是WHERE out_trade_no = ‘191120194747307594’,为了保证不出现幻读现象,innodb会使用next-key锁,就是锁住这行和间隙。
2.后一个支付发生,创建一个订单流水,插入到fc_pay_out_trade_log表,发现插入的区域已经被锁住,阻塞等待
3.前一个支付处理完其他事情,提交事务,释放fc_pay_out_trade_log表的next-key锁
4.后一个支付流水插入成功

下面这条语句会锁定哪些区域?锁多久才会释放?

UPDATE `fc_pay_out_trade_log` SET `pay_status`=1,`pay_time`=1574221674 WHERE `out_trade_no` = '191120194747307594' [ RunTime:0.000716s ]

innodb锁类型

S-共享锁:又叫读锁,其他事务可以继续加共享锁,但是不能继续加排他锁。
X-排他锁: 又叫写锁,一旦加了写锁之后,其他事务就不能加锁
IS意向共享锁:表达一个事务想要获取一张表中某几行的共享锁。
IX意向排他锁:表达一个事务想要获取一张表中某几行的共享锁。

InnoDB锁算法

记录锁

记录锁是锁住记录的,这里要说明的是这里锁住的是索引记录,而不是我们真正的数据记录。

如果锁的是非主键索引,会在自己的索引上面加锁之后然后再去主键上面加锁锁住.
如果表上没有索引(包括没有主键),则会使用隐藏的主键索引进行加锁。
如果要锁的列没有索引,则会进行全表记录加锁。
例如:select * from user where id= 1 for update; 会给user表加上IX,在主键索引1加上X锁。

间隙锁

锁间隙的意思就是锁定某一个范围,间隙锁又叫gap锁,其不会阻塞其他的gap锁,但是会阻塞插入间隙锁,这也是用来防止幻读的关键。

Innodb 锁机制——一次插入慢查询的排查

我们来分析一下fc_pay_out_trade_log,out_trade_no字段是一个普通索引,并且新增的数据都是有顺序的,所以WHERE out_trade_no = ‘191120194747307594’ 锁住的间隙会包括[191120194747307594,~),而新插入的值一定会在这个间隙区域中,所以会产出阻塞。

next-key锁

这个锁本质是记录锁加上gap锁。在RR隔离级别下(InnoDB默认),Innodb对于行的扫描锁定都是使用此算法,但是如果查询扫描中有唯一索引会退化成只使用记录锁。

解决办法:修改fc_pay_out_trade_log表的out_trade_no字段设置为唯一索引即可

本作品采用《CC 协议》,转载必须注明作者和本文链接
用过哪些工具?为啥用这个工具(速度快,支持高并发...)?底层如何实现的?
讨论数量: 2

从锁的问题出发,发现其实是索引的问题。

3年前 评论

间隙锁造成的阻塞,使用唯一索引可以避免间隙锁的发生。 :smile:

3年前 评论

讨论应以学习和精进为目的。请勿发布不友善或者负能量的内容,与人为善,比聪明更重要!