Mysql 隔离级别、锁、MVCC示例

Posted on | |

引言

通过构造示例,理解Mysql的隔离级别及其锁和MVCC的实现
参考极客时间mysql45讲之第3讲,7讲和8讲,19讲,20讲

事务隔离级别

  • RU 读未提交
  • RC 读已提交
  • RR 可重复读
  • S 顺序读
1
2
3
表结构
create table T(c int) engine=InnoDB;
insert into T(c) values (1);

在四种模式下分别试验例子1

1
2
3
4
5
6
7
8
9
10
11
12
例子1:
事务A          事务B
1.开启事务		1.开启事务
2.查询得到值1 		
			   2.查询得到值1
			   3.将1改成2
3.查询得到值v1	
			   4.提交事务B
4.查询得到值v2
5.提交事务A
6.查询得到值v3

  • s顺序读 读加读锁 写加写锁 写锁必须等待读锁完成. 事务B的第三部阻塞,v1,v2都是1,v3是2,简单但是并发性能不好
  • RR V1,v2都是1,v3是2 读不加锁 mvcc
    会有大量的undo log,并且如果有大量的事务会大致读取时遍历undolog导致读取性能下降
  • RC V1是1,v2是2,v3是2 会导致不可重复读
  • RU v1,v2,v3都是2 会导致脏读

重点看RR:

  • 如果事务A的第2步挪到事务B的第三步之后,效果如何.即读取视图是从何时开始的,应该是从第一个查询语句开始 （此时如果在A的第2步之前再加一个update语句会如何）

查询时才会开启一个事务

1
2
3
4
5
6
7
8
9
10
11
事务A          事务B
1.开启事务		1.开启事务
			   2.查询得到值1
			   3.将1改成2
			   4.提交事务B
2.查询得到值2		
3.查询得到值v1	
4.查询得到值v2
5.提交事务A
6.查询得到值v3

  • 如果在A中update之后在B中再次update,会如何

修改一下:

1
2
3
4
5
6
7
8
9
10
11
事务A          事务B
1.开启事务		1.开启事务
2.将1改成3
3.查询得到值3		

			   2.查询得到值1
			   3.将1改成2  阻塞....  有行锁 update T set c=2 where c=1 (因为c已经被修改为2,更新的时候是先读后写,读是当前读,而不是快照读)
4.提交事务A
			   4.提交事务B
5.查询得到值3    5.查询得到值3

写代码的时候需要判断影响行数,即是否真正执行了更新
再修改一下:最后得到的值是2

1
2
3
4
5
6
7
8
9
10
11
事务A          事务B
1.开启事务		1.开启事务
2.将1改成3
3.查询得到值3		

			   2.查询得到值1
			   3.将1改成2  阻塞....  有行锁 update T set c=2 
4.提交事务A
			   4.提交事务B
5.查询得到值2    5.查询得到值2

  • 上边示例中同时更新一行会有行锁,我们演示一下行锁

行锁在事务开始时加,但并不是语句结束后就释放, 而是事务结束后才释放,称之为两阶段锁协议.所以在一个事务中将最可能造成锁争用的语句放到事务最后执行

行锁通过锁索引记录来实现,如果没有索引,锁全表.

1
2
delete from T;
insert into T values (1),(5);

事务A更新1,事务B更新5

1
2
3
4
5
6
7
8
9
10
事务A          事务B
1.开启事务		1.开启事务
2.将1改成2
3.查询得到值2,5		

			   2.将5改成6 (因为c字段上边没有索引,所以是锁全表,此时无法更新)
4.提交事务A 
			   3.提交事务B
5.查询得到值2,6  4.查询得到值2,6

修改c增加uniq index
create unique index u_index_c on T (c);

1
2
3
4
5
6
7
8
9
10
事务A          事务B
1.开启事务		1.开启事务
2.将1改成2
3.查询得到值2,5		

			   2.将5改成6 
4.提交事务A 
			   3.提交事务B
5.查询得到值2,6  4.查询得到值2,6

事务A在1字段的唯一索引上加锁,所以事务B的第2条语句不会被阻塞

drop index u_index_c on T;
create index u_index_c on T (c);

修改c增加 index

1
2
3
4
5
6
7
8
9
10
事务A          事务B
1.开启事务		1.开启事务
2.将1改成2
3.查询得到值2,5		

			   2.将5改成6 
4.提交事务A 
			   3.提交事务B
5.查询得到值2,6  4.查询得到值2,6

此时的加锁原则是等值加锁,向右查找到第一个不满足条件的记录后退化为gap锁
但如果第2条语句换成插入3 例如insert into T values (3);会被阻塞

1
2
3
4
5
6
7
8
9
10
事务A          事务B
1.开启事务		1.开启事务
2.将1改成2
3.查询得到值2,5		

			   2.插入值3 (阻塞)
4.提交事务A 
			   3.提交事务B
5.查询得到值2,3,5  4.查询得到值2,3,5

如果有3条记录,1,2,5 那么插入记录3是可以插入的

1
2
3
4
5
6
7
8
9
10
事务A          事务B
1.开启事务		1.开启事务
2.将1改成2
3.查询得到值2,5		

			   2.插入值3 （不阻塞）
4.提交事务A 
			   3.提交事务B
5.查询得到值2,3,5  4.查询得到值2,3,5

参考链接

mysql