初步理解MySQL的gap锁
初识MySQL的gap，觉得这个设计比较独特，和其他数据库的做法不太一样，所以整理一个简单的memo（虽然关于gap锁，相关资料已经很多了）
1. 什么是gap
说白了gap就是索引树中插入新记录的空隙。相应的gap lock就是加在gap上的锁，还有一个next-key锁，是记录+记录前面的gap的组合的锁。
2. gap锁或next-key锁的作用
简单讲就是防止幻读。通过锁阻止特定条件的新记录的插入，因为插入时也要获取gap锁(Insert Intention Locks)。
3. 什么时候会取得gap lock或nextkey lock
这和隔离级别有关,只在REPEATABLE READ或以上的隔离级别下的特定操作才会取得gap lock或nextkey lock。
3.1 REPEATABLE READ
locking reads，UPDATE和DELETE时，除了对唯一索引的唯一搜索外都会获取gap锁或next-key锁。即锁住其扫描的范围。
下面对非唯一索引做个测试。
表定义如下:
mysql> show create table tb2; +-------+------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+------------------------------------------------------------------------------------------------------------------------------------------------+ | tb2 | CREATE TABLE `tb2` ( `id` int(11) DEFAULT NULL, `c1` int(11) DEFAULT NULL, KEY `tb2_idx1` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 | +-------+------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
表中有3条记录： 10,20,30。
mysql> select * from tb2; +------+------+ | id | c1 | +------+------+ | 10 | 0 | | 20 | 0 | | 30 | 0 | +------+------+ 3 rows in set (0.01 sec)
SESSION 1:
SESSION 1中更新id=20的记录
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> update tb2 set c1=2 where id=20; Query OK, 1 row affected (0.04 sec) Rows matched: 1 Changed: 1 Warnings: 0
SESSION 2中，执行插入操作，发现[10,30)范围不能插入数据。
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> insert into tb2 values(9,4); Query OK, 1 row affected (0.00 sec) mysql> insert into tb2 values(10,4); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql> insert into tb2 values(19,4); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql> insert into tb2 values(20,4); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql> insert into tb2 values(21,4); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql> insert into tb2 values(29,4); ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql> insert into tb2 values(30,4); Query OK, 1 row affected (0.01 sec)
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> update tb2 set c1=4 where id=10; Query OK, 0 rows affected (0.00 sec) Rows matched: 1 Changed: 0 Warnings: 0 mysql> update tb2 set c1=4 where id=20; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction mysql> update tb2 set c1=4 where id=30; Query OK, 0 rows affected (0.00 sec) Rows matched: 2 Changed: 0 Warnings: 0
3.2 READ COMMITTED
只会锁住已有记录，不会加gap锁。
3.3 SERIALIZABLE
和REPEATABLE READ的主要区别在于把普通的SELECT变成SELECT ... LOCK IN SHARE MODE，即对普通的select都会获取gap锁或next-key锁。
4. REPEATABLE READ和幻读
在“consistent-read”时，REPEATABLE READ下看到是事务开始时的快照，即使其它事务插入了新行通常也是看不到的，所以在常见的场合可以避免幻读。 但是，"locking read"或更新，删除时是会看到已提交的修改的，包括新插入的行。
下面看一个例子
SESSION 1:
mysql> START TRANSACTION; Query OK, 0 rows affected (0.00 sec) mysql> select id,c1 from tb1 where id=1; +----+------+ | id | c1 | +----+------+ | 1 | 100 | +----+------+ 1 row in set (0.00 sec)
mysql> update tb1 set c1=101 where id =1; Query OK, 1 row affected (0.03 sec) Rows matched: 1 Changed: 1 Warnings: 0
SESSION 1:
mysql> select id,c1 from tb1 where id=1 LOCK IN SHARE MODE; +----+------+ | id | c1 | +----+------+ | 1 | 101 | +----+------+ 1 row in set (0.00 sec) mysql> select id,c1 from tb1 where id=1; +----+------+ | id | c1 | +----+------+ | 1 | 100 | +----+------+ 1 row in set (0.00 sec) mysql> update tb1 set c1=c1+1000 where id=1; Query OK, 1 row affected (0.02 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select id,c1 from tb1 where id=1; +----+------+ | id | c1 | +----+------+ | 1 | 1101 | +----+------+ 1 row in set (0.00 sec)
5. 其它
RR和RC相比还有一个重要的区别，RC下，扫描过但不匹配的记录不会加锁，或者是先加锁再释放，即semi-consistent read。但RR下扫描过记录都要加锁。这个差别对有全表扫描的更新的场景影响极大。详细参考http://hedengcheng.com/?p=771，关于MySQL的加锁处理，这篇文章讲得很透彻！
