MySQL 如果未达到 COMMIT TRANSACTION，则自动回滚
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6121917/
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
Automatic Rollback if COMMIT TRANSACTION is not reached
提问by Koekiebox
Consider the following:
考虑以下：
START TRANSACTION;
BEGIN;
INSERT INTO prp_property1 (module_name,environment_name,NAME,VALUE) VALUES ('','production','','300000');
/** Assume there is syntax error SQL here...**/
Blah blah blah
DELETE FROM prp_property1 WHERE environment_name = 'production';
COMMIT TRANSACTION;
Question:
题：
I noticed that the transaction automatically rolls back and the record insert attempt fails.
我注意到事务自动回滚并且记录插入尝试失败。
If I don't provide a error handler or error check along with ROLLBACK TRANSACTIONas above, is it safe as it seems to be doing the job in an example like above because the COMMIT TRANSACTIONnever gets executed?
如果我不提供错误处理程序或错误检查以及ROLLBACK TRANSACTION如上所述，它是否安全，因为它似乎在上面的示例中完成了工作，因为它COMMIT TRANSACTION从未被执行？
I assume the transaction is rolled back immediately and discarded as soon as a error occurs.
我假设事务会立即回滚并在发生错误时立即丢弃。
回答by MarkR
No, transactions are not rolled back as soon as an error occurs. But you may be using a client-application which applies this policy.
不，事务不会在发生错误时立即回滚。但是您可能正在使用应用此策略的客户端应用程序。
For example, if you are using the mysql command-line client, then it normally stops executing when an error occurs and will quit. Quitting while a transaction is in progress does cause it to be rolled back.
例如，如果您使用的是 mysql 命令行客户端，那么它通常会在发生错误时停止执行并退出。在事务正在进行时退出确实会导致它被回滚。
When you are writing your own application, you can control the policy on rollback, but there are some exceptions:
当您编写自己的应用程序时，您可以控制回滚策略，但有一些例外：
- Quitting (i.e. disconnecting from the database) always rolls back a transaction in progress
- A deadlock or lock-wait timeout implicitly causes a rollback
- 退出（即与数据库断开连接）总是回滚正在进行的事务
- 死锁或锁等待超时隐式导致回滚
Other than these conditions, if you invoke a command which generates an error, the error is returned as normal, and you are free to do whatever you like, including committing the transaction anyway.
除了这些条件之外，如果您调用产生错误的命令，错误将正常返回，您可以随意做任何想做的事情，包括无论如何提交事务。
回答by Rogerio de Moraes
Use Mysql stored procedure
使用Mysql存储过程
BEGIN
DECLARE exit handler for sqlexception
BEGIN
ROLLBACK;
END;
DECLARE exit handler for sqlwarning
BEGIN
ROLLBACK;
END;
START TRANSACTION;
INSERT INTO prp_property1 (module_name,environment_name,NAME,VALUE) VALUES ('','production','','300000');
[ERROR]
COMMIT;
END
You can set if warning or error rollback, then you don't need delete, with transaction all entry is deleted.
您可以设置是否警告或错误回滚，那么您不需要删除，事务所有条目都被删除。
回答by tech_me
You may use procedureto do this more effectively.
Transaction with Stored Procedure in MySQL Server
您可以使用程序来更有效地执行此操作。
MySQL 服务器中存储过程的事务
回答by Nicola Pedretti
I would like to add to what @MarkR already said. Error Handling, assuming InnoDB engine, happens as described in the Mysql Server Documentation
我想补充@MarkR 已经说过的内容。错误处理，假设 InnoDB 引擎，发生在Mysql Server 文档中
- If you run out of file space in a tablespace, a MySQL Table is full error occurs and InnoDB rolls back the SQL statement.
- A transaction deadlock causes InnoDB to roll back the entire transaction.
- A duplicate-key error rolls back the SQL statement
- A row too long error rolls back the SQL statement.
- Other errors are mostly detected by the MySQL layer of code (above the InnoDB storage engine level), and they roll back the corresponding SQL statement
- 如果表空间中的文件空间不足，则会发生 MySQL Table is full 错误并且 InnoDB 回滚 SQL 语句。
- 事务死锁导致 InnoDB 回滚整个事务。
- 重复键错误回滚 SQL 语句
- 行太长错误回滚 SQL 语句。
- 其他错误大多由 MySQL 代码层（InnoDB 存储引擎级别以上）检测，并回滚相应的 SQL 语句
My understanding is also that when the Mysql session ends (when the php scripts ends), anything that is not committed is rolled back. I yet have to find a really reliable source to back this statement so do not take my word for it.
我的理解也是，当 Mysql 会话结束时（当 php 脚本结束时），任何未提交的内容都会回滚。我还必须找到一个真正可靠的来源来支持这个声明，所以不要相信我的话。
回答by Wen
I've tested these three situations; mySQL does not roll back automatically.
我已经测试了这三种情况；mySQL 不会自动回滚。
A transaction deadlock causes InnoDB to roll back the entire transaction. A duplicate-key error rolls back the SQL statement A row too long error rolls back the SQL statement.
事务死锁导致 InnoDB 回滚整个事务。重复键错误回滚 SQL 语句 行太长错误回滚 SQL 语句。
Only the affected records fail, the rest of the records succeed unless your application calls "rollback" explicitly.
只有受影响的记录失败，其余记录成功，除非您的应用程序显式调用“回滚”。
