如何使用实体框架在读取时锁定表? [英] How can I lock a table on read, using Entity Framework?

问题描述

我有一个使用实体框架 (4.1) 访问的 SQL Server (2012).在数据库中，我有一个名为 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.

有没有办法让 select-then-update 序列原子化以避免这种冲突?

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

推荐答案

我只能通过手动向表发出锁定语句来真正完成此操作.这是一个完整表锁，所以要小心！在我的例子中，它对于创建一个我不希望多个进程同时接触的队列很有用.

I was only able to really accomplish this by manually issuing a lock statement to a table. This does a complete table lock, so be careful with it! In my case it was useful for creating a queue that I didn't want multiple processes touching at once.

using (Entities entities = new Entities())
using (TransactionScope scope = new TransactionScope())
{
    //Lock the table during this transaction
    entities.Database.ExecuteSqlCommand("SELECT TOP 1 KeyColumn FROM MyTable WITH (TABLOCKX, HOLDLOCK)");

    //Do your work with the locked table here...

    //Complete the scope here to commit, otherwise it will rollback
    //The table lock will be released after we exit the TransactionScope block
    scope.Complete();
}

更新 - 在实体框架 6 中，尤其是使用 async/await 代码时，您需要以不同的方式处理事务.经过一些转换后，这对我们来说崩溃了.

Update - In Entity Framework 6, especially with async / await code, you need to handle the transactions differently. This was crashing for us after some conversions.

using (Entities entities = new Entities())
using (DbContextTransaction scope = entities.Database.BeginTransaction())
{
    //Lock the table during this transaction
    entities.Database.ExecuteSqlCommand("SELECT TOP 1 KeyColumn FROM MyTable WITH (TABLOCKX, HOLDLOCK)");

    //Do your work with the locked table here...

    //Complete the scope here to commit, otherwise it will rollback
    //The table lock will be released after we exit the TransactionScope block
    scope.Commit();
}

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