事务隔离级别-REPEATABLE-READ && 间隙锁

2019独角兽企业重金招聘Python工程师标准>>>

事务隔离级别-REPEATABLE-READ && 间隙锁

表结构

create table t(
 name varchar(255) primary key,
 id int not null,
 key idx_id (id)
);
insert into t(name,id) values ('a',15),
('b',10),('c',6),('d',10),('f',11),('zz',2);

Session A

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

Session B

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

Session A

mysql> select * from t;
+------+----+
| name | id |
+------+----+
| zz   |  2 |
| c    |  6 |
| b    | 10 |
| d    | 10 |
| f    | 11 |
| a    | 15 |
+------+----+
6 rows in set (0.00 sec)

mysql> select * from t where id = 10 for update;
+------+----+
| name | id |
+------+----+
| b    | 10 |
| d    | 10 |
+------+----+
2 rows in set (0.00 sec)

我们在Session A里执行当前读的select...for update操作，那这条sql语句时如何加锁的呢？看下图，

这幅图中有一个GAP锁，而且GAP锁看起来也不是加在记录上的，倒像是加载两条记录之间的位置，GAP锁有何用？

其实这个多出来的GAP锁，就是RR隔离级别，相对于RC隔离级别，不会出现幻读的关键。确实，GAP锁锁住的位置，也不是记录本身，而是两条记录之间的GAP。所谓幻读，就是同一个事务，连续做两次当前读 (例如：select * from t1 where id = 10 for update;)，那么这两次当前读返回的是完全相同的记录 (记录数量一致，记录本身也一致)，第二次的当前读，不会比第一次返回更多的记录 (幻象)。

如何保证两次当前读返回一致的记录，那就需要在第一次当前读与第二次当前读之间，其他的事务不会插入新的满足条件的记录并提交。为了实现这个功能，GAP锁应运而生。

如图中所示，有哪些位置可以插入新的满足条件的项 (id = 10)，考虑到B+树索引的有序性，满足条件的项一定是连续存放的。记录[6,c]之前，不会插入id=10的记录；[6,c]与[10,b]间可以插入[10, aa]；[10,b]与[10,d]间，可以插入新的[10,bb],[10,c]等；[10,d]与[11,f]间可以插入满足条件的[10,e],[10,z]等；而[11,f]之后也不会插入满足条件的记录。因此，为了保证[6,c]与[10,b]间，[10,b]与[10,d]间，[10,d]与[11,f]不会插入新的满足条件的记录，MySQL选择了用GAP锁，将这三个GAP给锁起来。

Insert操作，如insert [10,aa]，首先会定位到[6,c]与[10,b]间，然后在插入前，会检查这个GAP是否已经被锁上，如果被锁上，则Insert不能插入记录。因此，通过第一遍的当前读，不仅将满足条件的记录锁上 (X锁)，同时还是增加3把GAP锁，将可能插入满足条件记录的3个GAP给锁上，保证后续的Insert不能插入新的id=10的记录，也就杜绝了同一事务的第二次当前读，出现幻象的情况。

然后我们在Session B中做相应的Insert操作，验证一下上面的说法。

Session B

执行以下插入操作

mysql> select * from t;
+------+----+
| name | id |
+------+----+
| zz   |  2 |
| c    |  6 |
| b    | 10 |
| d    | 10 |
| f    | 11 |
| a    | 15 |
+------+----+
6 rows in set (0.00 sec)

mysql> insert t(name,id) values ('aa',10);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert t(name,id) values ('bb',10);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert t(name,id) values ('e',11);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql>

可以看到我们执行了多次插入，都失败了，就是因为GAP上加了间隙锁的原因，导致插入不成功，也就防止了Session A第二次当前读的时候不会出现幻读。

当执行这条sql时insert t(name,id) values ('bb',10)时，相应的锁的信息；

mysql> use information_schema
Database changed
mysql> select * from INNODB_LOCKS;
+--------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+
| lock_id      | lock_trx_id | lock_mode | lock_type | lock_table | lock_index | lock_space | lock_page | lock_rec | lock_data |
+--------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+
| 11662:65:4:5 | 11662       | X,GAP     | RECORD    | `test`.`t` | idx_id     |         65 |         4 |        5 | 10, 'd'   |
| 11661:65:4:5 | 11661       | X         | RECORD    | `test`.`t` | idx_id     |         65 |         4 |        5 | 10, 'd'   |
+--------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+
2 rows in set (0.00 sec)

===========END===========

转载于:https://my.oschina.net/xinxingegeya/blog/499867