MySQL面试题,如何书写 update 避免表锁?

点击上方“业余草”,选择“置顶公众号”

第一时间获取技术干货和业界资讯!

 

640?wx_fmt=png

                                                                        640?wx_fmt=png

前面我推荐了极客时间的 Java 高并发课程,很多人根据这篇文章《2019 Java 高并发学习路线图和必会的 50 道面试题!》的介绍买了课程,我希望大家能够认真的看!别把钱浪费了!

今天,我来说另外一个面试题。为什么推荐 MySQL 的 update 语句中 where 条件要有主键?

看到这个问题的朋友,我相信不少人有疑问,我 where 不加主键还不能更新了?

不是的,能更新,也能使用。但是我不建议你们这样做。因为我们大多数人使用 MySQL 都使用的是 innodb 存储引擎,它是支持事务的。如果你的 where 条件不加主键,那么 innodb 的行级锁就可能变成表级锁。如果升级为表级锁,那么并发性就将大打折扣了。

640?wx_fmt=jpeg

之前也有网友在我的文章里评论说,行锁升级为表锁与事务的隔离级别有关,因为事务的隔离性是靠加锁来实现的,而加锁不当势必会影响并发。

不一样的锁,支持的并发也是不一样的。而最终加什么样的锁,与索引也有莫大的关系,因此,可以说采用什么样的索引决定了支持多少并发。

常用的索引有三类:主键、唯一索引、普通索引。主键我就不再细说,自带最高效的索引属性;唯一索引指的是该属性值重复率为 0,一般可作为业务主键,例如订单号;普通索引 与前者不同的是,属性值的重复率大于 0,不能作为唯一指定条件,例如购买用户的姓名。今天我主要想说的是“普通索引对并发的影响”。

没有索引的情况,我就不说了,那对并发来说肯定是灾难,死锁估计是常有的事。

为什么我推荐 update 中 where 条件加入主键呢?

因为主键是唯一索引,你用其他唯一索引也可以,但是一般的表,可能只有主键才是唯一的。所以,我建议你更新的时候,记住加上主键就行了。

你只需要记住主键和唯一索引是行锁,其他索引并不一定是行锁,很可能是表锁。这样,死锁的概率就非常的高,并发也就随之下降。

下面我们通过一个简单的例子来看一下,普通索引的情况。

相关建表语句,索引,和数据如下所示:

640?wx_fmt=png

然后取消事务自动提交 set autocommit = off;

当我们表里面创建时间重复率比较高的时候。分别开启两个窗口,两个事务。

640?wx_fmt=png

为了演示,你可以把数据量加多点,比如 03-01 和 03-02 的数据各 10 万条。

 

依次执行两个窗口中的 SQL,你会发现,其中一个窗口中的更新失败了。提示:

 

640?wx_fmt=png

看似这两个事务不互相干,但是在其中一个事务中更新自己锁定的数据失败后,应该能说明在此时引发了表锁。这是在非主键索引或者说是唯一索引,并且索引数据重复量比较高的情况下,你的更新发生量表锁。并发能力就会大大下降!

你们可以试一下,如果此时使用主键或唯一索引会不会这样。

640?wx_fmt=jpeg

在我们的电商系统中,这样的代码并不少。在一些热门商品和秒杀、优惠、打折等活动中经常会发生一些莫名其妙的异常,导致用户体验大打折扣。

上面的测试数据,你把它们全部删除,然后再新增一些数据,这些数据中在 create_time 重复率为 0 的情况下,你会发现两个事务就都能成功了。这说明它们这时用的应该是行级锁,效率更高。

以上,测试说明在更新数据时,尽量使用主键或唯一索引。但是唯一索引并不少每个表都有的,而主键必须是每个表都必须有的。所以,我建议你们在更新数据时,都带上主键!

原文链接:https://www.xttblog.com/?p=3946

640?wx_fmt=png

10T技术资源大放送!包括但不限于:C/C++,Linux,Python,Java,PHP,人工智能,GO等等。在公众号内回复对应关键字或框架名字,即可免费获取!!

640?wx_fmt=png

 你再主动一点点 640?  我们就有故事了

业余草 CSDN认证博客专家 Java Go/GoLang Redis
如今的编程是一场程序员和上帝的竞赛,程序员要开发出更大更好、傻瓜都会用到软件。而上帝在努力创造出更大更傻的傻瓜。目前为止,上帝是赢的。个人网站:www.xttblog.com。个人微信:codedq或dart996
©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页