There are several characteristics of nested transactions, saying that in helping;
Microsoft® SQL ServerTM ignores submitting internal transactions. The operation taken according to the end of the outermost transaction will be submitted or rolled back. If an external transaction is submitted, the inner nesting transaction will also be submitted. If rollback external transactions, all inner transactions will roll back without submitting an inner-layer transaction separately.
Each call for commit transaction or commit work is applied to the final execution of Begin Transaction. If the nesting Begin Transaction statement, the Commit statement is applied only to the last nested transaction, which is in the innermost transaction. Even the commission transactions inside the nested transaction references the exterior transaction name, the submission is only applied to the innermost transaction.
The Transaction_name parameter of the Rollback Transaction statement references a group of named nested transactions is illegal, and transaction_name can only reference the exterior transaction. If you perform the ROLLBACK TRANSAACTION_NAME statement using an external transaction name at any level of a set of nested transactions, all nested transactions will roll back. If you execute the Rollback Work or Rollback Transaction statement without a transaction_name parameter at any level of a set of nested transactions, it will roll back all nested transactions, including the outermost transaction.
The @@TRANCOUNT function records the nested level of the current transaction. Each Begin Transaction statement makes @@ TRANCOUNT plus 1. Each commit transaction or commit work statement makes @@ TRANCOUNT 1. The Rollback Work or Rollback Transaction statement without a transaction will roll back all nested transactions and reduce @@ TRANCOUNT to 0. Rollback Transaction of transaction names using a set of nesting transactions, Rollback Transaction, will roll back all nested transactions and reduce @@ TRANCOUNT to 0. When you are unable to determine if you have already in your transaction, you can use the select @@ TRANCOUNT statement to determine @@ TRANCOUNT is 1 or bigger. If @@ TRANCOUNT is 0, it indicates that it is not in the transaction. How to solve the problem of nesting, I will give an example:
-
Create a Table
for
Testing Create Table T1 (V
int
Check (v
<
100
))
-
v must be less than 100
-
Create First Proc
TO
INSERT DATA CREATE PROC innertrans
AS
Begin Begin Tran1 INSERT INTO T1 VALUES
1
)
-
This stay be ok
IF
@@
Error
<>
0
Begin Rollback Tran1 Return
-
1
end
INSERT INTO T1 VALUES
200
)
-
Violate the constraint
IF
@@
Error
<>
0
Begin Rollback Tran1 Return
-
1
end
Commit Tran1 Return
1
-
OK here
End
Create Proc Outertranas
Begin Begin Tran2 Insert INTO T1 VALUES
2
)
-
OK
NOW
Declare @Invokecode
int
Exec @Invokecode
=
Innrtrans
IF
(@Invokecode
= -
1
) Rollback Tran Tran2
Else
Commit TRAN TRAN2
End
SELECT
*
From T1 Exec Innertrans
-
Worked OK EXEC OUTRAN
-
Does
NOT
Work
-
HOW
TO
Modify? ALTER Proc Innertrans
AS
Begin Begin Tran1 Save TRAN TTT
-
SavePoint Here Insert Into T1 VALUES
1
)
-
This stay be ok
IF
@@
Error
<>
0
Begin Rollback Tran TTT Commit Tran1 Return
-
1
end
INSERT INTO T1 VALUES
200
)
-
Violate the constraint
IF
@@
Error
<>
0
Begin Rollback Tran TTT Commit Tran1 Return
-
1
end
Commit Tran1 Return
1
-
OK here
End
Posted on 2005-03-12 15:00 MONTAQUE Reading (1777)
Comments (2)
edit
Collect
HREF = "http://www.cnblogs.com/montaque/services/pingback.aspx" Rel = "pingback" />
comment
#
Re: Question of nested transactions 2005-03-12 15:42
Lostinet
I am used to judge @@ TRANCOUNT and then choose Begin Transaction or Save Transaction
E.g:
- Begin Transaction
SELECT @@TRANCOUNT
Declare @rollbackit bit
Declare @savepoint nvarchar (36)
IF @@ TRANCOUNT = 0 Begin Transaction Else Begin Set @ SavePoint = NewId () Save Transaction @savePoint End
SELECT @savepoint
--DO Some Thing
IF @ rollbackit = 1
Begin
IF @savepoint is null rollback transaction else rollback transaction @savepoint
End
Else
Begin
IF @savepoint is null commit transaction
End
SELECT @@TRANCOUNT
- ROLLBACK TRANSACTION
If you need nested in the same name range, then if @ SavePoint1, @ savepoint2 .... #
Re: Question of nested transactions 2005-03-12 15:42
Lostinet
I am used to judge @@ TRANCOUNT and then choose Begin Transaction or Save Transaction
E.g:
- Begin Transaction
SELECT @@TRANCOUNT
Declare @rollbackit bit
Declare @savepoint nvarchar (36)
IF @@ TRANCOUNT = 0 Begin Transaction Else Begin Set @ SavePoint = NewId () Save Transaction @savePoint End
SELECT @savepoint
--DO Some Thing
IF @ rollbackit = 1
Begin
IF @savepoint is null rollback transaction else rollback transaction @savepoint
End
Else
Begin
IF @savepoint is null commit transaction
End
SELECT @@TRANCOUNT
- ROLLBACK TRANSACTION
If you need nested in the same name, it is @ savepoint1, @ savepoint2 ...
