What are Nested Transactions in SQL Server?

As the name suggests, nested transaction is one that is nested within a transaction. Let’s illustrate this with an example.

Let’s create a table:

CREATE TABLE PERSON
(
	ID INT IDENTITY(1,1),
	FirstName VARCHAR(25),
	LastName VARCHAR(25),
	Gender CHAR(1)
)
SELECT * FROM PERSON

As you can see, we have a table Person with no records.


Let’s initialize a transaction and view its count

BEGIN TRANSACTION  -- 1st Transaction
SELECT @@TRANCOUNT AS TrxCount

Now, insert a record into the table

INSERT INTO PERSON VALUES ('Bill', 'Meyer', 'M')
SELECT * FROM PERSON

Let’s initialize a second transaction and view its count:

BEGIN TRANSACTION -- 2nd Transaction
SELECT @@TRANCOUNT AS TrxCount

Let’s insert another record and then view its contents. Mind you, we are currently in a nested transaction.

INSERT INTO PERSON VALUES ('Lisa', 'Ray', 'F')
SELECT * FROM PERSON

Now, let’s rollback the transaction and do a count immediately afterwards.

--Rollback Transaction
ROLLBACK
SELECT * FROM PERSON
SELECT @@TRANCOUNT AS TrxCount


The rollback rollsback to the outermost transaction that was defined. Another thing to keep in mind is that you don’t need a COMMIT TRANSACTION in order to ROLLBACK.

About Obi Oberoi

I am Technology Aficionado who has a passion for learning, speaking, blogging and hanging out with geeks and like minded people!
This entry was posted in SQL Server. Bookmark the permalink.