Why would you commit a transaction within a catch clause?
Microsoft has the following example for try...catch in tsql:
USE AdventureWorks;
GO
-- SET XACT_ABORT ON will render the transaction uncommittable
-- when the constraint violation occurs.
SET XACT_ABORT ON;
BEGIN TRY
BEGIN TRANSACTION;
-- A FOREIGN KEY constraint exists on this table. This
-- statement will generate a constraint violation error.
DELETE FROM Production.Product
WHERE ProductID = 980;
-- If the delete operation succeeds, commit the transaction. The CATCH
-- block will not execute.
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
-- Test XACT_STATE for 0, 1, or -1.
-- If 1, t开发者_StackOverflowhe transaction is committable.
-- If -1, the transaction is uncommittable and should
-- be rolled back.
-- XACT_STATE = 0 means there is no transaction and
-- a commit or rollback operation would generate an error.
-- Test whether the transaction is uncommittable.
IF (XACT_STATE()) = -1
BEGIN
PRINT 'The transaction is in an uncommittable state.' +
' Rolling back transaction.'
ROLLBACK TRANSACTION;
END;
-- Test whether the transaction is active and valid.
IF (XACT_STATE()) = 1
BEGIN
PRINT 'The transaction is committable.' +
' Committing transaction.'
COMMIT TRANSACTION;
END;
END CATCH;
GO
Source for above sample: Using TRY...CATCH in Transact-SQL
I don't understand why you'd want to commit a transaction that resulted in an exception. It seems like at least 9 times out of 10 you'd want to IF (XACT_STATE()) !=0 ROLLBACK TRANSACTION. Why would you want a partial success over a clean slate?
This sample is completely wrong. Is understandable to have a try-catch block to deal with a key duplicate and recover and do an alternate operation (perhaps an update instead of the insert). But to have a block of code the COMMITS in case of success, but leaves the transaction open in case of error, and, even more, silently swallows the error is just mind blowing. This piece of code is a big big can of worms.
On my site there is a sample procedure template that handles errors and transactions correctly, allowing for embedded transaction to recover and continue on error correctly. Typical examples where you'd want the handling to recover on case of error is batch processing: as you progress through the batch, you savepoint before each record then attempt to process. If processing fails, you save the record in a failed table and continue without loosing the whole batch.
Update
LOL, I missed the commit in the catch too. Then is not that bad as my original comment. I still prefer my template, that uses a savepoint and allows for nested transactions.
精彩评论