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

表象：快照读(非阻塞读)--伪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;

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