关于mysql:MySQL事务的隔离级别与并发问题

MySQL 版本：8.0.27

一、事务并发执行面临的问题

1. 脏读（Dirty Read）

如果事务 A 读到了未提交的事务 B 批改过的数据，就意味着产生了脏读景象。

2. 不可反复读（Non-Repeatable Read）

如果事务 B 批改了未提交的事务 A 读取到的数据，就意味着产生了不可反复读景象。

3. 幻读（Phantom Read）

事务 A 先依据某个范畴条件查问出了一些记录，而事务 B 写入了一些合乎该条件的新记录，当事务 A 再次以雷同的条件查问时，查问到了新的记录，就意味着产生了幻读景象。

二、SQL 规范中的四种隔离级别

1. READ UNCOMMITTED（未提交读）

在 READ UNCOMMITTED 级别，事务中的批改，即便没有提交，对其余事务也都是可见的。
也就是说该隔离级别会呈现 脏读 问题。

2. READ COMMITTED（已提交读）

READ COMMITTED 解决了 脏读 问题，它满足事务隔离性的简略定义：一个事务从开始直到提交之前，所做的任何批改对其余事务都是不可见的。

3. REPEATABLE READ（可反复读）

REPEATABLE READ 解决了 脏读 和不可反复读 的问题。该级别保障了在同一个事务中屡次读取同样记录的后果是统一的。然而实践上，可反复读隔离级别还是无奈解决幻读（Phantom Read）问题。
InnoDB 存储引擎通过 MVCC（多版本并发管制）和 Next-Key (临键锁) 很大水平上防止了幻读问题。
可反复读是 MySQL 的默认事务隔离级别。

4. SERIALIZABLE（串行化）

SERIALIZABLE 通过强制事务串行执行，防止了 脏读 、 不可反复读 和幻读 的问题。SERIALIZABLE 会在读取的每一行数据上都加锁，所以可能导致大量的超时和锁争用问题。

三、四种隔离级别比照

四、四种隔离级别事务并行示例

查看事务的隔离级别

SHOW VARIABLES LIKE 'transaction_isolation';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+

设置事务的隔离级别

SET [GLOBAL|SESSION] TRANSACTION ISOLATION LEVEL <level>;

level 有 4 个可选值：

• READ UNCOMMITTED
• READ COMMITTED
• REPEATABLE READ
• SERIALIZABLE

批改全局隔离级别须要退出会话从新连贯 MySQL 失效。

初始数据

CREATE TABLE `user`  (`id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(10) NOT NULL COMMENT '姓名',
  `gender` char(1) NOT NULL COMMENT '性别',
  `age` tinyint(3) UNSIGNED NOT NULL COMMENT '年龄',
  `phone` char(11) NOT NULL COMMENT '电话',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `idx_username`(`name`) USING BTREE,
  UNIQUE INDEX `unq_phone`(`phone`) USING BTREE
) ENGINE=InnoDB CHARACTER SET=utf8mb4;

INSERT INTO `user` VALUES (10, 'M 小明', '男', 16, '11111111111');
INSERT INTO `user` VALUES (20, 'H 小红', '女', 15, '22222222222');
INSERT INTO `user` VALUES (30, 'L 小丽', '女', 18, '33333333333');
INSERT INTO `user` VALUES (40, 'M 小梅', '女', 21, '44444444444');
INSERT INTO `user` VALUES (50, 'L 小亮', '男', 20, '55555555555');

1. READ UNCOMMITTED（未提交读）

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SHOW VARIABLES LIKE 'transaction_isolation';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | READ-UNCOMMITTED |
+-----------------------+-----------------+

• 脏读

2. READ COMMITTED（已提交读）

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SHOW VARIABLES LIKE 'transaction_isolation';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | READ-COMMITTED |
+-----------------------+-----------------+

• 不可反复读

3. REPEATABLE READ（可反复读）

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SHOW VARIABLES LIKE 'transaction_isolation';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+

• 可反复读

• 幻读

因为 MySQL 的 InnoDB 存储引擎通过 MVCC（多版本并发管制）和 Next-Key (临键锁) 很大水平上防止了幻读问题，所以无奈演示大部分幻读景象，然而InnoDB 存储引擎并不能齐全禁止幻读。

4. SERIALIZABLE（串行化）

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SHOW VARIABLES LIKE 'transaction_isolation';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | SERIALIZABLE |
+-----------------------+-----------------+

SERIALIZABLE 通过强制事务串行执行，防止了 脏读 、 不可反复读 和幻读 的问题。

• 当事务 A 读取一条记录时，这条记录会被加上读锁（共享锁），其余事务能够查问这一条记录，然而无奈批改

• 当事务 A 批改一条记录时，这条记录会被加上写锁（排它锁），其余事务都无奈查问和批改这一条记录

• 当事务 A 读取范畴记录时，该范畴都会被加上读锁（共享锁），其余事务无奈在该范畴内增加、批改记录，也无奈将范畴外的记录批改为合乎范畴条件的记录。