创建表结构

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 and age_idx < 40;

or

start transaction;
UPDATE test_isolation.user_lock SET age_idx = 0 WHERE age_idx > 20 and age_idx < 40;

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 ('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 ('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 ('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);
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);

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 = '20', age_idx = 20, age_nidx = 20 WHERE age_idx = 20;
UPDATE test_isolation.user_lock SET name = '50', age_idx = 50, age_nidx = 50 WHERE age_idx = 50;

锁的范围

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 = '40', age_idx = 40, age_nidx = 40 WHERE age_idx = 40;

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

允许范围

DELETE FROM test_isolation.user_lock WHERE age_idx = 10;
DELETE FROM test_isolation.user_lock WHERE age_idx = 20;
DELETE FROM test_isolation.user_lock WHERE age_idx = 50;

锁的范围

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

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

COMMIT

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

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);
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);
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 = '40', age_idx = 40, age_nidx = 40 WHERE age_idx = 40;
DELETE FROM test_isolation.user_lock WHERE age_idx = 30;
DELETE FROM test_isolation.user_lock WHERE age_idx = 40;
COMMIT