前语:不要为了读文章而读文章,一定要带着问题来读文章,勤思考。在此,建议大家为本公众号加“星标”。如文章写得好,望大家阅读后在右下边“在看”处点个赞,以示鼓励!
作者:潘民兰 来源:http://t.cn/AiKrffGF
我们在实际业务场景中,经常会有一个这样的需求,插入某条记录,如果已经存在了则更新它如果更新日期或者某些列上的累加操作等,我们肯定会想到使用INSERT ... ON DUPLICATE KEY UPDATE语句,一条语句就搞定了查询是否存在和插入或者更新这几个步骤,但是使用这条语句在msyql的innodb5.0以上版本有很多的陷阱,即有可能导致death lock死锁也有可能导致主从模式下的replication产生数据不一致。
正如前言说的那样,在实际业务中,曾经有过一个需求就是插入一条业务数据,如果不存在则新增,存在则累加更新某一个字段的值,于是乎就想到了使用insert... on duplicate key update这个语句,但是有一天去测试环境查看错误日志时,却发现了在多个事务并发执行同一条insert...on duplicate key update 语句时,也就是insert的内容相同时,发生 了死锁。
对于insert...on duplicate key update这个语句会引发dealth lock问题,官方文档也没有相关描述,只是进行如下描述:
An INSERT ... ON DUPLICATE KEY UPDATE statement against a table having more than one unique or primary key is also marked as unsafe. (Bug #11765650, Bug #58637)
也就是如果一个表定义有多个唯一键或者主键时,是不安全的,这又引发了以一个问题,见https://bugs.mysql.com/bug.php?id=58637。
也就是当mysql执行INSERT ON DUPLICATE KEY的 INSERT时,存储引擎会检查插入的行是否会产生重复键错误。如果是的话,它会将现有的行返回给mysql,mysql会更新它并将其发送回存储引擎。当表具有多个唯一或主键时,此语句对存储引擎检查密钥的顺序非常敏感。根据这个顺序,存储引擎可以确定不同的行数据给到mysql,因此mysql可以更新不同的行。存储引擎检查key的顺序不是确定性的。例如,InnoDB按照索引添加到表的顺序检查键。
insert ... on duplicate key 在执行时,innodb引擎会先判断插入的行是否产生重复key错误,如果存在,在对该现有的行加上S(共享锁)锁,如果返回该行数据给mysql,然后mysql执行完duplicate后的update操作,然后对该记录加上X(排他锁),最后进行update写入。
如果有两个事务并发的执行同样的语句,那么就会产生death lock,如:
具体的bug描述见:
https://bugs.mysql.com/bug.php?id=52020https://bugs.mysql.com/bug.php?id=58637https://bugs.mysql.com/bug.php?id=21356
解决办法:
1、尽量不对存在多个唯一键的table使用该语句。
2、在有可能有并发事务执行的insert 的内容一样情况下不使用该语句。
---------------
看到这里还没过瘾,那么就来群里与更多的同学交流切磋技术,戳这里:咱们来一起抱团取暖,好吗?
---END---
热文推荐
技术团队一般是如何进行代码审查的?
面试官:给我说说你对Java GC机制的理解?