Fun With Transactions

Demo files can be found here

This is a short presentation on some of the confusing aspects of transaction handling in SQL Server.  We start with transaction naming:  

Transaction Names

Even on a case-insensitive database this will fail:  

But this will not fail:  

And even this will work because transaction names are totally ignored when issuing a COMMIT:

Autonomous Transactions

A sorely missed feature of SQL Server is native "autonomous transactions."  This is where you "pause" the current transaction and spin up a new transaction.  When the new transaction "completes" (commits or rolls back) then we continue the paused transaction.  Some use cases:

• out-of-band logging/auditing
• custom sequence generators

.NET and Java developers have the concept of .REQUIRES_NEW to "pause" an existing transaction and start a new, autonomous transaction.  Under the covers this is implemented as a new spid with potentially different transaction semantics.  I give some demos on how to use Linked Servers in SQL Server to generate your own autonomous transactions. 

Implicit Transactions

Implicit Transactions (IT) cause inordinate confusion.  At their worst they can cause what appears to be massive blocking events on your server.  Most developers and data access technologies do not use IT, the notable exception being the Java world (Spring JDBC, WebSphere, etc).  When SET IMPLICIT_TRANSACTIONS ON is set a transaction is started for you...if it is needed.  That "if" statement is what causes problems because the "if" assumptions are not properly documented by Microsoft.  If your driver forces you to use IT then ensure that whenever a connection is pulled from the pool that sp_reset_connection is being called.  This will reset the IT setting.  If this isn't done then you can "leak" connections where a developer might have turned OFF IT to get finer-grain control.  That's a big problem.  

I have lots of demos on the confusing aspects of implicit transactions as well as how to monitor your server for IT anomalies that may cause blocking or "abandoned spid" problems for you.  

Demo files can be found here

You have just read "[[Fun with Transactions]]" on davewentzel.com. If you found this useful please feel free to subscribe to the RSS feed.