MySQL 事务

多表关联的情况下，一条新记录的生成往往涉及多张表的操作。

一个典型的场景，银行转帐。要完成 A 转帐到 B，

• 从 A 帐户减去相应金额
• 给 B 帐户加上相应金额。

这两步要么一起成功，要么都失败，否则就会造成数据不一致。比如 A 的钱少了，但 B 的钱没增加，或者 A 的扣款失败，B 的钱也增加了。

所以需要一种机制来保证这一操作过程中每一步的正确性，当其中任意操作失败时应该将已经进行过的操作回滚，保证整体都失败。

此时这些被绑定的一连串操作便形成了 事务。

下面创建一张空表，为后面示例作准备。

mysql> CREATE TABLE test(idx int);
Query OK, 0 rows affected (0.02 sec)

事务的语法

• MySQL 中，通过 START TRANSACTION 语句来开始一个事务，也可以使用别名 BEGIN 和 BEGIN WORK 语句。
• COMMIT 语句提交修改。
• 通过 ROLLBACK 语句回滚。

其中 COMMIT 或 ROLLBACK 均可用来结束一个事务。

来看一个简单的示例：

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO test values(1);

Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO test values(2);

Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM test;

+------+

| idx  |

+------+

|    1 |

|    2 |

+------+

2 rows in set (0.00 sec)
mysql> ROLLBACK;

Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM test;

Empty set (0.00 sec)

上面创建了一个名为 test 的表，开始事务，向其中分两次插入记录。

然后查询刚刚插入的两条记录。因为此时是处于一个事务中，所以这两条记录实际上是可以被回滚的。

调用 ROLLBACK 后两次查询，表中已经没有了刚刚的两条记录。

autocommit

抛开事务，MySQL 默认情况下开启了一个自动提交的模式 autocommit，一条语句被回车执行后该语句便生效了，变更会保存在 MySQL 的文件中，无法撤消。当使用相应语句比如 BEGIN 显式声明开始一个事务时，autocommit 默认会是关闭状态。

可通过查询 @@autocommit 变量来查看当前是否是自动提交的状态，

mysql> SELECT @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)

通过设置 autocommit 为 0 可关闭自动提交，

SET autocommit = 0;

无论是否是自动提交模式，语句执行后都会生效，区别在于，非自动模式下，没提交的那些操作是可以回滚的，一旦提交后便不可撤消了。换句话说，当 autocommit 关闭时，一直是处于事务操作中的，可随时调用 ROLLBACK 进行回滚。

下面的语句展示了 autocommit 关闭时 COMMIT 与否的影响，

mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)
mysql> set autocommit=0;

Query OK, 0 rows affected (0.00 sec)
mysql> select * from test;

Empty set (0.00 sec)
mysql> insert into test values(1);

Query OK, 1 row affected (0.00 sec)
mysql> insert into test values(2);

Query OK, 1 row affected (0.00 sec)
mysql> select * from test;

+------+

| idx  |

+------+

|    1 |

|    2 |

+------+

2 rows in set (0.00 sec)
mysql> rollback;

Query OK, 0 rows affected (0.00 sec)
mysql> select * from test;

Empty set (0.00 sec)
mysql> insert into test values(3);

Query OK, 1 row affected (0.00 sec)
mysql> commit;

Query OK, 0 rows affected (0.00 sec)
mysql> rollback;

Query OK, 0 rows affected (0.00 sec)
mysql> select * from test;

+------+

| idx  |

+------+

|    3 |

+------+

1 row in set (0.00 sec)

事务中会触发隐式提交的操作

虽说事务模式下关闭了 autocommit 必需手动执行 COMMIT 才能提交，但有些语句和操作是会隐式触发提交的，在进行事务过程中需要注意，这些操作可在官方文档 Statements That Cause an Implicit Commit 中查找到。

所以不能单纯地认为一个事务中所有操作都是绝对安全可回滚的。

Node.js 示例

以下是来自 npm 模块 mysqljs/mysql 关于事务的示例：

connection.beginTransaction(function(err) {
  if (err) { throw err; }
  connection.query('INSERT INTO posts SET title=?', title, function (error, results, fields) {
    if (error) {
      return connection.rollback(function() {
        throw error;
      });
    }
<span class="pl-k">var</span> log <span class="pl-k">=</span> <span class="pl-s"><span class="pl-pds">'</span>Post <span class="pl-pds">'</span></span> <span class="pl-k">+</span> <span class="pl-smi">results</span>.<span class="pl-smi">insertId</span> <span class="pl-k">+</span> <span class="pl-s"><span class="pl-pds">'</span> added<span class="pl-pds">'</span></span>;

<span class="pl-smi">connection</span>.<span class="pl-en">query</span>(<span class="pl-s"><span class="pl-pds">'</span>INSERT INTO log SET data=?<span class="pl-pds">'</span></span>, log, <span class="pl-k">function</span> (<span class="pl-smi">error</span>, <span class="pl-smi">results</span>, <span class="pl-smi">fields</span>) {
  <span class="pl-k">if</span> (error) {
    <span class="pl-k">return</span> <span class="pl-smi">connection</span>.<span class="pl-en">rollback</span>(<span class="pl-k">function</span>() {
      <span class="pl-k">throw</span> error;
    });
  }
  <span class="pl-smi">connection</span>.<span class="pl-en">commit</span>(<span class="pl-k">function</span>(<span class="pl-smi">err</span>) {
    <span class="pl-k">if</span> (err) {
      <span class="pl-k">return</span> <span class="pl-smi">connection</span>.<span class="pl-en">rollback</span>(<span class="pl-k">function</span>() {
        <span class="pl-k">throw</span> err;
      });
    }
    <span class="pl-en">console</span>.<span class="pl-c1">log</span>(<span class="pl-s"><span class="pl-pds">'</span>success!<span class="pl-pds">'</span></span>);
  });
});

});

});

总结

关于 autocommit 与事务，他们其实是这样的关系：

• MySQL 每一步操作都可看成一个原子操作。
• 默认情况下，autocommit 是开启状态，所以第一条语句都是执行后自动提交，语句产生的效果被记录保存了下来。
• 关于 autocommit 后，语句不会自动提交，需要手动调用 COMMIT 来让效果被持久化。
• 也可通过 BEGIN 开始一个事务，此时 autocommit 隐式地被关闭了，因此事务操作过程中也是需要显式调用 COMMIT 来让效果永久生效。
• BEGIN 开启事务后，使用 COMMIT 或 ROLLBACK 来结束该事务。事务结束后 autocommit 回到原有的状态。

所以，autocommit 这个开关相当于一个记录的事务标记，它被关闭时你一直处于一个可回滚的状态。而 BEGIN 开启的是一次临时事务，一旦 COMMIT 或 ROLLBACK 本次事务便结束了。

相关资源

• MySQL Reference Manual - 13.3 Transactional and Locking Statements
• MySQL Transaction
• mysqljs/mysql