SQL Server Myth: There is a real "transaction nesting" in SQL Server

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.

In practice, however, when the inner transaction is rolled back, the entire inner 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.

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?

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:

ROLLBACK TRAN Innertran;

Go

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, from Books Online, I can only use the name of the external transaction or leave the transaction name blank to rollback the code as follows:

ROLLBACK TRAN;

Go

SELECT @ @TRANCOUNT, COUNT (*) from T1;

Go

Now I get the result is 0 and 0. As the Books Online puts it, the rollback operation rolls back the external transaction and sets 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.