视频串讲大型翻车现场:记录存在的话就更新,记录不存在的话就插入

飞天小牛肉

共 3788字,需浏览 8分钟

 ·

2021-10-11 11:34




《为研发同学定制的MySQL面试指南》 
贯穿单机、集群、中间件!
面试官都关注了!你还犹豫?

Hi,大家好!我是白日梦!本文是MySQL专题的第 27 篇。
点击阅读上一篇文章:“如何实现记录存在的话就更新,如果记录不存在的话就插入。” 
发布这个话题之后我陆陆续续收到了近20条私信其中有些读者朋友提到了一些很有价值、值得注意的点。所以就有了这一篇续文~。
下文在上一篇的基础上做一些延伸并且提及到了一些锁的概念。同样以问答的方式展现给大家!
欢迎关注、加入白日梦的圈子,一起系统的学数据库

1
视频串讲本文 













                              

视频是对本篇文章的串讲以及一些示例的实战
持续更新~
欢迎关注我哈~


2
好!我们开始吧! 













                              

那我们继续,还是这道场景题:现在我的业务中有这样的需求:如果目标记录存在的话我就更新它,如果记录不存在的话我就插入。说说看你知道哪些实现方式吧!


嗯,比如我可以像下面这样做


这种方式。

// 伪代码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的话题来看的话。


通过如下方案:

beginselect for update# insert or update;commit


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


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




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


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


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


暂时没有了,感谢大佬




2
推荐阅读

1、MySQL的修仙之路,图文谈谈如何学MySQL、如何进阶!

2、数据库面经,常见的面试题

3、谈谈MySQL中基数是什么?

4、聊聊什么是慢查?如何监控?如何排查?

5、对Not Null字段插入Null值有啥现象?

6、能谈谈year、date、datetime、time、timestamp的区别吗?

7、你有没有搞混查询缓存和Buffer Pool?谈谈看!

8、你知道数据库缓冲池中的LRU-List吗?

9、了解InnoDB的FreeList吗?谈谈看!

10、了解Flush-List吗?顺便说一下脏页的落盘机制!

11、用 11 张图讲清楚,当你CRUD时BufferPool中发生了什么!以及BufferPool的优化!

12、了解 MySQL的表空间 和 数据表吗?谈谈看!

13、了解 MySQL的数据行吗?行溢出机制呢?谈谈看!

14、了解MySQL数据页吗?说说什么是页分裂吧!

15、用一分钟了解fsync这个系统调用

16、简述undo log、truncate、以及undo log如何帮你回滚事务?

17、我劝!这位年轻人不讲MVCC,耗子尾汁!

18、传说中的MySQL的redo log是什么?谈谈看!

19、LSN、Checkpoint?谈谈MYSQL的崩溃恢复是怎么回事!

20、MySQL的 bin log有啥用?在哪里?谁写的?怎么配置?

21、bin log有哪些格式?有啥区别?优缺点?线上用哪种格式?

22、删库后!除了跑路还能干嘛?

23、全网最牛X的!MySQL两阶段提交串讲!没有之一!

24、自导自演的数据库面试现场--谈谈MySQL的10种文件

25、大型面试现场:一条update sql的执行都经历了什么?

26、大型翻车现场:如何实现记录存在的话就更新,如果记录不存在的话就插入。

3
欢迎关注

欢迎加入白日梦的圈子、一起吃肉!

长按二维码、关注白日梦

赠送精美脑图

(操作系统、网络、Java基础...)

长按二维码

关注白日梦吧!



浏览 58
点赞
评论
收藏
分享

手机扫一扫分享

分享
举报
评论
图片
表情
推荐
点赞
评论
收藏
分享

手机扫一扫分享

分享
举报