hi

i have a beginner question. i have i table holdling jobs to be processed
by multiple users. every user get himself a set of jobs (the oldest ones
by a timestamp) and, of course, i want that not two get the same jobs...

sounds for me like transactions would be necessary but, as i understand,
they dont prevent multiple users "seeing" states in the db but allowing
them to successfully commit actions... in my case i think something like
SELECT FOR UPDATE would be appropiate but this is not supported on
all databases...

now,
i thought a solution would be a simple update. in my table i have a field
indicating if the record is "in work" at a user, if its user_id is there
(0 means, the record is not processed and is waiting to be processed).

so I could simply "reserve" a work set by doing something like

update my_table set in_work=user_id where in_work=0 order by timestamp limit work_set_size.

after the records are reserved I can take as much time as I want and select and process the records....

I think the update command itself should be "atomar" what would guarantee that not two users could reserve the same records.

The other way round (first select free record and than update the in_work
flag) is not save because between select and updates other users could
have the same idea... ;-)

I know...have talked quite long now...sorry...

QUESTION:

- are my thoughts ok or am I missunderstanding some concepts?
- is there an easier solution?

AND FINALLY

- how can I realize this with Hibernate? As I know Hibernate (and I know
it little), I have to load and object first, before I can update is...this is
my second, "unsave" method...
is the only possiblity to go back to native sql....get the connection from
a session object and perform the sql update query?


thank you for any help!
mac

You will need to use locking to get this to work correctly.

I thought that...but what locking does the following:

- I start a transaction, select 10 records,....
- Meanwhile another user makes a select and does not even
get or see my selected records....

I thought with a transaction the second user will alway be able to
get my taken records and will finally have an exception if he want
to commit them (because I have them in "my transaction" or said
diffently, because the second user has a wrong version).

But I dont want it come so far...the second user should never run
into this problem...

The issue is mainly how to set the "in work flag" (which shows that
a record is taken by another person) in an atomic way...
As I understand it, a transaction will only guarantee me that never
to persons will be able to get two identical records and perform
successful commit with them...only one will succeed....but the other
will fail.

I see only two possibilities:

- first do the update as describe earlier
- use SELECT FOR UPDATE (which is not supported by every database)

But my knowledge ich quite limited with transactions so I would be
happy, if someone could concretely tell me what to do....


Thanks a lot!!!!!

Actually, thats the function of a lock, not a transaction. Transactions have nothing to do with pretecting data from concurrent update. A transaction will make sure that if user #1 tries to update multiple Jobs to "in work", neither wil get updated if any work done in the transaction fails.

True, some databases do not support the concept of selecting for update, but don't they all support the concept of locking? I thought they did (all the ones I've ever worked with do), but maybe I'm wrong.

So why not start a transaction, select your rows, lock them for update, set the "in work" flag, and commit the transaction.

Also, I would add with this the concept of optimistic locking or version checking. Hibernate has some built-in capabilities for versioning.

Well, not entirely true depending on the isolation levels of the transactions