This month article has nothing to do with Visual Studio. But it is neither unrelated to developers!

This article is about nested transactions into Microsoft SQL Server, and more specifically the rollback of nested transactions. The reason is that I recently read a nice article (an old article) on the CodeProject written by Saumendra Poddar titled “SQL Server Transactions and Error Handling”.

The context

If you write stored procedures to manipulate data into your database, you may come with nested transactions (a transaction hidden in another one).

A sentence in Saumendra’s article hit my attention: “A single ROLLBACK always rolls back the entire transaction”.

I showed the article to a couple of co-workers and we were all intrigued. We then started to think of all our stored procedures that are nested and thought that it would be good for me to really test that sentence!

For those like me who understand better and quicker with an image, here is a good one.

Figure 1: The effect of a Rollback

The test script

My friend Jonathan built a short example in a test database for us to be able to test this sentence without affecting THE database. I have freely modified his script to build one for this article. My full script is available for download attached to this article. Portion of the script are voluntarily not pasted in the article to ease reading.

Creating tables and stored procedures

I create 2 tables for testing purposes using this script:

CREATE TABLE Person(
	ID			INT	NOT NULL IDENTITY(1,1) 
	, FirstName	VARCHAR(50) NULL 
	, LastName	VARCHAR(50) NULL 
	, FullName  AS (ISNULL(FirstName+' ','')+ISNULL(LastName,''))
	, PRIMARY KEY CLUSTERED (ID)
)
CREATE TABLE Phone(
	ID			INT	NOT NULL IDENTITY(1,1) 
	, IDPerson	INT NOT NULL
	, Phone		VARCHAR(20) NOT NULL
	, Comment	VARCHAR(20) NULL
	, PRIMARY KEY CLUSTERED (ID)
)
go
ALTER TABLE [dbo].[Phone]  WITH CHECK ADD  CONSTRAINT [FK_Phone_Person] FOREIGN KEY([IDPerson])
REFERENCES [dbo].[Person] ([ID])
go

The first table will contain some names while the second will contain their phone numbers (a single person can have more than 1 phone number). Also notice that I have created a foreign key between the tables to ensure some reliability.

Once we have the tables, we can create some stored procedures to insert data into these tables.

The first stored procedure starts a transaction and insert into the Phone table before printing a message saying that the data has been inserted successfully. If ever the comment equals FAIL, I roll back the transaction (you understand that this is for demo purpose only). Otherwise, the transaction is committed.

CREATE PROCEDURE PhoneInsert
	@IDPerson	INT ,
	@Phone		VARCHAR(20),
	@Comment	VARCHAR(20)
AS 
BEGIN

	BEGIN TRANSACTION
	
	INSERT INTO Phone ( IDPerson, Phone, Comment )
	VALUES ( @IDPerson, @Phone, @Comment )

	PRINT 'Phone inserted successfully - ' + @Comment

	--a dummy test to fail the transaction
	IF @Comment = 'FAIL'
	BEGIN 
		ROLLBACK TRANSACTION
		PRINT 'Rollbacking Phone ' + @Comment
	END 
	ELSE
		COMMIT TRANSACTION 
	
END

The second stored procedure also starts a transaction. It then inserts data into the Person table and retrieve the identity of the record just inserted to pass to children tables. It then calls the PhoneInsert stored procedure as seen above. Finally, the transaction is committed. All this is included into a TRY … CATCH syntax.

CREATE PROCEDURE PersonInsert
	--person
	@FirstName	VARCHAR(50),
	@LastName	VARCHAR(50),
	--phone 1
	@Phone1		VARCHAR(20),
	@Comment1	VARCHAR(20),
	--phone 2
	@Phone2		VARCHAR(20),
	@Comment2	VARCHAR(20)
AS 
BEGIN

BEGIN TRY

	DECLARE @IDPerson INT
	
	BEGIN TRANSACTION
	
	INSERT INTO Person ( FirstName, LastName )
	VALUES ( @FirstName, @LastName )

	SET @IDPerson = SCOPE_IDENTITY()
	
	EXEC PhoneInsert @IDPerson, @Phone1, @Comment1
	EXEC PhoneInsert @IDPerson, @Phone2, @Comment2

	COMMIT TRANSACTION	
	PRINT 'Person inserted successfully - ' + @FirstName + ' ' + @LastName
	
END TRY
BEGIN CATCH
	if @@trancount > 0
	BEGIN
		--if you don't check, you may get this error
		--Msg 3903, Level 16, State 1, Procedure PersonInsert, Line 34
		--The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.	
		ROLLBACK TRANSACTION 
		PRINT 'Person - Error occured - ROLLBACK'
	END
	ELSE
	BEGIN
		PRINT 'Person - Error occured - No ROLLBACK required'
	END
END CATCH	

END

I want to bring something to your attention: “if @@trancount > 0”. While you will test, try to remove this test. You will see some side effect that is not wanted. If the PhoneInsert call fails, a full roll back is done and when we are back into the PersonInsert, there is no more effective transaction and an error is raised by SQL.

Testing the stored procedures

We are now ready to test these stored procedures.

The first test is one that should properly insert data into both tables. So I use this call:

EXEC PersonInsert @FirstName = 'Joe', 
    @LastName = 'Dalton', 
    @Phone1 = '(555) 123-4567', 
    @Comment1 = 'Jail', 
    @Phone2 = '(555) 123-4444', 
    @Comment2 = 'Home'

If you run this statement, you will see these results in the Messages window of SSMS:

(1 row(s) affected)

(1 row(s) affected)
Phone inserted successfully - Jail

(1 row(s) affected)
Phone inserted successfully - Home
Person inserted successfully - Joe Dalton 

You can also run some Select statements to ensure every in properly inserted:

SELECT * FROM Person
SELECT * FROM Phone

We are now ready to test a scenario that should fail to insert data and roll back. So I use this call (specifically passing FAIL in the comment argument):

EXEC PersonInsert @FirstName = 'Jack', 
    @LastName = 'Dalton', 
    @Phone1 = '(555) 123-9876', 
    @Comment1 = 'Jail', 
    @Phone2 = '(555) 123-4444', 
    @Comment2 = 'FAIL'

This time, the result window will show you these messages:

(1 row(s) affected)

(1 row(s) affected)
Phone inserted successfully - Jail

(1 row(s) affected)
Phone inserted successfully - FAIL
Rollbacking Phone FAIL
Person - Error occured - No ROLLBACK required

We clearly see that the second call to insert data into the Phone table worked properly but because the comment is set to FAIL, the inner transaction is rolled back. Because of the roll back in the nested call, the execution jumps to the begin catch where we should normally roll back the transaction. But because the nested transaction already has rolled back, we have nothing left to do. This is where the “if @@trancount > 0” is important.

Conclusion

The impacts of the roll back from the nested call were not clear to me so I needed to do my own test set.

May I suggest that you check and test your nested calls to see if you handle your transactions properly? The article of Saumendra also introduces you to other techniques (such as error handling, named transactions, save points …) that are worth looking at.

I really think Microsoft SQL developers made a good job here. What about yours?