视频+图文串讲: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;


是可以保证并发修改的安全性的....


嗯嗯、其实通过实验来看,确实是安全的。




小伙子可以的!整体感觉还不错。


欢迎关注我。不久会给你安排下一面


我没有问题了,你有什么想问我的吗?


暂时没有了,感谢大佬





推荐阅读

  1. MySQL的修仙之路,图文谈谈如何学MySQL、如何进阶!(已发布)
  2. 面前突击!33道数据库高频面试题,你值得拥有!(已发布)
  3. 大家常说的基数是什么?(已发布)
  4. 讲讲什么是慢查!如何监控?如何排查?(已发布)
  5. 对NotNull字段插入Null值有啥现象?(已发布)
  6. 能谈谈 date、datetime、time、timestamp、year的区别吗?(已发布)
  7. 了解数据库的查询缓存和BufferPool吗?谈谈看!(已发布)
  8. 你知道数据库缓冲池中的LRU-List吗?(已发布)
  9. 谈谈数据库缓冲池中的Free-List?(已发布)
  10. 谈谈数据库缓冲池中的Flush-List?(已发布)
  11. 了解脏页刷回磁盘的时机吗?(已发布)
  12. 用十一张图讲清楚,当你CRUD时BufferPool中发生了什么!以及BufferPool的优化!(已发布)
  13. 听说过表空间没?什么是表空间?什么是数据表?(已发布)
  14. 谈谈MySQL的:数据区、数据段、数据页、数据页究竟长什么样?了解数据页分裂吗?谈谈看!(已发布)
  15. 谈谈MySQL的行记录是什么?长啥样?(已发布)
  16. 了解MySQL的行溢出机制吗?(已发布)
  17. 说说fsync这个系统调用吧! (已发布)
  18. 简述undo log、truncate、以及undo log如何帮你回滚事物! (已发布)
  19. 我劝!这位年轻人不讲MVCC,耗子尾汁! (已发布)
  20. MySQL的崩溃恢复到底是怎么回事? (已发布)
  21. MySQL的binlog有啥用?谁写的?在哪里?怎么配置 (已发布)
  22. MySQL的bin log的写入机制 (已发布)
  23. 删库后!除了跑路还能干什么?(已发布)
  24. 自导自演的面试现场,趣学数据库的10种文件(已发布)
  25. 大型面试现场:一条update sql执行都经历什么?(已发布)
  26. 大型翻车现场:如何实现记录存在的话就更新,如果记录不存在的话就插入。(已发布)
posted @ 2021-02-25 12:43  赐我白日梦  阅读(506)  评论(3编辑  收藏  举报