MacGruber Transactions

Found an interesting bit of code on the newsgroups the other day …. what I like to call MacGruber transactions. If you are a fan of SNL you know the story.

With System.Data.SqlClient.SqlConnection in .NET 2.0 we added a new connection string attribute named "Transaction Binding" to alter the behavior of how SqlConnection interacts with System.Transaction.

One might believe a transaction always spans a TransactionScope block, this is technically true but the transaction can rollback at any point inside the block. The transaction is still there but it’s state has changed.

With SqlClient our default behavior is to use Transaction Binding=Implicit Unbind which means we auto unbind from the transaction scope if the transaction rolls back and switch back to standard SQL auto-commit mode.

Hence work inside the TransactionScope could be half rolled back and half committed if for example the transaction times out half way through the code block. I recommend using Transaction Binding=Explicit Unbind since this gives you deterministic behavior. We are thinking about changing the default to this in future releases of System.Data.SqlClient driver let me know what you think.