Autonomous Transactions

IMO the most obnoxious shortcoming of Transact-SQL (after maybe the goofy error-handling that won't allow you to rethrow any and every error) is the lack of native autonomous transactions.  I wrote about this [[TSQL Autonomous Transactions or Another Reason I Like PLSQL over TSQL|here]] and [[Temporary Objects|here]].  I've only ever found 2 good reasons for autonomous transactions, but they are HUGE:  

1. The ability to ROLLBACK a long-running transaction but maintain any error logging for later analysis.  The workaround, and it has a lot of obnoxious limitations, is to log to a @TableVariable and persist that to an actual table after you have done your ROLLBACK.  
2. The ability to spawn off a separate transaction to get more keys from a central keystore.  In SQL 2012 we have SEQUENCEs which function the same way, unfortunately, not all ORMs and custom-built in-house code will be able to use them.  At least not right away.  

The problem with Number 2 is that we can't write stored procedures that spawn the separate transaction needed to get more keys.  Obviously there are more uses for autonomous transactions, but let's continue the demo.  In this screenshot I have a call to a key generator from within a transaction.  I ask for 10 keys and I can see my LastUsedObjId has been incremented to 645 as expected.  

Now let's assume that call is a long-running query.  Assume that another similar call is made from another connection/transaction while the first spid is executing.  What will happen?  Blocking...

OK.  Let's issue a ROLLBACK on both connections.  It turns out that SQL 2008 and above can get you an autonomous transaction if you are OK with using a Loopback linked server.  Here I create a loopback linked server and ensure that RPC OUT is set.  The magic is 'remote proc transaction promotion'.  This setting ensures that MSDTC does not enlist any calls to this linked server into a distributed transaction.  Essentially, it just spawned off an autonomous transaction for you.  

So, let's demo this.  All we need to do is alter our original code slightly to call the linked server.  Here is how I do it:  

Notice I build the stored proc call in a variable that simply builds my 4 part name call.  I have nothing "hard-coded" with this...it will work on a test, prod, or dev environment, which aids in supportability.  Note that my LastUsedObjId was 655 and it was incremented correctly to 665.  Now if I open a new window/spid and paste the code and execute it:  

Note that the spid is NOT blocked this time.  That's because the call to the sequence generator was called in an autonomous transaction.  Note that the new spid correctly saw the first spid's updated LastUsedObjId and properly incremented it's LastUsedObjId. 

That's what we wanted.  

So, it turns out you actually can issue an autonomous transaction in SQL Server, if you don't mind a little obnoxiousness.