黄东旭关于基础软件产品价值的思考
497
2023-05-16
浅谈MySQL的事务和ACID
浅谈MySQL的事务和ACID
所谓事务(Transaction)，就是通过确保成批的操作要么完全执行，要么完全不执行，来维护数据库的完整性。举一个烂大街的例子：A 向 B 转账 1000 元，对应的 SQL 语句为：(没有显式定义事务)
UPDATE deposit_table set depositdeposit = deposit - 1000 WHERE name = 'A'; UPDATE deposit_table set depositdeposit = deposit + 1000 WHERE name = 'B';
运行后的结果如下：
mysql> SELECT * FROM deposit_table; +------+---------+ | name | deposit | +------+---------+ | A | 3000 | | B | 5000 | +------+---------+
这样做可能遇到问题，比如执行完第一条语句之后，数据库崩溃了，最后的结果就可能会是这样(有待商榷，取决于下一条 SQL 有没有被写入日志)：
+------+---------+ | name | deposit | +------+---------+ | A | 2000 | | B | 5000 | +------+---------+
A 的 1000 块钱平白无故消失了，这肯定不合适。事务就是为了解决类似的问题而出现的，如果使用事务来处理转账，对应的 SQL 就是：
START TRANSACTION; UPDATE deposit_table set depositdeposit = deposit - 1000 WHERE name = 'A'; UPDATE deposit_table set depositdeposit = deposit + 1000 WHERE name = 'B'; COMMIT;
仅仅是在这原先的两条 SQL 语句前后加上了 START TRANSACTION 和 COMMIT ，就可以保证即使转账操作失败，A 的余额也不会减少。
仔细想一想发现这个例子不是特别合适，因为数据库的故障恢复技术(以后会谈到)会影响最终的结果，也不容易模拟这种故障，最后结果只能靠猜 : ) 但我也想不出其它更加合适的例子。
接下来就详细讨论事务的一些特性和(某些)实现细节。
ACID
A：Atomicity(原子性)C：Consistency(一致性)I：Isolation(隔离性)D：Durability(持久性)
1. Atomicity(原子性)
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 。
2. Consistency(一致性)
官网给出的解释如下：
翻译过来就是：在每次提交或回滚之后以及正在进行的事务处理期间，数据库始终保持一致状态，如果跨多个表更新了相关数据，则查询将看到所有旧值或所有新值，而不是新旧值的混合。
举个例子：
# 表 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 这两种情况。
有的博客将一致性解释为“数据符合现实世界中的约束，比如唯一性约束等等。” 我个人还是倾向于官方文档的解释，这点见仁见智吧，纠结这些概念意义不大。
3. Isolation(隔离性)
事务的隔离性是说，事务之间不能互相干扰，也不能看到彼此的未提交数据。这种隔离是通过锁机制实现的。我们在操作系统里也了解过，使用锁，往往就意味着并发性能的下降，因为可能会发生阻塞，甚至死锁现象。
当然，用户在确定事务确实不会相互干扰时，可以调整隔离级别，牺牲部分隔离性以提高性能和并发性，至于使用哪种隔离级别(isolation level)这就需要你自己做 trade off。
因为隔离性涉及的的内容很多，我把它放到下一篇文章详细解释。
4. Durability(持久性)
事务的持久性是说，一旦提交操作成功，该事务所做的更改就不会因为一些意外而丢失，比如电源断电，系统崩溃等潜在威胁。MySQL 提供了很多机制，比如日志技术，doublewrite buffer等等。
MySQL 的日志恢复技术我将单独写一篇文章，这里说说 doublewrite buffer 技术。
虽然这个技术名字叫做 buffer，但实际上该缓冲区并不位于内存，而是位于磁盘。这可能听起来很诡异——既然是把数据放入磁盘，为啥不直接写入到 data file，反而多此一举?
这是因为 InnoDB 的 Page Size 一般是 16kb，其数据校验也是针对页来计算的，在将数据刷入磁盘的过程中，如果发生断电等故障，该页可能只写入了一部分(partial page write)。这种情况是 redo 日志无法解决的，因为 redo 日志中记录的是对页的物理操作，如果页本身发生了损坏，再对其进行 redo 是没有意义的。所以我们需要一个副本，在发生这种情况时还原该页。
而且缓冲区是顺序写的，开销相对随机读写要小很多，所以 doublewrite 后，性能也不是降为原来的 50%。
二、事务中的常用语句
原来的表结构：
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 会进行隐式事务，自动提交每次的修改，所以就无法进行回滚了。
版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。
相关文章
