为什么需求空隙锁
咱们来一个简单业务,计算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是读已提交,与空隙有抵触。
思考题
为什么分布式数据库不必空隙锁?