创建表结构

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;
select * FROM test_isolation.user_lock where age_idx = 20 for update;

T2 - 事务2受行锁影响的查询操作

允许范围

start transaction;
SELECT * FROM test_isolation.user_lock where age_idx = 9;
SELECT * FROM test_isolation.user_lock where age_idx = 10;
SELECT * FROM test_isolation.user_lock where age_idx = 11;
SELECT * FROM test_isolation.user_lock where age_idx = 19;
SELECT * FROM test_isolation.user_lock where age_idx = 20;
SELECT * FROM test_isolation.user_lock where age_idx = 21;
SELECT * FROM test_isolation.user_lock where age_idx = 29;
SELECT * FROM test_isolation.user_lock where age_idx = 30;
SELECT * FROM test_isolation.user_lock where age_idx = 31;

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

允许范围

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 = 'a', age_idx = 10, age_nidx = 10 WHERE age_idx = 10;
UPDATE test_isolation.user_lock SET name = '11', age_idx = 11, age_nidx = 11 WHERE age_idx = 11;
UPDATE test_isolation.user_lock SET name = '19', age_idx = 19, age_nidx = 19 WHERE age_idx = 19;
UPDATE test_isolation.user_lock SET name = '21', age_idx = 21, age_nidx = 21 WHERE age_idx = 21;
UPDATE test_isolation.user_lock SET name = '29', age_idx = 29, age_nidx = 29 WHERE age_idx = 29;
UPDATE test_isolation.user_lock SET name = 'c', 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 = 11;
DELETE FROM test_isolation.user_lock WHERE age_idx = 19;
DELETE FROM test_isolation.user_lock WHERE age_idx = 21;
DELETE FROM test_isolation.user_lock WHERE age_idx = 29;
DELETE FROM test_isolation.user_lock WHERE age_idx = 30;

锁的范围

DELETE FROM test_isolation.user_lock WHERE age_idx = 20;

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

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_idx = 20;