Nested transaction mechanism

Document directory

• 2.1 How does the nested transaction roll back the outermost transaction affect the internal transaction?
• 2.2 How about nested transactions roll back internal nested transactions?
• Save transaction rollback point-you can select to commit or roll back internal nested transactions.

Favorites:
Today, we will focus on the use of nested transactions in sqlserver. The code can describe most of the problems and look at the code. 1. test the principle of committing nested transactions[C-sharp]
View plaincopy

1. Print 'trancount before transaction: '+ Cast (@ trancount as char (1 ))
3. Begin tran
4. Print 'after first begin Tran: '+ Cast (@ trancount as char (1 ))
6. Begin tran
7. Print 'after second begin Tran: '+ Cast (@ trancount as char (1 ))
8. Commit tran
10. Print 'after first commit Tran: '+ Cast (@ trancount as char (1 ))
11. Commit tran
13. Print 'after second commit Tran: '+ Cast (@ trancount as char (1 ))

Result:

Trancount before transaction: 0
After first begin Tran: 1
After second begin Tran: 2
After first commit Tran: 1
After second commit Tran: 0

We can conclude that:

1. You can see that every begin Tran statement increases @ trancount by 1;

2. Each commit Tran statement reduces @ trancount by 1;

3. As mentioned above, a 0-value @ trancount indicates that no transaction is opened;

4. Therefore, the transaction that ends when the value of @ trancount is reduced from 1 to 0 occurs when the outermost transaction is committed.

2. nested transactions roll back 2.1 what is the impact of nested transactions on internal transactions to roll back the outermost transactions?

Let's take a look at the Code:

[C-sharp]
View plaincopy

1. -- Create a temporary table
2. Create Table # testtrans (COLA int primary key,
3. COLB varchar (20) not null );
4. /* External transaction */
5. Begin transaction outofproc;
6. -- Internal transactions
7. Begin transaction inproc
8. Insert into # testtrans values (1, 'aaa ');
9. Commit transaction inproc;
11. /* Roll Back external transactions and internal transactions */
12. Rollback transaction outofproc;
14. /* No data. 1 indicates that the transaction rollback at the outermost layer will roll back all the transactions in it */
15. Select * from # testtrans;
17. Drop table # testtrans

Result: No data exists.

Now it seems that, no matter whether the data is submitted or not, as long as the outermost layer is rolled back, it will cause all internal Nested classes to roll back.

2.2 How about nested transactions roll back internal nested transactions?

Let's look at another piece of code:

[C-sharp]
View plaincopy

1. -- Create a temporary table
2. Create Table # testtrans (COLA int primary key,
3. COLB varchar (20) not null );
4. /* External transaction */
5. Begin transaction outofproc;
6. -- Internal transactions
7. Begin transaction inproc
8. Insert into # testtrans values (1, 'aaa ');
9. Rollback transaction inproc;
11. -- Internal transaction 2
12. Begin transaction inproc2
13. Insert into # testtrans values (2, '20140901 ');
14. Commit transaction inproc2;
16. /* Submit external transactions */
17. Commit transaction outofproc;
19. /* Error: internal transactions are not rolled back, and internal transactions cannot be rolled back. "The transaction or storage point with this name cannot be found. "*/
20. Select * from # testtrans;
22. Drop table # testtrans

Result:

Exception information:

(One row is affected)
Message 6401, level 16, state 1, 9th rows
Inproc cannot be rolled back. The transaction or save point with this name cannot be found.

(One row is affected)

(2 rows affected)

We can see that rollback transaction inproc is incorrect. The reason is that the Restore Point inproc is not saved. The Code should be changed to the following (For details, refer ):

[SQL]
View plaincopy

1. Begin transaction inproc
2. Save Tran inproc;
3. Insert into # testtrans values (1, 'aaa ');
4. Rollback transaction inproc;

Save Tran inproc (save Tran inproc) is saved before the operation. During rollback, sqlserver knows where to roll back.

3. Transaction Principle

Before proceeding, you must understand that the global variable @ trancount can determine whether there are opened transactions and their nested depth.

 

The committed transaction cannot be undone or rolled back.

When no opened transaction exists, @ trancount equals 0.

Run the begin Tran [tranname] statement to increase @ trancount by 1.

Run the commit Tran [tranname] statement to reduce @ trancount by 1.

Executing rollback Tran rolls back the entire transaction and sets @ trancount to 0.

 

There are two situations when executing the "rollback Tran tranname" statement:

If ("Save Tran tranname" was used before tranname) @ trancount value unchanged

Otherwise, @ trancount is reduced by 1.

 

Test code:

[SQL]
View plaincopy

1. -- Create a temporary table
2. Create Table # testtrans (COLA int primary key,
3. COLB varchar (20) not null );
5. Select @ trancount 'external transaction not opened ';
7. /* External transaction */
8. Begin transaction outofproc;
9. Select @ trancount 'open external transactions ';
11. -- Internal transactions
12. Begin transaction inproc
14. Select @ trancount 'open internal transaction 1 ';
16. Save Tran inproc;
18. Select @ trancount 'Save internal transaction 1 ';
19. Insert into # testtrans values (1, 'aaa ');
20. Rollback transaction inproc;
22. Select @ trancount 'roll back internal transaction 1 ';
24. -- Internal transaction 2
25. Begin transaction inproc2
26. Insert into # testtrans values (2, '20140901 ');
28. -- Internal transaction 21
29. Begin transaction inproc21
30. Select @ trancount 'start internal transaction 21 ';
31. Insert into # testtrans values (3, '123 ');
32. Commit transaction inproc21;
33. Select @ trancount 'submit internal transaction 21 ';
35. Commit transaction inproc2;
36. Select @ trancount 'commit internal transaction 2 ';
38. /* Submit external transactions */
39. Commit transaction outofproc;
41. Select @ trancount 'commit external transactions ';
43. Select * from # testtrans;
45. Drop table # testtrans

Result:

Others: Save the transaction rollback point-you can select to commit or roll back internal nested transactions.

Ideas

Check the value of @ trancount to determine whether a transaction needs to be started. If @ trancount is greater than 0, a new transaction is not started. You only need to store the rollback locations. Otherwise, a new transaction is started. The operations are as follows:

 

1. Save the rollback point:

[JavaScript]
View plaincopy

1. Declare @ trancount int -- commit, rollback only controls the Stored Procedure
2. Set @ trancount = @ trancount
3. If (@ trancount = 0)/* determines the transaction count, and determines whether to use the save point or create a transaction */
4. Begin Tran curtran -- the current transaction point. rollback and commit start from here.
5. Else
6. Save Tran curtran

2. Specify a "rollback point" for rollback ":

[SQL]
View plaincopy

1. If (@ error <> 0 or @ pay_way_error = 0) -- @ pay_way_error determines whether rollback is required.
2. Begin
3. Rollback Tran curtran
4. Set @ result =-1 -- Exception
5. End

[SQL]
View plaincopy

1. If (@ error <> 0 or @ pay_way_error = 0) -- @ pay_way_error determines whether rollback is required.
2. Begin
3. Rollback Tran curtran
4. Set @ result =-1 -- Exception
5. End

--- Http://blog.csdn.net/xiaoyong322/article/details/7355426