How to create an autonomous transaction in SQL Server 2008

[SQL-Server-Team]

First published on MSDN on Aug 22, 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