Add support for autonomous transactions
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.
Upvotes: 102<=-=Dec 11 2007 2:24PM=-=>
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.
SQL Server Engine Team<=-=Aug 31 2008 6:14AM=-=>
Also consider the ability to pick multiple “atonomous” transactions to simultaneously COMMIT or ROLLBACK.
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.<=-=Oct 20 2011 11:37AM=-=>
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
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?
Andrew Tindle commented
My organisation is currently migrating from Oracle to SQL Server
We use Autonomous Transactions heavily in Oracle to instrument the progression of Stored Procedures. We are finding the lack of a similar capability in SQL Server 2017 a real impediment.
Peter Åkerlund commented
Autonomous transaction is essential if you want to add any sort of instrumentation to you T-SQL! Please add ti as soon as possible so we don't need to implement ugly workarounds to be able to write autonomous log entries that are not rolled back when an error occur!