一条复杂SQL的加锁实现分析
再来看一个稍微复杂点的SQL，用于说明MySQL加锁的另外一个逻辑。SQL用例如下：
Id作为主键，联合索引idx_t1_pu（时间戳，UserId）
SQL：delete from t1 where pubtime > 1 and pubtime < 20 and userid='hdc' and comment is not null;
假定在Repeatable Read隔离级别下，同时，假设SQL走的是idx_t1_pu索引
拆分SQL中的where条件参考
Index key：pubtime > 1 and puptime < 20。用于确定SQL在idx_t1_pu索引上的查询范围。
Index Filter：userid = 'hdc' 。在idx_t1_pu索引上进行过滤。
Table Filter：comment is not NULL。在idx_t1_pu索引上无法过滤，只能在聚簇索引上过滤。
在分析出SQL where条件的构成之后，再来看看这条SQL的加锁情况 (RR隔离级别)
由Index Key所确定的范围，被加上了GAP锁。Index Filter锁给定的条件何时过滤，视MySQL的版本而定，在MySQL 5.6版本之前，不支持Index Condition Pushdown，在MySQL Server层过滤，在5.6后支持了ICP，则在index上过滤。若不支持ICP，不满足Index Filter的记录，也需要加上记录X锁，若支持ICP，则不满足Index Filter的记录，无需加记录X锁（图中，用红色箭头标出的X锁）。Table Filter对应的过滤条件，则在聚簇索引中读取后，在MySQL Server层面过滤，因此聚簇索引上也需要X锁。
最后，选取出了一条满足条件的记录[8,hdc,d,5,good]，但是加锁的数量，要远远大于满足条件的记录数量。
在Repeatable Read隔离级别下，针对一个复杂的SQL，首先需要提取其where条件。Index Key确定的范围，需要加上GAP锁；Index Filter过滤条件，根据MySQL版本是否支持ICP，加X锁；Table Filter过滤条件，无论是否满足，都需要加X锁。
死锁原理与分析
理解MySQL如何加锁，有两个比较重要的作用：
写出不会发生死锁的SQL；定位出线上产生死锁的原因；
两个死锁的例子
1 两个Session的两条SQL产生死锁
非常好理解，也是最常见的死锁，每个事务执行两条SQL，分别持有了一把锁，然后加另一把锁，产生死锁。
2 两个Session的一条SQL，产生死锁
第二个用例，虽然每个Session都只有一条语句，仍旧会产生死锁。需要用到本文前面提到的MySQL加锁的规则。
Session 1，从name索引出发，读到的[hdc, 1]，[hdc, 6]均满足条件，不仅会加name索引上的记录X锁，而且会加聚簇索引上的记录X锁，加锁顺序为先[1,hdc,100]，后[6,hdc,10]。
Session 2，从pubtime索引出发，[10,6],[100,1]均满足过滤条件，同样也会加聚簇索引上的记录X锁，加锁顺序为[6,hdc,10]，后[1,hdc,100]，跟Session 1的加锁顺序正好相反，如果两个Session恰好都持有了第一把锁，请求加第二把锁，死锁就发生了。
死锁的发生与否，并不在于事务中有多少条SQL语句，死锁的关键在于：两个(或以上)的Session加锁的顺序不一致。分析MySQL每条SQL语句的加锁规则，分析出每条语句的加锁顺序，然后检查多个并发SQL间是否存在以相反的顺序加锁的情况，就可以分析出各种潜在的死锁情况，也可以分析出线上死锁发生的原因。
转载：http://hedengcheng.com/?p=771#_Toc374698321
