mysql 锁 事务隔离级别

主题

最近在看mysql相关的书籍.实验了一些内容.分享一下,主要是关于事务隔离级别(read-committed和repeatable-read)和锁相关的.

 

很多网上文章上都能搜索到 read-committed可以防止脏数据.但是不能防止 不可重复读.

而repeatable-read可以防止 不可重复读.但是不能防止幻读.

现在我想分享下具体是怎么操作的. 

 

不可重复读下的区别

read-committed和repeatable-read的区别到底是什么?

我觉得在 不可重复读 这个问题上并没有涉及锁的问题.而是涉及一致性非锁定读的问题

 

假设有一张user,主键id int, name varchar, i varchar.

在i这一列上有索引,非唯一.

数据如下:

id　　name　　i

1　　v1　　　　i1

5　　v5　　　　i5

9　　v9　　　　i9

所有session的 autocommit=0 不会自动提交.

 

session A:

update user set name = 'v5-new' where id = 5;　　

这个时候锁定了id=5这一列.没有commit

 

Session B:

select name from user where id = 5

查询出来是v5.这是因为RR和RC都不会读到脏数据

 

然后session A:

commited;

这个时候行最新的name已经是v5-new了

 

Session B:

select name from user where id = 5

这个时候的select就有区别了.

RC读出来是v5-new, RR仍然是v5

但是要注意的是Session B在这里始终都只是一般的select,所以没有加锁.所以这里不可重复和锁我觉得没有关系(但是和session A在记录上加的X锁有点关系,因为这条记录被锁定了).

那为什么2种隔离级别会有区别呢? 因为一致性非锁定读的原因

 

session B第一次读取发现记录ID=5被锁定(有X锁).那读的时候就会从undo日志里找到原始的值.也就是v5.

第二次读的时候RC和RR有区别.RC因为session A已经committed,会读最新的行数据,也就是v5-new

而RR仍然会读取之前读到的那个snap-shot版本,也就是v5.所以会有区别..

 

幻读

RR模式下加上锁可以解决幻读的问题(还原之前的数据).

实验如下:

Session A:

update user set name = 'v55' where i = 'i5';

没有committed

 

Session B:

insert into user values(6, 'v6', 'i5');

 

这个时候如果B可以committed,那就会产生幻读的问题.因为A明明修改了所有i=i5的数据.

但是却没有修改B插入的那条.

 

但是实际上Session B执行这个insert的时候回等待.

因为A锁住了i = i5的数据.所以B是插入不了的.

 

那如果B执行的是

 insert into user values(6, 'v6', 'i6'); 可以吗?

事实上也会等待.因为A会锁定i=i5的数据和它到上下两条相邻数据的区间.

也就是说 i1-i5, i5, i5-i9的数据都被锁住了.都是不能插入的.

 insert into user values(10, 'v10', 'ia');

这条记录就可以插入.因为ia在i9之后(字母大于数字).

 

mysql对主键会锁单条记录.但是对非唯一的一般的索引,会锁单条记录加上上下范围