MySQL的事务学习

最新推荐文章于 2024-08-05 23:41:24 发布

梁道

最新推荐文章于 2024-08-05 23:41:24 发布

阅读量102

点赞数

分类专栏：数据库学习

本文链接：https://blog.csdn.net/zloong_y/article/details/106714543

版权

数据库学习专栏收录该内容

1 篇文章 0 订阅

订阅专栏

MySQL的事务

本文为《MySQL技术内幕: InnoDB存储引擎》的阅读笔记。

前言

事务就是一组原子性的SQL查询，或者说一个独立的工作单元。
事务内的语句，要么全部执行成功，要么全部执行失败。

一个运行良好的事务处理系统，具备以下特征。

原子性 (atomicity)
一致性 (consistency)
隔离性 (isolation)
持久性 (durability)

其中，ISO 和 ANIS SQL 标准制定了四种事务隔离级别的标准，但是很少有数据库厂商遵循这些标准。比如 Oracle 数据库就不支持 READ_UNCOMMITTED 和 REPEATABLE_READ。

事务的实现

在这里插入图片描述
注：redo 和 undo 的作用都可以视为一种恢复操作，redo 恢复提交事务修改的页操作，而 undo 回滚行记录到某个特定的版本。redo 通常是物理日志，记录的是页的物理修改操作，undo 是逻辑日志，根据每行记录进行记录。

redo

重做日志有两部分组成：一是内存中的重做日志缓冲 (redo log buffer)，其是易失的；二是重做日志文件 (redo log file)，其是持久的。

InnoDB 是事务的存储引擎，当事务提交时，必须先将该事务的所有日志写入到重做日志文件进行持久化，待事务的提交操作完成才算完成。在 InnoDB 中，redo log 用来保证事务的持久性。

undo

undo log 用来帮助事务回滚以及 MVCC 的功能。redo 存放在重做日志文件中，与 redo 不同，undo 存放在数据库内部的一个特殊段（segment）中，这个段称为 undo 段。undo 段位于共享表空间内。

undo 是逻辑日志，因此只是将数据库逻辑地恢复到原来的样子。当 InnoDB 存储引擎回滚时，它实际做的是与先前相反的工作。对于每个 INSERT，InnoDB 存储引擎会完成一个 DELETE；对于每个 DELETE，InnoDB 存储引擎会执行一个 INSERT。

在SQL标准中定义了四种隔离级别。较低级别的隔离通常可以执行更高的并发，系统的开销也更低。

undo 的另一个作用是 MVCC，即在 InnoDB 存储引擎中的 MVCC 的实现是通过 undo 来完成。当用户读取一行记录时，若该记录已经被其他事务占用，当前事务可以通过 undo 读取之前的版本信息，以此实现非锁定读取。

undo log 会产生 redo log。

四种隔离级别分别是：

未提交读 (READ_UNCOMMITTED)
提交读 (READ_COMMITTED)
可重复读 (REPEATABLE_READ)
可串行化 (SERIALIZABLE)

未提交读

READ_UNCOMMITTED 级别，事务中的修改，即使没有提交，对其他事务也都是可见的。事务可以读取未提交的数据，也称为脏读。从性能上来说，READ_UNCOMMITTED 不会比其他级别好太多，但却缺乏其他级别的很多好处，一般很少使用。

脏读

将两个会话的隔离级别都修改为未提交读。（我试了将一个会话设置为未提交读，另一个隔离级别默认，但是没有效果）

## 初始数据
mysql> SELECT * FROM t_bank;
+----+-------+---------+
| id | name  | balance |
+----+-------+---------+
|  1 | Tom   | 1600.00 |
|  2 | Cindy | 100.00  |
|  3 | Jerry | 100.00  |
+----+-------+---------+
3 rows in set (0.02 sec)

会话执行实例：下表第5行，两个事务中，会话B可以读取到会话A的修改结果。

Time	会话A	会话B
1	SELECT @@tx_isolation;	SELECT @@tx_isolation;
2	SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;	SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
3	START TRANSACTION;	START TRANSACTION;
4	UPDATE t_bank SET balance = balance - 100 WHERE id = 1;
5		SELECT * FROM t_bank;
6	ROLLBACK;	ROLLBACK;

提交读

大多数数据库系统默认隔离级别都是 READ_COMMITTED (但 MySQL 不是)。一个事务从开始直到提交之前，所做的任何修改对其他事务都是不可见的。这个级别有时候也叫做不可重复读，因为两次执行同样的查询，可能会得到不一样的结果。

复现未提交读

会话执行实例：下表第7行，两个事务中，会话A读取不到会话B的修改结果。可是一旦会话B提交了事务，会话A就可以读取到会话B修改的结果了。

Time	会话A	会话B
1	SELECT @@tx_isolation;	SELECT @@tx_isolation;
2	SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;	SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
3	START TRANSACTION;	START TRANSACTION;
4	SELECT * FROM t_bank;
5		UPDATE t_bank SET balance = balance - 100 WHERE id = 1;
6		SELECT * FROM t_bank; // 1700
7	SELECT * FROM t_bank; // 1800
8		COMMIT;
9	SELECT * FROM t_bank; // 1700
8	ROLLBACK;	ROLLBACK;

可重复读

REPEATABLE_READ 解决了脏读的问题。该级别保证了在同一事务中多次读取同样记录的结果是一致的。但是没有解决幻读问题。所谓幻读，指的是当某个事务在读取某个范围内的记录时，会产生幻行。InnoDB 和 XtraDB 存储引擎通过多版本并发控制解决了幻读问题。

可重复读是 MySQL 的默认事务隔离级别。

验证 REPEATABLE_READ 解决了可重复读

会话执行实例：下表第6行，说明解决了脏读。下表第8行，说明解决了不可重复读。注意第9行，是在会话B提交的结果上修改的。

Time	会话A	会话B
1	SELECT @@tx_isolation;	SELECT @@tx_isolation;
2	SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;	SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
3	START TRANSACTION;	START TRANSACTION;
4	SELECT * FROM t_bank;	SELECT * FROM t_bank;
5		UPDATE t_bank SET balance = balance + 100 WHERE id = 1;
6	SELECT * FROM t_bank; // 1600	SELECT * FROM t_bank; // 1700
7		COMMIT;
8	SELECT * FROM t_bank; // 1600	SELECT * FROM t_bank; // 1700
9	UPDATE t_bank SET balance = balance + 100 WHERE id = 1;
10	SELECT * FROM t_bank; // 1800	SELECT * FROM t_bank; // 1700
11	COMMIT;
12		SELECT * FROM t_bank; // 1800

关于幻读的操作，在一次事务里面，多次查询之后，结果集的个数不一致的情况叫做幻读，而多出来或者少出来的那一行叫做幻行。

在快照读的情况下，MySQL 通过 MVCC 来避免幻读。

在当前读的情况下，MySQL 通过 next-key 来避免幻读。

mysql> select * from t_bank;
+----+-------+---------+
| id | name  | balance |
+----+-------+---------+
|  1 | Tom   | 2100.00 |
|  2 | Cindy | 100.00  |
|  3 | Jerry | 100.00  |
+----+-------+---------+
3 rows in set (0.03 sec)

Time	会话A	会话B
1	SELECT @@tx_isolation;	SELECT @@tx_isolation;
2	SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;	SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
3	START TRANSACTION;
4	# 使用当前读（加共享锁） SELECT * FROM t_bank LOCK IN SHARE MODE;
5		START TRANSACTION;
6		INSERT INTO t_bank VALUES(4, “new_man”, 500); // 执行此条命令阻塞
7	ROLLBACK;	强行停止