创建表结构

  1. CREATE TABLE `user_lock` (
  2.   `id` int(11) NOT NULL AUTO_INCREMENT,
  3.   `name` varchar(45) DEFAULT NULL,
  4.   `age_idx` int(11) DEFAULT NULL,
  5.   `age_uidx` int(11) DEFAULT NULL,
  6.   `age_nidx` int(11) DEFAULT NULL,
  7.   PRIMARY KEY (`id`),
  8.   UNIQUE KEY `uidx_age` (`age_uidx`),
  9.   KEY `idx_name` (`name`),
  10.   KEY `idx_age` (`age_idx`),
  11.   KEY `idx_name_age` (`name`,`age_idx`)
  12. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

初始化数据

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

T1 - 设置隔离级别

REPEATABLE READ

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

image.png

T2 - 设置隔离级别

REPEATABLE READ

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

image.png

T1 - 事务1使用唯一索引

  1. start transaction;
  2. select * FROM test_isolation.user_lock where age_uidx = 20;

image.png

T2 - 事务2插入一些数据

  1. start transaction;
  2. INSERT INTO test_isolation.user_lock (`name`, `age_idx`, `age_uidx`, `age_nidx`) VALUES ('9', 9, 9, 9);
  3. INSERT INTO test_isolation.user_lock (`name`, `age_idx`, `age_uidx`, `age_nidx`) VALUES ('10', 10, 100, 10);
  4. INSERT INTO test_isolation.user_lock (`name`, `age_idx`, `age_uidx`, `age_nidx`) VALUES ('11', 11, 11, 11);
  5. INSERT INTO test_isolation.user_lock (`name`, `age_idx`, `age_uidx`, `age_nidx`) VALUES ('19', 19, 19, 19);
  6. INSERT INTO test_isolation.user_lock (`name`, `age_idx`, `age_uidx`, `age_nidx`) VALUES ('20', 20, 200, 20);
  7. INSERT INTO test_isolation.user_lock (`name`, `age_idx`, `age_uidx`, `age_nidx`) VALUES ('21', 21, 21, 21);
  8. INSERT INTO test_isolation.user_lock (`name`, `age_idx`, `age_uidx`, `age_nidx`) VALUES ('29', 29, 29, 29);
  9. INSERT INTO test_isolation.user_lock (`name`, `age_idx`, `age_uidx`, `age_nidx`) VALUES ('30', 30, 300, 30);
  10. INSERT INTO test_isolation.user_lock (`name`, `age_idx`, `age_uidx`, `age_nidx`) VALUES ('31', 31, 31, 31);
  11. INSERT INTO test_isolation.user_lock (`name`, `age_idx`, `age_uidx`, `age_nidx`) VALUES ('39', 39, 39, 39);
  12. INSERT INTO test_isolation.user_lock (`name`, `age_idx`, `age_uidx`, `age_nidx`) VALUES ('40', 40, 400, 40);
  13. INSERT INTO test_isolation.user_lock (`name`, `age_idx`, `age_uidx`, `age_nidx`) VALUES ('41', 41, 41, 41);
  14. INSERT INTO test_isolation.user_lock (`name`, `age_idx`, `age_uidx`, `age_nidx`) VALUES ('49', 49, 49, 49);
  15. INSERT INTO test_isolation.user_lock (`name`, `age_idx`, `age_uidx`, `age_nidx`) VALUES ('50', 50, 500, 50);
  16. INSERT INTO test_isolation.user_lock (`name`, `age_idx`, `age_uidx`, `age_nidx`) VALUES ('51', 51, 51, 51);

image.png

T2 - 唯一索引令间隙锁失效

对于具有唯一搜索条件的唯一索引，InnoDB仅锁定找到的索引记录，而不锁定其前的间隙。

image.png