Add support for autonomous transactions - by Anthony C Bloesch

Status : 

 


86
1
Sign in
to vote
ID 296870 Comments
Status Active Workarounds
Type Suggestion Repros 7
Opened 9/5/2007 4:10:55 PM
Access Restriction Public

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.
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 Sara [MSFT] 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