MySQL 事务隔离级别
| 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 回滚，然后重新开始：
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 对串行化异常的处理
MySQL 利用锁机制来处理串行化异常。
T1 and T2
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
T1
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> select sum(balance) from accounts;
+--------------+
| sum(balance) |
+--------------+
| 270.00 |
+--------------+
1 row in set (0.00 sec)
mysql> insert into accounts (owner, balance) values ('sum', 270);
Query OK, 1 row 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 |
| 4 | sum | 270.00 | 2021-07-24 18:29:02 | 2021-07-24 18:29:02 |
+----+-------+---------+---------------------+---------------------+
4 rows in set (0.00 sec)
T2
mysql> select * from accounts;
T1
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
评论