Entity Framework Operations to SQL - Prevent Deadl

Question

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 were 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

Answer 1

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 ...

Answer 2

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.

Answer 3

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
}