MySQL间隙锁实战，消灭幻读

为什么需求空隙锁

咱们来一个简单业务，计算test表查询有12条数据，咱们先承认是12条数据。承认后，别的一个会话履行刺进数据操作，咱们再检查承认一下是否12条数据操作。最后咱们把数据数目刺进到t1表，提交后， 咱们查询t1表，居然查出来是13。

这种情况加上for update也没有用，由于for update是行级锁【记录锁】，行级锁只能保障多个目标变更同一个数据有序进行。幻读的本质是由于其它用户在刺进数据，怎么保障当时业务进行时，其它业务没有刺进数据，这个便是表级锁的作用。表级锁的牺性太大了，于是MySQL创造了空隙锁。空隙锁的规模禁止一切增、删、查、改操作。

空隙锁原理DEMO

空隙锁创立了一系列行规模 ，在这个规模不允许刺进数据，在行规模外能够刺进数据，这样大大提高了性能。创立空隙锁命令SELECT * FROM 表名 WHERE 主键 BETWEEN 空隙上限 AND 空隙下限 FOR UPDATE;，也能够空隙上限 > XX and 空隙下限 < XX来表示。

从业务理解视点，咱们认为创立空隙锁，例如10到20之间，不允许刺进数据，直接便是，SELECT * FROM 表名 WHERE 主键 BETWEEN 10 AND 20 FOR UPDATE; 完全错了，大错特错，空隙规模必须树立主键索引之上，主键索引才是真正的鸿沟，必须要完结 空隙到主键的映射。

举一个简单的例子。

#创立test表
CREATE TABLE `test` (
  `id` int(1) NOT NULL AUTO_INCREMENT,
  `id2` int(2) NOT NULL,
  `name` varchar(8) DEFAULT NULL,
  PRIMARY KEY (`id`),
  key  key_id2(id2)
);

#刺进两行数据
insert into  test  (id,id2,name) values(2,2,'test2'),(7,7,'test7');

#再刺进一行数据
insert into  test  (id,id2,name) values(15,15,'test15');

# 现在test表空隙规模有3条鸿沟  2、7、15
mysql> select * from test;
 ----- ----- --------- 
| id  | id2 | name    |
 ----- ----- --------- 
|   2 |   2 | test2   |
|   7 |   7 | test7   |
|  15 |  15 | test15  |
 ----- ----- --------- 
3 rows in set (0.00 sec)
begin;
#   创立一个小于鸿沟15, 咱们界说【2,10】， 2是上限，10是下限
SELECT * FROM test WHERE id BETWEEN 2 AND 10 FOR UPDATE;
上述空隙锁的树立，咱们原意是2至10的规模内，不允许刺进数据，11之后的数据都能刺进，结果11到15的数据都无法刺进。
insert into  test  (id,id2,name) values(11,11,'test11');    ERROR 1205 (HY000)  block
insert into  test  (id,id2,name) values(12,12,'test12');    ERROR 1205 (HY000)  block
insert into  test  (id,id2,name) values(13,13,'test13');	ERROR 1205 (HY000)  block
insert into  test  (id,id2,name) values(14,14,'test14');	ERROR 1205 (HY000)  block
insert into  test  (id,id2,name) values(15,15,'test15');	ERROR 1205 (HY000)  block
咱们树立的空隙最大规模是10，映射到15,15是这个空隙锁最大值。
由于鸿沟15导致12、13、14、15不能进去，反观16、17就能成功。
insert into  test  (id,id2,name) values(16,16,'test16');   success 
insert into  test  (id,id2,name) values(17,17,'test17');   success
为什么10会映射到15呢?

# 再来一遍，同样是3条鸿沟  2、7、15,  这次咱们要大于鸿沟15,咱们界说一个【2,20】的空隙锁
begin;
SELECT * FROM test WHERE id BETWEEN 2 AND 20 FOR UPDATE;
insert into  test  (id,id2,name) values(12,12,'test12');    ERROR 1205 (HY000)  block
insert into  test  (id,id2,name) values(13,13,'test13');	ERROR 1205 (HY000)  block
insert into  test  (id,id2,name) values(14,14,'test14');	ERROR 1205 (HY000)  block
insert into  test  (id,id2,name) values(15,15,'test15');	ERROR 1205 (HY000)  block

#发现原来的16、17都无法刺进数据
insert into  test  (id,id2,name) values(16,16,'test16');   ERROR 1205 (HY000)  block 
insert into  test  (id,id2,name) values(17,17,'test17');   ERROR 1205 (HY000)  block

#试了更大的数据，都无法正常刺进，这迹象表明它的空隙下限无限，
insert into  test  (id,id2,name) values(21,21,'test21');  ERROR 1205 (HY000)  block
insert into  test  (id,id2,name) values(1600,1600,'test1600');ERROR 1205 (HY000)  block
insert into  test  (id,id2,name) values(170,170,'test170');ERROR 1205 (HY000)  block

#  可是咱们摸到它的上限，【2,10】和【2,20】都能刺进数据
insert into  test (id,id2,name) values(1,1,'test1');   susssess
空隙表上限与空隙表下限【2,10】和【2,20】  与  主键索引鸿沟的关系 【2、7、15 】 
从【2,10】拿出2，与【2、7、15 】对比，2没有比2大，停止于2，
从【2,10】拿出10，与【2、7、15 】对比，10比15小，停止于15，
最后【2,10】转换成 【2,15】,【2,15】的意思是从2到15的规模不允许刺进数据，除非业务完毕。
从【2,20】拿出2，与【2、7、15 】对比，2没有比2大，停止于2，
从【2,20】拿出10，与【2、7、15 】对比，20比15大，一直没有停止，
最后【2,20】转换成 【2,无限】，【2,无限】的意思是从2到15的规模不允许刺进数据，除非业务完毕。

# 要害上限的取值必定要比主键索引大，下限的取值必定要比主键索引小。

空隙锁实战

实战一个例子，现在test表主键索引如下，根据主键索引鸿沟有【1,2,7,14,15,120,121,122,150,170】

mysql> select * from test;
 ----- ----- --------- 
| id  | id2 | name    |
 ----- ----- --------- 
|   1 |   1 | test1   |
|   2 |   2 | test2   |
|   7 |   7 | test7   |
|  14 |  14 | test0   |
|  15 |  15 | test15  |
| 120 | 120 | test0   |
| 121 | 121 | test121 |
| 122 | 122 | test122 |
| 150 | 150 | test150 |
| 170 | 170 | test170 |
 ----- ----- --------- 
10 rows in set (0.00 sec)

#创立一个空隙锁，咱们界说【100,130】， 100是上限，130是下限
#  根据  【1,2,7,14,15,120,121,122,150,170】

#  100比【1,2,7,14,15】都大，可是比120小，100停止于15
#  130【1,2,7,14,15,120,121,122】都大，可是比150小，130停止于150

#最后【100,130】转换成 【15,150】,【15,150】的意思是从2到15的规模不允许刺进数据，除非业务完毕。

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

mysql> SELECT * FROM test WHERE id BETWEEN 100 AND 130 FOR UPDATE;
 ----- ----- --------- 
| id  | id2 | name    |
 ----- ----- --------- 
| 120 | 120 | test0   |
| 121 | 121 | test121 |
| 122 | 122 | test122 |
 ----- ----- --------- 
3 rows in set (0.00 sec)

#测验

mysql> insert into  test (id,id2,name) values(149,149,'test149');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into  test (id,id2,name) values(99,99,'99');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into  test (id,id2,name) values(16,16,'test16');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
l

mysql> insert into  test (id,id2,name) values(151,15,'test151');
Query OK, 1 row affected (0.00 sec)

mysql> delete from test where id=14;
Query OK, 1 row affected (0.01 sec)

总结

• 空隙的规律，按着鸿沟索引，上限和下限跳过最大的，找到比它小的便是它的对应值
• 下限必定要比主键索引小，假如主键索引没有比下限大，或许相等， 索规模便是无限。
• 真实生产环境比测验环境好，主键索引大多数有序递加排列，可是开发体验依然较杂乱，由于上限和下限还要根据实际的主键索引做匹对。
• mysql锁分三种，行锁【记录锁】、空隙锁、临键锁【记录 空隙】，通过三种锁完结4种安全程度不同的隔离等级。MysSQL的 RR加上空隙锁能够根绝幻读，可是根据RC等级是不能实现空隙锁的，由于RC是读已提交，与空隙有抵触。

思考题

为什么分布式数据库不必空隙锁?