MySQL（十八）MySQL事务(二)：事务的隔离级别
1.MySQL（一）Linux下MySQL的安装2.MySQL（二）字符集、比较规则与规范3.MySQL（三）数据目录4.MySQL（四）用户与权限管理5.MySQL（五）配置文件、系统变量与MySQL架构6.MySQL（六）存储引擎7.MySQL（七）索引8.MySQL（八）哈希索引、AVL树、B树与B+树的比较9.MySQL（九）InnoDB行格式10.MySQL（九）InnoDB数据结构11.MySQL（十）表空间结构：区、段与碎片区12.MySQL（十一）索引的分类和创建原则13.MySQL（十二）索引使用的情况分析14.MySQL（十三）MySQL性能分析工具：慢查询日志与PROFILE查询成本15.MySQL（十四）分析查询语句Explain 七千字总结16.MySQL（十五）分析优化器的查询计划：Trace17.MySQL（十六）索引优化：索引失效的情况分析18.MySQL（十七）查询优化（一）19.MySQL（十七）查询优化（二）与数据库主键设计20.MySQL（十八）MySQL事务(一)：事务的概述与使用
21.MySQL（十八）MySQL事务(二)：事务的隔离级别
22.MySQL（十九）MySQL事务日志（一）RedoLog23.MySQL（十九）MySQL事务日志（二）UndoLog24.MySQL（二十）锁（一）锁的概述与S、X锁25.MySQL（二十）锁（二）表锁、行锁与页级锁26.MySQL（二十）锁（三）乐观锁与悲观锁、显示隐式锁和其他锁27.MySQL（二十一）MVCC多版本并发控制28.MySQL（二十二）其他数据库日志（一）通用查询日志和错误日志29.MySQL（二十二）其他数据库日志（二）bin log二进制日志与中继日志30.【完结撒花】MySQL（二十三）主从复制MySQL（十八）MySQL事务(二)：事务的隔离级别
MySQL是一个客户端/服务器架构的软件，可以有若干个客户端与之连接，连接上之后都可以被称作是一个会话，每个客户端都可以在自己的会话中向服务器发出请求语句，一个请求语句可能是事务的一部分，因此对于服务器来说需要同时处理多个事务。由于事务具有隔离性，所以理论上事务对某个数据进行访问的时候，其他事务应该排队，等事务提交之后，其他事务才能继续执行，但是这样对性能影响太大。也就是需要在隔离性和并发性之间做取舍。
1 数据准备
CREATE TABLE `student1` ( `stu_no` INT, `name` VARCHAR(20), `class` VARCHAR(20), PRIMARY KEY(stu_no) )ENGINE=INNODB CHARSET=utf8; INSERT INTO student1 VALUES(1, 'A', '1班')
2 数据并发问题
如果事务不能保证串行执行的情况下可能会出现的问题：
2.1 脏写(dirty write)，也称作丢失更新
如果一个事务修改了另一个未提交事务 修改过的数据，就发生了脏写。
如上脏写示意图，AB均开启事务，B首先将数据name='A'改为了李四，然后事务A又改成张三并提交刷盘，然后事务B进行回滚操作到自己事务的初始状态name='A'，事务A的更新就丢失了，出现了脏写。
2.2 脏读
一个事务读取了另一个事务更新完但还没有提交的数据被称作脏读。若后续事务回滚，则第一个事务读取到的内容就是临时且无效的。
2.3 不可重复读
一个事务读取了一个字段的同时，另一个事务对其进行了更新，当第一个事务再读的时候，发现字段值不同了。
2.4 幻读
一个事务在一张表中读取了一个字段之后，另一个事务插入了一些新的行，如果前一个事务再次读取同一个表，就会发现多了几行，这就被称作幻读。新插入的几行被称作幻影记录。
-
如果删除了几行，导致读的记录变少了的情况算幻读吗？
不算，因为幻读强调是
插入，即读到了之前没有读过的记录，删除的这几条严格来说是不可重复读。
3 SQL的四种隔离级别
上面介绍了并发执行事务可能出现的问题，这些问题并不需要全部解决，否则并发性能太差，因此可以按照业务场景解决问题较轻的，按严重程度排序：
脏写 > 脏写 > 不可重复读 > 幻读
可以牺牲一部分隔离性来换取事务的并发性，SQL标准中设置了四个隔离级别，隔离级别越低，事务的并发程度就越高：
读未提交（READ UNCOMMITTED）：在该隔离级别下，所有事务都能看到其他未提交事务的执行结果。不能避免脏读、不可重复读和幻读问题。读已提交（READ COMMITTED）：在该隔离级别下，所有事务都只能看到其他已经提交事务的执行结果，可以避免脏读，但是不能解决不可重复读和幻读。这也是ORACLE数据库默认的隔离级别。可重复读(REPEATABLE READ)：在该隔离级别下，一个事务读取数据后，另一个事务对数据进行了修改提交，前一个事务读取的还是之前的结果，可以避免不可重复读和脏读，但是不能解决幻读。可串行化(SERIALIZABLE)：确保事务串行执行，没有任何其他事务对表进行修改操作，所有的并发问题都可以解决，但效率低下。
没有涉及
脏写问题，是因为脏写问题太严重了：事务提交修改还能出错，因此上面的四种隔离模式都能避免脏写问题
4 MYSQL支持的隔离级别
上面的四种隔离模式是SQL标准规定的，对于具体的数据库支持是不同的，比如ORACLE只支持读已提交和可串行化，并且默认的隔离级别是读已提交，而MySQL支持四种隔离级别，而且默认的隔离级别是可重复度。
4.1 MYSQL的隔离级别的设置与查看
MySQL的隔离级别的查看按照版本方式如下：
# 5.7之前 mysql> show variables like 'tx_isolation'; # 5.7之后 mysql> show variables like 'transaction_isolation'; +-----------------------+-----------------+ | Variable_name | Value | +-----------------------+-----------------+ | transaction_isolation | REPEATABLE-READ | +-----------------------+-----------------+ 1 row in set (0.00 sec) # 版本混用 mysql> select @@transaction_isolation; +-------------------------+ | @@transaction_isolation | +-------------------------+ | REPEATABLE-READ | +-------------------------+ 1 row in set (0.00 sec)
可以按照下面的语句修改隔离级别：
SET [GLOBAL|SESSION] TRANSACTION ISOLATION LEVEL > READ COMMITTED > READ UNCOMMITTED > REPEATABLE READ > SERIALIZABLE
或者：
SET [GLOBAL|SESSION] TRANSACTION_ISOLATION > READ-COMMITTED > READ-UNCOMMITTED > REPEATABLE-READ > SERIALIZABLE
关于设置GLOBAL或SESSION的影响：
-
使用
GLOBAL：SET GLOBAL TRANSACTION ISOLATION READ-COMMITTED; - 当前会话所有后续事务有效（当前事务无效）
- 对已经开启的事务执行期间运行，不会影响当前正在执行的事务
-
使用
SESSIONSET GLOBAL TRANSACTION ISOLATION READ-COMMITTED; - 当前会话生效
- 其他会话无效
4.2 不同隔离级别举例
数据准备
CREATE TABLE account ( id INT PRIMARY KEY AUTO_INCREMENT, `name` VARCHAR(15), balance DECIMAL(10, 2) ); INSERT INTO account(name, balance) VALUES('张三', 100), ('李四', 0);
不可重复读
首先对两个会话都设置隔离模式为读未提交
mysql> set session transaction_isolation = '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)
会话一执行事务，但是没有提交：
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> update account set balance = balance + 100 where id = 1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0
会话二能够获取到未提交数据
mysql> select * from account; +----+------+---------+ | id | name | balance | +----+------+---------+ | 1 | ?? | 200.00 | | 2 | ?? | 0.00 | +----+------+---------+ 2 rows in set (0.00 sec)
此时会话一回滚，那么会话二获取的数据就是临时且无效的
mysql> rollback; Query OK, 0 rows affected (0.00 sec)
再举一个列子，如果会话一给会话二转100，并没有提交事务：
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> update account set balance = balance - 100 where id = 1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> update account set balance = balance + 100 where id = 2; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from account; +----+------+---------+ | id | name | balance | +----+------+---------+ | 1 | ?? | 0.00 | | 2 | ?? | 100.00 | +----+------+---------+ 2 rows in set (0.00 sec)
此时事务二就读到了脏数据
mysql> select * from account; +----+------+---------+ | id | name | balance | +----+------+---------+ | 1 | ?? | 0.00 | | 2 | ?? | 100.00 | +----+------+---------+ 2 rows in set (0.00 sec)
而如果这时候事务二想把这些钱再转给事务一，就会发现卡住了，没有任何的执行结果
mysql> update account set balance = balance - 100 where id = 2;
这是因为四种隔离级别都解决了脏写的问题，即事务二想修改未提交的事务是不能的，因为mysql对表进行了加锁，不允许其他事务对表进行修改
读已提交
数据准备
mysql> truncate table account; Query OK, 0 rows affected (0.02 sec) mysql> insert into account values(1, 'zhangsan', 100), (2, 'lisi', 0); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from account; +----+----------+---------+ | id | name | balance | +----+----------+---------+ | 1 | zhangsan | 100.00 | | 2 | lisi | 0.00 | +----+----------+---------+ 2 rows in set (0.00 sec)
1、2均设置隔离级别为读已提交
set session transaction_isolation = 'read-committed';
1账户金额减五十，但是事务不提交
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> update account set balance = balance - 50 where id = 1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0
2读取金额发现1的金额为100，没有读到脏数据，说明解决了脏读问题
mysql> select * from account; +----+----------+---------+ | id | name | balance | +----+----------+---------+ | 1 | zhangsan | 100.00 | | 2 | lisi | 0.00 | +----+----------+---------+ 2 rows in set (0.00 sec)
1提交事务
commit;
2再读仍然是50，说明隔离级别为读已提交时不能够解决不可重复读问题。
mysql> select * from account; +----+----------+---------+ | id | name | balance | +----+----------+---------+ | 1 | zhangsan | 100.00 | | 2 | lisi | 0.00 | +----+----------+---------+ 2 rows in set (0.00 sec) mysql> select * from account; +----+----------+---------+ | id | name | balance | +----+----------+---------+ | 1 | zhangsan | 50.00 | | 2 | lisi | 0.00 | +----+----------+---------+ 2 rows in set (0.00 sec)
可重复读
设置1和2的隔离级别为可重复读。
mysql> set transaction_isolation = '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)
2查看数据：
mysql> select * from account; +----+----------+---------+ | id | name | balance | +----+----------+---------+ | 1 | zhangsan | 100.00 | | 2 | lisi | 0.00 | +----+----------+---------+ 2 rows in set (0.00 sec)
1将钱加50，并且不提交事务：
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> update account set balance = balance + 50 where id = 1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from account; +----+----------+---------+ | id | name | balance | +----+----------+---------+ | 1 | zhangsan | 100.00 | | 2 | lisi | 0.00 | +----+----------+---------+ 2 rows in set (0.00 sec)
2查看数据，没有读到脏数据，说明可重复读解决了不可重复读问题
mysql> select * from account; +----+----------+---------+ | id | name | balance | +----+----------+---------+ | 1 | zhangsan | 50.00 | | 2 | lisi | 0.00 | +----+----------+---------+ 2 rows in set (0.00 sec)
1提交事务
mysql> commit; Query OK, 0 rows affected (0.00 sec)
2再次查看，发现金额为100
可重复解决了一部分幻读问题
1和2都是INNODB默认的隔离级别可重复读：
mysql> select @@transaction_isolation; +-------------------------+ | @@transaction_isolation | +-------------------------+ | REPEATABLE-READ | +-------------------------+ 1 row in set (0.00 sec)
2查看表中的数据：
mysql> select * from account; +----+----------+---------+ | id | name | balance | +----+----------+---------+ | 1 | zhangsan | 50.00 | | 2 | lisi | 0.00 | +----+----------+---------+ 2 rows in set (0.00 sec)
此时如果1添加记录再提交，并且没有提交事务
mysql> select * from account; +----+----------+---------+ | id | name | balance | +----+----------+---------+ | 1 | zhangsan | 50.00 | | 2 | lisi | 0.00 | +----+----------+---------+ 2 rows in set (0.00 sec) mysql> insert into account(3, 'wangwu', 0); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '3, 'wangwu', 0)' at line 1 mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> insert into account values(3, 'wangwu', 0); Query OK, 1 row affected (0.00 sec)
2再次查看表中数据：
mysql> select * from account; +----+----------+---------+ | id | name | balance | +----+----------+---------+ | 1 | zhangsan | 50.00 | | 2 | lisi | 0.00 | +----+----------+---------+ 2 rows in set (0.00 sec) mysql> select * from account; +----+----------+---------+ | id | name | balance | +----+----------+---------+ | 1 | zhangsan | 50.00 | | 2 | lisi | 0.00 | +----+----------+---------+ 2 rows in set (0.00 sec)
这是不是说明可重复读解决了幻读问题呢？并不是，如果这时候2向表中添加一条数据，由于禁止脏写，所以如果1不提交事务的话，2是没有办法写数据的，所以1执行commit命令的同时，可以看到不允许插入主键为2的值，表名出现了幻读问题：
mysql> insert into account values(3, 'liliu', 1); ERROR 1062 (23000): Duplicate entry '3' for key 'account.PRIMARY'
2再次查看，这时候发现已经是三条数据了
mysql> select * from account; +----+----------+---------+ | id | name | balance | +----+----------+---------+ | 1 | zhangsan | 50.00 | | 2 | lisi | 0.00 | | 3 | wangwu | 0.00 | +----+----------+---------+ 3 rows in set (0.00 sec)
可序列化
在SERIALIZABLE隔离级别下，事务的执行会隐式地添加行(X)锁/gap(X)锁（也成间隙锁），事务一提交之后，事务二才能继续执行。
未完待续
【推荐】国内首个AI IDE，深度理解中文开发场景，立即下载体验Trae
【推荐】编程新体验，更懂你的AI，立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包，你的智能百科全书，全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell：AI 加持，快人一步
2022-05-09 【MyBatis】分页插件