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.
Dave Wentzel CONTENT
sql server data architecture