为什么需求空隙锁

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

MySQL空隙锁实战,消除幻读

这种情况加上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是读已提交,与空隙有抵触。

思考题

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