[InnoDB系列] - 实例解析Innodb的隔离级别以及锁模式

1、隔离级别为:READ COMMITTED

 

READ COMMITTED

一个有些象Oracle的隔离级别。所有SELECT ... FOR UPDATE和SELECT ... LOCK IN SHARE
MOD语句仅锁定索引记录,而不锁定记录前的间隙,因而允许随意紧挨着已锁定的记录插入新记录。UPDATE和DELETE语句使用一个带唯一搜索条件的唯一的索引仅锁定找到的索引记录,而不包括记录前的间隙。在范围类型UPDATE和DELETE语句,InnoDB必须对范围覆盖的间隙设置next-key锁定或间隙锁定以及其它用户做的块插入。这是很必要的,因为要让MySQL复制和恢复起作用,“幽灵行”必须被阻止掉。

持续读行为如同在Oracle中:即使在同一事务内, 每个持续读设置并读取它自己的新快照。请参阅15.2.10.4节,“持续非锁定读”

show global variables like ‘tx_isolation ';
| tx_isolation  | READ-COMMITTED |

看看实测步骤:



session 1

session 2

begin

 

 

begin

select * from v where id=1;

| id  
| name   |

|   
1 | name11 |

 

 

select * from v where id=1;

| id  
| name   |

|   
1 | name11 |

select * from v where id=1 lock in share
mode;

| id  
| name   |

|   
1 | name11 |

 

 

select * from v where id=1 lock in share
mode;

| id  
| name   |

|   
1 | name11 |

 

session1sesssion2请求的都是共享锁,不会互斥,因此无需等待。

select * from v where id=1 for update;

| id  
| name   |

|   
1 | name11 |

 

这个时候,由于session2发起了lock in share mode,需要请求一个共享锁,和for update所需要的排它锁是互斥的,因此session1需要等待session2提交或回滚才能继续。

 

 

commit;

 

select * from v where id=1;

| id  
| name   |

|   
1 | name11 |

 

select * from v where id =1 for update;

或者

select * from v where id =1 lock in share
mode;

update v set name = 'name 2' where id=1;

session1首先发起了一个select ..for update请求,会对该记录加一个排它锁,因此session2的请求会被等待,直到session1提交或者回滚。

commit;

 

| id  
| name   |

|   
1 | name 2 |

select * from v where id =1;

| id  
| name   |

|   
1 | name 2 |

 

 

select * from v where id =1;

| id  
| name   |

|   
1 | name11 |

 

select * from v where id =1 lock in share
mode;

| id  
| name   |

|   
1 | name 2 |

 

可以看到,如果只是发起最简单的select请求,则返回的结果是session2发生时看到的快照;如果发起一个select…for updateselect..lock in share mode,则可以看到最新的快照。

这是因为select…for updateselect…lock share mode会取得最新快照,并且请求加一个排它或者共享next-key锁。而普通的select查询不会请求加任何锁。

 

update v set name = ‘name 1’ where id =1;

commit;

select * from v where id=1;

| id  
| name   |

|   
1 | name 1 |

 

可以看到session2提交后的最新结果。

 

 

select * from v where id=1;

| id  
| name   |

|   
1 | name 1 |

 

可以看到session2提交后的最新结果。

2、隔离级别为:REPEATABLE READ

 

REPEATABLE READ

这是InnoDB的默认隔离级别。带唯一搜索条件使用唯一索引的SELECT ... FOR UPDATE, SELECT ... LOCK IN
SHARE MODE, UPDATE
和DELETE语句只锁定找到的索引记录,而不锁定记录前的间隙。用其它搜索条件,这些操作采用next-key锁定,用next-key锁定或者间隙锁定锁住搜索的索引范围,并且阻止其它用户的新插入。

在持续读中,有一个与之前隔离级别重要的差别:在这个级别,在同一事务内所有持续读读取由第一次读所确定的同一快照。这个惯例意味着如果你在同一事务内发出数个无格式SELECT语句,这些SELECT语句对相互之间也是持续的,请参阅15.2.10.4节,“持续非锁定读”。 

show global variables like ‘tx_isolation ';
| tx_isolation  | REPEATABLE-READ |

看看实测步骤:



session 1

session 2

begin;

 

 

begin;

select * from v where id=1 lock in share
mode;

| id  
| name   |

|   
1 | name 1 |

 

 

select * from v where id=1 lock in share
mode;

| id  
| name   |

|   
1 | name 1 |

 

session1sesssion2请求的都是共享锁,不会互斥,因此无需等待。

select * from v where id=1 for update;

| id  
| name   |

|   
1 | name 1 |

 

这个时候,由于session2发起了lock in share mode,需要请求一个共享锁,和for update所需要的排它锁是互斥的,因此session1需要等待session2提交或回滚才能继续。

 

 

commit;

 

begin;

 

select * from v where id=1;

| id  
| name   |

|   
1 | name 1 |

update v set name='name 2' where id=1;

 

 

select * from v where id=1 for update;

select * from v where id =1 lock in share
mode;

 

session1首先发起了一个select ..for update请求,会对该记录加一个排它锁,因此session2的请求会被等待,直到session1提交或者回滚。

commit;

 

 

| id  
| name   |

|   
1 | name 2 |

select * from v where id=1;

| id  
| name   |

|   
1 | name 2 |

 

 

select * from v where id=1 lock in share
mode;

| id  
| name   |

|   
1 | name 2 |

 

select * from v where id=1;

| id  
| name   |

|   
1 | name 2 |

 

这个时候,不管是select…lock in
share mode
还是select…for update,得到的结果都是session 1更新后提交的数据。

 

update v set name = 'name 1' where id=1;

select * from v where id=1;

| id  
| name   |

|   
1 | name 2 |

 

 

commit;

 

select * from v where id=1 ;

| id  
| name   |

|   
1 | name 1 |

select * from v where id=1 ;

| id  
| name   |

|   
1 | name 1 |

 

关于锁,摘取手册中的几条,更具体的请看mysql手册,"存储引擎和表类型" => "在InnoDB中不同SQL语句设置的锁定" 这节。

· SELECT ...
FROM
是一个持续读,读取数据库的快照并且设置不锁定,除非事务隔离级别被设为SERIALIZABLE。对于
SERIALIZABLE级别,这个设置对它遇到的索引记录设置共享的next-key锁定。

· SELECT ... FROM ... LOCK IN SHARE
MODE
对读遇到的所有索引记录设置共享的next-key锁定。

· SELECT ... FROM ... FOR
UPDATE
对读遇到的所有索引记录设置独占的next-key锁定。

评论

我明白,但我在客戶端內是用C#做WinForm程式,用的是最新MySQL Provider連接成功,我想用Read Commited功能,因為有時我會Update一Row而這個Row是不可給其他用戶讀的,直到更新結速.

我要用那個命令?? 是否For Update ??

不太明白你的意思,我想上面的例子已经够明白的了。

MySQL方案、培训、支持

老叶啊,REPEATABLE READ的那个例子里面,
"这个时候,不管是select…lock in
share mode还是select…for update,还是普通的select,得到的结果都是session 1更新后提交的数据。"
这个地方和我试出来的结果不太一样啊,我这里的普通select得到的是事务开始时的数据,也就是session1更新前的数据;for update得到的是session1更新后提交的数据。

测试过程和我上面贴的一模一样?版本号呢?

MySQL方案、培训、支持

过程是一样的,版本是5.0.37
我得到的结果是,在session2中的普通select语句返回的是事务刚开始时的值,for update 返回的是session1更新过的数据

hi,小付,谢谢你的细心测试,我重新测了一遍,结果却是如你所说。可能是我早先的测试有某些环节不够细心吧,上面的内容已经更改过来了 :)

MySQL方案、培训、支持

我也测试了下,确实,在REPEATABLE READ的那个例子里面,"不管是select…lock in
share mode还是select…for update,还是普通的select,得到的结果都是session 1更新后提交的数据。"