Can You Nest Transactions in SQL Server?

 dans Sites anglophones  étiqueté t-sql par

To find out, let’s set up a simple status log table:

DROP TABLE IF EXISTS dbo.StatusLog; CREATE TABLE dbo.StatusLog (TimeItHappened DATETIME2 PRIMARY KEY CLUSTERED, Step VARCHAR(20)); GO

And then let’s try a two-part transaction:

BEGIN TRAN INSERT INTO dbo.StatusLog VALUES (GETDATE(), ‘Step 1′); WAITFOR DELAY ’00:00:01’; BEGIN TRAN INSERT INTO dbo.StatusLog VALUES (GETDATE(), ‘Step 2’); SELECT @@TRANCOUNT AS OpenTransactions;

Right now, SQL Server shows that I have 2 open transactions:

What Happens If I Roll Back?

But what does “2 open transactions” mean, really? If I do a rollback, what gets rolled back? Let’s find out:

ROLLBACK; SELECT @@TRANCOUNT AS OpenTransactions; SELECT