【MySQL】MySQL锁和隔离级别浅析一

作者:poorX 和mysql相关  

参考:http://imysql.cn/2008_07_10_innodb_tx_isolation_and_lock_mode

本文只是对于“SELECT ... LOCK IN SHARE MODE”和“SELECT ... FORUPDATE”事务中的锁和RR隔离级别内的测试,针对于表结构、索引结构以及其他隔离级别情况下的触发锁类型,可以参考网易何登成网盘中“MySQL 加锁处理分析.pdf”这篇文章,很细致。

何登成百度网盘:http://pan.baidu.com/share/home?uk=4265849107&view=share

这个测试基于RR这个隔离级别,在《高性能MySQL》的1.5.1 InnoDB存储引擎章节InnoDB概览中对于RR防止幻读是通过间隙锁(next-key locking)实现的,上面提到的何登成“MySQL 加锁处理分析.pdf”这篇文章对于某些条件下操作的间隙锁解释非常易懂。在《高性能MySQL》的1.5.1 InnoDB存储引擎章节中对于间隙锁的说明如下:

间隙锁使得InnoDB不仅仅锁定查询涉及的行,还会对索引中的间隙进行锁定,以防止幻影行的插入。

MySQL参考手册中对于这块的说明是:

15.2.10.5. 锁定读SELECT ... FOR UPDATE和SELECT ... LOCK IN SHARE MODE
在一些环境中,一个持续读是不方便的。比如,你可能想要往表的子表里添加一个新行,并确信该子表在父表中有一个根。下列例子显示如何在你应用程序代码中实现参考的完整性。 
假设你使用一个持续读去读取父表并且看到表中子表的根。不能安全地往子表添加子行吗?不,因为可能同时发生一些其它用户从父表删除父行,而你没有注意到它的情况。 
解决办法是在使用LOCK IN SHARE MODE的锁定模式执行SELECT: 
SELECT * FROM parent WHERE NAME = 'Jones' LOCK IN SHARE MODE;
在共享模式执行一个读意味着我们读最新的可用数据,并在我们读的行设置一个共享锁定。共享模式锁防止其它人更新或删除我们已读的行。同时,如果最新的数据属于其它客户端尚未提交的事务,我们等着知道那个事务被提交。我们看到前述的查询返回父'Jones',我们可以安全地 往子表添加子记录并提交我们的事务。 
让我们看另外一个例子:我们在表child_codes 中有一个整数计数器域,我们用该表给每个添加到子表里的子项指派一个唯一的识别符。显然,使用持续读或者共享模式读去读取当前计数器的值并是一个好主意, 因为数据库的两个用户可能看到计数器的同一个值,如果两个用户试着用同一识别符往该表添加子项,就会发生一个重复键(duplicate-key)错误。 
在这里,如果两个用户同时读计数器,当试图更新计数器之时,至少它们中有一个会发生死锁错误并终止,因此LOCK IN SHARE MODE并不是一个好的解决方法。 
在这种情况下,有两个好方法去实现读计数器和增长计数器值:(1) 先更新计数器,让计数器值增1,之后读计数器,或者(2)用锁定模式FOR UPDATE先读计数器,之后计数器值增加。后一个途径可被如下实现: 
SELECT counter_field FROM child_codes FOR UPDATE;
UPDATE child_codes SET counter_field = counter_field + 1;
SELECT ... FOR UPDATE读最新的可见数据,在每个它读取的行设置独占锁定。因此,它设置与搜索的SQL UPDATE可能会在行上设置的锁定同样的锁定。 
请注意,以上仅是一个SELECT ... FOR UPDATE如何起作用的例子。在MySQL中,事实上生成一个唯一识别符的特殊任务可被用对该表的单独访问来完成: 
UPDATE child_codes SET counter_field = LAST_INSERT_ID(counter_field + 1);
SELECT LAST_INSERT_ID();
SELECT语句仅仅取回识别符信息(专门对当前连接)。它不访问任何表。 

翻译成普通话意思就是:

SELECT ... LOCK IN SHARE MODE sets a shared mode lock on the rows read. A shared mode lock enables other sessions to read the rows but not to modify them. The rows read are the latest available, so if they belong to another transaction that has not yet committed, the read blocks until that transaction ends. 
在读取的行上设置一个共享模式的锁。这个共享锁允许其它session读取数据但不允许修改它。 行读取的是最新的数据,如果他被其它事务使用中而没有提交,读取锁将被阻塞直到那个事务结束。 

SELECT ... FOR UPDATE sets an exclusive lock on the rows read. An exclusive lock prevents other sessions from accessing the rows for reading or writing. 
在读取行上设置一个排他锁。组织其他session读取或者写入行数据

下面的内容是参考上面链接博文测试的内容。

测试一:

  Variable_name Value      
tx_isolation REPEATABLE-READ session 1 session 2  
1 update未提交 select update t1 set b='z'
where a=1
select * from t1
where a=1
session 1 commit之前,普通select返回的结果都是session 1 commit提交前结果
2 update未提交 select … lock in share mode update t1 set b='y'
where a=1
select * from t1
where a=1 lock in share mode
session 1 commit以后session 2返回结果
3 update未提交 select … for update update t1 set b='x'
where a=1
select * from t1
where a=1 for update
session 1 commit以后session 2返回结果
  RR的隔离级别,对于a=1行的update操作会给行加排他锁
1、普通的select只是对于session 1事务提交前的行数据快照查询
2、select … lock in share mode属于共享锁,与session 1的排他锁互斥,需要等待session 1提交或者回滚
3、select … for update属于排他锁,与session 1的排它锁互斥,所以也需要等待需要等待session 1提交或者回滚

 

测试二:

 

  Variable_name Value      
tx_isolation REPEATABLE-READ      
session 1 session 2  
query result query result  
1 begin        
2     begin    
3 select * from t1 where a=1 for update        
4 update t1 set b='u' where a=1

 

    session 2查询需要等待session 1事务处理完成或者回滚
5     select * from t1 where a=1 for update

select * from t1 where a=1 lock in share mode
无返回,等待  
6 select * from t1 where a=1 for update

select * from t1 where a=1 lock in share mode
 

+---+------+
| a | b |
+---+------+
| 1 | u |
+---+------+
1 row in set (0.00 sec)

  无返回,等待 session 2查询需要等待session 1事务处理完成或者回滚
7 commit    

+---+------+
| a | b |
+---+------+
| 1 | u |
+---+------+
1 row in set (33.02 sec)

 
8     update t1 set b='w' where a=1   session 1事务处理完成或者回滚后session 2获得查询结果
9     select * from t1 where a=1 for update

select * from t1 where a=1 lock in share mode

+---+------+
| a | b |
+---+------+
| 1 | w |
+---+------+
1 row in set (0.00 sec)

 
10 select * from t1 where a=1 for update

select * from t1 where a=1 lock in share mode
无返回,等待     session 2事务处理完成或者回滚后session 1获得查询结果
11     commit    
12  

+---+------+
| a | b |
+---+------+
| 1 | w |
+---+------+
1 row in set (10.46 sec)

select * from t1 where a=1 for update

select * from t1 where a=1 lock in share mode

+---+------+
| a | b |
+---+------+
| 1 | w |
+---+------+
1 row in set (0.00 sec)

 

 

测试三:

 

  Variable_name Value      
tx_isolation REPEATABLE-READ      
session 1 session 2  
query result query result  
1 begin        
2 select * from t1 where a=1 lock in share mode

+---+------+
| a | b |
+---+------+
| 1 | w |
+---+------+
1 row in set (0.00 sec)

     
3     begin    
4     select * from t1 where a=1 lock in share mode

+---+------+
| a | b |
+---+------+
| 1 | w |
+---+------+
1 row in set (0.00 sec)

session 2事务虽然只有一个select但是由于update和select两个所持有的共享锁、排他锁互斥,所以session 1的update事务需要等到session 2提交以后完成
5 update t1 set b='m' where a=1 无返回,等待      
6   Query OK, 1 row affected (17.49 sec)
Rows matched: 1  Changed: 1  Warnings: 0
commit    
7     select * from t1 where a=1 lock in share mode 无返回,等待 session 1未提交事务,等待
8 commit    

+---+------+
| a | b |
+---+------+
| 1 | m |
+---+------+
1 row in set (7.16 sec)

 

 

 此后又做了几个测试,总结如下:

type 类型
select 快照
select … lock in share mode 共享锁
select … for update 排它锁
DML 排它锁

 

  select select … lock in share mode select … for update DML
select 快照 快照 快照 快照
select … lock in share mode 快照 共享实时 互斥等待 互斥等待
select … for update 快照 互斥等待 互斥等待 互斥等待
DML 快照 互斥等待 互斥等待 互斥等待

 

 

相关资料:

【MySQL】MySQL锁和隔离级别浅析一来源网络,如有侵权请告知,即处理!

编程Tags: