什么是间隙锁

一直都不明白什么是间隙锁和间隙锁的意义，网上都是互相抄来抄去的内容，要不就是面经里面的套话，反正说的都不是人话，估计写的人自己也不明白再说什么。

后来看了一本正经的数据库的书，一句话就说明白了，间隙锁锁的是尚不存在的资源。我们平时说道多线程的锁也好，分布式锁也好，锁的都是某个存在的且需要共享互斥的资源，而间隙锁锁定了一个范围内还不存在资源，避免其他事物在这个范围中创建这些不存在的资源。这也就是幻读导致的问题。

创建表结构

CREATE TABLE `user_lock` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) DEFAULT NULL,
  `age_idx` int(11) DEFAULT NULL,
  `age_uidx` int(11) DEFAULT NULL,
  `age_nidx` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uidx_age` (`age_uidx`),
  KEY `idx_name` (`name`),
  KEY `idx_age` (`age_idx`),
  KEY `idx_name_age` (`name`,`age_idx`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

初始化数据

INSERT INTO `test_isolation`.`user_lock` (`name`, `age_idx`, `age_uidx`, `age_nidx`) VALUES ('10', 10, 10, 10);
INSERT INTO `test_isolation`.`user_lock` (`name`, `age_idx`, `age_uidx`, `age_nidx`) VALUES ('20', 20, 20, 20);
INSERT INTO `test_isolation`.`user_lock` (`name`, `age_idx`, `age_uidx`, `age_nidx`) VALUES ('30', 30, 30, 30);
INSERT INTO `test_isolation`.`user_lock` (`name`, `age_idx`, `age_uidx`, `age_nidx`) VALUES ('40', 40, 40, 40);
INSERT INTO `test_isolation`.`user_lock` (`name`, `age_idx`, `age_uidx`, `age_nidx`) VALUES ('50', 50, 50, 50);

T1 - 设置隔离级别

REPEATABLE READ

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
select @@global.transaction_isolation,@@transaction_isolation;

T2 - 设置隔离级别

REPEATABLE READ

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
select @@global.transaction_isolation,@@transaction_isolation;

T1 - 事务1开启，删除或更新一条记录

start transaction;
DELETE FROM test_isolation.user_lock where age_idx = 20;

OR

start transaction;
UPDATE test_isolation.user_lock SET name = '20A', age_idx = 20 WHERE age_idx = 20;

T2 - 事务2受间隙锁影响的插入操作

允许的范围

start transaction;
INSERT INTO test_isolation.user_lock (`name`, `age_idx`, `age_uidx`, `age_nidx`) VALUES ('9', 9, 9, 9);
INSERT INTO test_isolation.user_lock (`name`, `age_idx`, `age_uidx`, `age_nidx`) VALUES ('30', 30, 300, 30);
INSERT INTO test_isolation.user_lock (`name`, `age_idx`, `age_uidx`, `age_nidx`) VALUES ('31', 31, 31, 31);
INSERT INTO test_isolation.user_lock (`name`, `age_idx`, `age_uidx`, `age_nidx`) VALUES ('39', 39, 39, 39);
INSERT INTO test_isolation.user_lock (`name`, `age_idx`, `age_uidx`, `age_nidx`) VALUES ('40', 40, 400, 40);
INSERT INTO test_isolation.user_lock (`name`, `age_idx`, `age_uidx`, `age_nidx`) VALUES ('41', 41, 41, 41);
INSERT INTO test_isolation.user_lock (`name`, `age_idx`, `age_uidx`, `age_nidx`) VALUES ('49', 49, 49, 49);
INSERT INTO test_isolation.user_lock (`name`, `age_idx`, `age_uidx`, `age_nidx`) VALUES ('50', 50, 500, 50);
INSERT INTO test_isolation.user_lock (`name`, `age_idx`, `age_uidx`, `age_nidx`) VALUES ('51', 51, 51, 51);

间隙锁范围

INSERT INTO test_isolation.user_lock (`name`, `age_idx`, `age_uidx`, `age_nidx`) VALUES ('10', 10, 100, 10);
INSERT INTO test_isolation.user_lock (`name`, `age_idx`, `age_uidx`, `age_nidx`) VALUES ('11', 11, 11, 11);
INSERT INTO test_isolation.user_lock (`name`, `age_idx`, `age_uidx`, `age_nidx`) VALUES ('19', 19, 19, 19);
INSERT INTO test_isolation.user_lock (`name`, `age_idx`, `age_uidx`, `age_nidx`) VALUES ('20', 20, 200, 20);
INSERT INTO test_isolation.user_lock (`name`, `age_idx`, `age_uidx`, `age_nidx`) VALUES ('21', 21, 21, 21);
INSERT INTO test_isolation.user_lock (`name`, `age_idx`, `age_uidx`, `age_nidx`) VALUES ('29', 29, 29, 29);

T2 - 事务2受行锁影响的更新操作操作

允许范围

UPDATE test_isolation.user_lock SET name = '10', age_idx = 10, age_nidx = 10 WHERE age_idx = 10;
UPDATE test_isolation.user_lock SET name = '30', age_idx = 30, age_nidx = 30 WHERE age_idx = 30;

锁的范围

UPDATE test_isolation.user_lock SET name = '20', age_idx = 20, age_nidx = 20 WHERE age_idx = 20;

T2 - 事务2受行锁影响的删除操作

允许范围

DELETE FROM test_isolation.user_lock WHERE age_idx = 10;
DELETE FROM test_isolation.user_lock WHERE age_idx = 30;

锁的范围

DELETE FROM test_isolation.user_lock WHERE age_idx = 20;

T1 - 事务1提交事务释放间隙锁

COMMIT

T2 - 事务2不再受间隙锁影响了

INSERT INTO test_isolation.user_lock (`name`, `age_idx`, `age_uidx`, `age_nidx`) VALUES ('10', 10, 100, 10);
INSERT INTO test_isolation.user_lock (`name`, `age_idx`, `age_uidx`, `age_nidx`) VALUES ('11', 11, 11, 11);
INSERT INTO test_isolation.user_lock (`name`, `age_idx`, `age_uidx`, `age_nidx`) VALUES ('19', 19, 19, 19);
INSERT INTO test_isolation.user_lock (`name`, `age_idx`, `age_uidx`, `age_nidx`) VALUES ('20', 20, 200, 20);
INSERT INTO test_isolation.user_lock (`name`, `age_idx`, `age_uidx`, `age_nidx`) VALUES ('21', 21, 21, 21);
INSERT INTO test_isolation.user_lock (`name`, `age_idx`, `age_uidx`, `age_nidx`) VALUES ('29', 29, 29, 29);
UPDATE test_isolation.user_lock SET name = '20', age_idx = 20, age_nidx = 20 WHERE age_idx = 20;
DELETE FROM test_isolation.user_lock WHERE age = 20;
COMMIT