The following sproc is implemented in accord with the template in this article: Exception handling and nested transactions. This sproc is supposed to handle deadlocks and it is called by another sproc that already creates a transaction. Some magic with BEGIN/COMMIT of the inner transaction is not matching, because I get this exception: Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0
. As far as I understand, catch is executed, @xstate = -1
is true and the whole outer transaction is rolled back.
Any ideas where the mismatch happens?
CREATE PROCEDURE [dbo].[mysproc]
AS
BEGIN
SET NOCOUNT ON;
SET DEADLOCK_PRIORITY LOW;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
BEGIN TRY
DECLARE @trancount int;
SET @trancount = @@TRANCOUNT;
IF (@trancount = 0)
BEGIN TRANSACTION;
ELSE
SAVE TRANSACTION InnerTran;
--
-- do some work that can potentially cause a deadlock
--
END TRY
BEGIN CATCH
DECLARE @xstate int
SELECT @xstate = XACT_STATE()
IF (@xstate = - 1)
ROLLBACK;
IF (@xstate = 1 and @trancount = 0)
ROLLBACK;
IF (@xstate = 1 and @trancount > 0)
ROLLBACK TRANSACTION InnerTran;
END CATCH
END
GO
The difference is that you do not raise an exception. In case
XACT_STATE()
is -1 in the catch block (ie. uncommittable transaction, like a deadlock would cause) In such a case your procedure would rollback (it must, it has no choice in -1 case) but return w/o raising an exception. Hence, the mismatch. You must raise an exception and catch it in the caller.See Uncommittable Transactions and XACT_STATE:
Deadlocks will always result in uncomittable transactions. In fact, in the case of deadlock, by the time you catch the deadlock exception, the transaction has already rolled back as the deadlock victim.