Hibernate version: 3.0.5

I plan to use a table to create a queue of work from which multiple threads can retrieve units, but I only want each thread to retrieve a single unit of work at a time. Also, I'd like to avoid collisions between threads looking for a new unit of work.

So, the plan is to have a "lock" column into which I'll insert a GUID that I've generated previously. This way, other threads can look for the first available row for which the "lock" column is null. Enter the problem.

So the order of opertion is this:
1) Update the first row found that has a lock column equal to null and set the lock column to my GUID.
2) Read the row that I just updated so that I can get the work information.
3) Commit

The reason for doing the update first is so that I get a database lock at the same time that I select a row in the database. Think of it as a database semaphore.

The problem is how to limit the number of rows affected by my update? I only want to affect the very first row I encounter that has a null lock column, so that I don't create problems for other threads looking for units of work.

Is there a way to do this? I haven't found one yet. Still looking.
Thanks,
Dave

Dear Dave:
What If two threads read the same row at the same time or at least before the lock has been set. You will end up with the row info in both threads and then one of them will get the lock and the updates in the second one will be lost. I am not a hibernate expert so I can not tell if this can be done through hibernate or not (I don't think so). Even it it can be done through hibernate (unlikly event), I would still use threads in my Data Access Layer. Define update method to be synchronized and set up the critical section just like you do in any threaded program. Therfore you garantee no two threads will be updating the same record at the same time.

Hope this helps.

I can't count on Java synchronization because it is possible that the threads reading the "queue" may not be running in the same VM.

I hope my description wasn't too confusing, but when I talk about locking the row, I mean at the database server core level - not my own "lock" column. I realize that if I were to rely on my own column that there is no way to guarantee the safety that I need. However, the database server is supposed to ensure that no two processes can get a lock on the same row of data simultaneously. This is what I'm after.

In the old days, I would have written a stored procedure that would perform an UPDATE statement followed by a SELECT statement. This would get me my row lock, then I could read the data. I'm trying to avoid using stored procedures if at all possible. I just can't find a way to limit the number of rows affected by the UPDATE.

I am doing this exact logical operation using NHibernate. However, I am using optimistic locking with the version column. Each process gets several items to process at a time, which really performs an HQL query to retrieve the top X number of items and then it iterates through each attempting to assign each item to itself. If I get an ObjectStaleException when attempting to persist the assignment, I skip that item and move on. No database transactions, so little contention. This works best for items that take some significant amount of CPU time to process as there can be a lot ObjectStaleExceptions if the processes are always looking for items. After retrieving items, I process the retrieved items in a thread pool that is adjusted for the particular task.

Hope this helps.

Good call. I was so blinded by my earlier experience using SQL to solve this problem, that I completely forgot about making use of the version of the object to do the job for me.

Glad I could help. I am using this system to perform massivly parallel computing and it works well. I don't see too many clashes and contention in the database is almost non-existent. Good luck!