mysql acid描述_浅谈 MySQL 的事务和 ACID

ゝ一纸荒年。 2022-10-27 02:18 157阅读 0赞

最近把个人博客搭建好了，链接在这里：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)

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 回滚事务，撤回所有未提交的更改，事务会终止

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)