MySQL锁和隔离级别浅析一【永利集团304com】

update未提交

update t1 set b=’m’ where a=1

select

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

 

begin

3

result

10

session 1未提交事务,等待

 

 

 

 

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

 

 

 

 

 

 

tx_isolation

 

4

 

session 1 commit以后session 2返回结果

 

5

 

 

 

2

commit

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

Value

 

2

 

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

何登成百度网盘:

12

 

Value

session
2事务虽然只有一个select但是由于update和select两个所持有的共享锁、排他锁互斥,所以session
1的update事务需要等到session 2提交以后完成

query

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提交或者回滚

result

 

session 2

 

 

update t1 set b=’y’
where a=1

 

 

11

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

REPEATABLE-READ

 

 

 

无返回,等待

session 2事务处理完成或者回滚后session 1获得查询结果

select * from t1
where a=1

Variable_name

 

 

query

begin

 

session 2

6

 

Query OK, 1 row affected (17.49 sec)
Rows matched: 1  Changed: 1  Warnings: 0

1

 

8

 

 

 

 

 

commit

REPEATABLE-READ

2

update未提交

 

8

 

 

 

 

1

 

 
select
select … lock in share mode
select … for update
DML

1

 

 

 

 

参考:

 

query

 

 

 

 

 

测试二:

select … for update

 

 

 

无返回,等待

 

 

 

 

REPEATABLE-READ

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

select * from t1 where a=1 for update

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

session 2查询需要等待session 1事务处理完成或者回滚

begin

 

commit

 

commit

 

 

7

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

update未提交

session 2

session 1 commit以后session 2返回结果

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

begin

 

 

select … lock in share mode

无返回,等待

 

 

 

 

 

session 1

session 2查询需要等待session 1事务处理完成或者回滚

 

 

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

tx_isolation

result

下面的内容是参考上面链接博文测试的内容,文字略加修改,方便自己查询和阅读。

tx_isolation

 

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

5

 

update t1 set b=’w’ where a=1

session 1

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

update t1 set b=’u’ where a=1

update t1 set b=’z’
where a=1

7

query

select * from t1
where a=1 for update

6

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

无返回,等待

 

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

select * from t1 where a=1 for update

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

 

Variable_name

session 1

 

 

select * from t1 where a=1 for update

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

  

select * from t1 where a=1 for update

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

 

 

Variable_name

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)

 

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读取或者写入行数据

3

result

 

9

 

测试一:

 

 

 

 

select * from t1 where a=1 for update

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

 

3

session 1事务处理完成或者回滚后session 2获得查询结果

 

session 1 commit之前,普通select返回的结果都是session 1 commit提交前结果

无返回,等待

update t1 set b=’x’
where a=1

 

Value

 

 

4

测试三:

发表评论

电子邮件地址不会被公开。 必填项已用*标注