Nested procedures with transactions
I have the following problem. I have to stored procedures (debug messages double-indended):
CREATE PROC innerProc
AS
BEGIN
SELECT 'innerProc 1',@@TRANCOUNT
BEGIN TRAN
SELECT 'innerProc 2',@@TRANCOUNT
ROLLBACK
SELECT 'innerProc 3',@@TRANCOUNT
END
GO -----------------------------------------
CREATE PROC outerProc开发者_Python百科
AS
BEGIN
SELECT 'outerProc 1',@@TRANCOUNT
BEGIN TRAN
SELECT 'outerProc 2',@@TRANCOUNT
EXEC innerProc
SELECT 'outerProc 3',@@TRANCOUNT
ROLLBACK
SELECT 'outerProc 4',@@TRANCOUNT
END
GO -----------------------------------------
EXEC outerProc
What they do?
outerProc
begins transaction (@@TRANCOUNT = 1)- executes
innerProc
(@@TRANCOUNT at the beginning of the proc = 1) innerProc
begins another transaction (@@TRANCOUNT = 2)innerProc
rollbacks transaction (@@TRANCOUNT = 0)- AND HERE IS THE PROBLEM: @@TRANCOUNT at the beginning of the
innerProc
is not equal to @@TRANCOUNT at the end. What am I doing wrong? Is it correct approach?
I believe you need to use named transactions or else you're killing all transactions when you rollback on the nested one, even if it is scoped to just the inner sproc
http://msdn.microsoft.com/en-us/library/ms188929.aspx
Further reading: http://msdn.microsoft.com/en-us/library/ms181299.aspx
ROLLBACK TRANSACTION without a savepoint_name or transaction_name rolls back to the beginning of the transaction. When nesting transactions, this same statement rolls back all inner transactions to the outermost BEGIN TRANSACTION statement. In both cases, ROLLBACK TRANSACTION decrements the @@TRANCOUNT system function to 0. ROLLBACK TRANSACTION savepoint_name does not decrement @@TRANCOUNT.
精彩评论