代码优先 - 在无死锁的事务中检索和更新记录 [英] Code First - Retrieve and Update Record in a Transaction without Deadlocks
问题描述
我有一个EF代码第一上下文，代表一个处理应用程序可以检索和运行的作业队列。这些处理应用程序可以在不同的机器上运行，但指向同一个数据库。
上下文提供了一个方法，如果有任何工作要返回 QueueItem code> CollectQueueItem 。
为了确保没有两个应用程序可以接收同一个作业， ISOLATION LEVEL of REPEATABLE READ 。这意味着如果有两次尝试同时拾取同一个作业，则会选择一个作为死锁受害者并回滚。我们可以通过捕获 DbUpdateException 并返回 null 来处理这个问题。
以下是 CollectQueueItem 方法的代码：
QueueItem CollectQueueItem（）
{
using（var transaction = new TransactionScope（TransactionScopeOption.Required，new TransactionOptions {IsolationLevel = IsolationLevel.RepeatableRead}））
{
try
{
var queueItem = this.QueueItems.FirstOrDefault（qi =>！qi.IsLocked）;
if（queueItem！= null）
{
queueItem.DateCollected = DateTime.UtcNow;
queueItem.IsLocked = true;
this.SaveChanges（）;
transaction.Complete（）;
return queueItem;
}
}
catch（DbUpdateException）//我们可能是死锁的受害者。不管。
{}
return null;
}
}
我在LinqPad上运行了一个测试，工作正常。这里是测试如下：
var ids = Enumerable.Range（0,8）.AsParallel（）。SelectMany（i = >
Enumerable.Range（0，100）.Select（j => {
using（var context = new QueueContext（））
{
var queueItem = context。 CollectQueueItem（）;
return queueItem == null？-1：queueItem.OperationId;
}
}）
）;
var sw = Stopwatch.StartNew（）;
var results = ids.GroupBy（i => i）.ToDictionary（g => g.Key，g => g.Count（））;
sw.Stop（）;
Console.WriteLine（Elapsed time：{0}，sw.Elapsed）;
Console.WriteLine（Deadlocked：{0}，results.Where（r => r.Key == -1）.Select（r => r.Value）.SingleOrDefault（））;
Console.WriteLine（Duplicates：{0}，results.Count（r => r.Key> -1& r.Value> 1））;
// IsolationLevel = IsolationLevel.RepeatableRead：
//经过时间：00：00：26.9198440
//死锁：634
//重复：0
// IsolationLevel = IsolationLevel.ReadUncommitted：
//经过时间：00：00：00.8457558
//死锁：0
//复制：234
我运行测试几次。没有 REPEATABLE READ 隔离级别，相同的作业由不同的theads检索（在234个重复中看到）。使用 REPEATABLE READ ，只能检索一次作业，但性能会下降，并且有634个死锁事务。
：有没有办法获得这种行为在EF没有死锁或冲突的风险？我知道在现实生活中会有更少的争用，因为处理器不会连续击中数据库，但仍然有一种方法来安全地做到这一点，而不必处理DbUpdateException？我可以获得性能更接近的版本没有 REPEATABLE READ 隔离级别？或者是Deadlocks不是那么糟，事实上，我可以安全地忽略该异常，让处理器在几毫秒后重试，并接受如果不是所有的事务都发生在同一时间的性能将是好的？
提前感谢！
Id建议采用不同的方法。
a）sp_getapplock
使用提供应用程序锁定功能的SQL SP
因此，您可以具有唯一的应用程序行为，这可能涉及从数据库读取或您需要控制的其他任何活动。
b）乐观并发
/ strong>http://msdn.microsoft.com/en-us/ data / jj592904
//对象属性：
public byte [] RowVersion {get;组; }
//对象配置：
属性（p => p.RowVersion）.IsRowVersion（）。IsConcurrencyToken（）;
APP锁的逻辑扩展或仅由其本身使用的是DB上的rowversion并发字段。允许脏读。但是当有人去更新记录当收集，它失败，如果有人打他们。开箱即用EF乐观锁。
您可以轻松删除收集的工作记录。
这可能是更好的方法，除非您期望高水平的并发。
I have a EF code first context which represents a queue of jobs which a processing application can retrieve and run. These processing applications can be running on different machines but pointing at the same database.
The context provides a method that returns a QueueItem if there is any work to do, or null, called CollectQueueItem.
To ensure no two applications can pick up the same job, the collection takes place in a transaction with an ISOLATION LEVEL of REPEATABLE READ. This means that if there are two attempts to pick up the same job at the same time, one will be chosen as the deadlock victim and be rolled back. We can handle this by catching the DbUpdateException and return null.
Here is the code for the CollectQueueItem method:
public QueueItem CollectQueueItem()
{
using (var transaction = new TransactionScope(TransactionScopeOption.Required, new TransactionOptions { IsolationLevel = IsolationLevel.RepeatableRead }))
{
try
{
var queueItem = this.QueueItems.FirstOrDefault(qi => !qi.IsLocked);
if (queueItem != null)
{
queueItem.DateCollected = DateTime.UtcNow;
queueItem.IsLocked = true;
this.SaveChanges();
transaction.Complete();
return queueItem;
}
}
catch (DbUpdateException) //we might have been the deadlock victim. No matter.
{ }
return null;
}
}
I ran a test in LinqPad to check that this is working as expected. Here is the test below:
var ids = Enumerable.Range(0, 8).AsParallel().SelectMany(i =>
Enumerable.Range(0, 100).Select(j => {
using (var context = new QueueContext())
{
var queueItem = context.CollectQueueItem();
return queueItem == null ? -1 : queueItem.OperationId;
}
})
);
var sw = Stopwatch.StartNew();
var results = ids.GroupBy(i => i).ToDictionary(g => g.Key, g => g.Count());
sw.Stop();
Console.WriteLine("Elapsed time: {0}", sw.Elapsed);
Console.WriteLine("Deadlocked: {0}", results.Where(r => r.Key == -1).Select(r => r.Value).SingleOrDefault());
Console.WriteLine("Duplicates: {0}", results.Count(r => r.Key > -1 && r.Value > 1));
//IsolationLevel = IsolationLevel.RepeatableRead:
//Elapsed time: 00:00:26.9198440
//Deadlocked: 634
//Duplicates: 0
//IsolationLevel = IsolationLevel.ReadUncommitted:
//Elapsed time: 00:00:00.8457558
//Deadlocked: 0
//Duplicates: 234
I ran the test a few times. Without the REPEATABLE READ isolation level, the same job is retrieved by different theads (seen in the 234 duplicates). With REPEATABLE READ, jobs are only retrieved once but performance suffers and there are 634 deadlocked transactions.
My question is: is there a way to get this behaviour in EF without the risk of deadlocks or conflicts? I know in real life there will be less contention as the processors won't be continually hitting the database, but nonetheless, is there a way to do this safely without having to handle the DbUpdateException? Can I get performance closer to that of the version without the REPEATABLE READ isolation level? Or are Deadlocks not that bad in fact and I can safely ignore the exception and let the processor retry after a few millis and accept that the performance will be OK if the not all the transactions are happening at the same time?
Thanks in advance!
Id recommend a different approach.
a) sp_getapplock Use an SQL SP that provides an Application lock feature So you can have unique app behaviour, which might involve read from the DB or what ever else activity you need to control. It also lets you use EF in a normal way.
OR
b) Optimistic concurrency http://msdn.microsoft.com/en-us/data/jj592904
//Object Property:
public byte[] RowVersion { get; set; }
//Object Configuration:
Property(p => p.RowVersion).IsRowVersion().IsConcurrencyToken();
a logical extension to the APP lock or used just by itself is the rowversion concurrency field on DB. Allow the dirty read. BUT when someone goes to update the record As collected, it fails if someone beat them to it. Out of the box EF optimistic locking. You can delete "collected" job records later easily.
This might be better approach unless you expect high levels of concurrency.
这篇关于代码优先 - 在无死锁的事务中检索和更新记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
