How Can I Avoid Deadlocks With Slow Queries

I have a WCF service that occasionally causes a database deadlock. My service allows you to submit applications to the council, also 开发者_开发百科it allows you to load them again because the council can updated them. Each of the applications has a number of documents associated with it.

So if a set of events like this occur on my service

1) Application #1 is submitted
2) A document is uploaded for application #1
3) Application #1 is loaded
4) Part 2 above finishes

Deadlock at part 3. I believe the reason is that the document in part 2 takes a while to submit to the SQL server from the WCF service, and during this time it locks the table.

So if we load from the database application #1, and the associated documents we get in trouble.

I am using the entity framework. How can I solve this? All I really want to do is load the documents in this circumstance that are completely submitted and not run into the table being locked or anything.

By the way this is the specific error I am getting,

Message: Transaction (Process ID 93) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

UPDATE: I have had a couple of comments that I could change the order of operations. I cannot actually do that because part 2 and part 3 are from different calls to the WCF service. So it is up to the user of the service to order the operations. 'Ask them to change it' you say, but it is not that simple either. The order of the operations actually depends on the end user just happening to 'refresh' their browser at the right moment.

Update #2: I need some advice on how to replicate this issue. I have written a test application and part 2 above does block the operation in part 3, but the consequences for me is that operation 3 just blocks until operation 2 is finished, and then operation 3 finishes.

So that means part 3 takes 1 minute 50 seconds instead of 3 which it normally would. Any ideas why it would be blocking for me, and not creating a deadlock? Could it relate to the amount of database traffic overall on that server seeing as I am using a test server, or could some database settings be affecting it?

There are several ways to avoid dead-locks in general (SQL Server and other database):

1) Call saveChanges() only at the end of the transaction, to execute all queries together.

2) Change the order to perform the database update, first perform all reading (SELECT) and last perform all updates.

3) Perform the readings out of the transaction of the data that would be not modified in the transaction.

4) Change the isolation level of the transaction to SERIALIZABLE (poor performance) or SNAPSHOT or other intermediate with no locking like READ UNCOMMITTED if application logic allows.

5) Create synchronized code blocks by using lock(lock_object) to avoid two threads performing same transaction in parallel or performing two different transaction that lock themselves.

A couple of solutions:

• Use an isolation level of READ UNCOMMITTED if you don't mind potentially having uncommitted rows show up in step 3 (this depends on your situation).

OR

• Batch your changes on your EF context more efficiently and make sure they execute all at once (without seeing your code I'm not sure whether you are doing this or not)

EDIT

The link below may also be helpful in troubleshooting and fixing your problem:

http://blogs.msdn.com/b/bartd/archive/2006/09/09/deadlock-troubleshooting_2c00_-part-1.aspx