Hi. I have a database table, web service, and multiple web clients. My web service can have multiple unrelated instances (running on separate jvms). The web clients might hit the services and ask for records from the table. What I want to avoid is that 2 clients will get the same row of the table simultaneously (or while other service is processing it) from the same instance of the service or multiple instances. If I synchronize the piece of code that accesses the DB on the service side it won�t help me because other service from other jvm might still give this row to other client. How do I solve this?
Interesting. There are various strategies for handling these types of issues. I suggest a close reading of the following article to get you started on developing a solution. Locking strategies for database access
In particular you are looking for the pessimistic offline lock. Basically a user cannot select a record unless he owns the lock to the record. And the lock can only be acquired if nobody else owns it. The lock is usually implemented as a user ID field on the record itself or on a lock record that can be joined to the record. There may also be a date/timeout field so that unreleased locks can be detected.
Oracle PL/SQL actually supports the SELECT FOR UPDATE syntax that locks all the records in the cursor until the next commit or rollback. However that doesn't actually help you in your situation as doesn't prevent anyone else from SELECTing these records, it just stops them from modifying them. Oracle, for performance reasons, is "optimistic" in nature.
If everybody used SELECT FOR UPDATE NO WAIT, attempts to select locked records would result in an exception. However anybody using simple SELECTs can still get at the locked records.
Thanks for all your help guys. I found out that I cannot mess with oracle. So what I am gonna do is add status column and change it after I grabbed the row (and make a status check before I grab it). If more then 1 pieces try to manipulate it simultaneously I'll get hibernate exception (by the way do you know what exception exactly?) Thanks again.