MySQL 事务隔离级别，教你抓住面试的重点_事务隔离级别面试-优快云博客

本文链接：https://blog.youkuaiyun.com/m0_60722379/article/details/119518041

分别在两个 session 中执行事务，在 T2 中对 ID 为 1 记录进行修改：


# T1

mysql> begin;

Query OK, 0 rows affected (0.00 sec)



mysql> select * from accounts where id = 1;

+----+-------+---------+---------------------+---------------------+

| id | owner | balance | created_at          | updated_at          |

+----+-------+---------+---------------------+---------------------+

|  1 | A     |  100.00 | 2021-07-24 16:21:58 | 2021-07-24 16:53:07 |

+----+-------+---------+---------------------+---------------------+

1 row in set (0.00 sec)



# T2

mysql> begin;

Query OK, 0 rows affected (0.00 sec)



mysql> select * from accounts where id = 1;

+----+-------+---------+---------------------+---------------------+

| id | owner | balance | created_at          | updated_at          |

+----+-------+---------+---------------------+---------------------+

|  1 | A     |  100.00 | 2021-07-24 16:21:58 | 2021-07-24 16:53:07 |

+----+-------+---------+---------------------+---------------------+

1 row in set (0.00 sec)



mysql> update accounts set balance = balance - 10 where id = 1;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1  Changed: 1  Warnings: 0



mysql> select * from accounts where id = 1;

+----+-------+---------+---------------------+---------------------+

| id | owner | balance | created_at          | updated_at          |

+----+-------+---------+---------------------+---------------------+

|  1 | A     |   90.00 | 2021-07-24 16:21:58 | 2021-07-24 16:59:23 |

+----+-------+---------+---------------------+---------------------+

1 row in set (0.00 sec)

复制代码

在 T 2 提交之前查看 T1 中相应记录的情况：


# T1

mysql> select * from accounts where id = 1;

+----+-------+---------+---------------------+---------------------+

| id | owner | balance | created_at          | updated_at          |

+----+-------+---------+---------------------+---------------------+

|  1 | A     |   90.00 | 2021-07-24 16:21:58 | 2021-07-24 16:59:23 |

+----+-------+---------+---------------------+---------------------+

1 row in set (0.00 sec)



mysql> commit;

Query OK, 0 rows affected (0.00 sec)



# T2

mysql> commit;

Query OK, 0 rows affected (0.01 sec)

复制代码

可以看到，在读未提交事务隔离级别下，T2 中对数据的修改在 T2 提交之前已经在 T1 中体现，因此造成了 T1 中的脏读。

读提交

首先将会话中的事务隔离级别改为读提交：


# T1 and T2

mysql> set session transaction isolation level read committed;

Query OK, 0 rows affected (0.00 sec)



mysql> select @@transaction_isolation;

+-------------------------+

| @@transaction_isolation |

+-------------------------+

| READ-COMMITTED          |

+-------------------------+

1 row in set (0.00 sec)

复制代码

在 T1 和 T2 中分别查看当前表中的数据：


# T1

mysql> begin;

Query OK, 0 rows affected (0.00 sec)



mysql> select * from accounts;

+----+-------+---------+---------------------+---------------------+

| id | owner | balance | created_at          | updated_at          |

+----+-------+---------+---------------------+---------------------+

|  1 | A     |   90.00 | 2021-07-24 16:21:58 | 2021-07-24 16:59:23 |

|  2 | B     |  100.00 | 2021-07-24 16:21:58 | 2021-07-24 16:21:58 |

|  3 | C     |  100.00 | 2021-07-24 16:21:58 | 2021-07-24 16:21:58 |

+----+-------+---------+---------------------+---------------------+

3 rows in set (0.00 sec)



# T2

mysql> begin;

Query OK, 0 rows affected (0.00 sec)



mysql> select * from accounts;

+----+-------+---------+---------------------+---------------------+

| id | owner | balance | created_at          | updated_at          |

+----+-------+---------+---------------------+---------------------+

|  1 | A     |   90.00 | 2021-07-24 16:21:58 | 2021-07-24 16:59:23 |

|  2 | B     |  100.00 | 2021-07-24 16:21:58 | 2021-07-24 16:21:58 |

|  3 | C     |  100.00 | 2021-07-24 16:21:58 | 2021-07-24 16:21:58 |

+----+-------+---------+---------------------+---------------------+

3 rows in set (0.00 sec)

复制代码

此时在 T2 中更新 ID 为 1 的记录并在 T1 和 T2 中分别查看 ID 为 1 的记录：


# T2

mysql> update accounts set balance = balance - 10 where id = 1;

Query OK, 3 rows affected (0.00 sec)

Rows matched: 3  Changed: 3  Warnings: 0



mysql> select * from accounts where id = 1;

+----+-------+---------+---------------------+---------------------+

| id | owner | balance | created_at          | updated_at          |

+----+-------+---------+---------------------+---------------------+

|  1 | A     |   80.00 | 2021-07-24 16:21:58 | 2021-07-24 17:13:02 |

+----+-------+---------+---------------------+---------------------+

1 row in set (0.00 sec)



# T1

mysql> select * from accounts where id = 1;

+----+-------+---------+---------------------+---------------------+

| id | owner | balance | created_at          | updated_at          |

+----+-------+---------+---------------------+---------------------+

|  1 | A     |   90.00 | 2021-07-24 16:21:58 | 2021-07-24 16:59:23 |

+----+-------+---------+---------------------+---------------------+

1 row in set (0.00 sec)



mysql> select * from accounts where balance >= 90;

+----+-------+---------+---------------------+---------------------+

| id | owner | balance | created_at          | updated_at          |

+----+-------+---------+---------------------+---------------------+

|  1 | A     |   90.00 | 2021-07-24 16:21:58 | 2021-07-24 16:59:23 |

|  2 | B     |  100.00 | 2021-07-24 16:21:58 | 2021-07-24 16:21:58 |

|  3 | C     |  100.00 | 2021-07-24 16:21:58 | 2021-07-24 16:21:58 |

+----+-------+---------+---------------------+---------------------+

3 rows in set (0.00 sec)

复制代码

由此可知，在读提交的事务隔离级别之下，T2 中的修改在 T2 提交之前不会体现在 T1 中。现在，将 T2 提交，然后再在 T1 中执行刚才的查询语句：


# T2

mysql> commit;

Query OK, 0 rows affected (0.01 sec)



# T1

mysql> select * from accounts where id = 1;

+----+-------+---------+---------------------+---------------------+

| id | owner | balance | created_at          | updated_at          |

+----+-------+---------+---------------------+---------------------+

|  1 | A     |   80.00 | 2021-07-24 16:21:58 | 2021-07-24 17:13:02 |

+----+-------+---------+---------------------+---------------------+

1 row in set (0.00 sec)



mysql> select * from accounts where balance >= 90;

+----+-------+---------+---------------------+---------------------+

| id | owner | balance | created_at          | updated_at          |

+----+-------+---------+---------------------+---------------------+

|  2 | B     |  100.00 | 2021-07-24 16:21:58 | 2021-07-24 16:21:58 |

|  3 | C     |  100.00 | 2021-07-24 16:21:58 | 2021-07-24 16:21:58 |

+----+-------+---------+---------------------+---------------------+

2 rows in set (0.00 sec)



mysql> commit;

Query OK, 0 rows affected (0.01 sec)

复制代码

再次查看 ID 为 1 的记录，发现 balance 发生了变化，造成了不可重复读。而以同样的条件过滤 balance，结果集中的记录少了一条，造成了幻读。

可重复读

首先更新会话的事务隔离级别：


# T1 and T2

mysql> set session transaction isolation level repeatable read;

Query OK, 0 rows affected (0.00 sec)



mysql> select @@transaction_isolation;

+-------------------------+

| @@transaction_isolation |

+-------------------------+

| REPEATABLE-READ         |

+-------------------------+

1 row in set (0.00 sec)

复制代码

在 T1 和 T2 中分别查看当前表的数据情况，之后在 T2 中更新 ID 为 1 的记录并提交：


# T1

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from accounts;

+----+-------+---------+---------------------+---------------------+

| id | owner | balance | created_at          | updated_at          |

+----+-------+---------+---------------------+---------------------+

|  1 | A     |   80.00 | 2021-07-24 16:21:58 | 2021-07-24 17:13:02 |

|  2 | B     |  100.00 | 2021-07-24 16:21:58 | 2021-07-24 16:21:58 |

|  3 | C     |  100.00 | 2021-07-24 16:21:58 | 2021-07-24 16:21:58 |

+----+-------+---------+---------------------+---------------------+

3 rows in set (0.00 sec)



# T2

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from accounts;

+----+-------+---------+---------------------+---------------------+

| id | owner | balance | created_at          | updated_at          |

+----+-------+---------+---------------------+---------------------+

|  1 | A     |   80.00 | 2021-07-24 16:21:58 | 2021-07-24 17:13:02 |

|  2 | B     |  100.00 | 2021-07-24 16:21:58 | 2021-07-24 16:21:58 |

|  3 | C     |  100.00 | 2021-07-24 16:21:58 | 2021-07-24 16:21:58 |

+----+-------+---------+---------------------+---------------------+

3 rows in set (0.00 sec)



mysql> update accounts set balance = balance - 10 where id = 1;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1  Changed: 1  Warnings: 0



mysql> select * from accounts;

+----+-------+---------+---------------------+---------------------+

| id | owner | balance | created_at          | updated_at          |

+----+-------+---------+---------------------+---------------------+

|  1 | A     |   70.00 | 2021-07-24 16:21:58 | 2021-07-24 17:35:15 |

|  2 | B     |  100.00 | 2021-07-24 16:21:58 | 2021-07-24 16:21:58 |

|  3 | C     |  100.00 | 2021-07-24 16:21:58 | 2021-07-24 16:21:58 |

+----+-------+---------+---------------------+---------------------+

3 rows in set (0.00 sec)



mysql> commit;

Query OK, 0 rows affected (0.00 sec)

复制代码

此时查看 T1 中的数据：


# T1

mysql> select * from accounts where id = 1;

+----+-------+---------+---------------------+---------------------+

| id | owner | balance | created_at          | updated_at          |

+----+-------+---------+---------------------+---------------------+

|  1 | A     |   80.00 | 2021-07-24 16:21:58 | 2021-07-24 17:38:22 |

+----+-------+---------+---------------------+---------------------+

1 row in set (0.00 sec)



mysql> select * from accounts where balance >= 80;

+----+-------+---------+---------------------+---------------------+

| id | owner | balance | created_at          | updated_at          |

+----+-------+---------+---------------------+---------------------+

|  1 | A     |   80.00 | 2021-07-24 16:21:58 | 2021-07-24 17:38:22 |

|  2 | B     |  100.00 | 2021-07-24 16:21:58 | 2021-07-24 16:21:58 |

|  3 | C     |  100.00 | 2021-07-24 16:21:58 | 2021-07-24 16:21:58 |

+----+-------+---------+---------------------+---------------------+

3 rows in set (0.00 sec)

复制代码

由此可见，可重复读的事务隔离级别成功的避免了不可重复读和幻读的情况。但如果此时在 T1 中对 ID 为 1 的记录的 balance 进行更新操作会发生什么情况呢？


# T1

mysql> update accounts set balance = balance - 10;

Query OK, 3 rows affected (0.00 sec)

Rows matched: 3  Changed: 3  Warnings: 0



mysql> select * from accounts where id = 1;

+----+-------+---------+---------------------+---------------------+

| id | owner | balance | created_at          | updated_at          |

+----+-------+---------+---------------------+---------------------+

|  1 | A     |   60.00 | 2021-07-24 16:21:58 | 2021-07-24 17:47:14 |

+----+-------+---------+---------------------+---------------------+

1 row in set (0.00 sec)



mysql> rollback;

Query OK, 0 rows affected (0.00 sec)

复制代码

可见，可重复读的隔离级别保证了更新之后结果的正确性，但却容易让人产生错觉。

串行化

首先修改会话的事务隔离级别：


# T1 and T2

mysql> set session transaction isolation level serializable;

Query OK, 0 rows affected (0.00 sec)



mysql> select @@transaction_isolation;

+-------------------------+

| @@transaction_isolation |

+-------------------------+

| SERIALIZABLE            |

+-------------------------+

1 row in set (0.00 sec)

复制代码

在 T1 和 T2 中分别查询当前表的数据情况，并在 T2 中更新 ID 为 1 的记录：


# T1

mysql> begin;

Query OK, 0 rows affected (0.00 sec)



mysql> select * from accounts;

+----+-------+---------+---------------------+---------------------+

| id | owner | balance | created_at          | updated_at          |

+----+-------+---------+---------------------+---------------------+

|  1 | A     |   70.00 | 2021-07-24 16:21:58 | 2021-07-24 17:39:37 |

|  2 | B     |  100.00 | 2021-07-24 16:21:58 | 2021-07-24 17:32:16 |

|  3 | C     |  100.00 | 2021-07-24 16:21:58 | 2021-07-24 17:32:16 |

+----+-------+---------+---------------------+---------------------+

3 rows in set (0.00 sec)



# T2

mysql> begin;

Query OK, 0 rows affected (0.00 sec)



mysql> select * from accounts;

+----+-------+---------+---------------------+---------------------+

| id | owner | balance | created_at          | updated_at          |

+----+-------+---------+---------------------+---------------------+

|  1 | A     |   70.00 | 2021-07-24 16:21:58 | 2021-07-24 17:39:37 |

|  2 | B     |  100.00 | 2021-07-24 16:21:58 | 2021-07-24 17:32:16 |

|  3 | C     |  100.00 | 2021-07-24 16:21:58 | 2021-07-24 17:32:16 |

+----+-------+---------+---------------------+---------------------+

3 rows in set (0.00 sec)



mysql> update accounts set balance = balance - 10 where id = 1;

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

复制代码

T2 中的 update 首先会被阻塞，最终会因为等待超时而失败。之所以产生这样的结果是因为在串行化的事务隔离级别中，MySQL 会将 select 语句隐式的转换为 select for share。此时，取得 select for share 锁的事务只允许其他事务对这些数据进行读操作，不允许写操作。所以，T2 中的 update 被 T1 中的锁阻塞并最终超时。

将 T2 回滚然后重新开始，仍然执行之前的操作，但这次不会让 T2 超时。在 T2 超时之前，在 T1 中执行相同的操作：


# T2

mysql> rollback;

Query OK, 0 rows affected (0.00 sec)



mysql> begin;

Query OK, 0 rows affected (0.00 sec)



mysql> select * from accounts;

+----+-------+---------+---------------------+---------------------+

| id | owner | balance | created_at          | updated_at          |

+----+-------+---------+---------------------+---------------------+

|  1 | A     |   70.00 | 2021-07-24 16:21:58 | 2021-07-24 17:39:37 |

|  2 | B     |  100.00 | 2021-07-24 16:21:58 | 2021-07-24 17:32:16 |

|  3 | C     |  100.00 | 2021-07-24 16:21:58 | 2021-07-24 17:32:16 |

+----+-------+---------+---------------------+---------------------+

3 rows in set (0.00 sec)



mysql> update accounts set balance = balance - 10 where id = 1;



# T1

mysql> update accounts set balance = balance - 10 where id = 1;

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction



# T2

mysql> update accounts set balance = balance - 10 where id = 1;

Query OK, 1 row affected (3.51 sec)

Rows matched: 1  Changed: 1  Warnings: 0

复制代码

此时，T2 中的 update 操作被 T1 中的 select 阻塞，而 T1 中的 uodate 操作同样被 T2 中的 select 阻塞，发生了死锁。此时 MySQL 会将 T1 回滚释放锁然后重新开始，在 T1 将锁释放之后，T2 中的 uodate 操作便可执行成功。

将 T1 和 T2 回滚，然后重新开始：

最后：学习总结——MyBtis知识脑图（纯手绘xmind文档）

学完之后，若是想验收效果如何，其实最好的方法就是可自己去总结一下。比如我就会在学习完一个东西之后自己去手绘一份xmind文件的知识梳理大纲脑图，这样也可方便后续的复习，且都是自己的理解，相信随便瞟几眼就能迅速过完整个知识，脑补回来。下方即为我手绘的MyBtis知识脑图，由于是xmind文件，不好上传，所以小编将其以图片形式导出来传在此处，细节方面不是特别清晰。但可给感兴趣的朋友提供完整的MyBtis知识脑图原件（包括上方的面试解析xmind文档）

除此之外，前文所提及的Alibaba珍藏版mybatis手写文档以及一本小小的MyBatis源码分析文档——《MyBatis源码分析》等等相关的学习笔记文档，也皆可分享给认可的朋友！

资料领取方式：戳这里免费下载

alance = balance - 10 where id = 1;

Query OK, 1 row affected (3.51 sec)

Rows matched: 1 Changed: 1 Warnings: 0

复制代码




  此时，T2 中的 `update` 操作被 T1 中的 `select` 阻塞，而 T1 中的 `uodate` 操作同样被 T2 中的 `select` 阻塞，发生了死锁。此时 MySQL 会将 T1 回滚释放锁然后重新开始，在 T1 将锁释放之后，T2 中的 `uodate` 操作便可执行成功。



  将 T1 和 T2 回滚，然后重新开始：




## 最后：学习总结——MyBtis知识脑图（纯手绘xmind文档）

学完之后，若是想验收效果如何，其实最好的方法就是可自己去总结一下。比如我就会在学习完一个东西之后自己去手绘一份xmind文件的知识梳理大纲脑图，这样也可方便后续的复习，且都是自己的理解，相信随便瞟几眼就能迅速过完整个知识，脑补回来。下方即为我手绘的MyBtis知识脑图，由于是xmind文件，不好上传，所以小编将其以图片形式导出来传在此处，细节方面不是特别清晰。但可给感兴趣的朋友提供完整的MyBtis知识脑图原件（包括上方的面试解析xmind文档）

[外链图片转存中...(img-O3jTzJIF-1628416013541)]

除此之外，前文所提及的Alibaba珍藏版mybatis手写文档以及一本小小的MyBatis源码分析文档——《MyBatis源码分析》等等相关的学习笔记文档，也皆可分享给认可的朋友！

**[资料领取方式：戳这里免费下载](https://gitee.com/vip204888/java-p7)**