Mysql隔离级别演示

---

一. 准备工作

本文章是基于MySQL8.0下演示，开启俩个窗口，为了方便说明，下面我会使用事务1和事务2来演示

1.1 准备表

mysql> select * from student;
+----+------+------+
| id | name | tid  |
+----+------+------+
|  3 | 小张 |    1 |
|  1 | 小明 |    1 |
|  2 | 小红 |    1 |
|  4 | 小李 |    2 |
|  5 | 小王 |    2 |
+----+------+------+
5 rows in set (0.00 sec)

1.2 查看事务隔离级别

mysql> show variables like 'transaction%';
+----------------------------------+-----------------+
| Variable_name                    | Value           |
+----------------------------------+-----------------+
| transaction_alloc_block_size     | 8192            |
| transaction_allow_batching       | OFF             |
| transaction_isolation            | REPEATABLE-READ |
| transaction_prealloc_size        | 4096            |
| transaction_read_only            | OFF             |
| transaction_write_set_extraction | XXHASH64        |
或使用
mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+

1.3 修改自动提交配置

mysql> show variables like '%autocommit%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> set autocommit = off;
Query OK, 0 rows affected (0.00 sec)

二、在读未提交的隔离级别下

2.1 修改隔离级别

mysql> set session transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.00 sec)

2.2 代码演示

对事务1进行修改操作，不提交事务

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

事务2进行查询操作

mysql> select * from student;
+----+------+------+
| id | name | tid  |
+----+------+------+
|  3 | 小张 |    1 |
|  2 | 小红 |    1 |
|  1 | 小黑 |    1 |
|  4 | 小李 |    2 |
|  5 | 小王 |    2 |
+----+------+------+
5 rows in set (0.00 sec)

2.3 总结

通过代码演示可以看出，事务1对数据进行了修改操作，但没有提交事务，这时，事务2进来，对数据进行了查询操作，查询到了事务1没有提交的数据.这就是脏读产生的原因.脏读只会在读未提交下产生.

三、在读已提交的隔离级别下

3.1 修改隔离级别

mysql> set session transaction isolation level read committed;
Query OK, 0 rows affected (0.00 sec)

3.2 代码演示

事务1进行修改操作，并不提交事务

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

事务2进行查询操作

mysql> select * from student;
+----+------+------+
| id | name | tid  |
+----+------+------+
|  3 | 小张 |    1 |
|  2 | 小红 |    1 |
|  1 | 小黑 |    1 |
|  4 | 小李 |    2 |
|  5 | 小王 |    2 |
+----+------+------+
5 rows in set (0.00 sec)

总结
可以看出，在读已提交的隔离级别下，对事务1进行了修改，事务2没有查询到修改的数据，这样就避免了会读到脏数据的，解决了脏读问题。但不能避免不可重复读问题，下面我会通过代码继续演示。

事务1进行修改操作，并未提交事务

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

这时，事务2进行读取数据

mysql> select * from student;
+----+------+------+
| id | name | tid  |
+----+------+------+
|  3 | 小张 |    1 |
|  2 | 小红 |    1 |
|  1 | 小黑 |    1 |
|  4 | 小李 |    2 |
|  5 | 小王 |    2 |
+----+------+------+
5 rows in set (0.00 sec)

事务1提交了事务

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

事务2再次读取数据

mysql> select * from student;
+----+------+------+
| id | name | tid  |
+----+------+------+
|  3 | 小张 |    1 |
|  2 | 小红 |    1 |
|  1 | 小蓝 |    1 |
|  4 | 小李 |    2 |
|  5 | 小王 |    2 |
+----+------+------+
5 rows in set (0.00 sec)

3.4 总结

在读已提交的隔离级别下，虽然解决了不可重复读的问题，但是没有解决不可重复读的问题，不可重复读就是事务2在事务1提交前读取到的数据，与 事务1提交后读取的数据不一致.

四 在可重复读的隔离级别下

4.1 修改隔离级别

mysql> set session transaction isolation level repeatable read;
Query OK, 0 rows affected (0.00 sec)

4.2代码演示

事务1进行修改操作，并未提交事务

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

事务2读取数据

mysql> select * from student;
+----+------+------+
| id | name | tid  |
+----+------+------+
|  3 | 小张 |    1 |
|  2 | 小红 |    1 |
|  1 | 小蓝 |    1 |
|  4 | 小李 |    2 |
|  5 | 小王 |    2 |
+----+------+------+
5 rows in set (0.00 sec)

事务1提交事务

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

事务2读取数据

mysql> select * from student;
+----+------+------+
| id | name | tid  |
+----+------+------+
|  3 | 小张 |    1 |
|  2 | 小红 |    1 |
|  1 | 小蓝 |    1 |
|  4 | 小李 |    2 |
|  5 | 小王 |    2 |
+----+------+------+
5 rows in set (0.00 sec)

总结
可以看出，在可重复读的隔离级别下，没有产生脏读，不可重复读的问题，但有可能产生幻读的问题.下面我会通过代码进行演示.
事务2查询 name=小紫 的数据

mysql> select * from where name = '小紫';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where name = '小紫'' at line 1

事务1插入一条 name=小紫 的数据，并提交事务

mysql> insert into student value('6','小紫','2');
Query OK, 1 row affected (0.00 sec)

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

事务2再去查询 name=小紫 的数据

mysql> select * from where name = '小紫';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where name = '小紫'' at line 1

事务2插入一条 name=小紫 的数据

mysql> insert into student value('6','小紫','2');
ERROR 1062 (23000): Duplicate entry '6' for key 'PRIMARY'

总结

时间点	事务2	事务1
1	开启事务
2		开启事务
3	查询数据“小紫”，不存在
4		插入数据“小紫”
5		提交事务
6	查询数据“小紫”，不存在
7	插入数据“小紫”，不成功

总结

下面我总结了各种隔离级别下可能出现的问题.