How do I avoid these deadlocks?

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:

  • Connecting to mssql using pdo through php and linux
  • What is the meaning of the prefix N in T-SQL statements?
  • Stored procedure working when run manually, not running from sql server agent
  • When do triggers fire and when don't they
  • How to add a comment to an existing table column in SQL Server?
  • From .net how to I send many “batches” of SQL to Sql-Server without lots of round trips?
  • 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.

  • SQL Date Format
  • SSRS Parameters
  • Is SQL Server/Windows integrated security good for anything?
  • 2 tables - Select a row from different table when column value changes
  • How to create default instance after creating a named instance?
  • displaying updation of data to multiple users which is inserted by another user
  • 4 Solutions collect form web for “How do I avoid these deadlocks?”

    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.

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

    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.

    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.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.