Entity Framework get the next available row exclusively

Question

I am using Entity Framework 6.1.1 (SQL Server back end) in an MVC app with a service layer.

I want to read the database from the service layer to get a row from the database, update something on it to advise it is being processed to stop the next read picking up on it, then return it.

So basically if 2 users call the same service, they will both get different rows as the first read will update the record (say to change a column "Processing" to true). The second read will bypass this record and go to the next one.

I can't think of a way to do this using normal linq queries in EF. The only way I can think of doing it is using a stored procedure. Although I am not 100% sure yet how to do it in an SP. If that is the only option then I will investigate it more.

Can this be done using EF?

Surely this all happens too fast for this to actually matter? — Derek, Dec 19 '14 at 10:12
Can you just select only rows where Processing <> true and get the next in line that way? — timothyclifford, Dec 19 '14 at 10:12
@Derek On a busy website, it will definitely happen. The time it takes to do a read, then an update, then a save changes. — eyeballpaul, Dec 19 '14 at 10:16
@timothyclifford This will only get the next row, it will not stop the next person getting it. — eyeballpaul, Dec 19 '14 at 10:16
You can lock access to your table in DbContext to ensure 2 users will not get the same result (get row, update, save changes in `lock` block. Something like imitating the stored procedure. — Martin Konopka, Dec 19 '14 at 10:26
@MartinKonopka In a lock block? Can you please explain? I'm not sure I understand. — eyeballpaul, Dec 19 '14 at 10:31

score 0 · Answer 1 · answered Dec 19 '14 at 10:31

0

Take a look at this article. Its about SQL Transaction Isolation levels.

The default Isolation level in SQl is READ COMMITED.

This means that when you commit a change to a row in a table, any query there after in a fraction of the same time frame, has to wait for the transaction to complete before it runs the query.

Here's a link :-

http://gavindraper.com/2012/02/18/sql-server-isolation-levels-by-example/

Hope this helps,

I'm pretty sure your going down the wrong road with this.

answered Dec 19 '14 at 10:31

Derek

8,300
12
56
88

Thank you for the answer. Can you advise why I am going down the wrong road? Basically, I have a table with a number of rows to be processed. A user will ask for the next row for them to process, and that will be what they work with. There will be many users, and they cannot work on the same rowm. – eyeballpaul Dec 19 '14 at 10:34
Also, "Read Committed" is the default. However, a transaction will not be started until after the "select" is done I believe. I think I can actually change the transactionscope as per the following http://stackoverflow.com/questions/13404061/how-can-i-lock-a-table-on-read-using-entity-framework – eyeballpaul Dec 19 '14 at 10:35

score 0 · Answer 2 · answered Dec 19 '14 at 10:40

I am not sure about viability of this solution, it is simple and may end slow the performance of your service because of locking on data layer (not in database).

public class MyDbContext : DbContext 
{
    private DbSet<Data> DataTable { get { return this.Set<Data>(); } }

    private static object lockObject = new object();
    public Data GetDataToProcess() 
    { 
       lock (lockObject) 
       {
          Data data = this.DataTable.FirstOrDefault(d => d.Processing == false);
          data.Processing = true;
          this.SaveChanges();
          return data;
       }
    }
 }

And then use GetDataToProcess method in your service, not the DataTable property.

Note: this code is just an example and may not be the right way, it is just a workaround. It also needs to be finished with null checking and possibly refactored by extracting the GetDataToProcess to other class which will use an instance of MyDbContext internally.

Thanks for the answer. This would work if the same context was used for every single read. But there are circumstances that mean this won't be the case. Different projects for web api and mvc apps, web farm etc etc — eyeballpaul, Dec 19 '14 at 10:43
Yes, it was just a simple idea. Then you have to move access checking down to the shared layer, i.e., the database. — Martin Konopka, Dec 19 '14 at 10:46

score 0 · Answer 3 · edited May 23 '17 at 12:20

I came across this post that may be what I need. I need to look into it further

How can I lock a table on read, using Entity Framework?

But basically it looks like you change the isolation level for that specific read/update like this:

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

Obviously using my own entities/logic etc

score 0 · Answer 4 · answered Dec 19 '14 at 14:31

I decided on going down the route of letting a stored procedure do the work. The reason being, that with the way my unit of work/rep/service layers were layered, it was more logical to do it this way

    WITH cte AS
    (
        SELECT TOP 1 *
        FROM   ***
        WHERE  ***
        ORDER BY ***
    )
   UPDATE cte WITH (ROWLOCK, READPAST)
   SET
      ***
   OUTPUT INSERTED.*

Entity Framework get the next available row exclusively

4 Answers4