Database Queue Management
To begin with, I am looking for various ways to solve this problem.
I have a queue in our application that is maintained in a database table. There is a scheduled processor that will queue and pull records based on the STATUS field in the record. It processes these records and, when deleted, removes the record from the table.
The problem is my application is clustered. Thus, there will be multiple instances of the scheduled processor that will pull the same records and then process them ...
To solve this problem, the approach I used is I update the status of the record before processing it (say from PENDING to WORKING) and also added a version to the Entity mapping of the table, so the sequence of actions would be
1) Query the table for PENDING records. 2) update the status as WORKING. (If another instance of Processor tries to update it when someone else has already updated a record, it will throw an exception and therefore move on to the next record) 3) Success. Delete the entry and then update it to PENDING.
Now by doing this it will solve the problem, but not quite like this idea ...
It is required to find out how people faced with a similar problem solved it.
I had another way to solve this problem, as the same application populates the table, assigns it to the host that populates it, and the paid processor on that particular tomcat only looks for that host's records. Basically trying to minimize the chopping that will continue with the first solution.
this is Spring 3.0.5 and Hibernate application
source to share
This is a fairly common problem. You can solve it in different ways:
-
Ownership of the currently processed record by assigning a new status + optimistic lock. This is your approach, it will work, but you have to remember to clear the table if node is currently handling some node.
-
Same as above, but with pessimistic blocking - may be a better approach if there are many nodes and optimistic failures often occur.
-
External / Global Locking - Only one node can access the entire queue table at a time. You can use table-level locking to lock all other nodes or some.
-
When placing a new entry in the queue table, randomize and assign it to the given node so that no other node can process it. Do not go down this path, a nightmare, therefore, when adding or removing nodes.
-
Use quartz-scheduler, it will automatically cluster jobs and run them on only one node. It uses techniques similar to those described above (pessimistic locking on a shared database)
-
... or just use a regular JMS provider, the database is not meant to be used as a queue ...
source to share