07 | 行锁功过：怎么减少行锁对性能的影响？

一、灵魂两问🤔️

🏁：1.MySQL的行锁是在引擎层还是在server层实现的呢？那表锁呢？

答：由于插件式的引擎是MySQL特有的，导致MySQL可在2个地方实现锁机制：Server层和存储引擎层，存储引擎层可通过接受Server层传递来的锁类型而自行决定该如何给数据上锁。
行锁在引擎层实现，因为不是所有的引擎都支持行锁的。
表锁是在Server层实现的锁定机制，MyISAM并没有自己实现，则是完全使用Server层传递来的表锁。

二、两阶段锁【脑袋打掉都得记住！】

🏁：1.行锁在什么时候锁住？什么时候释放？

在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议。

🏁：2.两阶段锁知晓原理后，对于我们使用事务的时候有什么帮助呢？

如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。

举例【一定要看，最后有彩蛋～～】
假设你负责实现一个电影票在线交易业务，顾客 A 要在影院 B 购买电影票。我们简化一点，

这个业务需要涉及到以下操作：

1.从顾客 A 账户余额中扣除电影票价；

2.给影院 B 的账户余额增加这张电影票价；

3.记录一条交易日志。

分析：试想如果同时有另外一个顾客 C 要在影院 B 买票，那么这两个事务冲突的部分就是语句 2 了。因为它们要更新同一个影院账户的余额，需要修改同一行数据。

所以：把语句 2 安排在最后，比如按照 3、1、2 这样的顺序，那么影院账户余额这一行的锁时间就最少。这就最大程度地减少了事务之间的锁等待，提升了并发度。

彩蛋：由于正确设计，影院余额这一行的行锁在一个事务中不会停留很长时间。但是，这并没有完全解决困扰，当在秒杀电影票的场景下，你的MySQL可能一下子就JJ了。

为什么JJ呢？现象CPU 消耗接近 100%，但整个数据库每秒就执行不到 100 个事务。这是什么原因呢？

Amazing：死锁和死锁检测！！！

三、死锁和死锁检测

👋：什么是死锁？

当并发系统中不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，就会导致这几个线程都进入无限等待的状态，称为死锁。

🏁：1.死锁出现后MySQL会用哪两种策略来解决这个问题呢？他们的默认值是什么？改变默认值后有什么影响？不改使用当前默认值的弊端是什么？

前置答案：和主动死锁检测

1⃣️超时等待，直接进入等待，直到超时。这个超时时间可以通过参数 innodb_lock_wait_timeout 来设置。

默认值是 50s。

设置较小会出现误伤情况【比如此时是锁等待而不是死锁】。

不改业务上较难接受这个值。

2⃣️主动死锁检测，过程就是每当一个事务被锁的时候，就要看看它所依赖的线程有没有被别人锁住，如此循环，最后判断是否出现了循环等待，也就是死锁。参数innodb_deadlock_detect 来设置

默认值本身就是 on。

关闭死锁检测，会发生大量死锁时等待锁超时，这个对业务是有损的。

这是一个整体时间复杂度是 O(n2) 的操作。举例：有 1000 个并发线程要同时更新同一行，那么死锁检测操作就是 100 万这个量级的。虽然最终检测的结果是没有死锁，但是这期间要消耗大量的 CPU 资源。因此，你就会看到 CPU 利用率很高，但是每秒却执行不了几个事务

🏁：2.那么不关闭死锁检测又想提高并发度的方案有没有呢？有两种方案

第一种【牛逼到极致的方案】：如果你有中间件，可以考虑在中间件实现；修改 MySQL 源码对于相同行的更新，在进入引擎之前排队。

第二种【普通人可以做的方案】：将单行的更新放到多行上，总之一拆多，比如我们营销内的现金券。但是这个可不是说拆分就拆分的，需要考虑如何拆分？拆分后的库存归并问题？

上述第二种专业点的词语就是分段汇总！！！

四、课后问题

如果你要删除一个表里面的前 10000 行数据，有以下三种方法可以做到：

第一种，直接执行 delete from T limit 10000;

第二种，在一个连接中循环执行 20 次 delete from T limit 500;

第三种，在 20 个连接中同时执行 delete from T limit 500。

你会选择哪一种方法呢？为什么呢？

点击展开内容
确实是这样的，第二种方式是相对较好的。

第一种方式（即：直接执行 delete from T limit 10000）里面，单个语句占用时间长，锁的时间也比较长；而且大事务还会导致主从延迟。

第三种方式（即：在 20 个连接中同时执行 delete from T limit 500），会人为造成锁冲突。

PS：如果可以加上特定条件，将这 10000 行天然分开，可以考虑第三种。实际上在操作的时候我也建议你尽量拿到 ID 再删除。

五、课后精选

🏁：1.关于死锁检测innodb_deadlock_detect是每条事务执行前都会进行检测吗？如果是这样，即使简单的更新单个表的语句，当每秒的并发量达到上千的话，岂不是也会消耗大量资源用于死锁检测吗？

点击展开内容
如果他要加锁访问的行上有锁，他才要检测。

详细解释：

1. 一致性读不会加锁，就不需要做死锁检测；

2. 并不是每次死锁检测都都要扫所有事务。比如某个时刻，事务等待状态是这样的：
   B在等A，
   D在等C，
   现在来了一个E，发现E需要等D，那么E就判断跟D、C是否会形成死锁，这个检测不用管B和A

其他网友的解释：

死锁检测其实就是算法，环的检测，不必每次遍历一遍当前事务，只需要判断事务链表中，每加入一个新事物后 是否 有环的生成，有就形成死锁。

🏁：2.Innodb行级锁是通过锁索引记录实现的。

比如update t set t.name=‘abc’ where t.name=‘cde’; name字段无索引，那么会锁整张表？如果加了limit 1会怎么样呢？

重磅炸弹💣

点击展开内容
所以，如果update的列没建索引，即使只update一条记录也会锁定整张表。

mysql 版本是 5.7.17，但是在RR隔离级别下锁整张表，RC更新没有用到索引并没有锁全表

版本5.7.13 ，加没加limit，update其他行数据都会失败，但是，加了limit，自增insert成功，但是，在行锁数据的前面加一条数据会被阻塞。没加limit，insert会阻塞，等待其他事务提交

由此分析，加limit 1应该加了间隙性锁，没加就全表锁

👋：问题，上一节讲的dml时会产生读MDL锁（表锁），也就是update会持有读MDL。读和读不互斥。但是对于行锁来说。两个update同时更新一条数据是互斥的。这个是因为多种锁同时存在时，以粒度最小的锁为准的原因么？

点击展开内容
你提的两个update同时更新一条数据，在innodb引擎下，其实与mdl读锁没关系，和表锁也没关系，主要是行锁引发的等待。