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 applications 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


A complete 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. Note: Extendeded Stored Procedure functionality is on the SQL Server deprecation list and Microsoft strongly recommends 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


Comments (3)

  1. Christoph Ingenhaag says:

    Good to know, but in this case I would prefer a table variable to log errors.

    This will work with older versions of SQL Server too.

  2. Daniel Adeniji says:

    While performing post-fix write-up, I found this Blog Posting.

    Thanks for documenting it and providing a couple of novel use-cases.  

    My usage was to collect data across a couple of servers via Linked Server enablement.  And, thankfully I scripted out the Linked Server Creation entry and was able to see this new option.  And, thus when error occurred it was the first thing I played with.  And, thankfully it worked.

    Us old-timers really do not know what we do not know.

    Big Ups to the Engineering Team and any contributing Connect requesters that might have requested this must have feature.

    BTW, my use case is available @ danieladeniji.wordpress.com/…/microsoft-sql-server-linked-server-error-ole-db-provider-sqlncli10-for-linked-server-hrdb001-returned-message-the-partner-transaction-manager-has-disabled-its-support-for-remotenetw

  3. Frank says:

    Trying this with 2014 and a logon trigger with no luck. Not sure yet if the stored proc is being fired. I would prefer this be stored in my audit database and not the tempdb but using either db does not seem to be working as of yet. I am not sure what Christopher means my using a Table variable for this. How would you take the table variable content and store it for reference if it is destroyed at the end of execute?

    Thanks for any help in advance.

    Frank