如何使用 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 表，检查状态为New"的 URL 条目，取第一个并将其标记为In Process".

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();
}

更新 - 在 Entity Framework 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();
}

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