如果未达到COMMIT TRANSACTION，则会自动回滚 [英] Automatic Rollback if COMMIT TRANSACTION is not reached

问题描述

请考虑以下内容:

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;

问题:

我注意到该事务自动回滚，并且记录插入尝试失败.

I noticed that the transaction automatically rolls back and the record insert attempt fails.

如果我没有像上面的ROLLBACK TRANSACTION一样提供错误处理程序或错误检查，那么由于COMMIT TRANSACTION从未执行，在上面的示例中似乎可以完成此工作，这样是否安全?

If I don't provide a error handler or error check along with ROLLBACK TRANSACTION as above, is it safe as it seems to be doing the job in an example like above because the COMMIT TRANSACTION never gets executed?

我认为该交易会立即回滚并在发生错误时立即丢弃.

I assume the transaction is rolled back immediately and discarded as soon as a error occurs.

推荐答案

否，一旦发生错误，事务就不会回滚.但是您可能正在使用应用此策略的客户端应用程序.

No, transactions are not rolled back as soon as an error occurs. But you may be using a client-application which applies this policy.

例如，如果您使用的是mysql命令行客户端，那么它通常会在发生错误时停止执行并退出.在事务进行过程中退出会导致其回滚.

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.

编写自己的应用程序时，可以控制回滚策略，但是有一些例外:

When you are writing your own application, you can control the policy on rollback, but there are some exceptions:

• 退出(即与数据库断开连接)总是回滚正在进行的事务
• 死锁或锁定等待超时会隐式导致回滚

除这些条件外，如果您调用生成错误的命令，则错误将按正常方式返回，并且您可以自由地做任何您想做的事情，包括无论如何都要提交事务.

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.

这篇关于如果未达到COMMIT TRANSACTION，则会自动回滚的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！