MySQL行锁、间隙锁、Next-Key-Lock如何确保并发安全实现记录存在更新不存在插入？

导读 Hi，大家好！我是白日梦！本文是MySQL专题的第 27 篇。 下文还是白日梦以自导自演的方式，围绕“如何实现记录存在的话就更新，如果记录不存在的话就插入。”展开本话题。看看你能抗到第几问吧 换一种写作风格，自导自演面试现场！感觉这样还是比较有趣的，欢迎大家订阅我的MySQL专题，公众号首发！持续更新中～ 点击阅读原文，有视频串讲、视频实战各种案例、格式也会好看一点哦～ 点击阅读原文，有视频串讲、视频实战各种案例、格式也会好看一点哦～ 点击阅读原文，有视频串讲、视频实战各种案例、格式也会好看一点哦～ 欢迎关注白日梦，公众号首发！持续连载中 推荐阅读原文，可以看视频教程！ 推荐阅读原文，可以看视频教程！ 推荐阅读原文，可以看视频教程！ 那我们继续，还是这道场景题：现在我的业务中有这样的需求：如果目标记录存在的话我就更新它，如果记录不存在的话我就插入。说说看你知道哪些实现方式吧！ 嗯，比如我可以像下面这样做 这种方式。 // 伪代码user=User.FindById(1)if user == null{ user.Insert()}else{ user.Update()} 嗯！你这段代码如果不存在并发访问还好，一旦出现并发访问的情况。你这段逻辑会有诸多的并发修改异常的！ 比如这样的例子：商品有上线和下线的状态，然后管理员可以在后台页面中修改商品的状态，比如代码这样写的： // 伪代码，如果将状态置反if product.Status == “online”{ product.Status = "offline";}else{ product.Status = "online"; } 这时管理员A、B并发的去操作商品状态： 嗯，确实存在这种情况，不过我可以自定义FindById()中的sql语句，通过 select for update的方式，规避你在图画出来的风险。 比如自定义SQL，让 user = User.FindByID(1)函数执行的SQL为 select * from user where id = 1 for update; 直接select时会给id = 1的行添加一把读锁，现在我通过select for update检索，在读select时给id = 的行添加写锁。 那么当我在读取使用这行数据时，其他的人select for update 就会被阻塞，因为写锁之间彼此是互 斥的。最终也不会出现Update彼此覆盖的情况 哦？那你画一个时序图出来瞧瞧 嗯嗯，时序图大概长下面这样 嗯，乍一看你上面画的这个图是没问题的，但我有个问题：如果id = xx的行不存在呢？ 嗯，所以我们不如分不同情况讨论一下。下面我列举不同场景，你画时序图怎样？ 嗯嗯，好的！ 嗯！首先我们知道了，select for update会尝试添加X锁，也就是写锁。 常见的写锁有这么几种： 1、record lock给指定行记录添加锁 2、gap lock间隙锁 3、next key lock 下面通过在不同条件下执行select for update sql，再观察实验结果，就能八九不离十的推测出for update的加锁情况。 实验一已知条件如下： create table t(a int,b int,primary key(a),key(b));insert into t select 1,1;insert into t select 3,1;insert into t select 5,3;insert into t select 7,6;insert into t select 10,8; select for update sql为： select * from t where a= 5 for update; 提示：注意id=5的行已经存在了。 下面你画一下时序图吧 嗯嗯，时序图加锁情况如下： 嗯，那如果我们需要执行的sql如下： select * from t where b = 3 for update; 注意b并不是唯一键，它只是一个普通索引哦！ 你能画一下它的加锁时序图吗？ 嗯嗯，时序图如下： (最后一条Sql条件是 where a = 5，不是a=3) 还是使用这些数据 create table t(a int,b int,primary key(a),key(b));insert into t select 1,1;insert into t select 3,1;insert into t select 5,3;insert into t select 7,6;insert into t select 10,8; 假如我执行的sql是： select * from t where a = 13 for update; 注意： 1、a=13的行并不存在哦！ 2、a是唯一索引 你画一下它的时序图吧！ 嗯呢，时序图如下： 还是使用这些数据 create table t(a int,b int,primary key(a),key(b));insert into t select 1,1;insert into t select 3,1;insert into t select 5,3;insert into t select 7,6;insert into t select 10,8; 假如我执行的sql是： select * from t where a = 8 for update; 注意： 1、a=8的行并不存在哦！ 2、a是唯一索引 你画一下它的时序图吧！ 嗯嗯、如下： 对唯一索引来说，gap的上下都锁不住！ 还是使用这些数据 create table t(a int,b int,primary key(a),key(b));insert into t select 1,1;insert into t select 3,1;insert into t select 5,3;insert into t select 7,6;insert into t select 10,8; 假如我执行的sql是： select * from t where b = 5 for update; 注意： 1、b=5的行并不存在哦！ 2、b是普通索引 你画一下它的时序图吧！ 嗯嗯、如下。 对普通索引来说，gap锁会 锁上不锁下！ 嗯，对的。回到我们一开始的主题：如果数据存在我们就update 数据不存在我们就insert的话题来看的话。 通过如下方案： begin；select for update；# insert or update;commit； 是可以保证并发修改的安全性的.... 嗯嗯、其实通过实验来看，确实是安全的。 小伙子可以的！整体感觉还不错。 欢迎关注我。不久会给你安排下一面。 我没有问题了，你有什么想问我的吗？ 暂时没有了，感谢大佬！ 推荐阅读 MySQL的修仙之路，图文谈谈如何学MySQL、如何进阶！（已发布） 面前突击！33道数据库高频面试题，你值得拥有！（已发布） 大家常说的基数是什么？（已发布） 讲讲什么是慢查！如何监控？如何排查？（已发布） 对NotNull字段插入Null值有啥现象？（已发布） 能谈谈 date、datetime、time、timestamp、year的区别吗？（已发布） 了解数据库的查询缓存和BufferPool吗？谈谈看！（已发布） 你知道数据库缓冲池中的LRU-List吗？（已发布） 谈谈数据库缓冲池中的Free-List？（已发布） 谈谈数据库缓冲池中的Flush-List？（已发布） 了解脏页刷回磁盘的时机吗？（已发布） 用十一张图讲清楚，当你CRUD时BufferPool中发生了什么！以及BufferPool的优化！（已发布） 听说过表空间没？什么是表空间？什么是数据表？(已发布) 谈谈MySQL的：数据区、数据段、数据页、数据页究竟长什么样？了解数据页分裂吗？谈谈看！(已发布) 谈谈MySQL的行记录是什么？长啥样？(已发布) 了解MySQL的行溢出机制吗？(已发布) 说说fsync这个系统调用吧! (已发布) 简述undo log、truncate、以及undo log如何帮你回滚事物! (已发布) 我劝！这位年轻人不讲MVCC，耗子尾汁！ (已发布) MySQL的崩溃恢复到底是怎么回事？ (已发布) MySQL的binlog有啥用？谁写的？在哪里？怎么配置 (已发布) MySQL的bin log的写入机制 (已发布) 删库后！除了跑路还能干什么？(已发布) 自导自演的面试现场，趣学数据库的10种文件(已发布) 大型面试现场：一条update sql执行都经历什么？(已发布) 大型翻车现场：如何实现记录存在的话就更新，如果记录不存在的话就插入。(已发布)