CODEWITHSUNDEEP
javaspringhibernate
Hrishikesh
Hrishikesh

Reputation: 2053

Queue management in the database

To begin with i am looking out for different ways to solve this problem.

I have a queue within our application which is maintained in a database table. There is a scheduled processor which will look at the queue and pull out records based on a STATUS field on the record. It processes those records and on success deletes the record from the table.

The problem is my application is clustered. So, there will be several instances of the scheduled processor which will pull out the same records and then process them...

To solve this, the approach that i followed is I update the status of the record before it is processed(say from PENDING to WORKING) and also added a version on 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 the Processor tries to update it when someone has already updated the record it will give an exception, and hence will move on to the next record) 3) On Success. Delete the record, else update it back to PENDING.

Now, by doing this it would fairly solve the problem but dont quite like this idea...

Wanted to find out how people who face a similar issue have resolved it.

I had another way to solve this, since the same application populates the table, assign it with the host who populated it and the scheduled processor on that particular tomcat only lookup for the records of that host. Basically trying to minimize the thrashing that will go on with the first solution.

it is a Spring 3.0.5 and Hibernate application

Upvotes: 3

Views: 1447

Answers (1)

Tomasz Nurkiewicz
Tomasz Nurkiewicz

Reputation: 340933

This is quite common problem. You can tackle it from different ways:

  • Owning the currently processed record by assigning new status + optimistic locking. This is your approach, it will work, but you have to remember about cleaning up the table if the node currently processing some node dies.

  • Same as above but with pessimistic locking - might be a better approach if there is a lot of nodes and optimistic locking failures occur often

  • External/global locking - only a single node can access the whole queue table at a time. You can use table-level locking to block all other nodes or some.

  • When placing new record in queue table, randomize and assign it to a given node, so that no other node can process it. Don't go this path, maintanence nightmare, e.g. when adding or removing nodes.

  • Use , it will automatically cluster jobs and run them on exactly one node. It uses techniques similar to above (pessimistic locking on a shared database)

  • ...or just use an ordinary JMS provider, database isn't really meant to be used as a queue...

Upvotes: 2

Related Questions