Environment:
Hibernate 3.2.1 with Annotations
MySQL 5.0.22, InnoDB, Transactions: Serializable
Spring 2.0.2 and MVC (session in view for some URLs)
JMS to be added

Have myself a webapp that mostly offers REST-style webservices.

Essentially, workers ask it for work and I do a query to see what work (if any) they can do given their abilities. It's a mini-grid and my servers the grid controller.

It works fine but I've had to do some things which I consider distasteful and I was hoping to hear some workarounds from folks.

The main problem is there are sometimes too many workers that "simultaneously" become elligible to work on a new piece of work. I quote "simultaneously" because the query to select out the next piece of work (which depends on things like did an outstanding, already vended job just become tardy (overdue) and should be revended again on the assumption the worker crashed or cannot reach the server with results, priority, et al.) It's a fairly expensive query (2 seconds in some cases, more depending on system load) and 2 seconds is a large window for workers to collide.

The distasteful hack was to put connection limits in (I have a Java 5 Mutex which allows n-simultaneous workers of any type through) and a synchronized LRU cache of recently vended pieces of work to see if the work item in the DB was already claimed within the last few seconds. Also, I limit my query to n work items so that if my first choice is already in the LRU, I can go onto my second choice, etc.

The Mutex kept the load under control in the case of the thundering herd swamping all at once.

The LRU keeps said thundering herd (reduced though it be) from getting the same work item and eventually having n-1 transaction rollbacks.

What are my options so I can make this work effectively in a cluster? I'd like to keep this entirely in the DB (but hopefully with no stored procedures).

Table:
WorkItems
WorkItem_Id, Priority, Name, ExpectedDueDate, Result, Type, WorkAttempts

My query (a left outter join of the table back onto itself) comes back with an array, effectively the pair of work items. The first of the pair is the source (work is effectively chained) item (completed successfully), and the uncompleted item.

How do I keep items from being revended twice without having to use the LRU described above which only works in the single JVM case? I'd like the to-be-vended record to be invisible to parallel queries.

What's a good strategy for this?

many thanks,
td