mysql 面试知识点笔记（七）RR如何避免幻读及非阻塞读、范式

2019独角兽企业重金招聘Python工程师标准>>>

表象：快照读（非阻塞读）--伪MVCC （Multi-Version Concurrent Controll多版本并发控制）

内在：next-key锁(record锁+gap锁) rr serializabel 都支持gap锁

问：在rr下对主键索引或者唯一索引会用gap锁嘛？

如果where条件全部命中，则不会用gap锁，只会加record锁（行锁）

如果部分命中或全不命中则会加gap锁

比如查询id in(1,3,5) ，id是主键。 查到了 1、3 ，没有查到5则会加gap锁，在3~+∞的区间。

如果加锁是主键之外的索引 则会在当前索引以及主键索引上都上排他锁。如下图：

-- 创建测试表
CREATE TABLE `tb`(
`name` varchar(10) NOT NULL,
`id` int(100) NOT NULL DEFAULT '0',
PRIMARY KEY(`name`),
UNIQUE KEY `unique_id` (`id`)
) ENGINE = INNODB DEFAULT CHARSET=utf8;

插入以下数据：

不加gap锁的情况

-- 第一个会话
SELECT @@tx_isolation;
set SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

start TRANSACTION;
DELETE from tb  where id = 9;
-- 执行完delete之后执行事务2的insert 是成功的 因为id=9存在 只加了行锁
ROLLBACK;

-- 第二个会话
SELECT @@tx_isolation;
set SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

start TRANSACTION;
insert into tb VALUES('i',10);
ROLLBACK;

加gap锁的情况

-- 1、全不命中
-- 第一个会话
start TRANSACTION;
DELETE from tb  where id = 7;
-- 这里在执行事务2的insert 则会block 证明加了gap锁
ROLLBACK;

-- 第二个会话
start TRANSACTION;
insert into tb VALUES('i',8);
ROLLBACK;

-- 2、部分命中情况
-- 第一个会话
start TRANSACTION;
-- 这里命中5和9 7没有命中 在6~8加了gap锁 如果是in (5,6,9)  则不会加gap锁
select * from tb where id in (5,7,9) lock in share mode;
ROLLBACK;

-- 第二个会话
start TRANSACTION;
-- 没有block
insert into tb VALUES('i1',4);
-- 有block
insert into tb VALUES('i2',7);
-- 有block
insert into tb VALUES('i3',8);
-- 没有block
insert into tb VALUES('i4',10);
ROLLBACK;

1. Gap锁还会用在非唯一索引或者不走索引的当前读中

• 非唯一索引都是到点的左开右闭区间

测试表 非唯一索引

CREATE TABLE `tb1`(
`name` varchar(10) NOT NULL,
`id` int(100) NOT NULL DEFAULT '0',
PRIMARY KEY(`name`),
KEY `non_unique_id` (`id`)
) ENGINE = INNODB DEFAULT CHARSET=utf8;

-- 第一个会话
start TRANSACTION;
DELETE from tb1  where id = 9; 
-- 此时执行会话2的insert 会被block
ROLLBACK;

-- 第二个会话
start TRANSACTION;
insert into tb1 VALUES('test',9);
#6-11的左开右闭区间
-- 不会block
insert into tb1 VALUES('test1',5);
-- 会block
insert into tb1 VALUES('test2',7);
-- 不会block
insert into tb1 VALUES('test3',12);
-- bb是主键c的左边 所以不会block
insert into tb1 VALUES('bb',6);
-- dd是主键c的右边 所以会block
insert into tb1 VALUES('dd',6);
-- e是主键f的左边 所以不会block
insert into tb1 VALUES('e',11);
-- g是主键f的右边 所以会block
insert into tb1 VALUES('g',11);
ROLLBACK;

• 不走索引（会对所有的gap加锁 就是锁表了）

测试表 无索引

CREATE TABLE `tb2`(
`name` varchar(10) NOT NULL,
`id` int(100) NOT NULL DEFAULT '0',
PRIMARY KEY(`name`)
) ENGINE = INNODB DEFAULT CHARSET=utf8;

-- 第一个会话
start TRANSACTION;
DELETE from tb2  where id = 9; 
ROLLBACK;

-- 第二个会话
start TRANSACTION;
-- 锁表 这里的insert是block的
insert into tb2 VALUES('test',2);
ROLLBACK;

MySQL InnoDB非阻塞式读的实现原理：MVCC

MySQL InnoDB使用MVCC来实现非阻塞式读，在这个模式下，数据库会为每个数据记录维护多个版本。在可重复读隔离级别下，事务第一次查询记录的时候，会记录下一个时间点，在该事务内如果再次（可是以不同的SELECT）查询相同的数据的话，事务只会取时间点前的记录版本，这样在不需要对数据加锁的情况下就实现了可重复读的隔离级别了，而且并发性能更好。在同一事务内多次查询同一数据，也不是就返回一个固定的记录版本，如果事务先查询了某个记录，随后自己又更新了这个数据，等再查询该数据的话，返回的就是自己更新过后的数据版本了。此处来自：https://ouyanggod.iteye.com/blog/2165668

范式参考：https://www.cnblogs.com/gdwkong/p/9012262.html

转载于:https://my.oschina.net/pentakill/blog/3045452