写点什么

MySQL 事务隔离级别,java 面试常问的设计模式

作者:Java高工P7
  • 2021 年 11 月 10 日
  • 本文字数:7900 字

    阅读完需:约 26 分钟

T1

mysql> set session transaction isolation level read uncommitted;


Query OK, 0 rows affected (0.00 sec)


mysql> select @@transaction_isolation;


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


| @@transaction_isolation |


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


| READ-UNCOMMITTED |


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


1 row in set (0.00 sec)

T2

mysql> set session transaction isolation level read uncommitted;


Query OK, 0 rows affected (0.00 sec)


mysql> select @@transaction_isolation;


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


| @@transaction_isolation |


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


| READ-UNCOMMITTED |


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


1 row in set (0.00 sec)


复制代码


分别在两个 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


《Android学习笔记总结+最新移动架构视频+大厂安卓面试真题+项目实战源码讲义》
浏览器打开:qq.cn.hn/FTe 免费领取
复制代码


| 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 回滚,然后重新开始:

T1 and 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)


复制代码


在 T1 中执行 update 并迅速将 T2 提交,此时可以看到 T1 中的 update 执行成功。

T1

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


Query OK, 1 row affected (3.13 sec)


Rows matched: 1 Changed: 1 Warnings: 0

T2

mysql> commit;


Query OK, 0 rows affected (0.00 sec)

T1

mysql> rollback;


Query OK, 0 rows affected (0.00 sec)


复制代码

⒌MySQL 对串行化异常的处理

用户头像

Java高工P7

关注

还未添加个人签名 2021.11.08 加入

还未添加个人简介

评论

发布
暂无评论
MySQL 事务隔离级别,java面试常问的设计模式