I have a list of items in a table. Now I want to dequeue the next item to be processed based on some condition. In the same action the item needs to be updated to reflect that its being processed.
So the non-atomic version would be:
var item = DB.Single<...>(...);
if(item!=null){item.isBeingProcessed=true; DB.Save(item);}
Point is, running this in a distributed environment with multiple worker processes it MAY occur that an item gets dequeued by two different workers.
How would I solve this with ORMLite? Are transactions the way to go?
OrmLite doesn’t have any such concept of atomic MQ APIs. First you’d need to workout how you would accomplish this in SQL with your RDBMS which is going to be different in every DB using their different features, locks & locking behavior.
If you’re using SQL Server Using tables as Queues shows examples of implementing different queue types with SQL Server using stored procedures, an example of implementing an atomic dequeue operation is covered by this stackoverflow answer.
In basically all cases you would need to use Custom SQL APIs to execute the custom SQL for the RDBMS you’re using, although if each worker has a unique id then you could assign it to your worker process id first before selecting the row as done in this example, although even this is RDBMS specific.