【MySQL】四种事务隔离级别学习

1 前提准备

建库建表初始化表

# create database
mysql> CREATE DATABASE IF NOT EXISTS test
    -> DEFAULT CHARACTER SET utf8
Query OK, 1 row affected (0.03 sec)

# create table
 CREATE TABLE `persons` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `last_name` varchar(255) DEFAULT NULL,
  `first_name` varchar(255) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  `city` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

# 插入一条初始化数据
mysql> insert into persons(last_name,first_name,address,city) values('c','j','haidian','beijing');
Query OK, 1 row affected (0.00 sec)

查询/设置事务隔离级别

参数解释：

• global 全局设置
• session 只是当前窗口
• transaction_isolation
  • read-uncmmitted
  • read-committed
  • repeatable-read
  • serializable

# 查询
select @@[global|session.]tx_isolation

# 设置
set  [global |  session] transaction_isolation ='read-committed';

MySQL的四种事务隔离级别分别为：

隔离级别	脏读	不可重复读	幻读
未提交读 read uncommitted	v	v	v
已提交读 read committed	x	v	v
可重复读 repeatable read	x	x	v
可串行化 serializable	x	x	x

• 未提交读(Read Uncommitted)：允许脏读，也就是可能读取到其他会话中未提交事务修改的数据

• 提交读(Read Committed)：只能读取到已经提交的数据。Oracle等多数数据库默认都是该级别 (不重复读)

• 可重复读(Repeated Read)：可重复读。在同一个事务内的查询都是事务开始时刻一致的，InnoDB默认级别。在SQL标准中，该隔离级别消除了不可重复读，但是还存在幻象读

• 串行读(Serializable)：完全串行化的读，每次读都需要获得表级共享锁，读写相互都会阻塞

2 复现

复现脏读

开启两个console

# session 1  默认事务隔离级别为 rr                                                 
mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set, 1 warning (0.00 sec)  

# session 2   设置session2事务隔离级别为read uncommitted                                                     
mysql> set session transaction_isolation='read-uncommitted';        
Query OK, 0 rows affected (0.00 sec)                                
	
mysql> select @@session.tx_isolation;                               
+------------------------+                                          
| @@session.tx_isolation |                                          
+------------------------+                                          
| READ-UNCOMMITTED       |                                          
+------------------------+                                          
1 row in set, 1 warning (0.00 sec) 

在session1 中开启一个更新记录字段事务，并且在未提交的时候用session2去查询

结果发现session2是可以查询得到session1对数据做的操作的

# session1 开启事务
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update persons set last_name='update' where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from persons;
+----+-----------+------------+---------+---------+
| id | last_name | first_name | address | city    |
+----+-----------+------------+---------+---------+
|  1 | update    | j          | haidian | beijing |
+----+-----------+------------+---------+---------+
1 row in set (0.00 sec)

# 事务隔离级别为read uncommitted的session2查询
mysql> select * from persons;
+----+-----------+------------+---------+---------+
| id | last_name | first_name | address | city    |
+----+-----------+------------+---------+---------+
|  1 | update    | j          | haidian | beijing |
+----+-----------+------------+---------+---------+
1 row in set (0.00 sec)

手动把session2的隔离级别修改为read committed，发现无法查询到了

mysql> set session transaction_isolation='read-committed';
Query OK, 0 rows affected (0.00 sec)

mysql> select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED |
+----------------+
1 row in set, 1 warning (0.00 sec)

mysql> select * from persons;
+----+-----------+------------+---------+---------+
| id | last_name | first_name | address | city    |
+----+-----------+------------+---------+---------+
|  1 | c         | j          | haidian | beijing |
+----+-----------+------------+---------+---------+
1 row in set (0.00 sec)

结论是一个事务在read uncommitted级别下，可以出现脏读（不需要管其他事务是怎样隔离级别）

复现不可重复读

现在session2 的隔离级别是read committed, session1 是RR

在session2 开启一个事务，两次读取persons表的记录，但是两次读取之间session1修改/插入数据

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

mysql> select * from persons;
+----+-----------+------------+---------+---------+
| id | last_name | first_name | address | city    |
+----+-----------+------------+---------+---------+
|  1 | update    | j          | haidian | beijing |
+----+-----------+------------+---------+---------+
1 row in set (0.00 sec)

(session1) mysql>update persons set last_name='re_update' where id =1;


(session2)mysql> select * from persons;
+----+-----------+------------+---------+---------+
| id | last_name | first_name | address | city    |
+----+-----------+------------+---------+---------+
|  1 | re_update | j          | haidian | beijing |
+----+-----------+------------+---------+---------+
1 row in set (0.00 sec)

结果是session2在一次事务中，读同一个数据，出现了不同的结果 错误：不可重复读

我们再次把session2的隔离级别修改为rr，在重复下之之前的操作，发现不可重复读的问题解决了

# session2
mysql> set session transaction_isolation='repeatable-read';
Query OK, 0 rows affected (0.00 sec)

# session2
mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set, 1 warning (0.00 sec)

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

# session2
mysql> select * from persons;
+----+-----------+------------+---------+---------+
| id | last_name | first_name | address | city    |
+----+-----------+------------+---------+---------+
|  1 | re_update | j          | haidian | beijing |
+----+-----------+------------+---------+---------+
1 row in set (0.00 sec)

# session1
insert into persons values('new','record','haidian','beijing');


# session2
mysql> select * from persons;
+----+-----------+------------+---------+---------+
| id | last_name | first_name | address | city    |
+----+-----------+------------+---------+---------+
|  1 | re_update | j          | haidian | beijing |
+----+-----------+------------+---------+---------+
1 row in set (0.00 sec)

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

# session2
mysql> select * from persons;
+----+-----------+------------+---------+---------+
| id | last_name | first_name | address | city    |
+----+-----------+------------+---------+---------+
|  1 | re_update | j          | haidian | beijing |
|  2 | new       | record     | haidian | beijing |
+----+-----------+------------+---------+---------+
2 rows in set (0.00 sec)

复现幻读

这个时候session1 和 session2 的隔离级别都是RR， 但是无法避免幻读

看一个幻读的例子

session1                                      				session2
start transaction
											  				start transactio

select * from persons;
+----+-----------+------------+---------+---------+
| id | last_name | first_name | address | city    |
+----+-----------+------------+---------+---------+
|  1 | re_update | j          | haidian | beijing |
|  2 | new       | record     | haidian | beijing |
+----+-----------+------------+---------+---------+
															insert into persons(id,last_name,first_name,address,city) values(3,'another','record','haidian','beijing');
															commit
mysql> insert into persons values(3,'test','test','test','test');
ERROR 1062 (23000): Duplicate entry '3' for key 'PRIMARY'
	

如何避免幻读？可以使用 加锁读

select * from persons for update;

参考

MySQL 四种事务隔离级的说明
MySQL 5.7 官方文档