mysql 隔离级别测试

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

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

Read UnCommited

可解决丢失更新

1
2
3
4
5
6
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

1
2
3
4
5
6
7
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

1
2
3
4
5
6
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 不能解决脏读和不可重复读

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

1
2
3
4
5
6
7
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;
1
2
3
4
5
6
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

解决 不可重复读

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

1
2
3
4
5
6
7
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;
1
2
3
4
5
6
7
8
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种情况

  • 一个事物之前读的时候,读到一条记录,再读发现记录没有了,被其它事务删了
  • 之前读的时候记录不存在,再读发现又有这条记录,其它事物插入了一条记录
1
2
3
4
5
6
set autocommit = 0;
start transaction;
select * from users;
select sleep(10);
select * from users;
commit;
1
2
3
4
5
set autocommit = 0;
start transaction;
select sleep(5);
insert into users values(2,'zzj',2);
commit;

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

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

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

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

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

更新操作不会去获取gap锁

1
2
3
4
5
6
7
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

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

事务2

1
2
3
4
5
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锁

1
2
3
4
5
6
7
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下扫描过记录都要加锁。这个差别对有全表扫描的更新的场景影响极大