Mysql事务隔离级别实践

06/04/2020 Mysql

理论

隔离级别

读未提交(read-uncommitted)
读已提交(read-committed)
可重复读(repeatable-read)
序列化(serializable)

mysql默认隔离级别是可重复读，也就是说会有幻读问题

并发问题

脏读：事务A读取了其他事务更新未提交的数据，如果其他事务回滚，此时读到的就是脏数据
不可重复读：事务A多次读，同时其他事务更新并提交，事务A多次读取结果不一致
幻读：事务A多次读，同时其他事务插入数据并提交，事务A多次查询结果集不一致，会多出幻行

不可重复读和幻读意思很相近，不可重复读对于行而言，而幻读对于表而言

实践

打开客户端A建表

create table `account` (
    `id`      bigint(20) NOT NULL,
    `balance` bigint(20) DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_bin;

insert account values(1, 1000);
insert account values(2, 100);
insert account values(3, 10000);

select * from account;

+----+---------+
| id | balance |
+----+---------+
|  1 |    1000 |
|  2 |     100 |
|  3 |   10000 |
+----+---------+

更改事务等级为读未提交

select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
set session transaction isolation level read uncommitted;

开启事务并查询

start transaction;
select * from account;
+----+---------+
| id | balance |
+----+---------+
|  1 |    1000 |
|  2 |     100 |
|  3 |   10000 |
+----+---------+

打开一个新的客户端B设置事务等级开启事务，并修改数据且不提交

set session transaction isolation level read uncommitted;
start transaction;
update account set balance=balance+50 where id=2;

在客户端A中查询

select * from account;
+----+---------+
| id | balance |
+----+---------+
|  1 |    1000 |
|  2 |     150 |
|  3 |   10000 |
+----+---------+

客户端B rollback

rollback;
select * from account;
+----+---------+
| id | balance |
+----+---------+
|  1 |    1000 |
|  2 |     100 |
|  3 |   10000 |
+----+---------+

客户端A查询

select * from account;
+----+---------+
| id | balance |
+----+---------+
|  1 |    1000 |
|  2 |     100 |
|  3 |   10000 |
+----+---------+

若A使用了rollback之前的数据，就产生了脏读

重新测试读已提交级别，A设置事务等级并开启事务查询

set session transaction isolation level read committed;
select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED |
+----------------+
start transaction;
select * from account;
+----+---------+
| id | balance |
+----+---------+
|  1 |    1000 |
|  2 |     100 |
|  3 |   10000 |
+----+---------+

B也修改事务级别，并开启事务，执行更新

set session transaction isolation level read committed;
start transaction;
update account set balance=balance+50 where id=2;
select * from account;
+----+---------+
| id | balance |
+----+---------+
|  1 |    1000 |
|  2 |     150 |
|  3 |   10000 |
+----+---------+

A查询

select * from account;
+----+---------+
| id | balance |
+----+---------+
|  1 |    1000 |
|  2 |     100 |
|  3 |   10000 |
+----+---------+

数据并没有更改，解决了脏读问题

B提交

commit;
select * from account;
+----+---------+
| id | balance |
+----+---------+
|  1 |    1000 |
|  2 |     150 |
|  3 |   10000 |
+----+---------+

A再查询

select * from account;
+----+---------+
| id | balance |
+----+---------+
|  1 |    1000 |
|  2 |     150 |
|  3 |   10000 |
+----+---------+

符合B事务提交后的正常结果，但在A事务中，两次查询结果不相同，这就是不可重复读问题

开始测试可重复读等级，A修改事务等级，开启事务查询

set session transaction isolation level repeatable read;
start transaction;
select * from account;
+----+---------+
| id | balance |
+----+---------+
|  1 |    1000 |
|  2 |     150 |
|  3 |   10000 |
+----+---------+

B同样修改事务等级，开启事务执行更新

set session transaction isolation level repeatable read;
start transaction;
update account set balance=balance+50 where id=2;
select * from account;
+----+---------+
| id | balance |
+----+---------+
|  1 |    1000 |
|  2 |     200 |
|  3 |   10000 |
+----+---------+

A查询

select * from account;
+----+---------+
| id | balance |
+----+---------+
|  1 |    1000 |
|  2 |     150 |
|  3 |   10000 |
+----+---------+

没有出现脏读问题

B提交后A再查询

select * from account;
+----+---------+
| id | balance |
+----+---------+
|  1 |    1000 |
|  2 |     150 |
|  3 |   10000 |
+----+---------+

也没有出现不可重复读问题，原理是MVCC机制

B重开一个事务，新增一条记录并提交

start transaction;
insert account values(4, 500);
commit;
select * from account;
+----+---------+
| id | balance |
+----+---------+
|  1 |    1000 |
|  2 |     200 |
|  3 |   10000 |
|  4 |     500 |
+----+---------+

A查询

select * from account;
+----+---------+
| id | balance |
+----+---------+
|  1 |    1000 |
|  2 |     200 |
|  3 |   10000 |
+----+---------+

并没有出现预想中的幻读问题

于是A也插入相同的数据

insert account values(4, 500);
ERROR 1062 (23000): Duplicate entry '4' for key 'PRIMARY'

意思是该数据已经存在了，发生了幻觉

实际使用

在实际使用过程中，一般使用命中主键的查询，并添加for update语句获取行级写锁

select * from account where id=4 for update;

这个锁直到commit才会被释放，在锁期间，所有对id=4的行的操作，包括select/update/insert都会阻塞，就算id=4的记录不存在，也会上锁

另一个坑

参看这篇文章
普通的select查询为快照读，而select for update为当前读，在RR等级下，只有第一次查询会起快照，update等操作影响不到该快照，RR和RC通过读哪次快照，区分了当前读/快照读