【MySQL】MySQL锁和隔离级别浅析一
时间：2022-03-10 17:23
参考：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
下面的内容是参考上面链接博文测试的内容，文字略加修改，方便自己查询和阅读。
测试一：
| 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 |
+---+------+ |
无返回，等待 | session 2查询需要等待session 1事务处理完成或者回滚 | |
| 7 | commit |
+---+------+ |
|||
| 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 |
+---+------+ |
|||
| 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 |
+---+------+ |
select * from t1 where a=1 for
update 或 select * from t1 where a=1 lock in share mode |
+---+------+ |
||
测试三：
| 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 |
+---+------+ |
|||
| 3 | begin | ||||
| 4 | select * from t1 where a=1 lock in share mode |
+---+------+ |
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 |
+---+------+ |
|||
此后又做了几个测试，总结如下：
| 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锁和隔离级别浅析一,布布扣,bubuko.com
