Myth #26: There is a real "transaction nesting" in SQL Server
Error
Nested transactions do not appear to allow transaction nesting as their syntax behaves. I don't know why anyone would write code like that, and the only thing I could think of was a guy who scoffed at the SQL Server community and wrote the code: "Play with You."
Let me explain in more detail that SQL Server allows you to open another transaction in one transaction, and SQL Server allows you to commit the nested transaction and also allows you to rollback the transaction.
However, nested transactions are not really "nested", and SQL Server can only recognize the outer transactions for nested transactions. Nested transactions are one of the main culprits of log abnormal growth because developers assume that the inner transaction is rolled back simply by rolling back the inner transaction.
But in fact when the inner transaction is rolled back, the entire transaction is rolled back, not just the inner layer. That's why I say nested transactions do not exist.
So as a developer, never nest transactions. Transaction nesting is evil.
If you don't believe what I'm saying, you'll believe it by following the example. After you create the database and tables, each record will cause the log to increase by 8K.
Copy Code code 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: Roll back internal transactions only when rolling back internal transactions?
Copy Code code as follows:
BEGIN TRAN Outertran;
Go
INSERT into T1 DEFAULT Values;
Go 1000
BEGIN TRAN Innertran;
Go
INSERT into T1 DEFAULT Values;
Go 1000
SELECT @ @TRANCOUNT, COUNT (*) from T1;
Go
You can see that the results are 2 and 2000, and I'm going to roll back and forth inside the transaction, according to our conjecture should only roll the 1000 bar, but in fact you will get the following results:
Copy Code code as follows:
ROLLBACK TRAN Innertran;
Go
Copy Code code as follows:
Message 6401, Level 16, State 1, line 2nd
The Innertran cannot be rolled back. The transaction or save point for the name could not be found.
Well, by Books OnlineSee, I can only use the name of an external transaction or leave the transaction name blank to roll back, as follows:
Copy Code code as follows:
ROLLBACK TRAN;
Go
SELECT @ @TRANCOUNT, COUNT (*) from T1;
Go
Now I get the result is 0 and 0. As Books OnlineSaid, the rollback operation rolled the external transaction back and set the global variable @ @TRANCOUNT to 0. All changes in the transaction are rolled back and only the save TRAN and rollback TRAN are used if you want to partially rollback.
Test #2: Will internal transactions be saved after internal transaction commits in a nested transaction?
Copy Code code as follows:
BEGIN TRAN Outertran;
Go
BEGIN TRAN Innertran;
Go
INSERT into T1 DEFAULT Values;
Go 1000
COMMIT TRAN Innertran;
Go
SELECT COUNT (*) from T1;
Go
As I expected, the result was 1000. This means that internal transaction commits are modified to disk. However, if the external transaction is rolled back, then the internal transaction should not be rolled back ...
Copy Code code as follows:
ROLLBACK TRAN Outertran;
Go
SELECT COUNT (*) from T1;
Go
However, after running the query, the result is 0, which means that the rollback of external transactions can affect internal transactions.
Test #3: Internal transactions that commit nested transactions can at least let me clear the log.
Before starting this test I first cleared the log and then ran the following code:
Copy Code code as follows:
BEGIN TRAN Outertran;
Go
BEGIN TRAN Innertran;
Go
INSERT into T1 DEFAULT Values;
Go 1000
DBCC sqlperf (' logspace ');
Go
Get the result:
I'll run checkpoint after I commit the transaction (the database for the simple recovery model will truncate the log) and get the result:
Copy Code code as follows:
COMMIT TRAN Innertran;
Go
CHECKPOINT;
Go
DBCC sqlperf (' logspace ');
Go
We found that the use of the log is not reduced because the log is written to the checkpoint record (see for details: How do checkpoints work and what gets logged）。 Committing an internal transaction does not cause the log to be purged, because external transactions are rolled back together with internal transactions (translator Note: So this part of the VLF will never be labeled reusable before the external transaction commits). So this part of the log will never be truncated until the external transaction is committed. To prove this, I submit an external transaction and then look at the log:
Copy Code code as follows:
COMMIT TRAN Outertran;
Go
CHECKPOINT;
Go
DBCC sqlperf (' logspace ');
Go
Well, the percentage of log usage has dropped dramatically.
For nested transactions---Just Say No. (This sentence you can take as a warm guy from sqlskill.com to the welfare:-)
