Wednesday, June 07, 2006

Using DbConnectionScope with Suppress TransactionScope

We have been using the ADO.NET team's DbConnectionScope manager for a few months now, and it really makes the 'stay LTM' mechanism quite effortless. There are, however, a few pitfalls to avoid, especially when using it inside a TransactionScopeOption.Suppress block.

Yesterday I spent a few hours tracking down a "Transaction Timeout / The transaction has aborted" exception in a new business process method that combined several existing methods. By running the applicable unit tests, I knew that all the methods used in the new process were working correctly, so it had to be the combination of transaction scopes that caused the error.

I turned on the 'Break when an exception is: CLR exceptions: thrown' (Debug-Exceptions in the VSTS main menu) to pinpoint the bug:
... and this lead me to a transaction suppress block that used the the current connection of the DbConnectionScope like this:

Using noTransx As New TransactionScope(TransactionScopeOption.Suppress)
...
Try
Me.ReadDataNoLock()
Catch ex as TrioKasseException
'ignore error, as no record found is OK in this method

End Try
...
noTransx.Complete()
End Using

Sub ReadDataNoLock()
...
ta.Connection = DbConnectionScope.Current.GetOpenConnection(...)
...
Throw New TrioKasseException(...) 'record not found
...
End Sub

To make a long story short, this code reuses the connection of the outer required block to perform a non-transactional read inside the suppress block; and the code causes an exception to be thrown and handled during the connection usage. This causes the next attempt to aquire a handle to the "required" transaction context to fail with "transaction has aborted". The fix for this exception is to avoid the connection reuse by forcing a new connection:

Using noTransx As New TransactionScope(TransactionScopeOption.Suppress)
Using dbconn As New DbConnectionScope(Of SqlConnection)(DbConnectionScopeOption.RequiresNew)
...
...
End Using
noTransx.Complete()
End Using

Takeaway: If you use a .Suppress transaction block inside a .Required transaction block, always use a .RequiresNew companion DbConnectionScope block to ensure that you do not reuse a connection across transaction blocks.

No comments: