alternatives to system-wide mutex when processing queue from DB?

Question

I've got some (Entity Framework 4) code that looks like this:

class QueueItem{ public bool Processed { get; set; } // ... other fields } class QueueContext: System.Data.Entity.DbContext { public System.Data.Entity.DbSet<QueueItem> Queue { get; set; } public void ProcessItems() { do { var item = Queue.FirstOrDefault(q => !q.Processed); if(item == null) break; ProcessItem(item); item.Processed = true; SaveChanges(); } while(true); } //... }

I want to change it to be multithreaded external to the ProcessItems method. In fact, I want multiple processes/appDomains to be running this code simultaneously. How do I keep multiple processes from picking the same item out of the Queue? I could use a system-wide (named) mutex, but I've seen those be terribly slow. I'm looking for some kind of atomic "pull and mark as in progress". I'm using SqlServerCE4 as the data storage.

Bryn Keller · Accepted Answer · 2012-05-04 21:27:56Z

It might be better to use an actual message queue like MSMQ, Azure Queues, or even Sql Server Service Broker (wouldn't work with CE, of course). Something like Redis might be easier too. If none of those are an option, you'll probably have to do something like the following:

Add an "InProgress" field to your table
Ensure that you have optimistic concurrency enabled for your table (e.g. via a RowVersion)
When you query, query for things that are neither InProgress nor Complete
When you get the first entity, update that InProgress field to indicate you've grabbed it, and SaveChanges.
Handle the concurrency exception if it happens. This means someone else has already grabbed this item at the same time you did, but they saved first. In that case, restart your loop and look for the next one.
If no exception was thrown, process as you have been, except that you have to use appropriate error handling to make sure that InProgress always gets cleared, even if an exception is thrown at some point after you marked it InProgress.

Collectives™ on Stack Overflow

alternatives to system-wide mutex when processing queue from DB?

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related