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(原子性)

先谈两个重要的概念：提交(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 。

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%。

二、事务中的常用语句

• 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  会进行隐式事务，自动提交每次的修改，所以就无法进行回滚了。

看完上述内容，你们对MySQL中事务和ACID的作用是什么有进一步的了解吗？如果还想了解更多知识或者相关内容，请关注亿速云行业资讯频道，感谢大家的支持。