sql try/catch rollback/commit - 防止回滚后错误提交
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25146656/
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 try/catch rollback/commit - preventing erroneous commit after rollback
提问by user3666839
I am trying to write an MS sql script that has a transaction and a try/catch block. If it catches an exception, the transaction is rolled back. If not, the transaction is committed. I have seen a few different websites saying to do it like this:
我正在尝试编写一个具有事务和 try/catch 块的 MS sql 脚本。如果它捕获异常，则事务将回滚。如果不是，则提交事务。我看到一些不同的网站说要这样做：
begin transaction
begin try
--main content of script here
end try
begin catch
rollback transaction
end catch
commit transaction
But won't we still hit the "commit transaction" line even in the case of catching an exception? Won't this lead to a SQL error because the transaction has already been rolled back? I think it should be done like this:
但是，即使在捕获异常的情况下，我们是否仍然会遇到“提交事务”这一行？这不会因为事务已经回滚而导致 SQL 错误吗？我认为应该这样做：
declare @success bit = 1
begin transaction
begin try
--main content of script here
end try
begin catch
rollback transaction
set @success = 0
end catch
if(@success = 1)
begin
commit transaction
end
Howcome the commonly-posted solution does not include the @success variable? Is there no sql error that happens as a result of committing a transaction that has already been rolled back? Am I incorrect in saying that the "commit transaction" line of the first code example will still be hit in the case of catching an exception?
为什么常见发布的解决方案不包含@success 变量？有没有因为提交已经回滚的事务而发生的 sql 错误？我说第一个代码示例的“提交事务”行在捕获异常的情况下仍然会被击中是错误的吗？
回答by Gary Walker
I always thought this was one of the better articleson the subject. It includes the following example that I think makes it clear and includes the frequently overlooked @@trancount which is needed for reliable nested transactions
我一直认为这是关于这个主题的更好的文章之一。它包括以下我认为很清楚的示例，并包括可靠嵌套事务所需的经常被忽视的@@trancount
PRINT 'BEFORE TRY'
BEGIN TRY
BEGIN TRAN
PRINT 'First Statement in the TRY block'
INSERT INTO dbo.Account(AccountId, Name , Balance) VALUES(1, 'Account1', 10000)
UPDATE dbo.Account SET Balance = Balance + CAST('TEN THOUSAND' AS MONEY) WHERE AccountId = 1
INSERT INTO dbo.Account(AccountId, Name , Balance) VALUES(2, 'Account2', 20000)
PRINT 'Last Statement in the TRY block'
COMMIT TRAN
END TRY
BEGIN CATCH
PRINT 'In CATCH Block'
IF(@@TRANCOUNT > 0)
ROLLBACK TRAN;
THROW; -- raise error to the client
END CATCH
PRINT 'After END CATCH'
SELECT * FROM dbo.Account WITH(NOLOCK)
GO
回答by Jim V.
In your first example, you are correct. The batch will hit the commit transaction, regardless of whether the try block fires.
在你的第一个例子中，你是对的。无论 try 块是否触发，批处理都将命中提交事务。
In your second example, I agree with other commenters. Using the success flag is unnecessary.
在你的第二个例子中，我同意其他评论者的意见。使用成功标志是不必要的。
I consider the following approach to be, essentially, a light weight best practice approach.
我认为以下方法本质上是一种轻量级的最佳实践方法。
If you want to see how it handles an exception, change the value on the second insert from 255 to 256.
如果您想查看它如何处理异常，请将第二个插入的值从 255 更改为 256。
CREATE TABLE #TEMP ( ID TINYINT NOT NULL );
INSERT INTO #TEMP( ID ) VALUES ( 1 )
BEGIN TRY
BEGIN TRANSACTION
INSERT INTO #TEMP( ID ) VALUES ( 2 )
INSERT INTO #TEMP( ID ) VALUES ( 255 )
COMMIT TRANSACTION
END TRY
BEGIN CATCH
DECLARE
@ErrorMessage NVARCHAR(4000),
@ErrorSeverity INT,
@ErrorState INT;
SELECT
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
RAISERROR (
@ErrorMessage,
@ErrorSeverity,
@ErrorState
);
ROLLBACK TRANSACTION
END CATCH
SET NOCOUNT ON
SELECT ID
FROM #TEMP
DROP TABLE #TEMP
回答by Rinoy Ashokan
I used below ms sql script pattern several times successfully which uses Try-Catch,Commit Transaction- Rollback Transaction,Error Tracking.
我在 ms sql 脚本模式下成功使用了几次，它使用了Try-Catch、Commit Transaction- Rollback Transaction、Error Tracking。
Your TRY block will be as follows
您的 TRY 块将如下所示
BEGIN TRY
BEGIN TRANSACTION T
----
//your script block
----
COMMIT TRANSACTION T
END TRY
Your CATCH block will be as follows
您的 CATCH 块将如下所示
BEGIN CATCH
DECLARE @ErrMsg NVarChar(4000),
@ErrNum Int,
@ErrSeverity Int,
@ErrState Int,
@ErrLine Int,
@ErrProc NVarChar(200)
SELECT @ErrNum = Error_Number(),
@ErrSeverity = Error_Severity(),
@ErrState = Error_State(),
@ErrLine = Error_Line(),
@ErrProc = IsNull(Error_Procedure(), '-')
SET @ErrMsg = N'ErrLine: ' + rtrim(@ErrLine) + ', proc: ' + RTRIM(@ErrProc) + ',
Message: '+ Error_Message()
Your ROLLBACK script will be part of CATCH block as follows
您的 ROLLBACK 脚本将成为 CATCH 块的一部分，如下所示
IF (@@TRANCOUNT) > 0
BEGIN
PRINT 'ROLLBACK: ' + SUBSTRING(@ErrMsg,1,4000)
ROLLBACK TRANSACTION T
END
ELSE
BEGIN
PRINT SUBSTRING(@ErrMsg,1,4000);
END
END CATCH
Above different script blocks you need to use as one block. If any error happens in the TRYblock it will go the the CATCHblock. There it is setting various details about the error number,error severity,error line ..etc. At last all these details will get append to @ErrMsg parameter. Then it will check for the count of transaction (@@TRANCOUNT >0) , ie if anything is there in the transaction for rollback. If it is there then show the error message and ROLLBACK TRANSACTION. Otherwise simply print the error message.
在不同的脚本块之上，您需要将其用作一个块。如果在TRY块中发生任何错误，它将转到CATCH块。在那里设置有关错误编号、错误严重性、错误行等的各种详细信息。最后，所有这些细节都将附加到@ErrMsg 参数中。然后它会检查事务的计数 (@@TRANCOUNT >0) ，即事务中是否有任何用于回滚的内容。如果存在，则显示错误消息和ROLLBACK TRANSACTION。否则只需打印错误消息。
We have kept our COMMIT TRANSACTION Tscript towards the last line of TRY block in order to make sure that it should commit the transaction(final change in the database) only after all the code in the TRY block has run successfully.
我们将COMMIT TRANSACTION T脚本保留在 TRY 块的最后一行，以确保只有在 TRY 块中的所有代码都成功运行后才应提交事务（数据库中的最终更改）。
回答by Arun Prasad E S
Transaction counter
交易柜台
--@@TRANCOUNT = 0
begin try
--@@TRANCOUNT = 0
BEGIN TRANSACTION tran1
--@@TRANCOUNT = 1
--your code
-- if failed @@TRANCOUNT = 1
-- if success @@TRANCOUNT = 0
COMMIT TRANSACTION tran1
end try
begin catch
print 'FAILED'
end catch
回答by Sreedhar Chintakunta
Below might be useful.
下面可能有用。
Source: https://msdn.microsoft.com/en-us/library/ms175976.aspx
来源：https: //msdn.microsoft.com/en-us/library/ms175976.aspx
BEGIN TRANSACTION;
BEGIN TRY
-- your code --
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage;
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
END CATCH;
IF @@TRANCOUNT > 0
COMMIT TRANSACTION;
GO
