#MySql-二阶段锁定协议##前言本篇博客主要讲述MySql的二阶段锁定（2PL）协议（仅限innodb），不是两阶段提交（2PC）协议，区别如下：2PL，两阶段锁定协议：主要用于单机事务中的一致性和隔离性。2PC，两阶段提交协议：主要用于分布式事务。MySql本身有一个MVCC（多版本控制）控制性能。本文不考虑这个技术，只考虑MySql本身的加锁协议。##什么时候加锁？当记录被更新或者（selectforupdate,sharemodel中的锁）时，记录会被加锁（共享锁，排他锁，意向锁，间隙锁，nextkey锁等），为了简单起见，本文不考虑锁的类型。##什么是事务中的两阶段锁，分为加锁（lock）阶段和解锁（unlock）阶段，即所有加锁操作都在加锁操作之前，如下图：##为什么需要两个stagelocking引入2PL是为了保证事务的隔离性，即多个事务在并发的情况下相当于串行执行。下面的阻塞定理在数学上得到证明：如果交易是良构的并且是两阶段的，那么任何合法的调度都是孤立的。具体的数学推导过程请参考《事务处理：概念与技术》一书7.5.8.2节。本书是数据库事务的圣经，无解（虽然中文翻译晦涩难懂，但大家可以继续阅读，强烈推荐）##工程实践中的双级锁-S2PL实际情况下，SQL是千变万化的，条目的数量是不确定的。数据库很难判断事务中什么是加锁阶段，什么是解锁阶段。于是引入了S2PL（Strict-2PL），即在事务中，只有commit或者rollback时才是解锁阶段，其余时间都是加锁阶段。如下图所示：这种情况在实际数据库中很容易实现。##两级锁对性能的影响两级锁上面已经很好的解释了，现在我们分析一下它对性能的影响。考虑以下两种不同的库存扣减方案：方案1:begin;//库存扣减updatet_inventorysetcount=count-5whereid=${id}andcount>=5;//锁定用户账户表select*fromt_user_accountwhereuser_id=123forupdate;//插入订单记录insertintot_trans;commit;方案2:begin;//锁定用户账户表select*fromt_user_accountwhereuser_id=123forupdate;//插入订单记录insertintot_trans;//扣除库存updatet_inventorysetcount=count-5whereid=${id}andcount>=5；承诺；因为它们在同一个事务中，所以这些对数据库的操作应该是等价的。但是在两级锁下确实在性能上有比较大的差距。两种方案的时序如下图所示：因为库存往往是最重要的热点，是整个系统的瓶颈。所以如果采用第二种方案，tps理论上应该可以提升3rt/rt=3倍。这只是业务只有3条SQL的情况，多一条SQL就多一条RT，时间翻倍。值得注意的是：在锁更新到数据库的时间点，才认为锁成功提交到数据库。两次round_trips的前半部分不会被统计，如下图所示：目前只考虑网络Delay，不考虑数据库和应用本身的时间消耗。##基于S2PL的性能优化从上面的例子可以看出，最热的记录需要放在事务***中，这样可以显着提高吞吐量。更进一步：记录越热，越接近事务结束（无论是commit还是rollback）。笔者认为顺序如下：###Avoiddeadlock这也是任何SQL锁都不可避免的。上面说了，交易是按照记录的Key的热度倒序排序的。那么在写代码的时候，任何可能并发的SQL都必须按照这个顺序处理，否则会造成死锁。如下图所示：###selectforupdate和updatewherepredicate计算我们可以直接在update的predicate中写一些简单的判断逻辑来减少加锁时间。考虑以下两种解决方案：解决方案1:begin:intcount=selectcountfromt_inventoryforupdate;ifcount>=5:updatet_inventorysetcount=count-5whereid=123commitelserollback方案2:begin:introws=updatet_inventorysetcount=count-5whereid=123andcount>=5ifrows>0:commit;elerollback;延迟如下图所示：可以看到，通过在update中加入predicate计算，减少了1rt的时间。由于update在执行过程中对满足谓词条件的记录加了和selectforupdate一样的排他锁（具体的锁类型比较复杂，这里不再赘述），所以两者的效果是一样的。#SummaryMySql使用两阶段锁定协议来实现隔离和一致性。只有深入理解这个协议，才能更好的优化我们的SQL，提高系统的吞吐量。
