SQL Server misunderstanding 30-Day26-SQL Server in the real "transaction nesting"

This series of articles I saw in the sqlskill.com's PAUL blog, many misunderstandings are more typical and representative, the original from the T-SQL Tuesday #11: Misconceptions about... EVERYTHING !!, After translation and arrangement by our team, it is published on AgileSharp. Hope to help you.

 

Misunderstanding #26: there is a real "transaction nesting" in SQL Server"

Error

 

Nested transactions do not seem to allow transaction nesting as their syntax shows. I really don't know why some people write code like this. The only thing I can think of is that a buddy sneer at the SQL Server community and then writes the code like this: "play with you ".

Let me explain in more detail that SQL Server allows you to enable nesting of another transaction in one transaction, SQL Server allows you to submit this nested transaction, and you can also roll back this transaction.

However, nested transactions are not really "nested". For nested transactions, SQL Server can only identify outer transactions. Nested transactions are one of the culprit in the abnormal growth of logs because developers think that the internal transaction is only rolled back the internal transaction.

But in fact, when you roll back the internal transaction, the entire internal transaction will be rolled back, not just the internal transaction. This is why I say that nested transactions do not exist.

Therefore, developers should never nest transactions. Transaction Nesting is evil.

If you don't believe what I said, you will believe it through the example below. After the database and table are created, each record will increase the Log Size by 8 KB.

Create database NestedXactsAreNotReal;
GO
USE NestedXactsAreNotReal;
GO
Alter database NestedXactsAreNotReal set recovery simple;
GO
Create table t1 (c1 int identity, c2 CHAR (8000) DEFAULT 'A ');
Create clustered index t1c1 ON t1 (c1 );
GO
Set nocount on;
GO

 

Test #1: Only internal transactions are rolled back when internal transactions are rolled back?

Begin tran OuterTran;
GO

Insert into t1 DEFAULT Values;
GO 1000

Begin tran InnerTran;
GO

Insert into t1 DEFAULT Values;
GO 1, 1000

SELECT @ TRANCOUNT, COUNT (*) FROM t1;
GO

 

As you can see, the result is 2 and 2000. Below I will roll back the internal transaction. We guess we should roll back only 1000 items, but in fact you will get the following results:

Rollback tran InnerTran;
GO

Message 6401, level 16, state 1, 2nd rows
InnerTran cannot be rolled back. The transaction or save point with this name cannot be found.

 

Well, from Books Online, I can only use the name of an external transaction or leave the transaction name blank for rollback. The Code is as follows:

Rollback tran;
GO

SELECT @ TRANCOUNT, COUNT (*) FROM t1;
GO

 

Now, the result is 0 and 0. As Books Online said, This rollback operation rolls back external transactions and sets the global variable @ TRANCOUNT to 0. All modifications to the transaction are rolled back. If you want to perform partial ROLLBACK, you can only use save tran and rollback tran.

Test #2: will the changes of the internal transaction be saved after the internal transaction in the nested transaction is committed?

Begin tran OuterTran;
GO

Begin tran InnerTran;
GO

Insert into t1 DEFAULT Values;
GO 1, 1000

Commit tran InnerTran;
GO

Select count (*) FROM t1;
GO

 

As I expected, 1000 is returned. This indicates that the internal transaction commit will be modified to the disk. However, if external transactions are rolled back at this time, internal transactions should not be rolled back...

Rollback tran OuterTran;
GO

Select count (*) FROM t1;
GO

 

However, after running the preceding query, the result is 0, which means that the rollback of the external transaction will affect the internal transaction.

 

Test #3: At least let me clear the logs for internal transactions that commit nested transactions.

Before starting this test, I first cleared the log and ran the following code:

Begin tran OuterTran;
GO

Begin tran InnerTran;
GO

Insert into t1 DEFAULT Values;
GO 1, 1000

Dbcc sqlperf ('logspace ');
GO

Expected result:

Next, run CheckPoint after the transaction is committed (logs will be truncated for databases in simple recovery mode). The result is as follows:

Commit tran InnerTran;
GO

CHECKPOINT;
GO

Dbcc sqlperf ('logspace ');
GO

 

 

We found that the use of logs is not reduced, because the logs are written into the CheckPoint record (for details, see: How do checkpoints work and what gets logged ). Commit an internal transaction will not cause the log to be cleared, because the external transaction rollback will also be rolled back together with the internal transaction (note: therefore, this part of VLF will never be marked with reusable before external transaction commit ). Therefore, this part of the log will never be truncated before the external transaction is committed. To prove this, I commit an external transaction and then read the log:

Commit tran OuterTran;
GO

CHECKPOINT;
GO

Dbcc sqlperf ('logspace ');
GO

 

The log usage percentage is greatly reduced.

For nested transactions --- Just Say no. (You can use this sentence as a benefit from an enthusiastic guy from SQLSkill.com)