3

I use Entity Framework so process long running tasks (10-30 secs on avg). I have many instances of workers and each worker fetches the next task id from a database table and with that it gets to the work description for that id.

Of course, the access to the task table must be serialized so that each request from a worker gets a new id. I thought this would do it:

static int? GetNextDetailId()
{
  int? id = null;
  using ( var ctx = Context.GetContext() )
    using ( var tsx = ctx.Database.BeginTransaction( System.Data.IsolationLevel.Serializable ))
    {
      var obj = ctx.DbsInstrumentDetailRaw.Where( x => x.ProcessState == ProcessState.ToBeProcessed ).FirstOrDefault();
      if ( obj != null )
      {
        id = obj.Id;
        obj.ProcessState = ProcessState.InProcessing;
        ctx.SaveChanges();
      }
      tsx.Commit();
    }

  return id;


} // GetNextDetailId

Unfortunately when I run it with 10 workers I nearly immediately get

Transaction (Process ID 65) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

I do not have any explanation for this behavior. I know deadlock situations: we have two or more resources and two or more processes that try to aquire the resources not in the same order. But here we only have one resource! All I want is the processes to have sequential access to this resource. So if A has a transaction open, B should simply wait until A commits/rollbacks. This seems not to happen here.

Can someone please

  1. shed some light what is going on here, to educate me.

  2. Give a ( "THE?" ) solution to the problem. I assume that this problem should be very common in programing.

Thanks Martin

ProgrammingRookie
  • 71
  • 8
  • The Default Isolation level is set to Serializable, which in usage, creates unnecessary blocking and deadlocks. Therefore, it is suggested you override the default isolation level to ReadCommitted, which reflects the default within SQL Server. – Quinn Johns Nov 19 '15 at 20:55
  • ... sry, that has nothing to do with the problem. Solution please? – ProgrammingRookie Nov 19 '15 at 21:39
  • No, Rookie, but just suggesting it could help reduce the number of deadlocks, when you have a larger degree of requests. If it was a solution, would have posted below. :) – Quinn Johns Nov 20 '15 at 00:28

3 Answers3

0

You can verify this using SQL Profiler to sniff the SQL statements that are being executed on your SQL server, but the issue likely is that, even though you are inside a transaction with the isolation level set to serializable, an exclusive lock is not being issued, so what is occurring is that two threads are accessing the same row at the same time, and both are trying to update it.

The best advice I've seen is that, if you need to control locking at this level, execute a stored procedure or SQL instead of attempting to use LINQ.

Locking a table with a select in Entity Framework

Community
  • 1
  • 1
DVK
  • 2,726
  • 1
  • 17
  • 20
0

shed some light what is going on here, to educate me.

  1. ctx.DbsInstrumentDetailRaw.Where ... acquires a shared lock on the table. With serializable isolation level, this lock is held until the transaction is committed or rolled back.
  2. ctx.SaveChanges() needs an exclusive lock to update the row.

Two or more transactions can simultaneously get a shared lock in step 1, but then none of them can get an exclusive lock in step 2. Deadlock.

Give a ( "THE?" ) solution to the problem.

I can think of 2 ways to solve this problem.

  1. Change the order of operations: update one row, then return it. You will have to use a stored procedure to do it in EF.
  2. Use a lower isolation level (e.g. repeatable read) and optimistic concurrency. You will not get deadlocks (shared locks will be released immediately after select). When 2 workers try to update the same row, one of them will get a concurrency exception.
Jakub Lortz
  • 14,616
  • 3
  • 25
  • 39
  • Tnx. I did not know that two transactions with serializable isolation level could simultaneously read the same rows..... I thought to avoid exactly that was the purpose of "serializable". Maybe I need something more like a r/w lock for a row. – ProgrammingRookie Nov 19 '15 at 20:22
0

OK, I now do this:

select 1 from InstrumentDetailRaw with (tablockx, holdlock) where 0 = 1"

at the beginnning of my transaction, which, according to this post:

Locking a table with a select in Entity Framework

does the trick. No deadlocks with 10 workers running for hours now.

Community
  • 1
  • 1
ProgrammingRookie
  • 71
  • 8
  • This will lock the table, so if you are running more than one worker against this database it will have to wait for the other one to finish what it is doing. – slinzerthegod Jul 06 '16 at 08:06