How do I avoid these deadlocks?

Question

I have a routine which is updating my business entity. The update involves about 6 different tables. All the commands are being executed within a transaction.

Recently, I needed to add some code into the routine which accesses a lookup table from the database. The lookup code already existed in another business object so I used that business object. For example:

Using tr As DbTransaction = myConnection.BeginTransaction()
    ExecuteCommand1(tr)
    ExecuteCommand2(tr)
    If myLookupTable.GetLookupTable().FindById(id).HasFlagSet Then
        ExecuteCommand3(tr)
    End If
End Using

However, the lookup table business object hangs/deadlocks. I think this is because it doesn't have a reference to the transaction being used by the original routine.

After doing some research, I attempted to put the lookup table logic in its own transaction, setting the IsolationLevel to ReadUncommitted. This gave me the results I desired. However, after further research, I'm now second-guessing if I've implemented this correctly.

Assuming a reference to the active transaction is unavailable to my lookup table object, is what I've described considered best practice? I feel like I might be missing something.

Answer 1

If it were me I would rewrite the logic so I do not have to do an uncommitted read.

Answer 2

The golden rule to avoid deadlocks is to always take table locks in the same order in every transaction. So look at the code in the other transactions to see what order they take table locks; then make sure you use the same order in your transaction.

Answer 3

Apparently your look up is attempting to access a row(s) that is exclusively locked by transaction tr. If you use a readuncommitted transaction or alternatively use WITH(NOLOCK) in your lookup query, you will see all uncommitted changes by transactions that might be occurring and effecting your lookup logic. So I am not so sure how desirable this would be.

I think it is best to find a way to ensure that your lookup query participates in the current transaction if you need to do the lookup during that transaction. If all of these operations are to be executed in the same thread, one thing that you can do is to store the transaction object in thread local storage when you create one and have GetLookupTable method check the thread local storage for a transaction object and if there is a transaction set, you can get the connection from that transaction object. Otherwise, you create a new connection. This way your lookup will become part of that transaction and it should run its logic without getting blocked by the current transaction and in turn blocking the current transaction and thus leading to a deadlock.

Answer 4

If you're doing a read in the middle of your transaction then you should do it under the transaction context, not using a different transaction and dirty reads. Luckily there is an easy solution: instead of using the ADO.Net transaction objects use the .Net TransactionScope object. The ADO.Net code is sensible to it and will enlist all your operations in this transaction, including your other business component reads. Just make sure your business object does not open a different connection, this will result in attempting to escalate the existing transaction into a distributed transaction and enlist the new connection into it.

The alternative is to pass down your SqlConnection/SqlTransaction pair on each call, but that propagates horribly ugly everywhere in your code.