记一次mysql事务隔离级别踩坑

原创

mariolu

修改于 2019-08-03 02:11:17

1.6K0

修改于 2019-08-03 02:11:17

文章被收录于专栏：CDN及云技术分享

一、事务的隔离级别

1.1 隔离级别对应的读到的状态可能性

事务隔离级别有四种，以下图为例，四种隔离级别的读数据状态会是不一样的：

事务 A:  |---0---|---1---|---2---|---3---|---4---|
事务 B:                          |---5---|---6---|---7---|---8---|
查询操作:|读事务开始 <--- 不同的隔离级别在不同的时间点读到不同的状态-->|

READ UNCOMMITABLE: 这种读到状态可能是最多的0-8都有可能
READ COMMITTABLE : 这种读到0、4、8
REPEATEDABLE READ：这种读到0
SERAIABLE：查询操作不可访问，并且事务B的操作会被阻塞。效果如下图

事务 A: |---0---|---1---|---2---|---3---|---4---|
事务 B:                                         |---5---|---6---|---7---|---8---|
查询操作： 无论是读事务或者写事务 都会得到排他锁，如果是对同一记录操作，事务A一旦先开始，事务B就不能操作，或者查询也不能操作。

1.2 从锁机制看事务隔离级别

READ UNCOMMITABLE：无任何事务控制，无加任何读锁，写锁
READ COMMITABLE，写时候加了排他锁，读了时候使用记录粒度的读锁（共享锁，这个共享锁不锁事务，锁记录）。事务A查询的时候读到的是就记录，事务B做了提交，事务A再次查询（也就是不可重复读）。这两次会得到不一样的结果。如果在一个事务里，这个事务里做了两次同样的记录数量查询。两次查询的结果不一样(幻读)。
REPEATEDLE READ，可重复读，和READ COMMITABLE不同的时，读锁（共享锁）锁的是整个事务执行的过程。所以在整个事务的执行过程中，任何其他事务尝试更新完这条记录的结果，这个读事务都是用MVCC提交前的版本）。和READ COMMITABLE区别的是，读记录锁的是记录还是事务过程。两者锁的时间不一样。
SERAIABLE：RANGE KEY，查询满足这个range范围的这些个数的记录都被锁住。

1.2.1 不可重复读和幻读的区别

不可重复读的重点是修改:

同样的条件 , 你读取过的数据 , 再次读取出来发现值不一样了

幻读的重点在于新增或者删除（导致记录数变化）

同样的条件 , 第 1 次和第 2 次读出来的记录数（强调的是记录数，而不是记录本身，因为读锁的锁粒度是记录自身，而不是整张表）不一样。

1.3 不同隔离级别的错误读取

通过在写的时候加锁，可以解决脏读。

通过在读的时候加锁（或者MVCC提供旧的提交版本），可以解决不可重复读。

通过串行化，可以解决幻读。

隔离级别	脏读	不可重复读	幻读
READ UNCOMMITABLE	1	1	1
READ COMMITABLE	0	1	1
REPEATABLE READ	0	0	1
SERIAL	0	0	0

二、mysql的实现级别

2.1 默认隔离级别

默认是隔离级别是read commitable。这个隔离级别，做个试样：

模拟是否可重复读，和幻读：

事务1（窗口1）：

mysql> begin
    -> ;
Query OK, 0 rows affected (0.00 sec)

mysql> select count(*) from pem_of_plat_73;//执行一次结果数量集的统计，检验是否有幻读
+----------+
| count(*) |
+----------+
|       48 |
+----------+
1 row in set (0.00 sec)

mysql> select status from pem_of_plat_73 where pem_id=10154;//执行一次记录的修改，检验是否可重复读
+--------+
| status |
+--------+
|      0 |
+--------+
1 row in set (0.00 sec)


事务2：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update pem_of_plat_73 set  status=2  where pem_id=10154;//完成记录修改
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> insert into pem_of_plat_73 (plat_id, pem_id, pem_addon_type, task_id, mtime, status, prev_status) values (73, 10155, 0, 22616, now(), 0, -1);
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)


事务1：

mysql> select count(*) from pem_of_plat_73; //查询记录数量无变多
+----------+
| count(*) |
+----------+
|       48 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from pem_of_plat_73 where pem_id=10154; //查询记录无修改。
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

这跟我们预想的有一点点不一样，如果REAPEATABLE READ应该是有幻读啊。可是为什么记录数一直是48呢。理论上应该有幻读啊。

2.2 mysql（MVCC机制）的幻读

InnoDB使用了MVCC版本控制。 MVCC记录了每一次的增删改查。另一个事务的增删改记录的版本号要高于本事务。所以这些另一个增删改记录是没影响的。

MVCC并发控制机制 -多版本并发控制（Multiversion Concurrency Control），每一个写操作都会创建一个新版本的数据，读操作会从有限多个版本的数据中挑选一个最合适的结果直接返回；在这时，读写操作之间的冲突就不再需要被关注，而管理和快速挑选数据的版本就成了 MVCC 需要解决的主要问题。

2.3 怎么复现幻读

create table ab(a int primary key, b int);

Tx1:
begin; 
select * from ab; // empty set

Tx2: 
begin; 
insert into ab values(1,1);
commit; 
Tx1: 
select * from ab; // empty set, expected phantom read missing. 
update ab set b = 2 where a = 1; // 1 row affected. //最主要是这一步，通过在本事务更新记录使得MVCC机制select出这条记录
select * from ab; // 1 row. phantom read here!!!!