隔离级别测试

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;

RU 可以解决丢失更新的问题

对应数据库的一级封锁协议

修改数据之前加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

RU 解决不了脏读的问题,更解决不了不可重复读的问题

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

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

SERIALIZABLE

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

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

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

REPEATABLE READ

RR 能解决的问题之一

解决了不可重复读的问题,一个事务中多次读取不会出现不同的结果,保证了可重复读

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;  

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

RC:只要是当前语句执行前已经提交的数据都是可见的

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

RR 创建事务时,就生成了当前的global read view,一直维持到事务结束。这样就能实现可重复读

事务2创建时,生成一份read view

事务1的事务id trx_id1=1

事务2的事务id trx_id2=2

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

事务2第一个select执行前,生成的read view为{1},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中的封锁协议定义一一对应的

RR 能解决的问题之二

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

不可重复读是对数据的修改更新产生的

而幻读是插入或删除数据产生的

幻读有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新插入的数据,没有发生幻读现象

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

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

Mysql InnoDB引擎通过自己的方式在RR隔离级别上解决了幻读的问题

InnoDB三种行锁算法

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

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

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

验证Gap Lock和Next-Key Lock

给state字段加上索引。然后准备几条数据

truncate table users;
create index ix_state on users(state);
INSERT INTO users ( id, NAME, state )
VALUES
	( 1, 'dd', 1 ),
	( 2, 'zzj', 3 ),
	( 3, 'zzj', 5 ),
	( 4, 'zzj', 8 );

事务1

set autocommit=0;
start transaction;  
select * from users where state=3 for update;

事务2

set innodb_lock_wait_timeout=2;
insert into users values(5,'zzj',1);
-- [err] 1205 - lock wait timeout exceeded; try restarting transaction

insert into users values(6,'zzj',2);
-- [err] 1205 - lock wait timeout exceeded; try restarting transaction

insert into users values(6,'zzj',3);
-- [err] 1205 - lock wait timeout exceeded; try restarting transaction

insert into users values(6,'zzj',4);
-- [err] 1205 - lock wait timeout exceeded; try restarting transaction

insert into users values(5,'zzj',0);
-- 受影响的行: 1

insert into users values(6,'zzj',5);
-- 受影响的行: 1

insert into users values(7,'zzj',7);
-- 受影响的行: 1

InnoDB对于行的查询用Next-Key Lock算法,锁定的不是单个值,而是一个范围(GAP)

索引值有1,3,5,8,GAP区间

(-∞,1],(1,3],(3,5],(5,8],(8,+∞)

左开右闭的空间

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

事务1锁定的范围是(1,3],下个键值范围是(3,5],插入1~4之间的值的时候都会被锁定,要求等待,等待超过一定时间便会进行超时处理(默认50秒)

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

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