主键id，索引c。
- 下面的语句怎么加锁，何时释放？
该语句会命中d=5一行，对应主键id=5。
因此在select 语句执行完后，id=5一行会加写锁。因两阶段锁协议，写锁会在执行commit语句时释放。
由于字段d无索引，该查询语句会全表扫描。其他被扫到但不满足条件的5行记录会不会被加锁呢？
幻读InnoDB默认事务隔离级别可重复读。
若只在id=5一行加锁，而其他行不加锁：
- 假设只在id=5一行加行锁
session A执行三次查询-Q1、Q2和Q3,SQL语句相同：查所有d=5的行，且使用当前读并加写锁。 - Q1只返回id=5一行
- T2时，session B把id=0一行的d值改成5，因此T3时Q2查出来的是id=0和id=5这两行
- T4时，session C插入（1,1,5），因此T5时Q3查出来的是id=0、id=1和id=5的三行
Q3读到id=1这一行称为“幻读”，即一个事务在前后两次查询同一范围时，后一次查询看到前一次查询没看到的行。
在可重复读下，普通查询是快照读，不会看到别的事务插入的数据。因此，幻读在“当前读”下才会出现。
session B的修改结果，被session A之后的select语句用“当前读”看到，不能称为幻读。幻读仅专指新插入的行。
这三查询都加了for update，都是当前读。当前读就是要能读到所有已提交的记录的最新值。
session B和sessionC的两条语句，执行后就会提交，所以Q2和Q3就应该看到这俩事务的操作效果，所以这和事务的可见性不矛盾。
不，这里还真有问题。
幻读的问题语义问题
session A在T1时刻就声明了，“我要把所有d=5的行锁住，不准别的事务进行读写操作”。而实际上，这个语义被破坏了。
再往session B和session C里面分别加一条SQL语句，你再看看会出现什么现象。
- 假设只在id=5这行加行锁 - 语义被破坏
session B的第二条语句update t set c=5 where id=0，由于在T1，session A 还只是给id=5这行加行锁， 并未给id=0这行加锁。
因此，session B在T2，可执行这两条update。这就破坏了 session A 里Q1语句要锁住所有d=5的行的加锁声明。
同理，session C对id=1这行的修改，也是破坏了Q1的加锁声明。
数据一致性问题
锁是为了保证数据一致性。而这个一致性，不止是DB内部数据状态在此刻的一致性，还包含数据和日志在逻辑上的一致性。
我给session A在T1时刻再加一个更新语句，即：update t set d=100 where d=5。
- 假设只在id=5这一行加行锁 - 数据一致性问题
update的加锁语义和select …for update 一致，所以这时加上这条update语句也很合理。session A声明说“要给d=5的语句加锁”，就是为更新数据，新加的这条update语句就是把它认为加上锁的这行d值修改成了100。
以上序列执行完成后，DB结果是啥呢？
- T1后，id=5这行变成 (5,5,100)，该结果最终在T6时刻正式提交
- T2后，id=0这行变成(0,5,5)
- T4后，表里多了行(1,5,5)
其他行跟该执行序列无关，保持不变。
这样看，这些数据也没啥问题，但再看binlog：
- T2，session B事务提交，写入两条语句
- T4，session C事务提交，写入两条语句
- T6，session A事务提交，写入update t set d=100 where d=5 语句。
放到一起：
update t set d=5 where id=0; /*(0,0,5)*/ update t set c=5 where id=0; /*(0,5,5)*/ insert into t values(1,1,5); /*(1,1,5)*/ update t set c=5 where id=1; /*(1,5,5)*/ update t set d=100 where d=5;/*所有d=5的行，d改成100*/
这个语句序列，不论是拿到备库去执行，还是以后用binlog来克隆一个库，这三行结果都变成了 (0,5,100)、(1,5,100)和(5,5,100)。
即id=0和id=1这两行，发生数据不一致！
为何会数据不一致？这是我们假设“select * from t where d=5 for update这条语句只给d=5这一行，也就是id=5的这一行加锁”导致的。
所以我们认为，上面的设定不合理，要改。
那怎么改？
把扫描过程中碰到的行，也都加上写锁，再来看看执行效果。
- 假设扫描到的行都被加上了行锁
由于session A把所有行都加了写锁，所以session B在执行第一个update语句时就被锁住。需要等到T6时session A提交后，session B才能继续执行。
这样对于id=0这行，在DB的最终结果还是 (0,5,5)。在binlog里执行序列是这样：
insert into t values(1,1,5); /*(1,1,5)*/ update t set c=5 where id=1; /*(1,5,5)*/ update t set d=100 where d=5;/*所有d=5的行，d改成100*/ update t set d=5 where id=0; /*(0,0,5)*/ update t set c=5 where id=0; /*(0,5,5)*/
可见按日志顺序执行，id=0这行的最终结果也是(0,5,5)。所以，id=0这行问题解决了。
但id=1这一行，在DB里结果是(1,5,5)，而根据binlog的执行结果是(1,5,100)，即幻读依旧。
- 为何把所有记录都上锁了，还是阻止不了id=1这行的插入和更新？
在T3时，我们给所有行加锁时，id=1这行还不存在，不存在也就加不上锁。
即使把所有记录都加锁，还是阻止不了新插入的记录，这也是为什么“幻读”会被单独拿出来解决。
- 幻读的原因
行锁只能锁行，但是新插入记录这个动作，要更新的是记录之间的“间隙”。因此，为了解决幻读，InnoDB只好引入间隙锁(Gap Lock)，两个值之间的空隙。比如文章开头的表t，初始化插入了6个记录，这就产生了7个间隙。 - 表t主键索引上的行锁和间隙锁
当执行 select * from t where d=5 for update时，就不止是给数据库中已有的6个记录加上了行锁，还同时加了7个间隙锁。这样就确保了无法再插入新记录。
即在一行行扫描过程中，不仅给行加上了行锁，还给行两边的空隙加上了间隙锁。
数据行是可以加上锁的实体，数据行之间的间隙，也是可以加上锁的实体。
-
两种行锁间的冲突关系
跟行锁有冲突关系的是“另外一个行锁”。
但间隙锁不一样，跟间隙锁存在冲突关系的，是“往这个间隙中插入一个记录”这个操作。
间隙锁之间不存在冲突关系。
举个例子： -
间隙锁之间不互锁
session B不会被堵住。因为表t里并没c=7记录，因此session A加的间隙锁(5,10)。而session B也是在这个间隙加的间隙锁。它们有共同的目标，即：保护这个间隙，不允许插入值。但它们之间不冲突。
间隙锁和行锁合称next-key lock，每个next-key lock是前开后闭区间。即我们的表t初始化以后，如果用select * from t for update要把整个表所有记录锁起来，就形成了7个next-key lock，分别是 (-∞,0]、(0,5]、(5,10]、(10,15]、(15,20]、(20, 25]、(25, +supremum]。
本文没有特别说明，就把间隙锁记为开区间，把next-key lock记为前开后闭区间。
- supremum是啥玩意？
因为+∞是开区间，代码实现上，InnoDB给每个索引加了不存在的最大值supremum，就符合后闭区间了。
间隙锁和next-key lock解决了幻读，但也带来“困扰”。
案例需求
任意锁住一行，若：
- 该行不存在，就插入
- 存在，就更新数据
实现
begin; select * from t where id=N for update; /*如果行不存在*/ insert into t values(N,N,N); /*如果行存在*/ update t set d=N set id=N; commit;
就这？
insert … on duplicate key update
不就能解决。但在有多个唯一键时，该方法无法满足该需求。
逻辑分析
该逻辑一旦有并发，就可能死锁。可这个逻辑每次操作前用for update锁了，已经是最严格模式了，怎么还有死锁？
模拟
两个session并发，假设N=9。
- 间隙锁导致的死锁
不需要用到后面的update语句，就已经死锁。 - session A 执行select … for update，由于id=9这行不存在，因此会加间隙锁(5,10)
- session B 执行select … for update，同样加间隙锁(5,10)，间隙锁之间不冲突，因此可执行成功
- session B 试图插入(9,9,9)，被session A的间隙锁挡住，进入等待
- session A试图插入(9,9,9)，被session B的间隙锁挡住
session互相等待形成死锁。当然，InnoDB的死锁检测马上就发现了这对死锁关系，让session A的insert语句报错返回了。
所以间隙锁的引入可能导致同样语句锁住更大范围。
为解决幻读，引入这么多内容，有更简单方法吗？
没有特别说明，本文分析都是可重复读，间隙锁在可重复读隔离级别下才生效。
所以，你如果把隔离级别设置为读提交，就没间隙锁。
但同时，要解决可能出现的数据和日志不一致问题，要把binlog格式设为row。这也是很多公司使用的配置。
如果读提交隔离级别够用，即业务无需保证可重复读，考虑到读提交下操作数据的锁范围更小（无间隙锁），这个选择就是合适的。
总结如果大家都用读提交，可是逻辑备份时，mysqldump为什么要把备份线程设置成可重复读？
然后，在备份期间，备份线程用的是可重复读，而业务线程用的是读提交。同时存在两种事务隔离级别，会不会有问题？
进一步地，这两个不同的隔离级别现象有什么不一样的，关于我们的业务，“用读提交就够了”这个结论是怎么得到的？
如果业务开发和运维团队这些问题都没有弄清楚，那么“没问题”这个结论，本身就是有问题的。
即使给所有行加上行锁，仍无法解决幻读，因此引入间隙锁。
行锁确实比较直观，判断规则也相对简单，间隙锁的引入会影响系统的并发度，也增加了锁分析的复杂度，但也有章可循。
