Encounter nested transactions-notes

Related SQL statements:

Begin tran

Save tran

Commit tran

Rollback tran

Nested transaction example:

Begin Tran tran1

Begin Tran tran2

Commit 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 can be used to roll back the transaction name at the outermost layer. 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!
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 that nested transactions are not very useful. 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.
Pay special attention to the submission. 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?