SQL Server misunderstanding on the last 30 days: SQL Server has a true "transaction nesting" in 26th days"

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.
However, when an internal transaction is rolled back, the entire transaction will be rolled back, rather than 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.

Copy codeThe Code is as follows:
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?
Copy codeThe Code is as follows:
Begin tran OuterTran;
GO
Insert into t1 DEFAULT Values;
GO 1, 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:
Copy codeThe Code is as follows:
Rollback tran InnerTran;
GO

Copy codeThe Code is as follows:
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:
Copy codeThe 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?
Copy codeThe Code is as follows:
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...
Copy codeThe Code is as follows:
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:
Copy codeThe Code is as follows:
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:
Copy codeThe Code 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:
Copy codeThe Code is as follows:
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 :-)