浅谈 MySQL 的事务与 ACID-低调大师

浅谈 MySQL 的事务与 ACID

2020-01-08 573

最近把个人博客搭建好了，链接在这里：tobe的呓语，文章会先在博客和公众号更新~ 希望大家多多收藏啊

所谓事务（Transaction），就是通过确保成批的操作要么完全执行，要么完全不执行，来维护数据库的完整性。举一个烂大街的例子：A 向 B 转账 1000 元，对应的 SQL 语句为：（没有显式定义事务）

UPDATE deposit_table set deposit = deposit - 1000 WHERE name = 'A';
UPDATE deposit_table set deposit = deposit + 1000 WHERE name = 'B';

运行后的结果如下：

mysql> SELECT * FROM deposit_table;
+------+---------+
| name | deposit |
+------+---------+
| A    |    3000 |
| B    |    5000 |
+------+---------+

这样做可能遇到问题，比如执行完第一条语句之后，数据库崩溃了，最后的结果就可能会是这样（毕竟咱不会模拟这种故障）：

+------+---------+
| name | deposit |
+------+---------+
| A    |    2000 |
| B    |    5000 |
+------+---------+

A 的 1000 块钱平白无故消失了，这肯定不合适。事务就是为了解决类似的问题而出现的，如果使用事务来处理转账，对应的 SQL 就是：

START TRANSACTION;
UPDATE deposit_table set deposit = deposit - 1000 WHERE name = 'A';
UPDATE deposit_table set deposit = deposit + 1000 WHERE name = 'B';
COMMIT;

仅仅是在这原先的两条 SQL 语句前后加上了 START TRANSACTION 和 COMMIT ，就可以保证即使转账操作失败，A 的余额也不会减少。

仔细想一想发现这个例子不是特别合适，因为数据库的故障恢复技术（以后会谈到）会影响最终的结果，也不容易模拟这种故障，最后结果只能靠猜 : ) 但我也想不出其它更加合适的例子。。。如果你们有更好的例子欢迎留言讨论。

接下来就详细讨论事务的一些特性和（某些）实现细节。

ACID

A：Atomicity（原子性）
C：Consistency（一致性）
I：Isolation（隔离性）
D：Durability（持久性）

Atomicity（原子性）

先谈两个重要的概念：提交（commit）和回滚（rollback），当我们执行提交操作后，将对数据库进行永久性的修改，执行回滚操作，意味着数据库将撤销正在进行的所有没有提交的修改。注意这里的永久性并不意味这事务一完成就把数据刷到磁盘上，即使没有刷入磁盘，MySQL 也有日志机制来保证修改不会丢失。

事务是支持提交和回滚的工作单元，原子性，就是说事务对数据库进行多次更改时，要么在提交事务的时候所有更改都成功，要么在回滚事务的时候撤销所有更改。这是官方文档的表述，但有的人似乎错误理解了 commit 语句，实际上，哪怕事务里某一语句出现了错误，一旦你执行 commit，前面正常的修改仍然会被提交，MySQL 不会自动判断事务中的 SQL 执行成功与否。

我们接下来用例子来看看 commit 和 rollback：

mysql> SELECT * FROM deposit_table;
+------+---------+
| name | deposit |
+------+---------+
| A    |    2000 |
| B    |    6000 |
+------+---------+
2 rows in set (0.04 sec)

mysql> 
START TRANSACTION;
INSERT INTO deposit_table VALUES('C', 7000);
INSERT INTO deposit_table VALUES('D', 8000);
#再次插入 D，由于主键的唯一性，该语句会执行失败
INSERT INTO deposit_table VALUES('D', 9000);
COMMIT; #提交事务

Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

1062 - Duplicate entry 'D' for key 'PRIMARY'
Query OK, 0 rows affected (0.07 sec)

mysql> SELECT * FROM deposit_table;
+------+---------+
| name | deposit |
+------+---------+
| A    |    2000 |
| B    |    6000 |
| C    |    7000 |
| D    |    8000 |
+------+---------+
4 rows in set (0.04 sec)

我们可以看到，在执行 INSERT INTO deposit_table VALUES('D', 9000) 的时候，由于前一条语句已经插入了 D，所以这一句 SQL 语句执行失败，报出 1062 - Duplicate entry 'D' for key 'PRIMARY' 错误，但执行 COMMIT 后，前面的修改仍然得到了提交，这显然是不符合我们的预期的。

注意：如果你是使用 Navicat 的查询界面，将执行不到 COMMIT 语句，只能执行到报错的地方，建议使用命令行来执行。

所以在实际情况中，我们需要根据 MySQL 的错误返回值来确定，是使用 ROLLBACK 还是 COMMIT 。就像这样：

# 创建一个存储过程
CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_test`()
BEGIN
	# 创建一个标志符，出现错误就将其置为 1
	DECLARE err_flg INTEGER;
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET err_flg = 1;

    START TRANSACTION;
        INSERT INTO deposit_table VALUES('C', 7000);
				INSERT INTO deposit_table VALUES('D', 8000);
				INSERT INTO deposit_table VALUES('D', 9000);
        
        # 发生错误，回滚事务
        IF err_flg = 1 THEN
			SELECT 'SQL Err Invoked'; # 错误提示信息
            ROLLBACK;
			SELECT * FROM deposit_table;
		# 没有发生错误，直接提交
        ELSE
			SELECT 'TRANSACTION Success';
            COMMIT;
			SELECT * FROM deposit_table;
        END IF;
	
END

接下来我们调用该存储过程：

mysql> call insert_test();
+-----------------+
| SQL Err Invoked |
+-----------------+
| SQL Err Invoked |
+-----------------+
1 row in set (0.04 sec)

+------+---------+
| name | deposit |
+------+---------+
| A    |    2000 |
| B    |    6000 |
+------+---------+
2 rows in set (0.09 sec)

Query OK, 0 rows affected (0.00 sec)

结果里打印出了错误信息 SQL Err Invoked 表的内容也没有更改，表明我们的 ROLLBACK 成功回滚了事务，达到我们的预期。如果你是使用其他语言调用 MySQL 的接口，也只需要获取错误标志，相应的执行 ROLLBACK 或者 COMMIT 。

Consistency（一致性）

官网给出的解释如下：

The database remains in a consistent state at all times — after each commit or rollback, and while transactions are in progress. If related data is being updated across multiple tables, queries see either all old values or all new values, not a mix of old and new values.

翻译过来就是：在每次提交或回滚之后以及正在进行的事务处理期间，数据库始终保持一致状态，如果跨多个表更新了相关数据，则查询将看到所有旧值或所有新值，而不是新旧值的混合。

举个例子：

# 表 a，b 的定义略过
START TRANSACTION;
UPDATE a SET name = 'a_new' WHERE name = 'a_old';
UPDATE b SET name = 'b_new' WHERE name = 'b_old';
COMMIT;

这个例子里的一致性，就是说，如果此时有查询 SELECT a.name, b.name FROM a, b; 得到的结果要么是 a_old b_old （表明事务已回滚或者正在执行），要么是 a_new b_new （表明事务已经成功提交），而不会出现 a_old b_new 以及 a_new b_old 这两种情况。

有的博客将一致性解释为“数据符合现实世界中的约束，比如唯一性约束等等。” 我个人还是倾向于官方文档的解释，这点见仁见智吧，纠结这些概念意义不大。

Isolation（隔离性）

事务的隔离性是说，事务之间不能互相干扰，也不能看到彼此的未提交数据。这种隔离是通过锁机制实现的。我们在操作系统里也了解过，使用锁，往往就意味着并发性能的下降，因为可能会发生阻塞，甚至死锁现象。

当然，用户在确定事务确实不会相互干扰时，可以调整隔离级别，牺牲部分隔离性以提高性能和并发性，至于使用哪种隔离级别（isolation level）这就需要你自己做 trade off。

因为隔离性涉及的的内容很多，我把它放到下一篇文章详细解释。

Durability（持久性）

事务的持久性是说，一旦提交操作成功，该事务所做的更改就不会因为一些意外而丢失，比如电源断电，系统崩溃等潜在威胁。MySQL 提供了很多机制，比如日志技术，doublewrite buffer等等。

MySQL 的日志恢复技术我将单独写一篇文章，这里说说 doublewrite buffer 技术。

虽然这个技术名字叫做 buffer，但实际上该缓冲区并不位于内存，而是位于磁盘。这可能听起来很诡异——既然是把数据放入磁盘，为啥不直接写入到 data file，反而多此一举？

这是因为 InnoDB 的 Page Size 一般是 16kb，其数据校验也是针对页来计算的，在将数据刷入磁盘的过程中，如果发生断电等故障，该页可能只写入了一部分（partial page write）。这种情况是 redo 日志无法解决的，因为 redo 日志中记录的是对页的物理操作，如果页本身发生了损坏，再对其进行 redo 是没有意义的。所以我们需要一个副本，在发生这种情况时还原该页。

而且缓冲区是顺序写的，开销相对随机读写要小很多，所以 doublewrite 后，性能也不是降为原来的 50%。

事务中的常用语句

START TRANSACTION / BEGIN 显式开启一个事务
COMMIT 提交事务，永久性修改数据库
SAVEPOINT 在事务里创建保存点
RELEASE SAVAPOINT 移除某保存点
ROLLBACK 回滚事务，撤回所有未提交的更改，事务会终止
ROLLBACK TO [SAVEPOINT] 回滚到给定保存点，但事务不终止，另外，该保存点后的行锁不会被释放，详见SAVEPOINT, ROLLBACK TO SAVEPOINT, and RELEASE SAVEPOINT Statements：

InnoDB does not release the row locks that were stored in memory after the savepoint. (For a new inserted row, the lock information is carried by the transaction ID stored in the row; the lock is not separately stored in memory. In this case, the row lock is released in the undo.)
SET TRANSACTION 设置事务隔离级别
SET autocommit 0/1 是否自动提交（默认自动提交）

强调一下 autocommit 参数，默认情况下，如果不显式使用 START TRANSACTION / BEGIN ，MySQL 会把每一句 SQL 当做独立的事务，举个例子：

原来的表结构：

mysql> SELECT * FROM deposit_table;
+------+---------+
| name | deposit |
+------+---------+
| A    |    2000 |
| B    |    6000 |
+------+---------+
2 rows in set (0.04 sec)

新的存储过程（仅仅删除了 START TRANSACTION ）：

CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_test`()
BEGIN
	#Routine body goes here...
	DECLARE err_flg INTEGER;
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET err_flg = 1;

    # START TRANSACTION;
    INSERT INTO deposit_table VALUES('C', 7000);
	INSERT INTO deposit_table VALUES('D', 8000);
	INSERT INTO deposit_table VALUES('D', 9000);
        
        IF err_flg = 1 THEN
			SELECT 'SQL Err Invoked';
            ROLLBACK;
			SELECT * FROM deposit_table;
        ELSE
			SELECT 'TRANSACTION Success';
            COMMIT;
			SELECT * FROM deposit_table;
        END IF;
	
END

调用的结果：

mysql> call insert_test();
+-----------------+
| SQL Err Invoked |
+-----------------+
| SQL Err Invoked |
+-----------------+
1 row in set (0.24 sec)

+------+---------+
| name | deposit |
+------+---------+
| A    |    2000 |
| B    |    6000 |
| C    |    7000 |
| D    |    8000 |
+------+---------+
4 rows in set (0.28 sec)

Query OK, 0 rows affected (0.21 sec)

在这里，我们看到尽管确实执行了 ROLLBACK，但 C 和 D 仍然插入到了 deposit_table 。这是因为没有显式标明事务，MySQL 会进行隐式事务，自动提交每次的修改，所以就无法进行回滚了。

事务的基本概念就介绍这么多，以后我将会讲到事务的隔离机制，范式设计等内容，敬请期待！

希望你在看完我的文章之后有所收获，期待你的赞和转发！

如果本文对你有帮助，欢迎关注我的公众号 tobe的呓语 ，带你深入计算机的世界～公众号后台回复关键词【计算机】有惊喜哦~

微信关注我们

原文链接：https://my.oschina.net/u/4198159/blog/3154836

转载内容版权归作者及来源网站所有！

低调大师中文资讯倾力打造互联网数据资讯、行业资源、电子商务、移动互联网、网络营销平台。持续更新报道IT业界、互联网、市场资讯、驱动更新,是最及时权威的产业资讯及硬件资讯报道平台。

高效程序员的七个习惯

软件工程师花费大量时间通过练习leet code问题和完善简历来获得更好的面试通过可能。一旦他们最终被谷歌、亚马逊或其他公司录用，他们可能会发现：过去用来得到这份工作的技能与他们日常工作中需要的技能并不匹配。我们的团队受到 TechLead 创建的高效程序员七项技能的启发。我们想提供我们自己对这个话题的看法。以下是我们总结的高效程序员的七项技能。 1. 学习如何阅读别人的代码除了你，每个人写的代码都是垃圾？实际上，能够在别人的代码之上继续工作是一项有多重好处的伟大技能。不管以前工程师的代码是多么混乱或者考虑不周，您仍然需要能够扩展它。毕竟，这是你的工作。同时，这个“以前的工程师”也可能是一年前的你。这项技能在两个方面对你有益。第一，能够阅读他人的代码是一个了解什么是糟糕设计的好机会。当你浏览别人的代码时，你会知道什么是有效的，什么是无效的。更重要的是，您可以了解什么类型的代码对于其他工程师来说是容易扩展的，以及什么类型的代码难以扩展。你需要确保在阅读他人代码时尽可能多地找出问题所在。这样，其他的工程师就会知道你是一个多么优秀的工程师。确保您提出了关于可维护代码和良好注释的重要...

2020-01-08

600

为什么说 AIO 受”误解“，虽然这个”误解“被打上了双引号，但还是不得不承认它的发展状况并不好。AIO 是 Java 7 开始提供的新特性，而这个”新特性“到如今都成了陈年老酒还鲜有人去品味它。要知道 Java 7 可是在 2011年7月份发布的，市面上基于 AIO 实现的通信框架竟然寥寥无几，关于这项技术的介绍文章也普遍比较粗略。通过阅读那些介绍 AIO 的文章，似乎从学术层面大家就不怎么待见这项技术。作为 AIO 的学习者、受益者，我觉得有必要先对网上的一些 ”偏见“ 表达一下自己的观点。如果能有幸在认知上搭成共识，之后的学习交流会更加顺畅一点。通常偏见源于比较，AIO 与 BIO、NIO 的对比明细如表所示。 BIO NIO AIO 客户端 : I/O 线程数 1 : 1 N : 1 N : 0 I/O类型同步阻塞同步非阻塞异步非阻塞 API使用难度简单复杂一般调试难度简单复杂一般可靠性差高高吞吐量低高高适用场景适用于连接数量不多，并发量不高的场景。充分发挥易编程的优势。适用于对连接数量以及稳定性、实时性有较高要求的场景，采用 NI...

2020-01-08

565

资源下载

更多资源

优质分享App

近一个月的开发和优化，本站点的第一个app全新上线。该app采用极致压缩，本体才4.36MB。系统里面做了大量数据访问、缓存优化。方便用户在手机上查看文章。后续会推出HarmonyOS的适配版本。

Mario

马里奥是站在游戏界顶峰的超人气多面角色。马里奥靠吃蘑菇成长，特征是大鼻子、头戴帽子、身穿背带裤，还留着胡子。与他的双胞胎兄弟路易基一起，长年担任任天堂的招牌角色。

Rocky Linux

Rocky Linux（中文名：洛基）是由Gregory Kurtzer于2020年12月发起的企业级Linux发行版，作为CentOS稳定版停止维护后与RHEL（Red Hat Enterprise Linux）完全兼容的开源替代方案，由社区拥有并管理，支持x86_64、aarch64等架构。其通过重新编译RHEL源代码提供长期稳定性，采用模块化包装和SELinux安全架构，默认包含GNOME桌面环境及XFS文件系统，支持十年生命周期更新。

Sublime Text

Sublime Text具有漂亮的用户界面和强大的功能，例如代码缩略图，Python的插件，代码段等。还可自定义键绑定，菜单和工具栏。Sublime Text 的主要功能包括：拼写检查，书签，完整的 Python API ， Goto 功能，即时项目切换，多选择，多窗口等等。Sublime Text 是一个跨平台的编辑器，同时支持Windows、Linux、Mac OS X等操作系统。