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

[git-zjx]

MySQL 的行锁是由存储引擎实现的，行锁就是针对数据表中行记录的锁。行锁比表锁的粒度小，发生锁争用的可能小，并发度高。但行锁比表锁开销大，因为锁的各种操作（包括获取锁、释放锁、以及检查锁状态）都会增加系统开销。

InnoDB行锁是通过给索引上的索引项加锁来实现的。所以，只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁

从两段锁说起

InnoDB 事务中，行锁是在需要的时候才加上，等到事务结束之后才释放，这就是两阶段锁协议。

根据两阶段锁协议，在事务中如果需要锁多个行，要把最可能造成锁冲突、影响并发度的行往后放。

死锁和死锁检测

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

当出现死锁以后，有两种策略：

• 直接进入等待，直到超时。这个超时时间可以通过参数 innodb_lock_wait_timeout 来设置。
• 发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑。

在 InnoDB 中，innodb_lock_wait_timeout 的默认值是 50s，这个等待时间长得无法接受；但是如果将这个参数设置得过小，那么InnoDB可能就分不清锁等待和死锁。所以，第二种策略是比较常用的：主动监测死锁。

但是第二种策略也会面临一个问题：死锁检测可能会极大耗费CPU资源。死锁检测的一般过程是：每当一个事务被锁的时候，就要看看它所依赖的线程有没有被别人锁住，如此循环，最后判断是否出现了循环等待，也就是死锁。

怎么解决由热点行更新导致的性能问题呢？

• 如果能确保这个业务一定不会出现死锁，可以临时把死锁检测关掉。但是这种操作本身带有一定的风险，因为业务设计的时候一般不会把死锁当做一个严重错误，毕竟出现死锁了，就回滚，然后通过业务重试一般就没问题了，这是业务无损的。而关掉死锁检测意味着可能会出现大量的超时，这是业务有损的。
• 控制并发度。但是如果在客户端做并发控制，如果客户端很多，即使每个客户端并发量很小，MySQL服务器的并发量也会很大。因此，这个并发控制要做在数据库服务端。如果有中间件，可以考虑在中间件实现；如果团队有能修改 MySQL 源码的人，也可以做在 MySQL 里面。基本思路就是，对于相同行的更新，在进入引擎之前排队。这样在 InnoDB 内部就不会有大量的死锁检测工作了。
• 从设计上改善，通过将一行改成逻辑上的多行来减少锁冲突

问题

1. 死锁检测是每条事务执行前都会进行检测吗？
   如果要加锁访问的行上有锁才要检测。一致性读不会加锁，就不需要做死锁检测； 并不是每次死锁检测都都要扫所有事务。比如某个时刻，事务等待状态是这样的： B在等A， D在等C，现在来了一个E，发现E需要等D，那么E就判断跟D、C是否会形成死锁，这个检测不用管B和A
2. 表锁同一张表在同一时刻只能有一个更新。但是表级锁中的MDL锁，DML语句会产生MDL读锁，而MDL读锁不是互斥的，也就是说一张表可以同时有多个dml语句操作。感觉这两种说法有点矛盾
   不矛盾，MDL锁和表锁是两个不同的结构。
   比如：你要在myisam 表上更新一行，那么会加MDL读锁和表的写锁；然后同时另外一个线程要更新这个表上另外一行，也要加MDL读锁和表写锁。第二个线程的MDL读锁是能成功加上的，但是被表写锁堵住了。从语句现象上看，就是第二个线程要等第一个线程执行完成。
3. 多种锁同时存在时，以粒度最小的锁为准么？
   如果有多种锁，必须得“全部不互斥”才能并行，只要有一个互斥，就得等
4. 当备库用–single-transaction 做逻辑备份的时候，如果从主库的 binlog 传来一个 DDL 语句会怎样？

Q1:SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Q2:START TRANSACTION  WITH CONSISTENT SNAPSHOT；
/* other tables */
Q3:SAVEPOINT sp;
/* 时刻 1 */
Q4:show create table `t1`;
/* 时刻 2 */
Q5:SELECT * FROM `t1`;
/* 时刻 3 */
Q6:ROLLBACK TO SAVEPOINT sp;
/* 时刻 4 */
/* other tables */

在备份开始的时候，为了确保 RR（可重复读）隔离级别，再设置一次 RR 隔离级别 (Q1);
启动事务，这里用 WITH CONSISTENT SNAPSHOT 确保这个语句执行完就可以得到一个一致性视图（Q2)；(Q2是启动了一致性视图，但一致性视图不包含表结构)
设置一个保存点，这个很重要（Q3）；
show create 是为了拿到表结构 (Q4)，然后正式导数据 （Q5），回滚到 SAVEPOINT sp，在这里的作用是释放 t1 的 MDL 锁 （Q6）。
DDL 从主库传过来的时间按照效果不同，我打了四个时刻。题目设定为小表，我们假定到达后，如果开始执行，则很快能够执行完成。
参考答案如下：

1. 如果在 Q4 语句执行之前到达，现象：没有影响，备份拿到的是 DDL 后的表结构。
2. 如果在“时刻 2”到达，则表结构被改过，Q5 执行的时候，报 Table definition has changed, please retry transaction，现象：mysqldump 终止；
3. 如果在“时刻 2”和“时刻 3”之间到达，mysqldump 占着 t1 的 MDL 读锁，binlog 被阻塞，现象：主从延迟，直到 Q6 执行完成。
4. 从“时刻 4”开始，mysqldump 释放了 MDL 读锁，现象：没有影响，备份拿到的是 DDL 前的表结构。