mysql 隔离级别测试

ddatsh

dev db #mysql 2020/05/25

解决并发事务互相干扰问题，MySQL 事务隔离级别：

READ UNCOMMITTED：读未提交
READ COMMITTED：读已提交
REPEATABLE READ：可重复读
SERIALIZABLE：序列化

Read UnCommited

可解决丢失更新

create table users (
    id int auto_increment not null primary key,
    name char(10) not null,
    state int not null
); 
insert into users values(1,'dd',0);

事务1

set autocommit = 0;
set global transaction isolation level read uncommitted ;
set session transaction isolation level read uncommitted ; 
start transaction;
update users set state=state+1 where id=1;
select sleep(10);
commit;

事务2

set autocommit = 0;
set global transaction isolation level read uncommitted ;
set session transaction isolation level read uncommitted ; 
start transaction;
update users set state=state+1 where id=1;
commit;

数据库的一级封锁锁（修改前加X锁，事务结束释放X锁，读不加锁）

事务1先执行修改，修改前申请持有X锁，事务结束释放X锁；事务2也执行修改操作，修改前也申请持有X锁

事务1，10秒才提交释放锁，事务2申请持锁需要等待，直到事务1结束才能获取到锁的持有权进行修改

这样对同一数据的修改会变成串行化的修改，所以不会出现因为并发只进行一次+1的情况，也就不会出现丢失修改的问题

RU 不能解决脏读和不可重复读

另一个事务回滚就会产生脏读

set autocommit = 0;
set global transaction isolation level read uncommitted ;
set session transaction isolation level read uncommitted ; 
start transaction;
update users set state=1 where id=1;
select sleep(5);
rollback;

set autocommit = 0;
set global transaction isolation level read uncommitted ;
set session transaction isolation level read uncommitted ; 
start transaction;
select * from users where id=1;
commit;

事务2结果=1

事务1和事务2都执行结束时，再进行一次查询=0

事务1先修改，修改前申请持有X锁，

事务2读，不需要申请持锁，读出的数据是事务1修改后的，而此时事务1回滚，修改的数据被还原，产生脏读现象

SERIALIZABLE

读加共享锁，写加排他锁，读写互斥

悲观锁理论，所有操作串行化执行，数据更加安全，但是并发能力非常差

业务并发的特别少或者没有并发，同时又要求数据及时可靠的话，可以使用这种模式

REPEATABLE READ

解决不可重复读

一个事务中多次读取不会出现不同的结果，保证了可重复读

set autocommit = 0;
set global transaction isolation level repeatable read ;
set session transaction isolation level repeatable read ; 
start transaction;
select sleep(5);
update users set state=1 where id=1;
commit;

set autocommit = 0;
set global transaction isolation level repeatable read ;
set session transaction isolation level repeatable read ; 
start transaction;
select * from users where id=1;
select sleep(10);
select * from users where id=1;
commit;

read view

RR的read view生成机制和RC不同

RC

当前语句执行前已经提交的数据都是可见的
RR

当前事务执行前已经提交的数据都是可见的

RR 创建事务时，生成当前global read view,一直维持到事务结束，实现可重复读

事务2创建时，生成read view，事务1 trx_id1=1，事务2 trx_id2=2

设事务2第一次读取数据前，此行数据的事务trx_id=0

事务2第一个select执行前，生成的read view为，trx_id_min=1，trx_id_max=1

trx_id(0)<trx_id_min(1)，该行记录的当前值可见，将该可见行的值state=0返回

RR，只有在事务创建时才会重新生成read view

事务2第二次读取数据之前，事务1对数据进行了更新操作，此行数据的事务trx_id=1

trx_id_min(1)=trx_id(1)=trx_id_max(1)，此行数据对事务2不可见

该行记录DB_ROLL_PTR指向的回滚段中，取出最新的undo-log版本号的数据，将该可见行的值state=0返回

事务2第二次读和第一次读一致，state=0，数据是可重复读的

从事务1的update可以得知，RR 读不加锁

因为如果读要加S锁，是在事务结束时释放S锁

事务1的update，申请X锁时会等待事务2的S锁释放。现实并不是

MySql InnoDB引擎通过MVCC在保证数据的安全性的同时，实现读的非阻塞

额外存储空间，做更多的行检查工作；但是保证了读操作不用加锁，提升了性能，牺牲空间换取时间

MVCC只在RC和RR 下工作。其他两个隔离级别都和MVCC不兼容，RU 总是读取最新的数据行，而不是符合当前事务版本的数据行

SERIALIZABLE会对所有读取的行都加锁

各个数据库引擎实现各种隔离级别的方式并不是和标准sql中的封锁协议定义一一对应的

解决幻读

幻读是不可重复读的一种特殊情况

不可重复读

对数据的修改更新产生
幻读

插入或删除数据产生

幻读有2种情况

一个事物之前读的时候，读到一条记录，再读发现记录没有了，被其它事务删了
之前读的时候记录不存在，再读发现又有这条记录，其它事物插入了一条记录

set autocommit = 0;
start transaction;
select * from users;
select sleep(10);
select * from users;
commit;

set autocommit = 0;
start transaction;
select sleep(5);
insert into users values(2,'zzj',2);
commit;

事务1第二条select并没有读取到事务2新插入的数据，没有发生幻读现象

按照封锁协议定义，三级封锁协议是解决不了幻读的问题的。只有最强封锁协议，读和写都对整个表加锁，才能解决幻读的问题

但是这样做相当于所有的操作串行化，数据库支持并发的能力会变得极差

InnoDB在RR隔离级别解决了幻读的问题

InnoDB三种行锁算法

Record Lock：单个行记录上的锁
Gap Lock：间隙锁，锁定一个范围，但不包括记录本身。目的，是为了防止同一事务的两次当前读，出现幻读的情况
Next-Key Lock：1+2，锁定一个范围，并且锁定记录本身。主要目的是解决幻读的问题

RR级别下，如果查询条件能使用上唯一索引，或者是一个唯一的查询条件，那么仅加行锁（通过唯一的查询条件查询唯一行，当然不会出现幻读的现象）

如果是一个范围查询，给这个范围加上 Gap锁或者 Next-Key锁 (行锁+Gap锁)。理论上不会发生幻读

Gap Lock和Next-Key Lock

简单讲就是防止幻读。通过锁阻止特定条件的新记录的插入，因为插入时也要获取gap锁(Insert Intention Locks)

更新操作不会去获取gap锁

create table test (
  id int(11) DEFAULT NULL,
  c1 int(11) DEFAULT NULL,
  KEY test_idx1 (id)
) ;

insert into test values (10,0),(20,0),(30,0);

事务1

set autocommit=0;
begin;
update test set c1=2 where id=20;

事务2

set innodb_lock_wait_timeout=2;
set autocommit=0;
begin;
insert into test values(9,4);
insert into test values(10,4);

InnoDB 行查询用Next-Key Lock，锁定的不是单个值，而是一个范围（GAP）

上面索引值有10，20，30，其记录的GAP的区间如下：是一个左开右闭的空间（原因是默认主键的有序自增的特性，结合后面的例子说明）

（-∞,10]，(10,20]，(20,30]，(30,+∞）

InnoDB还会对辅助索引下一个键值加上Gap Lock

该SQL语句锁定的范围是（10,20]，下个下个键值范围是（20,30]，所以插入10~29之间的值的时候都会被锁定，要求等待

插入非这个范围内的值都正常

对于更新操作，仅20这条记录不能更新，因为更新操作不会去获取gap锁

begin;
update test set c1=4 where id=10;

update test set c1=4 where id=20;
 Lock wait timeout exceeded; try restarting transaction
 
update test set c1=4 where id=30;

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

RR 读到底加不加锁？

当理解了RR 是如何解决幻读问题时，另一个疑问

RC 和RR通过MVCC的方式避免读操作加锁的问题

但RR又为了解决幻读的问题加Gap Lock或Next-Key Lock

RR 读到底加不加锁？这篇文章

思考如果InnoDB对普通的查询也加了锁，那和SERIALIZABLE的区别又在哪里？

InnoDB提供了Next-Key Lock，但需要应用自己去加锁

这里又涉及到一致性读（快照读）和当前读

如果选择一致性读，也就是MVCC的模式，读就不需要加锁，读到的数据是通过Read View控制的

如果选择当前读，读是需要加锁的，也就是Next-Key Lock，其他的写操作需要等待Next-Key Lock释放才可写入，这种方式读取的数据是实时的

MVCC是实现的是快照读，Next-Key Lock是对当前读。MySQL InnoDB的可重复读并不保证避免幻读，需要应用使用加锁读来保证，而这个加锁读使用到的机制就是Next-Key Lock

READ COMMITTED

只会锁住已有记录，不会加gap锁

SERIALIZABLE

和REPEATABLE READ的主要区别在于把普通的SELECT变成SELECT … LOCK IN SHARE MODE，即对普通的select都会获取gap锁或next-key锁

REPEATABLE READ和幻读

consistent-read 时，REPEATABLE READ看到是事务开始时的快照，即使其它事务插入了新行通常也是看不到的，所以在常见的场合可以避免幻读

但 locking read 或更新，删除时是会看到已提交的修改的，包括新插入的行

RR和RC相比还有一个重要的区别，RC下，扫描过但不匹配的记录不会加锁，或者是先加锁再释放，即semi-consistent read

但RR下扫描过记录都要加锁。这个差别对有全表扫描的更新的场景影响极大

mysql 隔离级别测试

ddatsh

Read UnCommited

可解决丢失更新

RU 不能解决脏读和不可重复读

SERIALIZABLE

REPEATABLE READ

解决 不可重复读

read view

解决 幻读

InnoDB三种行锁算法

Gap Lock和Next-Key Lock

RR 读到底加不加锁？

READ COMMITTED

SERIALIZABLE

REPEATABLE READ和幻读

解决不可重复读

解决幻读