Entity Framework Operations to SQL - Prevent Deadlocks

I have a table in SQL Server that looks something like:

Col1  Id  -Int and Key    
Col2  ProductId  int    
Col3  ProductDesc   Varchar

Within my Silverlight app, I have two grids showing data from this table - seperated out by the ProductId - In other words, I have all the product A's in one grid and all the Product B's in the other grid.

When I select any item in either gird, I write it out to the table. If I deselect an item from the gird, I delete the row from the talble. Because of Silverlight's use of async calls, while one grid is busy, the user can still work with the other grid (which is what I want). However, if the user unselects items from one grid while items are being inserted from the other, I get deadlock errors.

All of my inserts are being done after each entity has been updated and I made the call to SubmitChanges(). The deletions are being handled in a different way. Since there is no DeleteAll in EF, I am using the Object Context's ExecuteStoreCommand() and submitting a DELETE query - which may be were my problem is coming from.

How can I use the same table to accomplish this without getting deadlock errors? I'm really trying to avoid creating a seperate table for each grid.

If I we开发者_C百科re to use EF to delete from my entities instead of ExecuteStoreCommand() would EF handle the deadlocks better? It seems like a resource waist to load the table into memory, the delete each row one at a time.

EDIT: I wanted to add that I verified that my deadlocks come when I am deleting from the talbe at the same time EF is inserting into it.

Thanks,

-Scott

Try using isolation levels for your transactions:

using (TransactionScope scope = 
          new TransactionScope(TransactionScopeOption.RequiresNew, 
             new TransactionOptions() 
             {
                IsolationLevel = IsolationLevel.ReadUncommitted 
             }))
{
   // read only work - no locks on records. effectively SELECT xx from xxx WITH (NOLOCK)
}

However, AFAIK this is for scoping EF context queries. If your using ExecuteStoreCommand then you might have to manually put the NOLOCK hint on the query itself.

Is your delete statement touching the same rows as the other operations?

If not, try adding a rowlock hint: delete from xyz with (rowlock) where ...

The following works for me.

using (var context = new XXX())   // Replace XXX with your specifics
{
  context.Database.ExecuteSqlCommand("SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;"); 
  // your LINQ code here
}