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?