Using SQL dB column as a lock for concurrent operations in Entity Framework

Question

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

The master Azure SQL table structure columns are approximated to

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

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

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.

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

Code:

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

score 0 · Answer 1 · answered Dec 13 '12 at 00:19

0

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;

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;

Then we COMMIT the transaction.

COMMIT;

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

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

answered Dec 13 '12 at 00:19

cha

10,301
1
18
26

Thanks, but that would require us to have the above as a stored proc and then call the stored proc from EF (during Database first, it asks to read stored procs too). Is it possible to stay within Entity Framework for atomicity of read-modify-write without resorting to stored proc? – DeepSpace101 Dec 13 '12 at 02:24
a simple select isn't the issue (`.where(...)`) - it's atomicity that is the core of the question. – DeepSpace101 Dec 13 '12 at 05:41
this process is pretty much atomic if you keep the updates within BEGIN ... COMMIT TRANSACTION – cha Dec 13 '12 at 05:56
You are missing the question, I'm trying this in EF - not in SQL script nor a SQL stored procedure. Although EF might use the same mechanisms eventually, I need to construct them within EF itself. – DeepSpace101 Dec 13 '12 at 18:30
I am not missing the question. The process above is just a pseudo code we use in our various systems (PowerBuilder, MFC, WinApp). We have created the wrapper classes to support this process that will eventually do these steps. The important thing is to select from table WITH (ROWLOCK, NOWAIT) to check if the record is locked and lock it if required. It is an equivalent of FOR UPDATE NOWAIT in Oracle, if you are familiar with it. – cha Dec 13 '12 at 21:13

Using SQL dB column as a lock for concurrent operations in Entity Framework

1 Answers1

Linked