20 | 幻读

2020-12-30 17:48:40 浏览数 (1)
代码语言：javascript复制
CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `c` (`c`)
) ENGINE=InnoDB;

insert into t values(0,0,0),(5,5,5),
(10,10,10),(15,15,15),(20,20,20),(25,25,25);

加锁：

代码语言：javascript复制
begin;
select * from t where d=5 for update;
commit;

比较好理解的是，这个语句会命中 d=5 的这一行，对应的主键 id=5，因此在 select 语句执行完成后，id=5 这一行会加一个写锁，而且由于两阶段锁协议，这个写锁会在执行 commit 语句的时候释放。

由于字段 d 上没有索引，因此这条查询语句会做全表扫描。那么，其他被扫描到的，但是不满足条件的 5 行记录上，会不会被加锁呢？

答案是会的。

demo： 当前事务隔离级别：rr ，间隙锁在可重复读隔离级别下才有效。若没有特殊说明，默认是可重复读隔离级别。 表：

Gap Lock在InnoDB的唯一作用就是防止其他事务的插入操作，以此防止幻读的发生。

  1. session A 执行 select … for update 语句，由于 id=9 这一行并不存在，因此会加上间隙锁 (5,10);
  2. session B 执行 select … for update 语句，同样会加上间隙锁 (5,10)，间隙锁之间不会冲突，因此这个语句可以执行成功；
  3. session B 试图插入一行 (9,9,9)，被 session A 的间隙锁挡住了，只好进入等待；
  4. session A 试图插入一行 (9,9,9)，被 session B 的间隙锁挡住了。

如果SESSION 1的表扫描没有用到索引，那么gap或next-key锁住的范围是整个表，即任何值都不能插入。

间隙锁的引入，可能会导致同样的语句锁住更大的范围，这其实是影响了并发度的。

代码语言：javascript复制
-- session1
BEGIN;
SELECT * FROM t WHERE c=10 LOCK in SHARE mode ; -- 存在 ，成功
-- session2
BEGIN;
SELECT * FROM t WHERE c=10 LOCK in SHARE mode ; -- ，存在，成功



-- session1
BEGIN;
SELECT * FROM t WHERE c=10 LOCK in SHARE mode ; -- 存在 ，成功
-- session2
BEGIN;
SELECT * FROM t WHERE c=10 for UPDATE ; -- 失败，阻塞



-- session1
BEGIN;
SELECT * FROM t WHERE c=7 LOCK in SHARE mode ; -- c=7不存在，但是加了间隙锁间隙锁 (5,10)
-- session2
BEGIN;
SELECT * FROM t WHERE c=7 for UPDATE ; -- -- c=7不存在，重复加了间隙锁间隙锁 (5,10)

-- 它们有共同的目标，即：保护这个间隙，不允许插入值。但，它们之间是不冲突的。

间隙锁和行锁合称 next-key lock，每个 next-key lock 是前开后闭区间。也就是说，我们的表 t 初始化以后，如果用 select * from t for update 要把整个表所有记录锁起来，就形成了 7 个 next-key lock，c锁住的范围分别是 (-∞,0]、(0,5]、(5,10]、(10,15]、(15,20]、(20, 25]、(25, supremum]。

冲突关系，读锁就是共享锁，写锁就是排他锁。

测试demo2:

代码语言：javascript复制
-- 测试demo2：
CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `c` (`c`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `cbank`.`t`(`id`, `c`, `d`) VALUES (0, 0, 0);
INSERT INTO `cbank`.`t`(`id`, `c`, `d`) VALUES (2, 5, 5);
INSERT INTO `cbank`.`t`(`id`, `c`, `d`) VALUES (5, 10, 10);
INSERT INTO `cbank`.`t`(`id`, `c`, `d`) VALUES (9, 15, 15);
INSERT INTO `cbank`.`t`(`id`, `c`, `d`) VALUES (12, 25, 25);
INSERT INTO `cbank`.`t`(`id`, `c`, `d`) VALUES (15, 20, 20);


-- session1
BEGIN;
SELECT * FROM t WHERE c=15 for UPDATE;

-- session2
BEGIN;
UPDATE t set d =26 WHERE c =15; -- 行锁，失败
UPDATE t set d =26 WHERE c =10; -- 行锁，成功
INSERT INTO `cbank`.`t`(`id`, `c`, `d`) VALUES (6, 10, 10);  -- 失败
INSERT INTO `cbank`.`t`(`id`, `c`, `d`) VALUES (4, 10, 10);  -- 成功
INSERT INTO `cbank`.`t`(`id`, `c`, `d`) VALUES (3, 11, 11); -- 失败

INSERT INTO `cbank`.`t`(`id`, `c`, `d`) VALUES (11, 19, 19); -- 失败
INSERT INTO `cbank`.`t`(`id`, `c`, `d`) VALUES (11, 20, 20); -- 失败

INSERT INTO `cbank`.`t`(`id`, `c`, `d`) VALUES (11, 21, 21); -- 成功

gap锁：

如图，c的范围有gap锁，范围是(10,20),开闭区间，需要开id的排序。

commit key lock select session

0 人点赞