一 前言
该文源自于和一个DBA 同行的技术讨论。
RC级别:
create table t1(id int primary key, name varchar(30));
insert into t1 values(1, 'a'),(4, 'c'),
(7, 'b'),(10, 'a'),(20, 'd'),(30, 'b');
commit;案例一
--sess1 --sess2
begin;
delete from t1
where id = 10;
begin;
delete from t1
where id < 9; ## 被阻塞如果会话A,会话B的执行顺序调整一下，会话B 则不会被阻塞。
案例二
--sess1 --sess2
begin;
delete from t1
where id < 9;
begin;
delete from t1
where id = 10;#不会阻塞案例三 sess2 使用 delete from t1 where id < 9 order by id desc; 则没有阻塞。
--sess1 --sess2
begin;
delete from t1
where id = 10;
begin;
delete from t1
where id < 9 order by id desc; ## 没有阻塞为什么呢？
二 基础知识
sql被block住说明存在锁等待/锁冲突，等待其他会话释放锁。其实问题的核心在于 加锁顺序和加锁范围。这里结合 丁奇 《MySQL 实战45讲》中的讲述的 加锁方式(2个原则2个优化1个bug)
原则 1：加锁的基本单位是 next-key lock。
原则 2：查找过程中访问到的对象才会加锁。
优化 1：索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁。
优化 2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。
一个 bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。
在读提交隔离级别下还有一个优化，即：语句执行过程中加上的行锁，在语句执行完成后，就要把“不满足条件的行”上的行锁直接释放了，不需要等到事务提交
三 案例分析
案例一
sess1 持有id = 10 该记录的行锁 lock_mode X locks rec but not gap
sess2 where 条件是 id<9 ，因为id=9 的记录不存在，根据原则 一个bug 故需要申请第一个不满足条件的记录id=10 的next-key ，也就是(7,10]，此时id=10 的行锁被sess1 持有，故sess2产生锁等待被block住。
案例二
sess1 先执行delete t1 where id<9 ，根据RC模式的加锁方式 ，虽然要获取到第一个不满足记录的id=10 的next-key lock ，但是因为id=10 不符合 id<9 的条件，会释放锁，最终降级为 (7,10) 之间的gap lock。
再解释一下:这个过滤操作是 MySQL Sever层做的，也就是 innodb层把包括id=10的记录加锁然后发送给MySQL Server层，然后 MySQL Sever层判断是否where条件可以结束了，可以结束了则将不满足条件的id=10解锁。
sess2 delete where id=10 和 sess1 持有的gap lock不冲突，故可以顺利执行。
案例三
sess1 持有id = 10 该记录的行锁 lock_mode X locks rec but not gap
sess2 where 条件是 id<9 order by id desc ，通过innodb api接口访问数据的时候从获取到第一个满足条件的记录是id=7，不会访问 id=10这条记录。故也不会加上id=10 的next-key lock. 不会与sess1持有的锁冲突。
小结
经过这个几个案例又复习了一次 MySQL的 加锁机制。
