MySql-丁奇-学习笔记-如何减少行锁对性能的影响(死锁检测带来的性能消耗)

本文链接：https://blog.youkuaiyun.com/Ives_WangShen/article/details/110311857

一. 两阶段锁协议

使用InnoDB，如下的操作序列中，事务B执行会有什么现象？

事务A	事务B
begin; update t set k=k+1 where id=1; update t set k=k+1 where id=2;
	begin; update t set k=k+2 where id=1;
commit;

答：B的update语句会阻塞，直到A执行commit后才会执行。

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

优化方案

如果在事务中需要锁多个行，要把最有可能造成冲突、最有可能影响并发度的锁尽量往后放。
举个例子，做一个电影院卖票服务，其数据库操作过程如下：

用户A账户余额扣除电影票钱
影院账户余额增加这张票价
记录一条交易记录

分析一下这个过程，我们需要update 1、2，insert3，而且1、2、3要放在一个事务中。如果有其他顾客也在买票，那么最可能出现冲突的操作就是2，因为他们都要修改电影院账户余额。

由于两阶段锁协议，只有事务结束才会释放行锁，所以把2操作放到最后，比如按照3、1、2的顺序操作，那么影院账户月这一行的所时间就最短。最大程度的减少了事务之间的锁等待，提高了并发度。

二. 死锁和锁等待

问题０
如果影院做活动，可以低价预售一年的电影票，而且活动只做一天。于是活动开始时，MySql就挂了。你登上服务器一看，CPU消耗接近100%，但整个数据库每秒就执行不到100个事务，是怎么回事捏？

1. 死锁

当并发系统中不同线程出现了循环资源依赖，涉及的线程都在等待其他线程释放资源时，就会导致这几个线程都进入无限等待的状态，即死锁。
死锁的详细介绍
比如：

事务A	事务B
begin; update t set k=k+1 where id=1;	begin;
	update t set k=k+2 where id=2;
update t set k=k+2 where id=2;
	update t set k=k+1 where id=1;

2. 死锁的解决方案

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

问题１
ＩｎｎｏＤＢ默认的innodb_lock_wait_timeout是５０ｓ，这个等待时间太长了，如果设置为很小的值，又会出现新的问题，即简单的锁等待由于超时时间短出现误伤。所以正常情况下采用死锁监测的方案。

死锁监测的过程
每当一个事务被锁的时候，都要看看它依赖的线程有没有被别人锁住，如此循环，最后判断是否出现死锁。

问题２
如果所有事务都要更新同一行的场景下会怎样捏？
答：每个新来的线程都会判断会不会因为自己的加入而导致死锁，如果有１０００个并发线程同时要更新同一行，那么死锁监测操作就是（１０００＊１０００＝１００００００）１００万这个量级的。虽然最后可能没有死锁，但是这期间要消耗大量的ＣＰＵ资源。因此就会看到问题０中的情况，ｃｐｕ利用率很高，但是每秒执行不了几个事务。

问题３
如何解决由这种热点行更新导致的性能问题？

如果能确保这个业务不会出现死锁，那么可以临时把死锁检测关了。　这种操作的风险在于业务设计时不会把死锁当成严重错误，出现死锁就回滚，这是业务无损的，如果把死锁检测关了，就可能出现大量的超时，是业务有损的。
控制并发度。　比如同一行同时最多只有１０个线程在更新，那么死锁检测的成本就很低。一个直接的想法就是在客户端做并发控制，但也不可行，因为客户端很多，及时一个客户端只有５个并发线程，汇总到数据库服务端后，峰值并发数也要达到３０００。
因此并发控制要做在服务端　，对于相同行的更新，可以在进入引擎前排队，这样在ＩｎｎｏＤＢ内部就不会有大量的死锁检测了。
将一行改成逻辑上的多行来减少锁冲突，　比如上例中的影院账户，可以考虑放在多条记录上，比如１０个记录，影院账户总额等于这１０个记录的总和。这样每次给影院账户增加余额时就可以随机选择其中一条来加。这样每次冲突概率就变成了原来的１／１０，减少了锁等待个数和死锁检测的ｃｐｕ消耗。

三. 思考

如果你要删除一个表里的前１００００行数据，有以下三种方法：
１．直接执行　ｄｅｌｅｔｅ　ｆｒｏｍ　Ｔ　ｌｉｍｉｔ　１００００；
２．在一个连接中循环执行２０次　ｄｅｌｅｔｅ　ｆｒｏｍ　Ｔ　ｌｉｍｉｔ　５００；
３．在２０个连接中同时执行　ｄｅｌｅｔｅ　ｆｒｏｍ　Ｔ　ｌｉｍｉｔ　５００；
哪种方法比较好？