Share via

How to create an autonomous transaction in SQL Server 2008

  • Article

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 (https://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

  • Anonymous
    August 23, 2008
    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.

  • Anonymous
    March 06, 2013
    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

  • Anonymous
    February 11, 2015
    The comment has been removed