怎样才能锁定在读表，使用实体框架？ [英] How can I lock a table on read, using Entity Framework?

问题描述

我有一个SQL Server（2012年），我访问使用实体框架（4.1）。
在数据库中，我有成一个独立的过程饲料新的URL的表中调用URL。
在URL表中的条目可以在国家新，进行中或加工。

I have a SQL Server (2012) which I access using Entity Framework (4.1). In the database I have a table called URL into which an independent process feeds new URLs. An entry in the URL table can be in state "New", "In Process" or "Processed".

我需要从不同的电脑访问URL表中，选中URL条目状态为新建，占据第一位，并将其标记为进行中。

I need to access the URL table from different computers, check for URL entries with status "New", take the first one and mark it as "In Process".

var newUrl = dbEntity.URLs.FirstOrDefault(url => url.StatusID == (int) URLStatus.New);
if(newUrl != null)
{
    newUrl.StatusID = (int) URLStatus.InProcess;
    dbEntity.SaveChanges();
}
//Process the URL

由于查询和更新不是原子的，我可以读两个不同的计算机，并更新数据库相同的URL条目。

Since the query and update are not atomic, I can have two different computers read and update the same URL entry in the database.

有没有一种方法，使选择，然后更新序列原子避免这样的冲突？

Is there a way to make the select-then-update sequence atomic to avoid such clashes?

推荐答案

试试这个：

using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, new TransactionOptions { IsolationLevel = IsolationLevel.RepeatableRead }))
{
    var newUrl = dbEntity.URLs.FirstOrDefault(url => url.StatusID == (int) URLStatus.New);
    if(newUrl != null)
    {
        newUrl.StatusID = (int) URLStatus.InProcess;
        dbEntity.SaveChanges();
    }
    scope.Complete();
}

的 IsolationLevel.RepeatableRead 将应用锁定到全行读以这样的方式，一个发2 无法从表A 如果表A 已通过读线程1 和发1 未完成交易。的（每它似乎不是如此评论）的

The IsolationLevel.RepeatableRead will apply a lock to all rows that are read in such a way that a Thread 2 cannot read from Table A if Table A has been read by Thread 1 and Thread 1 did not complete the Transaction. (Per the comments it appears not to be the case)

只是一个侧面说明有关TransactionScopes：

Just a side note about TransactionScopes:

在你的周围code以的TransactionScope 没有创建任何交易，你只是在创建可能需要事务的范围。当你读你的insinde 使用，实体框架将创建一个事务，但不会了，因为现在，由于的TransactionScope ，你负责的事务。那就是：你会确定的IsolationLevel，你有责任commiting或回滚事务。默认情况下，隔离级别将在写应用的锁。也就是说，如果我写的网址，将锁定表的网址阅读和书面方式。为了在读取应用锁，您可以使用 IsolationLevel.RepeatableRead 。

When you surround your code with a TransactionScope you are not creating any transaction, you are just creating a scope in which a transaction may be required. When you read data insinde your using, Entity Framework would create a transaction, but it won't anymore, because now, due to the TransactionScope, you are in charge of the transaction. That is: You will determine the IsolationLevel and you are responsible for commiting or rolling back the transaction. By default, the isolation level will apply a lock on write. That is, if I write an URL, it will lock the URLs table for reading and writting. In order to apply a lock on read, you use IsolationLevel.RepeatableRead.

如果您创建里面更多的交易你的的TransactionScope 将被提升到一个分布式事务，但那是我的回答的范围。

If you create more transactions inside your TransactionScope it will be promoted to a distributed transaction, but that is outside the scope of my answer.

这篇关于怎样才能锁定在读表，使用实体框架？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！