实体框架操作到SQL - 防止死锁

问题描述

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

在我的Silverlight应用程序中，我有两个网格显示这张表由ProductId分离 - 换句话说，我把所有的产品A都放在一个网格中，所有的产品B都在另一个网格中。

当我选择任何一个东西，我都把它写出来。如果我从网路上取消选择一个项目，那么我将删除这个从顶级的行。由于Silverlight使用异步调用，而一个网格正忙，用户仍然可以使用其他网格（这是我想要的）。但是，如果用户在从另一个网格中插入项目时取消选择项目，则会遇到死锁错误。

我们的所有插入都是在每个实体具有已更新，我调用了SubmitChanges（）。这些删除是以不同的方式处理的。由于EF中没有DeleteAll，我使用对象上下文的ExecuteStoreCommand（）并提交一个DELETE查询 - 这可能是我的问题来自。

如何我使用相同的表来完成这个而不会产生死锁错误？我真的试图避免为每个网格创建一个单独的表。

如果我使用EF删除我的实体，而不是ExecuteStoreCommand（）将EF处理僵局更好？看起来像一个资源腰，将表加载到内存中，每次删除一行一个。

编辑：我想补充一点，我证实我的僵局来了当我从同时删除文件时，EF插入它。

谢谢，

Scott

解决方案

尝试为您的交易使用隔离级别：

<$ p $使用（TransactionScope scope =
new TransactionScope（TransactionScopeOption.RequiresNew，
new TransactionOptions（）
{
IsolationLevel = IsolationLevel.ReadUncommitted
}））
{
//只读工作 - 记录上没有锁。有效地从xxx WITH SELECT SELECT（NOLOCK）
}

然而，AFAIK这是为范围界定EF上下文查询。如果您使用 ExecuteStoreCommand ，那么您可能需要手动将NOLOCK提示放在查询本身上。

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

解决方案

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.

这篇关于实体框架操作到SQL - 防止死锁的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！