php 事务中的 Mysql 事务
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1490846/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Mysql transactions within transactions
提问by Brian
In a PHP script working with a mysql database, I recently had the need to use a transaction at a point that happened to be inside another transaction. All my tests seem to indicate this is working out fine, but I can't find any documentation on this usage.
在使用 mysql 数据库的 PHP 脚本中，我最近需要在碰巧在另一个事务中的某个点使用事务。我所有的测试似乎都表明这运行良好，但我找不到有关此用法的任何文档。
I want to be sure - are transactions within transactions valid in mysql? If so, is there a way to find out how many levels deep you are in nested transactions? (ie. how many rollbacks it would take to return to normal)
我想确定 - 交易中的交易在 mysql 中是否有效？如果是这样，有没有办法找出嵌套事务的深度？（即恢复正常需要多少回滚）
Thanks in advance, Brian
提前致谢，布赖恩
采纳答案by Pascal MARTIN
This page of the manual might interest you : 12.3.3. Statements That Cause an Implicit Commit; quoting a few sentences :
您可能对手册的这一页感兴趣：12.3.3。导致隐式提交的语句；引用几句话：
The statements listed in this section (and any synonyms for them) implicitly end a transaction, as if you had done a
COMMITbefore executing the statement.
本节中列出的语句（以及它们的任何同义词）隐含地结束了一个事务，就好像您
COMMIT在执行该语句之前已经完成了一样。
And, a bit farther in the page :
而且，在页面中更远一点：
Transaction-control and locking statements.
BEGIN,LOCK TABLES,SET autocommit = 1(if the value is not already 1),START TRANSACTION,UNLOCK TABLES.
事务控制和锁定语句。
BEGIN,LOCK TABLES,SET autocommit = 1(如果该值还不是 1),START TRANSACTION,UNLOCK TABLES.
See also this paragraph :
另见本段：
Transactions cannot be nested.
This is a consequence of the implicitcommitperformed for any current transaction when you issue aSTART TRANSACTIONstatement or one of its synonyms.
事务不能嵌套。
这是commit在您发出START TRANSACTION语句或其同义词之一时对任何当前事务隐式执行的结果。
回答by Buttle Butkus
Contrary to everyone else's answer, you can effectively create transactions within transactions and it's really easy. You just create SAVEPOINT locations and use ROLLBACK TO savepointto rollback part of the transaction, where savepointis whatever name you give the savepoint. Link to MySQL documentation: http://dev.mysql.com/doc/refman/5.0/en/savepoint.htmlAnd of course, none of the queries anywhere in the transaction should be of the type that implicitly commit, or the whole transaction will be committed.
与其他人的答案相反，您可以在交易中有效地创建交易，这真的很容易。您只需创建 SAVEPOINT 位置并使用 ROLLBACK TO savepoint回滚部分事务，其中savepoint是您为保存点指定的任何名称。链接到 MySQL 文档：http: //dev.mysql.com/doc/refman/5.0/en/savepoint.html当然，事务中任何地方的查询都不应该是隐式提交的类型，或者整个交易将被提交。
Examples:
例子：
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
In PHP I have written code like this, and it works perfectly:
在 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
}
回答by Vladislav Rastrusny
I want to be sure - are transactions within transactions valid in mysql?
我想确定 - 交易中的交易在 mysql 中是否有效？
No.
不。
回答by troelskn
MySql doesn't support nested transactions. There are a few ways that you can emulate it though. First, you can use savepoints as a form of transaction, so that gives you two levels of transactions; I've used this for testing, but I'm not sure about the limitations, if you use it in production code. A simpler solution is to ignore the second begin transactionand instead increase a counter. For each commit, you decrease it. Once you hit zero, you do an actual commit. There are obvious limitations of this; Eg. a rollback will roll alltransactions back, but for a case where you only use transactions for error-handling, that may be acceptable.
MySql 不支持嵌套事务。不过，您可以通过几种方法来模拟它。首先，您可以使用保存点作为事务的一种形式，从而为您提供两个级别的事务；我已经将它用于测试，但如果您在生产代码中使用它，我不确定它的局限性。一个更简单的解决方案是忽略第二个begin transaction而是增加一个计数器。对于每个commit，你减少它。一旦你达到零，你就会做一个实际的commit. 这有明显的局限性；例如。回滚将回滚所有事务，但对于仅将事务用于错误处理的情况，这可能是可以接受的。
回答by pfuri
There are some great answers in this thread, however, if you use innoDB as your MySQL storage engine and are using MySQL 5.0.3 or higher, you get nested transactions right out of the box without the need for any extra work on your part or any of the fancy techniques described by others in this thread.
该线程中有一些很好的答案，但是，如果您使用 innoDB 作为 MySQL 存储引擎并使用 MySQL 5.0.3 或更高版本，则您可以立即获得嵌套事务，而无需您进行任何额外的工作或此线程中其他人描述的任何花哨技术。
From the MySQL docs on XA Transactions:
来自 XA 事务的 MySQL 文档：
MySQL 5.0.3 and up provides server-side support for XA transactions. Currently, this support is available for the InnoDB storage engine. The MySQL XA implementation is based on the X/Open CAE document Distributed Transaction Processing: The XA Specification. This document is published by The Open Group and available at http://www.opengroup.org/public/pubs/catalog/c193.htm. Limitations of the current XA implementation are described in Section E.5, “Restrictions on XA Transactions”.
MySQL 5.0.3 及更高版本为 XA 事务提供服务器端支持。目前，此支持可用于 InnoDB 存储引擎。MySQL XA 实现基于 X/Open CAE 文档分布式事务处理：XA 规范。本文档由 The Open Group 发布，可从http://www.opengroup.org/public/pubs/catalog/c193.htm 获得。当前 XA 实现的限制在第 E.5 节“XA 事务的限制”中描述。
My XA Transaction Example Just For You:
我的 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';
# 0,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 0,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!
MySQL Documentation For XA Transactions:
XA 事务的 MySQL 文档：
- 13.3.7. XA Transactions
- 13.3.7.1. XA Transaction SQL Syntax
- 13.3.7.2. XA Transaction States
- E.6. Restrictions on XA Transactions
I <3 XA Transactions 4 Eva!
我 <3 XA 交易 4 Eva！
