MySQL InnoDB 锁的二三事

标签：... name update value 二三 InnoDB MySQL config select

近日, 在一个小型项目中, 遇到了一个触及我知识盲区的bug.

 

项目用的是MySQL 5.7.25, 其中有一张表 config_data, 包含四个字段, id, name, value, expireAt. 其中id为主键, name建有唯一索引, 表的用途大概就是存放一些有时效性的配置. 以上就是这次故事的背景.

(不要问我为什么要用这么奇怪的方式处理需要过时的配置, 项目太简陋以至只有一台虚拟主机和一个数据库, 别的什么都没了, 包括Redis)

 

这张表的使用场景大致为, 假设需要使用某配置a, 先尝试从表中查找a, 若找到, 判断是否过期, 过期或者值不存在则从外部获取配置的值并存入表中, 以便下次使用. 伪代码流程如下:

config = query('select value, expireAt from config_data where name = "a" lock in share mode;');

if (!config || config.expireAt < now) {    // 不存在或已过期
    beginTransaction();
    config = query('select value, expireAt from config_data where name = "a" for update;');
    if (config && config.expireAt > now) {
        rollback();
        return config.value;
    }
    value = getConfigValueFrom3rdPartyServer();    // 从外部服务器获取配置值
    execute('insert config_data (name, value, expireAt) value ("a", value, newExpireTime) on duplicate key update `value`=value, `expireAt`=newExpireTime;');    // 插入或更新配置值以及过期时间
    commit();
    return value;
}

return config.value;

由于配置的值需要从外部服务器通过接口调用获取, 执行代价较大, 更重要的是, 第三方服务器的接口有每日调用次数限制, 因此必须控制出现并发更新配置值时 (即同一时间多个请求到来时配置项过期了) 只有一个进程发起请求获取配置值并更新数据库, 其余进程需等待更新完成并使用更新后的数据.

Again, 只有虚拟主机+DB, 故只好借用数据库方式加锁. 基本思路就是, 开始时使用共享锁 (S Lock) 查找配置值 (数据库使用了默认的autocommit, 语句执行完后共享锁自动释放), 如果需要更新, 开启事务, 使用排他锁 (X Lock) 锁住待更新行, 从外部服务器获取配置值 (不考虑获取失败情况, 配置值都获取不了只能直接往外抛异常了) , 使用 insert ... on duplicate key update 方式插入或更新数据库, 提交事务, done~

假设有两个进程A, B同时获取配置值, A, B均能同时获得共享锁并查询到已过期的配置, 然后尝试获取排他锁, 但只会有一个进程能成功获取排他锁, 这里假设是A, 则B在第5行时会被block住, 在A更新完成并提交事务后, B才能从第5行继续并获取到最新的配置值. 假如在A更新完成前, 第三个进程C又需要获取这个配置值, 则会在第1行尝试获取共享锁时由于排他锁已被A获得而被block住. 同样, 待A提交事务后C就能获得共享锁并拿到最新的值.

粗看逻辑没有问题, 并发的问题貌似完全可以由MySQL的行锁 (Record Lock) 解决. Perfect~ 于是就简单试了下功能, 扔代码上主机, 项目就上线运行了.

 

就这样过了两三天, 项目体量实在太迷你了, 每天最多也就1~2k的访问量, 因此服务器配置也是低得令人发指. 期间偶尔收到反馈说接口会报500错误, 我一概以“服务器配置太低”或者“网络问题”为由搪塞过去 (甩锅小能手~) , 倒也无惊无险地过来了. 直到那一次, 收到某个需求要小改一下前端界面, 调试的时候偶遇了这个神秘的500, 好奇看了一眼报错内容……

Deadlock found when trying to get lock; try restarting transaction

WTF? Deadlock???

 

一顿操作排查之后, 基本可以确定问题就是出在上面这段查找配置值的代码上. 当配置值过期后需要更新时, 如果同时有多个进程尝试执行上面的代码更新配置值时, 就会被检测出死锁. 具体表现为, 其中某个进程成功更新了数据库, 其余进程全部会抛出死锁异常, 几乎100%必现 (必现的bug就是好bug~).

按一般对死锁的理解, 常见的场景是两个进程按相反顺序加锁访问两个资源, 然后卡在互相等对方释放第一个资源造成的. 然而, 上面的代码明显和这个场景完全不沾边啊?…… 百思不得其解, 只能用尽各种模拟方法尝试找到原因. 还好最后终于确认了重现的步骤:

首先惯例假设有两个进程A和B.

A操作步骤	B操作步骤
开始事务	开始事务
select ... for update 查找name=a的行并获得结果
	select ... for update 查找name=a的行 (被阻塞)
insert ... on duplicate key update ... 更新数据成功
	(deadlock found, gg) 事务被强行中断并回滚
提交事务, 完成更新

然后我就 (黑人问号.jpg) . Why???? 我不就是更新了行数据, 你都被阻塞了, 等我更新完再去拿结果不就好了?

而且, 即使我将 insert ... on duplicate key update ... 替换成 insert ... , 也照样能造成B死锁, 只是A也因唯一索引冲突插入失败而已, 也就是说, 死锁和更新无关 (也许吧).

这真的超出我理解范围了. 调出死锁分析看看 (执行 show engine InnoDB status; 然后查看Status字段的 LATEST DETECTED DEADLOCK 部分)

------------------------
LATEST DETECTED DEADLOCK
------------------------
2022-09-16 09:57:38 0x7f17c41d5700
*** (1) TRANSACTION:
TRANSACTION 2311, ACTIVE 5 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 5, OS thread handle 139740050691840, query id 134 172.17.0.1 root statistics
select * from config_data where name = 'a' 
LIMIT 0, 1000
for update
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 24 page no 4 n bits 72 index name_UNIQUE of table `test`.`config_data` trx id 2311 lock_mode X locks rec but not gap waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 61; asc a;;
 1: len 4; hex 80000001; asc     ;;

*** (2) TRANSACTION:
TRANSACTION 2310, ACTIVE 9 sec inserting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1
MySQL thread id 3, OS thread handle 139740051232512, query id 136 172.17.0.1 root update
insert config_data (name, value) value ('a', 2) on duplicate key update value = 2
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 24 page no 4 n bits 72 index name_UNIQUE of table `test`.`config_data` trx id 2310 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 61; asc a;;
 1: len 4; hex 80000001; asc     ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 24 page no 4 n bits 72 index name_UNIQUE of table `test`.`config_data` trx id 2310 lock_mode X waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 61; asc a;;
 1: len 4; hex 80000001; asc     ;;

*** WE ROLL BACK TRANSACTION (1)

大意就是, A在第一步select时拿到了name = a那行的行排他锁, B同时在select时等待name = a的行锁, 然后A尝试插入name = a的行数据时, 插入操作也需要获取对应行的排他锁, 这时相当于A和B都在等待同一行的排他锁, 而锁目前被A在第一步select ... for update占据着, InnoDB 认为A要完成插入操作需要等待B获取并释放锁, 而B要获取锁需要等待A释放第一步取得的锁, 而A要释放已取得的锁必须要完成插入操作, 于是, boom, deadlock found. InnoDB检测到死锁之后会判断以最小代价 (即选择已进行较少修改操作的事务) 中断并回滚涉及事务, 因此B被中断, A继续执行并提交事务.

为了验证以上的推论, 修改了几种操作顺序及方式并测试结果: (上述的死锁原因分析我并没有找到严谨的官方资料说明, 只是通过多次试验及阅读官方关于锁的介绍文档推理所得)

• 如果将A的 insert ... on duplicate key update ... 替换成普通的 insert ... , A的插入操作会因为唯一索引冲突而失败, 但B仍然会因检测到死锁而被强制回滚. 区别仅是 on duplicate key update 方式尝试获取的行锁是排他锁 (X Lock) (因为涉及更新操作) 而普通insert冲突后尝试获取的行锁是共享锁 (S Lock). 此处并不对上面的结论造成影响, 故不详细分析.
• 如果将A的 insert ... on duplicate key update ... 替换成 update ... 操作, B会等待update操作完成并提交或回滚后顺利获得行锁, 死锁并不会出现. 推测是因为 update 和 insert 需要的锁类型不同, update 需要的锁已在前面的 select ... for update 处取得, 故能直接执行更新操作.
• 如果将A的 insert ... on duplicate key update ... 与B的 select ... for update 调换顺序 (即先完成插入后再在另外一个事务尝试获取锁) , 则B会在A事务提交或回滚后获得行锁, 死锁同样不会出现. 因此可知获取锁的顺序对结果有影响.

可是, 明显上述的推演在逻辑上并不那么令人信服, 尽管B的select先于A的insert尝试获取锁, 但A已经事实上持有对应行的行锁, 按理是可以完成插入操作的 (由调换B的select与A的insert则避免死锁可以证明). 因此, 这种行为与其说是feature, 更像是bug. (实际上, 这种死锁场景只会在5.7版本出现, 8.0以后是重现不了的, 但我找不到具体是哪个版本的哪项更新内容, 如有了解的大神烦请指教) (btw, 像docker这种容器技术在这种多版本测试场景是真香)

到目前为止, 看上去问题找到了, 之后只要找到解决问题的方法就好, so far so good, right?

然而, 坑总是不会单独出现的, 避开一个总有一串坑等着你.

 

因为config_data这张配置表的设计是如果查询的配置项不存在, 需要由一个进程获取配置项的值并插入到表中. 为了测试这种场景, 我将config_data的数据清空, 验证初次使用配置项的逻辑.

照旧假设有两个进程A和B, 开始时我以为流程和表中已有数据的情景是大同小异的, 但实际结果却是

A操作步骤	B操作步骤
开始事务
select ... for update 查找name=b的行并获得空结果
	select ... for update 查找name=b的行并获得空结果 (非阻塞)
请求第三方服务接口获取配置项b的值	请求第三方服务接口获取配置项b的值
(假设较B更早获得请求结果) insert ... on duplicate key update ... 插入数据并被阻塞
	insert ... on duplicate key update ... 插入数据并触发InnoDB死锁检测, 事务被强制中断并回滚 (gg again)
插入数据成功
提交事务

问题更大条了. 首先, select ... for update 并没能block住进程, 导致重复请求了第三方服务. 考虑到前文提到的第三方接口有日请求次数限制, 万一同时有一千个进程被执行, 瞬间一千次访问配额就被消耗了. 其次, 每个进程等于都会尝试执行插入操作一次, 但有且仅有一个进程能成功, 无疑是对资源的极大浪费.

可为什么加锁读 (Locking read) 没能阻塞住进程呢? 为什么最先的插入操作会被阻塞, 而后面的插入操作又会触发死锁呢?

一番查阅官方文档和各种第三方资料, 才明白这种看似违反直觉的结果是我又一个知识盲区.

首先是前置知识, InnoDB的索引可以分为两种: Clustered Index 和 Secondary Index (抱歉不太确定准确的中文翻译是什么). Clustered Index 本质就是主键+数据, 可以视作表本身; Secondary Index 就是创建的各种索引, 由组成索引的字段+主键构成. 使用主键查找数据时, 直接使用Clustered Index查找并返回结果; 使用其它索引查找数据时, 先使用Secondary Index找到主键, 再根据主键查找数据 (如果要查找的数据就是索引的一部分或者主键本身, 则省略第二步). InnoDB的锁实际上必须加在上述两种索引任意之一的记录上 (或者说, 必须以索引中的记录作为锁的锚点). 比如说, 某索引由字段a构成, 现有两条记录 a = 1 , a = 10 . 如果想对 a = 5 加锁, 只能获取两条记录1和10之间的间隙锁 (Gap Lock).

使用select ... for update时, 如果查找的条件完全命中一个唯一索引 (即, 假如唯一索引由a和b两个字段组成, 则查找条件必须完全包含这两个字段), 且返回了一条结果, 则会对这条结果加上行锁. 但如果指定的查询条件找不到任何结果, 因为索引的记录实际不存在, 无法加行锁, 所以只能对查找条件对应的区间加上间隙锁. 例如上面的流程中, 假设name分别有a和c两条记录, 使用name = b作查找条件进行locking read时将会加上a到c之间的间隙锁 (如果没有c这条记录, 则是a到正无穷之间).

间隙锁与行锁的不同之处在于, 即使获取的间隙锁是排他锁, 同一个间隙锁 (更确切地说, 是同一个区间上的间隙锁) 可以被多个事务同时持有. 这就是上面的流程中进程A和B执行select ... for update时均能立即返回而没有阻塞的原因, 它们都获取到了name = b所在区间的间隙排他锁 (这里叫排他锁多少显得不太准确). 由于间隙锁可以被多个事务同时持有, 可以推论出间隙锁只能是一种“纯限制”(purely inhibitive) 锁, 即, 获得锁之后, 只能禁止其它事务往这个区间上修改数据, 并不能使当前事务能够修改此区间上的数据.

因为间隙锁的获取并不能保证可以插入数据, 因此A进程到insert那一步时, 仍然需要等待获得这行的行排他锁. 由于B进程也持有这个区间的间隙锁, 禁止了A插入数据, 因此必须等B进程释放持有的间隙锁才能完成插入操作. 然而, B进程在释放间隙锁之前, 同样需要向同一个位置插入数据, 也需要等待A释放间隙锁后获取这行的行锁. 因此, A和B都在等对方释放间隙锁, deadlock found, again.

值得一提的是, 上述情景只会在默认的隔离级别 (Isolation Levels) 可重复读 (REPEATABLE READ) 或更高的级别中出现, 读提交 (READ COMMITTED) 或更低级别中, 由于不会使用间隙锁, 因此不会造成死锁. (但仍然存在多次访问第三方服务接口问题)

 

问题原因基本上确定了, 然后就是寻找解决方案. 可行的方案例如有:

方案一. 更新配置值的事务开始前, 先查找一次配置项, 若为空 (即未初始化), 先尝试插入一个超时时间戳为0的数据, 然后再执行更新操作. 具体流程伪代码如下:

config = query('select value, expireAt from config_data where name = "a" lock in share mode;');

if (!config) {
    execute('insert ignore config_data (name, value, expireAt) values ("a", 0, 0);');    // expireAt 必须设置为小于当前时间戳, 确保插入的数据无效
    config = query('select value, expireAt from config_data where name = "a" lock in share mode;');
}

if (config.expireAt < now) {
    beginTransaction();
    config = query('select value, expireAt from config_data where name = "a" for update;');
    if (config.expireAt > now) {
        rollback();
        return config.value;
    }
    value = getConfigValueFrom3rdPartyServer();    // 从外部服务器获取配置值
    execute('update config_data set `value`=value, `expireAt`=newExpireTime;');    // 更新配置值以及过期时间
    commit();
    return value;
}

return config.value;

相当于配置项不存在时, 强制初始化一个过期的配置项再走常规的查找或更新流程. 由于单纯的更新操作可以直接使用select ... for update时获得的锁, 因此不会造成死锁问题.

方案二. 使用MySQL提供的GET_LOCK/RELEASE_LOCK方法, 在查找配置项前加锁, 查找或更新后释放锁. 相当于将所有查找配置项操作变为线性顺序, 避免任何并发. 缺点是性能代价较高.

方案三. 依赖外部锁机制. 这在本项目中较难实现.

综合考虑, 对于本项目来说, 方案一是较为合适的解决方案. 因为只有第一次初始化时需要执行插入操作, 其余时间除了配置项过期需要更新时会出现阻塞等待, 绝大部分场景都可以进行并发读取数据, 性能相对较好.

 

 

refs.

https://fastmail.blog/advanced/mysql-lock-nonexistent-row/

https://mysqlquicksand.wordpress.com/2019/12/20/select-for-update-on-non-existent-rows/

https://dev.mysql.com/doc/refman/5.7/en/innodb-locks-set.html

https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html

https://jahfer.com/posts/innodb-locks/

标签：...,name,update,value,二三,InnoDB,MySQL,config,select
From： https://www.cnblogs.com/reginald-lee/p/16697879.html