使用SQL dB列作为实体框架中并发操作的锁 [英] Using SQL dB column as a lock for concurrent operations in Entity Framework

问题描述

我们有一个长时间运行的用户操作，由一组工作进程处理。数据输入和输出来自Azure SQL。

We have a long running user operation that is handled by a pool of worker processes. Data input and output is from Azure SQL.

主Azure SQL表结构列近似为

The master Azure SQL table structure columns are approximated to

[UserId, col1, col2, ... , col N, beingProcessed, lastTimeProcessed ] 

beingProcessed 是布尔值，而 lastTimeProcessed 是DateTime。每个工作人员角色的逻辑如下所示，并且多个工作者处理（每个都有自己的实体框架层），实质上 beingProcessed 正在使用MutEx目的的锁

beingProcessed is boolean and lastTimeProcessed is DateTime. The logic in every worker role is as shown below and with multiple workers processing (each with their own Entity Framework layer), in essence beingProcessed is being used a lock for MutEx purposes

问题：如何处理 isProcessed 锁定本身的并发问题基于上述负载？我认为 isProcessed 中的 read-modify-write 操作需要是原子的，但我对其他策略开放。

Question: How can I deal with concurrency issues on the beingProcessed "lock" itself based on the above load? I think read-modify-write operation on the beingProcessed needs to be atomic but I'm open to other strategies. Open to other code refinements too.

[更新]：我想知道 TransactionScope 是否需要在这里？ http://msdn.microsoft。 com / en-US / library / system.transactions.transactionscope（v = vs.110）.aspx

[Update]: I wonder if TransactionScope is what's needed here ... http://msdn.microsoft.com/en-US/library/system.transactions.transactionscope(v=vs.110).aspx

代码：

public void WorkerRoleMain()
{
    while(true)
    {
        try
        {
            dbContext db = new dbContext();

            // Read
            foreach (UserProfile user in db.UserProfile
                    .Where(u => DateTime.UtcNow.Subtract(u.lastTimeProcessed) 
                            > TimeSpan.FromHours(24) & 
                            u.beingProcessed == false))
            {
                user.beingProcessed = true; // Modify
                db.SaveChanges();           // Write
                // Do some long drawn processing here
                ...
                ...
                ...
                user.lastTimeProcessed = DateTime.UtcNow;
                user.beingProcessed = false;
                db.SaveChanges();
            }
        }
        catch(Exception ex)
        {
            LogException(ex);
            Sleep(TimeSpan.FromMinutes(5));
        }
    } // while ()
}

推荐答案

我们通常做的是这样的：

What we usually do is this:

在长操作开始时，我们开始一个事务：

At the beginning of a long operation we start a transaction:

BEGIN TRANSACTION

然后我们从表中选择一行，我们要使用以下提示更新/删除：

Then we select a row from the table we would like to update/delete using these hints:

SELECT * FROM Table WITH (ROWLOCK, NOWAIT) Where ID = 123;

然后我们检查一下我们有这行。如果行被其他进程锁定，则会出现SQL错误。在这种情况下，我们回滚交易并建议用户。
如果记录被锁定，我们处理记录，并使用我们用于锁定记录的相同事务对象进行所需的更新：

Then we check that we have the row. If the row is locked by another process there will be an SQL Error. In this case we rollback the transaction and advise the user. If the record is locked we process the record, and do the required updates, using the same transaction object we used to lock the record:

UPDATE Table SET Col1='value' WHERE ID = 123;

然后我们提交交易。

COMMIT;

这只是进程的伪代码。你将不得不在你的程序中实现它。

This is just the Pseudo-code of the process. You will have to implement it in your program.

有关上述过程的一个小笔记。在SQL Server（或Azure）中锁定记录时，请使用WHERE子句中的主键，否则SQL Server将决定使用Page lock或Table lock

One small note regarding the above process. When you lock the record in SQL Server (or Azure), use the primary key in your WHERE Clause, otherwise the SQL Server will decide to use a Page lock, or Table lock

这篇关于使用SQL dB列作为实体框架中并发操作的锁的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！