SQL Server - 事务在出错时回滚？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1749719/
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
SQL Server - transactions roll back on error?
提问by jonathanpeppers
We have client app that is running some SQL on a SQL Server 2005 such as the following:
我们有在 SQL Server 2005 上运行一些 SQL 的客户端应用程序，例如：
BEGIN TRAN;
INSERT INTO myTable (myColumns ...) VALUES (myValues ...);
INSERT INTO myTable (myColumns ...) VALUES (myValues ...);
INSERT INTO myTable (myColumns ...) VALUES (myValues ...);
COMMIT TRAN;
It is sent by one long string command.
它由一个长字符串命令发送。
If one of the inserts fail, or any part of the command fails, does SQL Server roll back the transaction? If it does not rollback, do I have to send a second command to roll it back?
如果其中一个插入失败，或者命令的任何部分失败，SQL Server 会回滚事务吗？如果它不回滚，我是否必须发送第二个命令来回滚它？
I can give specifics about the api and language I'm using, but I would think SQL Server should respond the same for any language.
我可以提供有关我正在使用的 api 和语言的详细信息，但我认为 SQL Server 应该对任何语言做出相同的响应。
回答by
You can put set xact_abort onbefore your transaction to make sure sql rolls back automatically in case of error.
您可以set xact_abort on在事务之前放置以确保 sql 在出现错误时自动回滚。
回答by Raj More
You are correct in that the entire transaction will be rolled back. You should issue the command to roll it back.
您是正确的，整个事务将被回滚。您应该发出命令将其回滚。
You can wrap this in a TRY CATCHblock as follows
您可以将其包装在一个TRY CATCH块中，如下所示
BEGIN TRY
BEGIN TRANSACTION
INSERT INTO myTable (myColumns ...) VALUES (myValues ...);
INSERT INTO myTable (myColumns ...) VALUES (myValues ...);
INSERT INTO myTable (myColumns ...) VALUES (myValues ...);
COMMIT TRAN -- Transaction Success!
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRAN --RollBack in case of Error
-- you can Raise ERROR with RAISEERROR() Statement including the details of the exception
RAISERROR(ERROR_MESSAGE(), ERROR_SEVERITY(), 1)
END CATCH
回答by samwise
Here the code with getting the error message working with MSSQL Server 2016:
这是获取与 MSSQL Server 2016 一起使用的错误消息的代码：
BEGIN TRY
BEGIN TRANSACTION
-- Do your stuff that might fail here
COMMIT
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRAN
DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE()
DECLARE @ErrorSeverity INT = ERROR_SEVERITY()
DECLARE @ErrorState INT = ERROR_STATE()
-- Use RAISERROR inside the CATCH block to return error
-- information about the original error that caused
-- execution to jump to the CATCH block.
RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
END CATCH
回答by Vitaly
From MDSN article, Controlling Transactions (Database Engine).
来自 MDSN 文章Controlling Transactions (Database Engine)。
If a run-time statement error (such as a constraint violation) occurs in a batch, the default behavior in the Database Engine is to roll back only the statement that generated the error. You can change this behavior using the SET XACT_ABORT statement. After SET XACT_ABORT ON is executed, any run-time statement error causes an automatic rollback of the current transaction. Compile errors, such as syntax errors, are not affected by SET XACT_ABORT. For more information, see SET XACT_ABORT (Transact-SQL).
如果批处理中出现运行时语句错误（例如违反约束），则数据库引擎中的默认行为是仅回滚生成错误的语句。您可以使用 SET XACT_ABORT 语句更改此行为。执行 SET XACT_ABORT ON 后，任何运行时语句错误都会导致当前事务的自动回滚。编译错误，例如语法错误，不受 SET XACT_ABORT 的影响。有关详细信息，请参阅 SET XACT_ABORT (Transact-SQL)。
In your case it will rollback the complete transaction when any of inserts fail.
在您的情况下，当任何插入失败时，它将回滚完整的事务。
回答by Quassnoi
If one of the inserts fail, or any part of the command fails, does SQL server roll back the transaction?
如果其中一个插入失败，或者命令的任何部分失败，SQL Server 会回滚事务吗？
No, it does not.
不，不是的。
If it does not rollback, do I have to send a second command to roll it back?
如果它不回滚，我是否必须发送第二个命令来回滚它？
Sure, you should issue ROLLBACKinstead of COMMIT.
当然，你应该发出ROLLBACK而不是COMMIT.
If you want to decide whether to commit or rollback the transaction, you should remove the COMMITsentence out of the statement, check the results of the inserts and then issue either COMMITor ROLLBACKdepending on the results of the check.
如果要决定是提交还是回滚事务，则应将COMMIT语句从语句中删除，检查插入的结果，然后根据检查结果发出COMMIT或ROLLBACK。
