How to create an autonomous transaction in SQL Server 2008


I have been asked by many customers and partners, especially those migrating from Oracle, this question: how to create an autonomous transaction in SQL Server? It turns out to be a tricky thing to do since SQL Server doesn’t have built-in autonomous transaction support like Oracle.


An Autonomous transaction is essentially a nested transaction where the inner transaction is not affected by the state of the outer transaction. In other words, you can leave the context of current transaction (outer transaction) and call another transaction (autonomous transaction). Once you finish work in the autonomous transaction, you can come back to continue on within current transaction. What is done in the autonomous transaction is truly DONE and won’t be changed no matter what happens to the outer transaction. To make it easier to understand, here is an example of the described scenario.


BEGIN TRAN OuterTran


      INSERT TABLE1


      BEGIN “AUTONOMOUS” TRAN InnerTran


            INSERT TABLE2


      COMMIT “AUTONOMOUS” TRAN InnerTran


ROLLBACK TRAN OuterTran


The above pseudo script is meant to preserve result of INSERT TABLE2”. In SQL Server 2008 or prior versions, “ROLLBACK TRAN” would always rollback all inner transactions to the outermost “BEGIN TRAN” statement (without specifiying savepoint). So the “InnerTran” transaction would be rolled back as well, which is not the desired behavior for the particular scenario.


You could wonder why we need an autonomous transaction in the first place. Why can’t we just implement two separate transactions so they don’t interfere with each other? There are scenarios where people do need logic structured like this. Logging errors in database is one of the most common scenarios. Below is a TSQL script demonstrating a nested transaction where the inner transaction attempts to save the runtime errors in a table.


USE TEMPDB


GO


CREATE TABLE ErrorLogging (logTime DATETIME, msg VARCHAR(255))


CREATE TABLE TestAT (id INT PRIMARY KEY)


GO


CREATE PROCEDURE usp_ErrorLogging


      @errNumber INT


AS


      INSERT INTO ErrorLogging VALUES (GETDATE(), ‘Error ‘ + CAST(@errNumber AS VARCHAR(8)) +‘ occurred.’)


GO


 


DECLARE @ERROR AS INT


INSERT INTO TestAT VALUES (1)


BEGIN TRAN OuterTran


      INSERT INTO TestAT VALUES (1) — This will raise primary key constraint violation error


     


      SELECT @ERROR = @@ERROR


      IF @ERROR <> 0


      BEGIN


            BEGIN TRAN InnerTran


                  EXEC usp_ErrorLogging @ERROR


            COMMIT TRAN InnerTran


     


            ROLLBACK TRAN OuterTran


      END


 


IF @@TRANCOUNT > 0     


COMMIT TRAN OuterTran


GO


SELECT * FROM TestAT


SELECT * FROM ErrorLogging


GO


If you run above script against SQL Server, you would see no error message recorded in table “ErrorLogging” due to the “ROLLBACK TRAN OuterTran” statement. So, how can we make it work?


In SQL Server 2008, you can implement a loopback linked server to achieve the same goal. For more information about loopback linked server, check Books Online for details (http://msdn.microsoft.com/en-us/library/ms188716.aspx).


 


USE MASTER


GO


EXEC sp_addlinkedserver @server = N’loopback’,@srvproduct = N’ ‘,@provider = N’SQLNCLI’, @datasrc = @@SERVERNAME


GO


EXEC sp_serveroption loopback,N’remote proc transaction promotion’,‘FALSE’


Go


Note ‘remote proc transaction promotion’ is a new option on SQL Server 2008, which allows you to control whether or not you want to enlist remote stored procedure call in a distributed transaction. When this option is off  (FALSE) as we set in the above example, the local transaction will not be promoted to distributed transaction. This is how we are able to separate outer and inner transactions in a “autonomous transaction” fashion.


The Inner transaction above can be replaced by:


      BEGIN TRAN InnerTran


            EXEC loopback.tempdb.dbo.usp_ErrorLogging @ERROR


      COMMIT TRAN InnerTran


Full working script is in the appendix below. I want to point out that this method of using a loopback linked server might not scale well if it’s executed very frequently. And it only works in SQL Server 2008 due to new server option of ‘remote proc transaction promotion’ as discussed above. As always, test before you use it.


If you are looking for alternative ways of creating autonomous transaction on SQL 2008 or 2005, you have these options:




  1. Loopback connection from SQLCLR procedure to start a new transaction. Compared to more rigid structure need of loopback linked server, SQLCLR is more flexible and gives you more control over how you want to handle interaction with database. If the logic of the autonomous transaction includes computational tasks, it’s one of SQLCLR’s strengths to provide performance gain as extra benefit.


  2. Using table variable to save data within transaction. Table variables are not affected by transaction rollback thus serve as temporary buffer for transaction data. Once transaction is done, you can dump data out of table variable to a permanent table. Table variables have limited scope and are less flexible. Usually they would also be slower due to lack of index/statistics. However, it does offer you a pure TSQL option with no need to create anything new.


  3. Loopback connection from Extended Stored Procedures.

Extended Stored Procedure are on the SQL Server deprecation list and we strongly recommend NOT using it.


In a future blog, I’ll provide sample SQLCLR code and a script using a table variable to create autonomous transactions. I will also compare their performance differences with loopback linked server in a scalability test. Stay tuned.


Appendix



USE MASTER


GO


EXEC sp_addlinkedserver @server = N’loopback’,@srvproduct = N’ ‘,@provider = N’SQLNCLI’, @datasrc = @@SERVERNAME


GO


EXEC sp_serveroption loopback,N’remote proc transaction promotion’,‘FALSE’


EXEC sp_serveroption loopback,N’RPC OUT’,‘TRUE’ — Enable RPC to the given server.


Go


USE TEMPDB


GO


CREATE TABLE ErrorLogging (logTime DATETIME, msg VARCHAR(255))


CREATE TABLE TestAT (id INT PRIMARY KEY)


GO


CREATE PROCEDURE usp_ErrorLogging


      @errNumber INT


AS


      INSERT INTO ErrorLogging VALUES (GETDATE(), ‘Error ‘ + CAST(@errNumber AS VARCHAR(8)) +‘ occurred.’)


GO


 


DECLARE @ERROR AS INT


INSERT INTO TestAT VALUES (1)


BEGIN TRAN OuterTran


      INSERT INTO TestAT VALUES (1) — This will raise primary key constraint violation error


     


      SELECT @ERROR = @@ERROR


      IF @ERROR <> 0


      BEGIN


            BEGIN TRAN InnerTran


                  EXEC loopback.tempdb.dbo.usp_ErrorLogging @ERROR


            COMMIT TRAN InnerTran


           


            ROLLBACK TRAN OuterTran


      END


 


IF @@TRANCOUNT > 0     


COMMIT TRAN OuterTran


GO


SELECT * FROM TestAT


SELECT * FROM ErrorLogging

GO

Cross Posted from http://blogs.microsoft.com/mssqlisv


Comments (5)

  1. Emulating autonomous transactions in MS SQL Server

  2. msolcia says:

    Good afternoon,

    I’m very interested to the SQLCLR sample and script you mentioned in the post.

    Currently for logging purpose inside a Stored Procedure, I’m using the 2nd option ("Using table variable to save data within transaction."). A disadvantage of this solution is that I can dump the table-variable only after the end of the transaction. This doesn’t allow to use the log as a trace of the execution (answer to the question: at which point of SP the execution is?)  

    Thanks for your posts

  3. tomponent says:

    I think you could simply use SAVE TRANSACTION intelligently without having to worry about trying to enlist a transaction in a distributed transaction as in:

    BEGIN TRAN OUTER

    BEGIN TRAN INNER1

    SAVE TRAN INNER1

    –code

    — choose to commit/ rollback inner1 or not

    BEGIN TRAN INNER2

    SAVE TRAN INNER2

    — code

    — choose to commit/rollback inner2 or not

    COMMIT OUTER

  4. Lee Everest says:

    Well, that might work, Tom, but I still contend that his placement of the ROLLBACK TRAN OuterTran is in the wrong place;  putting it along with the COMMIT TRAN OuterTran with a check for the error works perfectly for me in this instance…

    Lee Everest