entity framework - Code First - Retrieve and Update Record in a Transaction without Deadlocks -

i have ef code first context represents queue of jobs processing application can retrieve , run. these processing applications can running on different machines pointing @ same database.

the context provides method returns queueitem if there work do, or null, called collectqueueitem.

to ensure no 2 applications can pick same job, collection takes place in transaction isolation level of repeatable read. means if there 2 attempts pick same job @ same time, 1 chosen deadlock victim , rolled back. can handle catching dbupdateexception , return null.

here code collectqueueitem method:

public queueitem collectqueueitem() {     using (var transaction = new transactionscope(transactionscopeoption.required, new transactionoptions { isolationlevel = isolationlevel.repeatableread }))     {         try         {             var queueitem = this.queueitems.firstordefault(qi => !qi.islocked);              if (queueitem != null)             {                 queueitem.datecollected = datetime.utcnow;                 queueitem.islocked = true;                  this.savechanges();                  transaction.complete();                  return queueitem;             }         }         catch (dbupdateexception) //we might have been deadlock victim. no matter.         { }          return null;     } } 

i ran test in linqpad check working expected. here test below:

var ids = enumerable.range(0, 8).asparallel().selectmany(i =>     enumerable.range(0, 100).select(j => {         using (var context = new queuecontext())         {             var queueitem = context.collectqueueitem();             return queueitem == null ? -1 : queueitem.operationid;         }     }) );  var sw = stopwatch.startnew(); var results = ids.groupby(i => i).todictionary(g => g.key, g => g.count()); sw.stop();  console.writeline("elapsed time: {0}", sw.elapsed); console.writeline("deadlocked: {0}", results.where(r => r.key == -1).select(r => r.value).singleordefault()); console.writeline("duplicates: {0}", results.count(r => r.key > -1 && r.value > 1));   //isolationlevel = isolationlevel.repeatableread: //elapsed time: 00:00:26.9198440 //deadlocked: 634 //duplicates: 0  //isolationlevel = isolationlevel.readuncommitted: //elapsed time: 00:00:00.8457558 //deadlocked: 0 //duplicates: 234 

i ran test few times. without repeatable read isolation level, same job retrieved different theads (seen in 234 duplicates). repeatable read, jobs retrieved once performance suffers , there 634 deadlocked transactions.

my question is: there way behaviour in ef without risk of deadlocks or conflicts? know in real life there less contention processors won't continually hitting database, nonetheless, there way safely without having handle dbupdateexception? can performance closer of version without repeatable read isolation level? or deadlocks not bad in fact , can safely ignore exception , let processor retry after few millis , accept performance ok if not transactions happening @ same time?

thanks in advance!

id recommend different approach.

a) sp_getapplock use sql sp provides application lock feature can have unique app behaviour, might involve read db or ever else activity need control. lets use ef in normal way.

or

b) optimistic concurrency http://msdn.microsoft.com/en-us/data/jj592904

//object property: public byte[] rowversion { get; set; } //object configuration: property(p => p.rowversion).isrowversion().isconcurrencytoken(); 

a logical extension app lock or used rowversion concurrency field on db. allow dirty read. when goes update record collected, fails if beat them it. out of box ef optimistic locking. can delete "collected" job records later easily.

this might better approach unless expect high levels of concurrency.