MYSQL事务中的嵌套事务

Question

在一个与mysql数据库交互的PHP脚本中，我最近需要在一个已有事务中使用另一个事务。目前我所有的测试结果都表明这样做是可行的，但我无法找到相关文档证实这种用法的正确性。

我希望确认一下：在mysql中，嵌套事务是有效的吗？如果是，有没有方法可以查找嵌套事务的层数？（例如，需要回滚多少次才能返回正常状态）

提前致谢， Brian

- Brian

Answer 1

与其他人的答案相反，您可以有效地在事务中创建事务，并且非常容易。您只需创建SAVEPOINT位置，并使用ROLLBACK TO savepoint来回滚部分事务，其中savepoint是您给出的任何名称。 MySQL文档链接：http://dev.mysql.com/doc/refman/5.0/en/savepoint.html 当然，在事务中任何位置的查询都不应该是隐式提交类型，否则整个事务将被提交。

示例：

START TRANSACTION;

# queries that don't implicitly commit

SAVEPOINT savepoint1;

# queries that don't implicitly commit

# now you can either ROLLBACK TO savepoint1, or just ROLLBACK to reverse the entire transaction.

SAVEPOINT savepoint2;

# queries that don't implicitly commit

# now you can ROLLBACK TO savepoint1 OR savepoint2, or ROLLBACK all the way.
# e.g.

ROLLBACK TO savepoint1;
COMMIT; # results in committing only the part of the transaction up to savepoint1

在PHP中，我编写了以下代码，它能够完美地工作：

foreach($some_data as $key => $sub_array) {
  $result = mysql_query('START TRANSACTION'); // note mysql_query is deprecated in favor of PDO
  $rollback_all = false; // set to true to undo whole transaction
  for($i=0;$i<sizeof($sub_array);$i++) {
    if($sub_array['set_save'] === true) {
      $savepoint = 'savepoint' . $i;
      $result = mysql_query("SAVEPOINT $savepoint");
    }
    $sql = 'UPDATE `my_table` SET `x` = `y` WHERE `z` < `n`'; // some query/queries
    $result = mysql_query($sql); // run the update query/queries

    $more_sql = 'SELECT `x` FROM `my_table`'; // get data for checking
    $result = mysql_query($more_sql);
    
    $rollback_to_save = false; // set to true to undo to last savepoint
    while($row = mysql_fetch_array($result)) {
      // run some checks on the data
      if( /*some check says to go back to savepoint */) {
        $rollback_to_save = true; // or just do the rollback here.
      }
      if( /* some check says to rollback entire transaction */ ) {
        $rollback_all = true;
      }
    }
    if($rollback_all === true) {
      mysql_query('ROLLBACK'); // rollback entire transaction
      break; // break out of for loop, into next foreach
    }
    if($rollback_to_save = true) {
      mysql_query("ROLLBACK TO $savepoint"); // undo just this part of for loop
    }
  } // end of for loop
  mysql_query('COMMIT'); // if you don't do this, the whole transaction will rollback
}

- Buttle Butkus

---

没试过，但我觉得有必要评论一下这是多么美丽。 - Marco Aurélio Deleu

@MarcoAurélioDeleu 谢谢！我觉得我从来没有因我的任何代码而获得过如此的赞美。它在我的应用程序中完美运行，并且似乎有几个其他人也通过点赞使其工作。 - Buttle Butkus

没错，一个事务内可以设置保存点并回滚到该保存点。但是这里的重点是，嵌套事务是不可能的，因为启动内部事务将隐式提交前一个外部事务语句，这可能不是您想要的。这可能会带来问题：例如，如果有一个名为House的类和另一个名为Room的类，每个类都有自己的DB创建语句事务；在House对象内创建Room对象将中断外部House的事务！！！ - sbrbot

@sbrbot “玫瑰花的名字不重要，重要的是它的芳香。”你可以嵌套START TRANSACTION语句，但我在我的答案中粗略概述的SAVEPOINT方法正好实现了嵌套事务的功能。假设你有一个room对象数组，你想将它们添加到一个house对象中。你实际上可以使用数据库约束来决定何时房子“满了”。你可以尝试添加每个房间，并在房子满了后返回一个house full异常，这可以与数据库模型返回错误并回滚到“savepointX”相吻合。“无法添加第5个浴室。满屋了。” - Buttle Butkus

我有一个数据库对象（使用共享的DB连接），并被所有我的类（对象）使用。实际上，它是一个抽象类，具有将db作为所有类的包装器。我从您的评论中得到了_transactionalLevel_属性的想法 - db是否应该启动事务（默认情况下是是）。当我在房子内创建房间时，我应该设置此属性，并告诉DB不要启动事务，因为它已经在外部类中启动了。 - sbrbot

Answer 2

这份手册中的内容可能会让你感兴趣：12.3.3. 导致隐式提交的语句，其中摘录了几句话：

本节列出的语句（以及它们的任何同义词）隐式地结束一个事务，就好像在执行该语句之前已经执行了COMMIT一样。

而且，在页面的稍远处：

事务控制和锁定 语句。 BEGIN, LOCK TABLES, SET autocommit = 1 (如果值不是 已经是1),START TRANSACTION, UNLOCK TABLES。

还可以参考以下段落：

事务不能嵌套。
这是在发出START TRANSACTION语句或其同义词之一时为任何当前事务执行的隐式commit的结果。

- Pascal MARTIN

---

在同一连接中或不同连接中都不能嵌套事务。 - Rafael Barros

通常情况下，不同的连接之间彼此独立，因此一个连接中的事务并不知道另一个连接中的事务。虽未经测试，但应该是正确的。 - Alex Lomakin

Answer 3

我希望确认一下，在MySQL中，事务内的事务是否有效？

不会。

- Vladislav Rastrusny

Answer 4

MySql 不支持嵌套事务，但是可以通过几种方法来模拟。首先，您可以使用保存点作为一种事务形式，这样就可以实现两个级别的事务；我已经在测试中使用过它，但不确定如果在生产代码中使用时是否有限制。另一种更简单的解决方案是忽略第二个 begin transaction，而是增加一个计数器。对于每个commit，您将其减少。一旦计数器为零，您就会执行实际的commit。这种方法显然存在一些限制；例如，回滚将回滚所有事务，但对于仅将事务用于错误处理的情况，这可能是可接受的。

- troelskn

Answer 5

在这个主题中有一些很好的答案，然而，如果您使用innoDB作为MySQL存储引擎，并且使用MySQL 5.0.3或更高版本，则无需进行任何额外的工作或使用其他线程中描述的复杂技术，即可获得嵌套事务支持。

从MySQL XA Transactions文档中可以看到：MySQL 5.0.3及更高版本提供了对XA事务的服务器端支持。目前，这种支持仅适用于InnoDB存储引擎。MySQL XA实现基于X/Open CAE文件“Distributed Transaction Processing: The XA Specification”。该文件由The Open Group发布，可在http://www.opengroup.org/public/pubs/catalog/c193.htm获取。当前XA实现的限制在第E.5节“关于XA事务的限制”中描述。

以下是示例XA事务供您参考：

# Start a new XA transaction
XA START;

    # update my bank account balance, they will never know!
    UPDATE `bank_accounts` SET `balance` = 100000 WHERE `id` = 'mine';

    # $100,000.00 is a bit low, I'm going to consider adding more, but I'm not sure so 
    # I will start a NESTED transaction and debate it...
    XA START;

        # max int money! woo hoo!
        UPDATE `bank_accounts` SET `balance` = 2147483647 WHERE `id` = 'mine';

    # maybe thats too conspicuous, better roll back
    XA ROLLBACK;


# The $100,000 UPDATE still applies here, but the max int money does not, going for it!
XA COMMIT;

# Oh No!  Sirens!  It's the popo's!!! run!!
# What the hell are they using ints for money columns anyway!  Ahhhh!

XA 事务的 MySQL 文档:

• 13.3.7. XA Transactions（XA 事务）
• 13.3.7.1. XA Transaction SQL Syntax（XA 事务 SQL 语法）
• 13.3.7.2. XA Transaction States（XA 事务状态）
• E.6. Restrictions on XA Transactions（XA 事务限制）

我 <3 XA 事务永远！

- pfuri

---

在MySQL 5.6上尝试使用“XA”命令需要一个字符串xid，尝试启动嵌套事务会出现以下错误：“ERROR 1399 (XAE07)：XAER_RMFAIL：当全局事务处于活动状态时无法执行该命令”。 - HectorJ

我遇到了与@HectorJ相同的问题。我希望这个方法有效，但实际上并不行。 - Ryan Ewen

@RyanEwen 我的 SAVEPOINT 解决方案有什么问题吗？对我来说完美地运作。 - Buttle Butkus

@ButtleButkus 是的，不幸的是对我来说它不是一个合适的解决方案。我记不太清了为什么。我想这可能与嵌套函数和/或隐式提交的查询有关。最终我意识到，在我的应用程序函数中不应使用事务，因为它们可能会被嵌套，而只能在代码的最高级别处使用事务，在没有任何冲突的情况下将整个过程包装在事务中。 - Ryan Ewen

@RyanEwen 如果你的应用程序跟踪事务嵌套级别以确定是否发出“START TRANSACTION”或“SAVEPOINT savepoint-n”，那么它将起作用。当然，在事务期间不能使用DDL语句，如添加列、表等。但有时可以使用临时表。在我的情况下，我在开始事务之前创建一个临时表。https://dev.mysql.com/doc/refman/8.0/en/implicit-commit.html - Buttle Butkus

Answer 6

你可能需要检查你的测试方法。除了MaxDB之外，MySQL不支持任何类似嵌套事务的功能。

- Alana Storm

---

"MySQL不支持任何类似嵌套事务的功能。" 你认为保存点和嵌套事务有相似之处吗？ - Buttle Butkus

Answer 7

它执行： http://dev.mysql.com/doc/refman/5.0/en/xa.html

- K. Norbert

---

这些真的是嵌套事务吗？有一些示例吗？ - William Denniss