Home Dashboard Directory Help

Add support for autonomous transactions by Anthony C Bloesch


Status: 

Active


81
0
Sign in
to vote
Type: Suggestion
ID: 296870
Opened: 9/5/2007 4:10:55 PM
Access Restriction: Public
3
Workaround(s)
view

Description

Support for autonomous transactions (i.e. the ability to nest a unit of work) would allow for more concurrency. At the moment an operation that is logically independent such as a logging operation can lock resources until the transaction as a whole succeeds.

A great example of where this would be useful is in the implementation of sequences. Once a sequence range is allocated the change can be committed independent of the surrounding transaction. Doing so allows other transactions to allocate sequence ranges while the first transaction is progressing. This allows for greater concurrency.
Details
Sign in to post a comment.
Posted by Randy in Marin on 12/6/2011 at 4:23 PM
Please include a AUTONOMOUS or COMMIT query hint or such for a simple one-off autonomous query. For example,

INSERT INTO ErrorLog WITH (AUTONOMOUS) (Number, Severity, State)
SELECT ERROR_MESSAGE(), ERROR_SEVERITY(), ERROR_STATE()

This is a simple case that might be a first step to full autonomous support. I don't think there is any need to worry about how the transactions are nested here.

A ROLLBACK can undo work back to a savepoint. Is it a great deal more effort to rollback to the save point while committing the changes rather than undoing them? Are locks created since a savepoint hard to release?
Posted by fotis12 on 10/20/2011 at 11:37 AM
Competition was and still is so much always ahead:
Sequences, autonomous transactions, materialized views supporting left outer joins, snapshot isolation long before it was implemented in sql server, paging and so on...
You make it seem as if you are happy to even store data at the disk mdf file and have some indexes around it.
Come on, give sql server a chance and develop all the features we need in ONE edition.
Im tired of wating for years and years and 4 diferent editions (2005,2008, 2008r2, 2011) to get all the features i need out of the box
Posted by Jason Kresowaty on 8/31/2008 at 6:14 AM
Also consider the ability to pick multiple "atonomous" transactions to simultaneously COMMIT or ROLLBACK.

For example,
BEGIN TRANSACTION A
BEGIN TRANSACTION B
BEGIN TRANSACTION C
COMMIT TRANSACTION B,C
BEGIN TRANSACTION D
ROLLBACK TRANSACTION A,D

Here is the kicker: the COMMIT or ROLLBACK is atomic with respect to all named transactions that are listed.
Posted by Microsoft on 12/11/2007 at 2:24 PM
Hello Anthony

Thanks for your feedback and feature request. Autonomous transactions are a powerful feature and can be used to satisfy a variety of core requirements, such as sequence generation, error logging, and more. Though autonomous transactions will not be available in SQL Server 2008, we are actively tracking this as a feature request and are considering supporting it in a future release of SQL Server.

Thank you

SQL Server Engine Team
Sign in to post a workaround.
Posted by brbate on 2/24/2014 at 6:16 AM
Here is a workaround posted on the MSDN "SQL Programmability & API Development Team Blog", which uses a "loopback linked server" with 'remote proc transaction promotion' turned off. Note this only works with SQL 2008+

http://blogs.msdn.com/b/sqlprogrammability/archive/2008/08/22/how-to-create-an-autonomous-transaction-in-sql-server-2008.aspx

Here is an excerpt from the article:
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.
Posted by DennisAtHome on 4/29/2009 at 6:56 AM
Could you clarify what happens when the outer transaction hits an exception (which is typically the reason for wishing to log autonomously)? This extract from
http://msdn.microsoft.com/en-us/library/system.transactions.transactionscope.aspx
seems to suggest that everything is fine until you hit an exception, at which point the transaction is rolled back. I think the exception referred to is the inner transaction.
Could you give some examples of how your workaround would be used from T-SQL in the event of the "functional" transaction hitting eg a SQL exception, calling the logging transaction, and then rolling back the functional transaction but committing the logging transaction?
Many thanks
Posted by Vedran Kesegic on 4/21/2009 at 11:24 PM
Workaround is not very pretty, and not very performant because it has overhead of opening new connection to database from CLR stored procedure.
Here it is (CLR stored procedure):

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Imports System.Transactions

Partial Public Class StoredProcedures
    <Microsoft.SqlServer.Server.SqlProcedure()> _
    Public Shared Sub log_insertAutonomous(ByVal level As SqlByte, ByVal message As SqlString, ByVal exception As SqlString, ByVal spid As SqlInt16, ByVal logger As SqlString)

        Using ts As New TransactionScope(TransactionScopeOption.RequiresNew) 'new transaction, autonomous
            Using conn As New SqlConnection("Data Source=localhost;User Id=xxx;Password=xxx;")
                conn.Open()
                Dim cmd As SqlCommand = New SqlCommand( ... )
                ...
                cmd.ExecuteNonQuery()
                ts.Complete() 'commits autonomous transaction
            End Using
        End Using
    End Sub
End Class


-- Compile vb file into dll
cd "C:\Windows\Microsoft.NET\Framework\v2.0.50727"
vbc.exe /t:library c:\log_insertAutonomous.vb

-- Now, in sql server enable stored procedures:
sp_configure 'clr_enabled',1
go

reconfigure
go

ALTER DATABASE xy SET TRUSTWORTHY ON
GO

EXEC dbo.sp_changedbowner @loginame = N'sa', @map = false
go

-- Create CLR stored proceedure
create assembly TSqlLogger
from 'C:\log_insertAutonomous.dll'
with permission_set = EXTERNAL_ACCESS
go

-- Unfortunately, SqlString maps to nvarchar, not varchar!
create procedure log_insertAutonomous(@level tinyint, @message nvarchar(4000), @exception nvarchar(4000), @spid smallint, @logger nvarchar(255))
as
external name TSqlLogger.StoredProcedures.log_insertAutonomous
go