Methods of server nested transaction processing

Source Document Http://wenku.baidu.com/link?url=yUH8Yhb8isIvJb8A7c0Hv_ ktfslt-jtvrqd2e2tgmfwzwgwqkjffb1txv5zr1fmp52s9cpxuhn7b6fmwybz28wryg-4-awobj3z7mkiwf6g
A Transaction definition: A transaction is the smallest unit of work for SQL Server, and each SQL statement is a transaction, specifying the unit of work as a transaction must have four properties, i.e. acid
Standard:
Atomicity: A transaction must be an atomic unit of work; for its data to be modified, it is either all executed or not executed.
Consistency: When a transaction is complete, it must keep all data in a consistent state.
Isolation: Changes made to the database by the transaction are not visible to other transactions,
Other transactions only see state data before or after the transaction commits, and other transactions do not view the data in the intermediate state.
Persistence: After a transaction completes, its effect on the system is permanent.
Two Nested transactions

SQL Server supports nested transactions: That is, a new transaction can be started before the previous transaction is completed, and only the outer COMMIT TRAN statement will cause permanent changes to the database.
Try executing the following statement:
BEGIN TRAN
BEGIN TRAN
ROLLBACK TRAN
ROLLBACK TRAN
Execution results: server: Msg 3903, Level 16, State 1, line 5
ROLLBACK TRANSACTION
The request does not have a corresponding
BEGIN TRANSACTION.
Cause Analysis: 1)
SQL Server
Record the number of transactions opened per connection in global variables
@ @trancount, like a counter, each BEGIN Tran statement causes @ @trancount to increment by 1, and each commit Tran statement causes @ @trancount to decrement by 1, with only the outermost commit Tran (when
@ @trancount =1) affects the changes in the database and is no longer stored in the transaction log. 2)
Unfortunately, regardless of how deep the nested transaction hierarchy is, the rollback TRAN statement without the savepoint will directly set the @ @trancount to 0.
Solution Ideas:
1)
With named transactions: You can name a transactional statement, let's first try to use a named transaction directly to resolve the
The problem of the only one:
Execute statement:
BEGIN TRAN A
BEGIN TRAN B

ROLLBACK TRAN B
ROLLBACK TRAN A
Execution Result: server: Msg 6401, Level 16, State 1, line 3 cannot roll back B. No transaction or savepoint was found for that name.
Cause analysis: It's a beautiful misunderstanding. SQL Server records only the outer transaction name, and an error occurs if an attempt is to roll back any of the inner transactions.

2) using the Save point:
SQL Server
Provides a mechanism for rolling back part of a transaction:
Save Tran, it will not be on @ @trancount
has no effect but marks the point at which the transaction can be reached when it is rolled back. --Defines whether a flag is a nested transaction

DECLARE

@nestedFlag BIT

IF (@ @trancount >0)
BEGIN
--
is a nested transaction: Use SavePoint to avoid nesting again
SET @nestedFlag =1
SAVE TRAN TestA
END
ELSE
BEGIN
--
Not a nested transaction: Opening a transaction
SET
@nestedFlag =0
BEGIN TRAN TestA
END
--
Perform business operations,
Roll back the transaction point if an error occurs and return immediately
IF (@ @error <>0)
BEGIN
ROLLBACK TRAN TestA
RETURN 0
END
--
Commit if not nested transactions
IF (@nestedFlag =0)
BEGIN
COMMIT TRAN TestA
END

-

Methods of server nested transaction processing