MySQL 事务隔离级别

最新推荐文章于 2024-07-13 10:38:18 发布

2401_85763952

最新推荐文章于 2024-07-13 10:38:18 发布

阅读量665

点赞数 12

文章标签： mysql android 网络

本文链接：https://blog.csdn.net/2401_85763952/article/details/140029977

版权

MySQL 的事务必须满足 A（原子性）C（一致性）I（隔离性）D（持久性）原则。其中，隔离性是为了尽量减少并发事务彼此之间的影响，最高的隔离级别可以保证并发事务之间互不影响。

在实际应用过程中，为了适应不同的应用场景，隔离级别往往无法设置为最高级别。这就导致并发执行的事务之间或多或少都会相互影响，这些影响导致了各种读操作异常。

⒈ 四种读操作异常

脏读

事务 T1 与 T2 同时执行，T1 读到了 T2 写入但还没有提交的数据。如果最终 T2 执行事务的回滚，则 T1 最终返回了错误的结果集。

不可重复读

事务 T1 和 T2 同时执行，在 T1 中的 select 语句执行完之后，T2 对某条特定的数据进行了修改并提交，当再次在 T1 中执行 select 操作时，得到的同一条记录的结果与上一次不同。

不可重复读

幻读

幻读的情况与不可重复读类似，只不过幻读针对的是查询多条记录的场景。在 T1 中执行 select 语句之后，T2 中执行了 insert/update/delete 操作，导致在 T1 中再次执行 select 得到的结果集与上一次不同。

串行化异常

多个事务在没有解决冲突的前提下同时对相同数据进行修改并同时提交，导致最终的结果无法预测。

⒉ 四种隔离级别

读未提交（read uncommitted）

事务中可以看到其他事务写入但未提交的记录

读提交（read committed）

事务中可以看到其他事务写入并提交的记录

可重复读（repeatable read）

保证同一事务中的 select 多次执行得到相同的结果集，无论在此期间是否有别的事务对数据进行了修改

串行化（serializable）

最高的事务隔离级别，并发事务按照某种顺序串行执行并且相互之间没有交叠

⒊ 读操作异常与事务隔离级别的对应关系

| | 脏读 | 不可重复读 | 幻读 | 串行化异常 |

| — | — | — | — | — |

| 读未提交 | Y | Y | Y | Y |

| 读提交 | N | Y | Y | Y |

| 可重复读 | N | N | N | Y |

| 串行化 | N | N | N | N |

⒋ 验证

MySQL 的事务隔离级别有全局的，也有针对当前会话的。分别使用以下方式查看：

查看当前会话的事务隔离级别

select @@transaction_isolation；

查看全局的事务隔离级别

select @@global.transaction_isolation;

复制代码

同样，修改 MySQL 的事务隔离级别也有只修改当前会话和修改全局两种情况：

只修改当前会话

set session transaction isolation level repeatable read;

修改全局

set global transaction isolation level repeatable read;

复制代码

验证用到的表以及数据：

drop database if exists bank;

create database bank;

use bank;

drop table if exists accounts;

create table accounts(

id bigint unsigned auto_increment primary key comment ‘主键’,

owner char(32) not null default ‘’ comment ‘账户名称’,

balance decimal(10,2) not null comment ‘账户余额’,

created_at datetime not null default current_timestamp comment ‘创建时间’,

updated_at datetime not null default current_timestamp on update current_timestamp comment ‘更新时间’

)engine=innodb charset=utf8 collate=utf8_general_ci;

insert into accounts(owner, balance) values

(‘A’, 100),

(‘B’, 100),

(‘C’, 100);

复制代码

读未提交

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

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;

±—±------±--------±--------------------±--------------------+

±—±------±--------±--------------------±--------------------+

| 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;

±—±------±--------±--------------------±--------------------+

±—±------±--------±--------------------±--------------------+

| 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;

±—±------±--------±--------------------±--------------------+

±—±------±--------±--------------------±--------------------+

| 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;

±—±------±--------±--------------------±--------------------+

±—±------±--------±--------------------±--------------------+

| 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;

±—±------±--------±--------------------±--------------------+

±—±------±--------±--------------------±--------------------+

| 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;

±—±------±--------±--------------------±--------------------+

±—±------±--------±--------------------±--------------------+

| 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;

±—±------±--------±--------------------±--------------------+

±—±------±--------±--------------------±--------------------+

| 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;

±—±------±--------±--------------------±--------------------+

±—±------±--------±-----------------
—±--------------------+

| 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;

±—±------±--------±--------------------±--------------------+

±—±------±--------±--------------------±--------------------+

| 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;

±—±------±--------±--------------------±--------------------+

±—±------±--------±--------------------±--------------------+

| 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;

±—±------±--------±--------------------±--------------------+

±—±------±--------±--------------------±--------------------+

| 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;

±—±------±--------±--------------------±--------------------+

±—±------±--------±--------------------±--------------------+

| 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;

±—±------±--------±--------------------±--------------------+

±—±------±--------±--------------------±--------------------+

| 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;

±—±------±--------±--------------------±--------------------+

±—±------±--------±--------------------±--------------------+

| 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;

±—±------±--------±--------------------±--------------------+

±—±------±--------±--------------------±--------------------+

| 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;

±—±------±--------±--------------------±--------------------+

±—±------±--------±--------------------±--------------------+

| 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;

±—±------±--------±--------------------±--------------------+

±—±------±--------±--------------------±--------------------+

| 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;

±—±------±--------±--------------------±--------------------+

±—±------±--------±--------------------±--------------------+

| 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;

±—±------±--------±--------------------±--------------------+

±—±------±--------±--------------------±--------------------+

| 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;

±—±------±--------±--------------------±--------------------+

±—±------±--------±--------------------±--------------------+

| 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;

±—±------±--------±--------------------±--------------------+

±—±------±--------±--------------------±--------------------+

| 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;

±—±------±--------±--------------------±--------------------+

±—±------±--------±--------------------±--------------------+

| 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;

±—±------±--------±--------------------±--------------------+

±—±------±--------±--------------------±--------------------+

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

T2

mysql> select * from accounts;

±—±------±--------±--------------------±--------------------+

±—±------±--------±--------------------±--------------------+

| 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 (21.19 sec)

mysql> select sum(balance) from accounts;

±-------------+

| sum(balance) |

±-------------+

| 540.00 |

±-------------+

1 row in set (0.00 sec)

mysql> insert into accounts (owner, balance) values (‘sum’, 540);

Query OK, 1 row affected (0.00 sec)

mysql> select * from accounts;

±—±------±--------±--------------------±--------------------+

±—±------±--------±--------------------±--------------------+

| 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 |

| 5 | sum | 540.00 | 2021-07-24 18:32:05 | 2021-07-24 18:32:05 |

±—±------±--------±--------------------±--------------------+

5 rows in set (0.00 sec)

mysql> rollback;

Query OK, 0 rows affected (0.00 sec)

复制代码

锁机制保证了 T1 和 T2 中查询得到的 sum(balance) 的唯一性，因此在往数据表中写入数据时保证了写入数据的唯一性，确保不会写入 balance 相同的 sum 记录。

最后

小编这些年深知大多数初中级工程师，想要提升自己，往往是自己摸索成长，自己不成体系的自学效果低效漫长且无助。

因此我收集整理了一份《2024年Java全套学习资料》，初衷也很简单，就是希望能够帮助到想自学提升又不知道该从何学起的朋友，同时减轻大家的负担。

由于文件比较大，这里只是将部分目录截图出来，每个节点里面都包含大厂面经、学习笔记、源码讲义、实战项目、讲解视频，并且会持续更新！

如果你需要这些资料，⬅专栏获取
um’, 540);