mysql隔离级别（例子测试验证）

mysql隔离级别（version=8.0.17 引擎：InnoDB ）

一.设置隔离级别

​ //设置read uncommitted级别：
​ set session transaction isolation level read uncommitted;

​ //设置read committed级别：
​ set session transaction isolation level read committed;

​ //设置repeatable read级别：
​ set session transaction isolation level repeatable read;

​ //设置serializable级别：
​ set session transaction isolation level serializable;

二.事务的并发问题 （脏读，不可重复读，幻读）

• 脏读：事务A读取了事务B更新的数据，事务B未提交，如果回滚，那么A读取到的数据是脏数据。（read-committed可以解决）
• 不可重复读：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果 不一致。（repeatable-read可以解决）
• 幻读：并不是说两次读取获取的结果集不同，幻读侧重的方面是某一次的 select 操作得到的结果所表征的数据状态无法支撑后续的业务操作。
  • select 某记录是否存在，不存在，准备插入此记录，但执行 insert 时发现此记录已存在，无法插入，此时就发生了幻读。
  • select 某记录是不存在，更新该记录后，再次查询该记录，发现该记录存在，发生幻读。

三.MySQL事务隔离级别

事务隔离级别	脏读	不可重复读	幻读
读未提交（read-uncommitted）	是	是	是
读提交（read-committed）	否	是	是
可重复读（repeatable-read）	否	否	是
串行化（serializable）	否	否	否

四.例子说明隔离级别情况

测试读未提交（read-uncommitted）

# 设置隔离级别为 read uncommitted
mysql> set session transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.00 sec)
# 查看隔离级别
mysql> show variables like "%transaction_isolation%";
+-----------------------+------------------+
| Variable_name         | Value            |
+-----------------------+------------------+
| transaction_isolation | READ-UNCOMMITTED |
+-----------------------+------------------+
1 row in set, 1 warning (0.00 sec)

测试用例说明： 事务A 更新数据，事务不提交。 事务B读取。

# 事务B 第一次读取数据
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from stu;
+----+------+------+---------+
| id | age  | name | address |
+----+------+------+---------+
|  1 |  100 | 1sss | sss     |
+----+------+------+---------+
1 row in set (0.00 sec)

# 事务A更新数据 不提交事务
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update stu set age = 50;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

# 事务B 第二次读取数据
mysql> select * from stu;
+----+------+------+---------+
| id | age  | name | address |
+----+------+------+---------+
|  1 |   50 | 1sss | sss     |
+----+------+------+---------+
1 row in set (0.00 sec)

测试结果：

事务B读取了事务A未提交的数据，也就是脏读。

# 事务A 插入一条记录
mysql> insert into stu (id,age) values (2,20);
Query OK, 1 row affected (0.01 sec)

测试读提交（read-committed）

# 设置隔离级别 读提交
mysql> set session transaction isolation level read committed;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like "%transaction_isolation%";
+-----------------------+----------------+
| Variable_name         | Value          |
+-----------------------+----------------+
| transaction_isolation | READ-COMMITTED |
+-----------------------+----------------+
1 row in set, 1 warning (0.00 sec)

测试说明：事务A 更新数据，事务不提交。 事务B读取。

# 事务B第一次读取数据
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from stu;
+----+------+------+---------+
| id | age  | name | address |
+----+------+------+---------+
|  1 |   50 | 1sss | sss     |
|  2 |   20 | NULL | NULL    |
+----+------+------+---------+
2 rows in set (0.00 sec)

# 事务A 更新数据
mysql> begin;
mysql> update stu set age = 30 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

# 事务 B第二次读取数据
mysql> select * from stu;
+----+------+------+---------+
| id | age  | name | address |
+----+------+------+---------+
|  1 |   50 | 1sss | sss     |
|  2 |   20 | NULL | NULL    |
+----+------+------+---------+
2 rows in set (0.00 sec)

测试结果：

事务B两次读取的数据一致,事务A未提交的数据，事务B不会读取。解决了脏读问题。

继续测试–>

# 事务A提交
mysql> select * from stu;
+----+------+------+---------+
| id | age  | name | address |
+----+------+------+---------+
|  1 |   30 | 1sss | sss     |
|  2 |   20 | NULL | NULL    |
+----+------+------+---------+
2 rows in set (0.00 sec)
mysql> commit;

# 事务B第三次读取
mysql> select * from stu;
+----+------+------+---------+
| id | age  | name | address |
+----+------+------+---------+
|  1 |   30 | 1sss | sss     |
|  2 |   20 | NULL | NULL    |
+----+------+------+---------+
2 rows in set (0.00 sec)

测试结果：

事务B第三次读取，和前两次不一致。事务B读取数据时，事务A更改数据并提交。导致事务B读取的数据不一致。也就是不可重复读问题。

测试可重复读（repeatable-read）

# 设置事务隔离级别为 repeatable-read
mysql> set session transaction isolation level repeatable read;
Query OK, 0 rows affected (0.00 sec)
mysql>  show variables like "%transaction_isolation%";
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set, 1 warning (0.00 sec)

测试说明：事务A 更新数据，事务B读取数据在事务A事务提交前后。

# 事务B第一次读取数据
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from stu;
+----+------+------+---------+
| id | age  | name | address |
+----+------+------+---------+
|  1 |   30 | 1sss | sss     |
|  2 |   20 | NULL | NULL    |
+----+------+------+---------+
2 rows in set (0.00 sec)

# 事务A更新数据 事务未提交
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

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

mysql> select * from stu;
+----+------+------+---------+
| id | age  | name | address |
+----+------+------+---------+
|  1 |   52 | 1sss | sss     |
|  2 |   20 | NULL | NULL    |
+----+------+------+---------+
2 rows in set (0.00 sec)

# 事务B第二次读取数据
mysql> select * from stu;
+----+------+------+---------+
| id | age  | name | address |
+----+------+------+---------+
|  1 |   30 | 1sss | sss     |
|  2 |   20 | NULL | NULL    |
+----+------+------+---------+
2 rows in set (0.00 sec)

# 事务A提交
mysql> commit;
Query OK, 0 rows affected (0.01 sec)

mysql>

# 事务B 第三次读取数据
mysql> select * from stu;
+----+------+------+---------+
| id | age  | name | address |
+----+------+------+---------+
|  1 |   30 | 1sss | sss     |
|  2 |   20 | NULL | NULL    |
+----+------+------+---------+
2 rows in set (0.00 sec)

测试结果：

事务B读取的数据三次一致。无论事务A是否提交，事务B在一个事务中读取的数据都是一致。没有出现脏读，不可重复读问题。

继续测试–>测试说明：事务A删除一条记录，事务B读取。

# 事务A删除了一条记录 提交事务
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from stu where id = 2;
Query OK, 1 row affected (0.00 sec)

mysql> select * from stu;
+----+------+------+---------+
| id | age  | name | address |
+----+------+------+---------+
|  1 |   52 | 1sss | sss     |
+----+------+------+---------+
1 row in set (0.00 sec)
mysql> commit;

# 事务B第四次读取数据
mysql> select * from stu;
+----+------+------+---------+
| id | age  | name | address |
+----+------+------+---------+
|  1 |   52 | 1sss | sss     |
|  2 |   20 | NULL | NULL    |
+----+------+------+---------+

测试结果：

事务B读取的数据四次一致。事务A删除了一条记录，事务B读取的依旧和之前一致。从而说明了：幻读，并不是说两次读取获取的结果集不同。

继续测试–>测试说明：事务A插入一条记录，事务B插入同样的记录（主键一致）。

# 事务A插入一条记录 id=3,并提交事务
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into stu (id,age) values (3,33);
Query OK, 1 row affected (0.00 sec)

mysql> select * from stu;
+----+------+------+---------+
| id | age  | name | address |
+----+------+------+---------+
|  1 |   30 | 1sss | sss     |
|  3 |   33 | NULL | NULL    |
+----+------+------+---------+
2 rows in set (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.01 sec)

# 事务B读取了数据，没有id=3的记录。
mysql> select * from stu;
+----+------+------+---------+
| id | age  | name | address |
+----+------+------+---------+
|  1 |   30 | 1sss | sss     |
|  2 |   20 | NULL | NULL    |
+----+------+------+---------+
2 rows in set (0.00 sec)
# 事务B插入记录id=3的数据
mysql> insert into stu (id,age) values (3,33);
ERROR 1062 (23000): Duplicate entry '3' for key 'PRIMARY'
mysql>

测试结果：

事务B读取数据发现没有id=3的记录，插入时错误描述已存在。这个就是幻读。

继续测试–>测试说明：事务A插入一条记录id=4，事务B更新id=4的记录。

# 事务A插入一条记录id=4
mysql> insert into stu (id,age) values (4,44);
Query OK, 1 row affected (0.01 sec)

mysql> select * from stu where id=4 ;
+----+------+------+---------+
| id | age  | name | address |
+----+------+------+---------+ |
|  4 |   44 | NULL | NULL    |
+----+------+------+-----

# 事务B读取数据发现没有id=4的记录
mysql> select * from stu where id=4;
Empty set (0.00 sec)
2 rows in set (0.00 sec)

# 事务B更新id=4的记录
mysql> update stu set age = 55 where id = 4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

# 事务B读取到id=4的记录
mysql> select * from stu where id=4;
+----+------+------+---------+
| id | age  | name | address |
+----+------+------+---------+
|  4 |   55 | NULL | NULL    |
+----+------+------+---------+
1 row in set (0.00 sec)

测试结果：

事务B中不存在id=4的记录，更新后可以查到，也就是幻读。

测试串行化（serializable）

# 设置隔离级别为串行化
mysql> set session transaction isolation level serializable;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like "%transaction_isolation%";
+-----------------------+--------------+
| Variable_name         | Value        |
+-----------------------+--------------+
| transaction_isolation | SERIALIZABLE |
+-----------------------+--------------+
1 row in set, 1 warning (0.00 sec)

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

测试说明：事务B开启中，事务A插入一条不存在的记录，事务B，插入相同的记录。

# 事务B查询数据
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from stu;
+----+------+------+---------+
| id | age  | name | address |
+----+------+------+---------+
|  1 |   30 | 1sss | sss     |
|  3 |   33 | NULL | NULL    |
|  4 |   55 | NULL | NULL    |
+----+------+------+---------+
3 rows in set (0.00 sec)

# 事务A插入记录 阻塞中
mysql>  insert into stu (id, age) values (6,61);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql>

测试结果：

事务B开启中，事务A插入一条不存在的记录，阻塞中，无法插入。事务串行处理

总结

事务隔离级别	脏读	不可重复读	幻读
读未提交（read-uncommitted）	是	是	是
读提交（read-committed）	否	是	是
可重复读（repeatable-read）	否	否	是
串行化（serializable）	否	否	否