SQL nested transaction learning notes

Modify the following content according to this official document: (vsql.105). The use scenario or purpose of aspx nested transactions is mainly to call the Stored Procedure containing transactions. Otherwise, you do not need to use nested transactions. The following example shows the purpose of nested transactions. Transactions are included in the TransProc stored procedure, and transactions are started in other code to call TransProc and

Modify the following content according to this official document: (v = SQL .105). The use scenario or purpose of aspx nested transactions is mainly to call the Stored Procedure containing transactions. Otherwise, you do not need to use nested transactions. The following example shows the purpose of nested transactions. Transactions are included in the TransProc stored procedure, and transactions are started in other code to call TransProc and

Modify the following content according to this official document: (v = SQL .105). aspx

The use scenario or purpose of nested transactions is mainly to call the Stored Procedure containing transactions. Otherwise, you do not need to use nested transactions.

The following example shows the purpose of nested transactions. The TransProc Stored Procedure contains transactions. In other code, the transaction is started to call TransProc and the transaction is not started to call TransProc.

SET QUOTED_IDENTIFIER OFF; goset nocount off; GOUSE AdventureWorks2008R2; TestTrans (Cola, Colb CHAR (3) not null); TransProc (InProcINSERT INTO TestTrans VALUES (@ PriKey, @ CharCol ), @ CharCol) commit transaction InProc; OutOfProc; GO/* Roll back the outer transaction, this willroll back TransProc's nested transaction. outOfProc; GO/* The following SELECT statement shows only rows 3 and 4 arestill in the table. this indicates that the commitof the inner transaction from the first EXECUTE statement ofTransProc was overridden by the subsequent rollback. testTrans; GO

Nested transactions have the following features:

1. the SQL Server database engine ignores the commit of internal transactions, except for reducing @ TRANCOUNT by 1. The true commit or rollback of internal transactions depends on the commit or rollback at the end of the most external transaction. If an external transaction is committed, an internal nested transaction is also committed. If an external transaction is rolled back, all internal transactions are rolled back, regardless of whether internal transactions have been committed separately.

2. For the first 1st transactions, if the internal TRANSACTION is committed (commit transaction or commit work), COMMIT only takes effect for the current TRANSACTION, that is to say, even if transaction_name in commit transaction transaction_name is the name of an external TRANSACTION, this external TRANSACTION will not be committed. The focus of this article is that internal transactions cannot commit external transactions.

3. The @ TRANCOUNT function records the nesting level of the current transaction. @ TRANCOUNT = 0 indicates that it is not in the transaction. If it is equal to 1, it indicates that it is in a transaction. If it is greater than 1, it indicates that it is in a nested transaction. Each begin transaction statement increases @ TRANCOUNT by 1. Each commit transaction or commit work statement deducts @ TRANCOUNT by 1. If there is a ROLLBACK, @ TRANCOUNT is equal to 0.

4. The transaction_name of the rollback transaction statement can only be the TRANSACTION name of the most external TRANSACTION. The internal TRANSACTION name is invalid. ROLLBACK statements with the most external transaction name or without any name roll back all nested transactions, including the most external transactions. @ TRANCOUNT equals 0.

5. For article 4th, it is particularly worth noting that, although "the ROLLBACK statement will roll back all nested transactions, including the most external transactions ", however, the premise here is to perform ROLLBACK at the outermost layer. After my own experiments, If you execute a ROLLBACK with the most external transaction name or a ROLLBACK without any name in an internal transaction, only the current internal transaction and the executed external transaction statement are rolled back. The subsequent outer transaction of the internal transaction will continue to be executed and the data can be modified successfully, however, in subsequent external transactions, all rollback and commit will not work, and an error message is prompted, because @ TRANCOUNT is already equal to 0, and the database engine cannot find the corresponding begin transaction. You can verify it by using the bad code example in the following code example.

For the following best code demonstration instructions, refer to the following blog:

NORTHWIND; (N, N) TESTTRAN; create table testtran (COLA, colb char (3); OUTERTRAN; begin transaction INNER1; begin transaction INNER2;); (, 16, 1) INNER2;); INNER1;); OuterTran; TESTTRAN (NOLOCK); SELECT @ Trancount;

Next let's take a look at how to handle internal transaction errors (when Rollback, Commit, and error transfer)

NORTHWIND; (N, N) TESTTRAN; create table testtran (COLA, colb char (3); OUTERTRAN; begin transaction INNER1; begin transaction INNER2;); INNER2; INNER2; INNER2;); INNER1; INNER1; INNER1;); (, 16, 1) OUTERTRAN; TESTTRAN (NOLOCK)

Considering SP calls, @ ERROR should be returned at the end of SP development for the caller to check. In addition, check @ Trancount. Sometimes the result looks correct. However, if @ Trancount is not equal to 0, the code is faulty.