Rollback of nested transactions

 

Error Handling of nested transactions and transaction retention points

 

 

For nested transactions.
1. Start external transactions, start internal transactions, and try catch
Internal error: if an internal transaction error occurs, internal and external transactions are all rolled back, and all operations before external rollback do not exist, but subsequent operations continue to be performed.
External error: if an error occurs, internal and external tasks are all rolled back. Operations before external rollback do not exist, but subsequent operations continue.
Note: If the internal transaction cannot contain the transaction name, if an internal error occurs, all the transactions in the session will be rolled back and an exception is reported.

2. Start external transactions, start internal transactions, and no try catch internally
Internal error: if an internal transaction error occurs, internal and external transactions are all rolled back, and all operations before external rollback do not exist, but subsequent operations continue to be performed.
External error: if an internal transaction error occurs, internal and external transactions are all rolled back, and all operations before external rollback do not exist, but subsequent operations continue to be performed.

3. Start external transactions. internal transactions are not supported, but try catch exists.
Internal error: the external transaction is submitted normally, the external transaction does not enter rollback, and the record is executed normally after an internal error occurs. During internal operations, the try operation is normal before the error occurs. The try operation is not executed after the operation, and then enters the Catch Block to perform the operation.
External error: all internal and external events are rolled back. All operations before external rollback do not exist, but subsequent operations continue.

4. Start external transactions, internal transactions are not available, but try catch is not available.
Internal error: if an internal transaction error occurs, internal and external transactions are all rolled back, and all operations before external rollback do not exist, but subsequent operations continue to be performed.
External error: if an internal transaction error occurs, internal and external transactions are all rolled back, and all operations before external rollback do not exist, but subsequent operations continue to be performed.

5. External transactions are not supported and internal transactions are initiated, but try catch exists.
Internal error: the external operation is executed normally. All the internal rollback operations are rolled back before the internal rollback operation, and subsequent operations are executed normally.
External error: operations before an error operation are not rolled back. operations after an error are not executed. internal transactions are not rolled back when you jump into the Catch Block.

6. External transactions are not supported and internal transactions are initiated, but no try catch.
Internal error: the external operation is executed normally and all rollback operations are performed before the internal rollback operation. Because no Catch Block exists, all external operations are performed.
External error: internal transactions are committed normally. Only when a record fails, other operations are executed normally, but serious errors are reported.

For transaction retention points
Only the SAVE and rollback operations are supported for transaction storage points. When an external call is used for internal storage points, internal problems do not affect external transactions, and external operations are executed normally. When an external operation fails, all internal operations are rolled back.

For example, start external transactions, start internal storage points, and try catch exists in both internal and external tables.
Internal error: the external operation is normal, and the catch is not entered. The internal transaction rolls back to the storage point and continues to be executed later.
External error: if an exception occurs before an external transaction is saved, all external and internal operations are rolled back. If an exception occurs before the external transaction is saved, because the transaction has been committed at the save point, the external rollback cannot find the corresponding transaction point.

 

 

 

 

Http://blog.sina.com.cn/s/blog_6003801e0100drus.html

 

Nesting of transactions

 

Print 'trancount before transaction: '+ Cast (@ trancount as char (1 ))

Begin tran

Print 'after first begin Tran: '+ Cast (@ trancount as char (1 ))

Begin tran

Print 'after second begin Tran: '+ Cast (@ trancount as char (1 ))

Commit tran

Print 'after first commit Tran: '+ Cast (@ trancount as char (1 ))

Commit tran

Print 'after second commit Tran: '+ Cast (@ trancount as char (1 ))

In the result, we can see that every begin Tran statement increases @ trancount by 1 and every commit Tran statement reduces it by 1. As mentioned above, a 0 @ trancount indicates that no transaction is opened. Therefore, the transaction that ends when the value of @ trancount is reduced from 1 to 0 occurs when the outer transaction is committed. Therefore, each internal transaction must be committed. Because the transaction starts with the first Tran in TRAN and ends with the last commit TranThe transaction at the outermost layer determines whether internal transactions are fully committed. If the outermost transaction is not committed, the nested transaction is not committed.

Type and execute the following batches to check what happens during transaction rollback:

Begin tran

Print 'after 1st begin Tran: '+ Cast (@ trancount as char (1 ))

Begin tran

Print 'after 2nd begin Tran: '+ Cast (@ trancount as char (1 ))

Begin tran

Print 'after 3rd begin Tran: '+ Cast (@ trancount as char (1 ))

Update data1

Set value1 = 1000000

Where id = 1

Commit tran

Print 'after first commit Tran: '+ Cast (@ trancount as char (1 ))

Rollback tran

Print 'after rollback Tran: '+ Cast (@ trancount as char (1 ))

 

Select * From data1

Where id = 1;

In this example, the data table data1 is updated in a nested transaction, which is committed immediately. Then rollback Tran is executed. Rollback Tran reduces @ trancount to 0 and rolls back the entire transaction and its nested transactions, whether or not they have been committed. Therefore, the updates made in the nested transaction are rolled back, and the data has not changed.

 

Always remember,In nested transactions, only the outermost transaction determines whether to commit internal transactions..Each commit Tran statement is always applied to the last executed Tran in tran. Therefore, for each commit Tran, a commit Tran must be called to commit transactions. Rollback Tran statements always belong to the outermost transactions, and therefore they always roll back the entire transaction regardless of how many nested transactions are opened. Therefore, managing nested transactions is complicated. If each nested stored procedure starts a transaction in itself, most of the nested transactions will occur in the nested stored procedure. To avoid nested transactions, check the value of @ trancount at the beginning of the process to determine whether a transaction needs to be started. If @ trancount is greater than 0, because the process is already in a transaction and the called instance can roll back the transaction when an error occurs.

 

 

 

Rollback in Stored Procedures and triggers if the value of @ trancount is different from that of process execution when the stored procedure is completed, a 266 information error is generated. This error is not generated by the same condition in the trigger.
　　
When a stored procedure is called, if @ trancount is 1 or larger and the process executes the rollback transaction or rollback work statement, error 266 is returned. This is because rollback rolls back all unfinished transactions and reduces @ trancount to 0, which is smaller than the call process.
　　
If rollback transaction is triggered:
　　
All data modifications made to that point in the current transaction will be rolled back, including the changes made by the trigger.
　　
　　
The trigger continues to execute all other statements after the rollback statement is executed. If any statement in these statements modifies data, these modifications are not rolled back. Executing other statements does not trigger nested triggers.
　　
　　
In batch processing, all statements after the statement that triggers the trigger are not executed.
　　
　　
The rollback in the trigger closes and releases all the declarations and opened cursors in the batch processing of statements containing the trigger. This includes the declared and opened cursor in the stored procedure called by the batch processing of the trigger. The cursor declared in the batch processing before initiating the trigger's batch processing will only be closed, but the static or insensitive cursor will not be closed under the following conditions:
Cursor_close_on_commit is set to off.
　　
　　
A static cursor is either a synchronous cursor or a fully filled asynchronous cursor.
When a trigger is executed, the trigger operation always seems to have an unfinished transaction working. If the statement that triggers the trigger is in a implicit or explicit transaction, this will certainly happen. This is also true in automatic submission mode. When a statement starts to run in automatic submission mode, if an error occurs, an implicit begin transaction statement will allow you to resume all modifications generated by this statement. This implicit transaction has no impact on other statements in the batch processing, because when the statement is completed, the transaction is either committed or rolled back. However, when the trigger is called, the implied transaction will still be valid.
　　
This means that as long as the begin transaction statement is issued in the trigger, a nested transaction is actually started. When a nested transaction is rolled back, the nested begin transaction statement is ignored. The rollback transaction in the trigger always rolls back all the begin transaction statements issued by the trigger. Rollback rolls back to the external begin transaction.
　　
To perform partial rollback in a trigger, you must use the save transaction statement even if the call is always in the automatic submission mode. The following triggers clarify this:
　　
Create trigger testtrig on testtab for update
Save transaction myname
Insert into testaudit
Select * From inserted
If (@ error <> 0)
Begin
Rollback transaction myname
End
　　
This also affects the commit transaction statement after the begin transaction statement in the trigger. Because begin transaction starts a nested transaction, the subsequent commit statement only applies to this nested transaction. If you execute the rollback transaction statement after the commit statement, the rollback will always roll back to the most external begin transaction. The following triggers clarify this:
　　
Create trigger testtrig on testtab for update
Begin transaction
Insert into trigtarget
Select * From inserted
Commit transaction
Rollback transaction
　　
This trigger will never be inserted in the trigtarget table. Begin transaction always starts a nested transaction. Commit transaction only commits nested transactions, while the following rollback transaction rolls back to the most external begin transaction.

 

 

 

Http://www.cnblogs.com/yangpei/archive/2010/10/07/1894408.html

 

Nested transactions

 

 

 

 

Related SQL statements:

 

 

 

 

 

 

 

 

Rollback can roll back a transaction storage point (save Tran transave1), such as rollback Tran transave1. However, it should be clear that rollback of transaction storage points does not reduce the number of transactions @ trancount, you have nested several transactions, but there are still several transactions.
Note that if you create a transaction tran1 in the parent stored procedure and then execute rollback Tran in the sub-stored procedure, the sub-stored procedure throws an exception!Transactions can only be created, rolled back, and committed in the same stored procedure. They cannot be separated from each other in different stored procedures.
Based on the above features, I personally think nested transactions do not play a major role,The internal processing of SQL is actually processing the transaction point at the outermost layer.. SQL throws transaction-related exceptions instead of code issues, but reminds us to pay attention to transaction control. We only need to use try catch to catch relevant exceptions. We only need to ensure that the designed transaction point can be rolled back or committed normally.

Solution 1:

Try catch exceptions

Solution 2:

If a transaction already exists, no internal transaction is created. I think the SQL transaction exception reminder is to tell you that the transaction cannot be put anywhere.

If @ trancount = 0

Begin tran

 

Try catch considerations:

The SQL statement does not contain try catch. Even if an exception occurs, subsequent SQL statements are executed. However, if try catch is added externally, exceptions will be caught, resulting in the subsequent SQL statements not being executed.
Whether SQL exceptions are fatal and common. When try catch is not added, the common statements can continue, but the fatal statements won't go down. This is a major difference from the C # programming language. Once an exception occurs in the programming language, subsequent code will not be executed!
When processing nested transactions, pay special attention to ensuring that the transaction is completely closed or rolled back!

Rollback is easier to control.No matter how many transactions there are, you only need to roll back once.. However, if it is rollback Tran tranname and tranname is not the first level, an exception will occur, which means that the rollback operation is not executed.

 

SubmitPay special attention to it.When you create three transactions in TRAN in Tran, you must commit Tran to commit the transaction three times to ensure that the number of transactions is fully committed.. You can use @ trancount to view the number of current transactions.Once the transaction is not fully committed in the stored procedure, the transaction lock table may occur.! If the process for creating a transaction is destroyed, even if there are uncommitted transactions, it should be destroyed. Is it a rollback?

 

Example of a in transave trancommit tranrollback Tran nested transaction: Begin Tran tran1begin Tran tran2commit Tran can specify a transaction name separately, such as tran1 and tran2. In fact, it has no effect. Even if tran2 is successfully committed, as long as the outer transaction tran1 is rolled back, the data stored in tran2 is rolled back!
Rollback Tran cannot specify a transaction name for rollback!Only rollback Tran or rollback Tran tran1 is allowed.Only the transaction name at the outermost layer can be rolled back.If rollback Tran tran2 is executed, the SQL prompt "cannot roll back tran2. The transaction or save point with the name cannot be found. The error is caused by tran2 being not the outermost transaction.. To sum up, rollback either rolls back all the transactions, or an exception occurs during rollback, and no transaction is rolled back!

 

 

 

Related SQL statements:

 

 

 

 

 

 

 

 

Rollback can roll back a transaction storage point (save Tran transave1), such as rollback Tran transave1. However, it should be clear that rollback of transaction storage points does not reduce the number of transactions @ trancount, you have nested several transactions, but there are still several transactions.
Note that if you create a transaction tran1 in the parent stored procedure and then execute rollback Tran in the sub-stored procedure, the sub-stored procedure throws an exception!Transactions can only be created, rolled back, and committed in the same stored procedure. They cannot be separated from each other in different stored procedures.
Based on the above features, I personally think nested transactions do not play a major role,The internal processing of SQL is actually processing the transaction point at the outermost layer.. SQL throws transaction-related exceptions instead of code issues, but reminds us to pay attention to transaction control. We only need to use try catch to catch relevant exceptions. We only need to ensure that the designed transaction point can be rolled back or committed normally.

Solution 1:

Try catch exceptions

Solution 2:

If a transaction already exists, no internal transaction is created. I think the SQL transaction exception reminder is to tell you that the transaction cannot be put anywhere.

If @ trancount = 0

Begin tran

 

Try catch considerations:

The SQL statement does not contain try catch. Even if an exception occurs, subsequent SQL statements are executed. However, if try catch is added externally, exceptions will be caught, resulting in the subsequent SQL statements not being executed.
Whether SQL exceptions are fatal and common. When try catch is not added, the common statements can continue, but the fatal statements won't go down. This is a major difference from the C # programming language. Once an exception occurs in the programming language, subsequent code will not be executed!
When processing nested transactions, pay special attention to ensuring that the transaction is completely closed or rolled back!

Rollback is easier to control.No matter how many transactions there are, you only need to roll back once.. However, if it is rollback Tran tranname and tranname is not the first level, an exception will occur, which means that the rollback operation is not executed.

 

SubmitPay special attention to it.When you create three transactions in TRAN in Tran, you must commit Tran to commit the transaction three times to ensure that the number of transactions is fully committed.. You can use @ trancount to view the number of current transactions.Once the transaction is not fully committed in the stored procedure, the transaction lock table may occur.! If the process for creating a transaction is destroyed, even if there are uncommitted transactions, it should be destroyed. Is it a rollback?