gpt4 book ai didi

记录一个MySql 分区表+Gap锁引起插入超时的案例

转载 作者:知者 更新时间:2024-03-13 06:52:29 25 4
gpt4 key购买 nike

最近有同事在项目上遇到一个场景,定时任务在往MySql插入一条数据超时了,而排查其他SQL,没有锁表的动作。排查到最后,发现是分区表导致id不唯一,加上Gap锁导致的。下面简单分析一下。

1.场景重现

1.1 没有分区的场景

先建一个没有分区的表

插入一些数据

此时开启一个事务(称为事务1),把id=5的数据改为changed

事务1未提交,同时在另外一个窗口,再开一个事务(称为事务2),插入id为4的数据;

成功插入,两个事务分别提交,可以看到数据已经写入。

这很好理解,两条语句操作的id不一样,不会互相影响。可是加了分区,就不一样了。

1.2 分区的场景

注意,主键已经不只是id了,而是id+country。如果不把country加入到主键中,会报错。

[Err] 1503 - A PRIMARY KEY must include all columns in the table's partitioning function

而这导致了通过id并不能唯一确认一条数据(虽然从程序的逻辑上是唯一的),进而导致更新时会锁住不止一行,影响了其他事务的操作。

插入数据,此时country值是一样的(后面解释)

和上面一样,开一个事务1,update id=5的数据,暂不提交。

在事务2,先后插入id=8和id=4的数据,可以看到,第一条顺利插入,第二条被阻塞,直到事务超时。

  1. 原因简析

参考美团技术团队关于MySql锁的分析:https://tech.meituan.com/2014/08/20/innodb-lock.html

简单来说,就是MySql的默认事务级别是RR,在这个级别是解决了幻读问题的。不可重复读和幻读的关注点不一样,前者是update/delete,后者是insert.

为了防止幻读,MySql用Gap锁把id=5的数据前后都锁住了,即(3,5] 和(5,7],所以插入id=4或6的数据会被阻塞,而插入8不会。

那么为什么明明有ID,还要分区呢?这是因为有时候数据量大,利用分区可以访问快一点,例如以记录的create time作为分区依据。但这样就要求分区字段作为主键的一部分,破坏了原主键的唯一性。

上面的例子,是基于country=CHINA的分析,因为通过实验,这个锁对于同样分区的才生效。如果把其他数据的country,改为USA或JAPAN,结果又不一样了。

比如把7改为JAPAN, 这时(5,'CHINA') 和 (7,'JAPAN') 并不在同个分区,那么Gap锁锁住的是(3,5]和(5,9],插入id=8, country=CHINA同样阻塞(当然插入id=8, country=JAPAN不会阻塞,不在同个分区!)。

所以其实是 分区->ID+分区字段才能组成主键->误以为ID是主键,但实际并不唯一,导致了Gap锁->多锁了几行

还有其他的场景,参考这个例子:https://www.zhihu.com/question/51390849/answer/294352412

事务2如果是update,并且改变了索引字段的值,也就改变了索引的位置,那么更新后的数据可能落入gap区间,造成阻塞。

如果不改变索引字段的值,不会有这样的问题。

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com