Hello, i've got a question regarding database locking!
We're developing an application on OC4J (Orion), and have chosen to develop an EJB application with Bean Managed Persistence, with some or little entity beans.
Now to the classic issue.
Two people is accessing the same row at the same time
They read it fine!
User one alters some information, and presses 'save' on the application
The database row is updated with the information from user one
User two alters some information, and presses 'save'
The database row is now updated with user two's update
User one's update is lost!
How do we avoid this scenario ?
Since we're using bean managed persistence, we could do a specific
SELECT FOR UPDATE
But that requires that we keep the connection alive from the moment User x does a 'SELECT FOR UPDATE' untill he actually makes an update. This, as i see it, can only be done with statefull beans? (feel free to correct me here, or make suggestions)
I've read about 'serialized transaction', and as i understand it, two users can open a row for 'update' at the same time, and when one user updates, the second is automaticlly rolled back, thus forcing him to realod the row with the new data!
But i still have a hard time visualizing this, and how to actually implement it in the bean ? (references?)
How does this actually work ? and do i have to put these transactions is statefull beans ?
Thanks alot !
Optimistic locking is one possibility -- it should be mentioned in the patterns section of this site.
It uses a version number on each row which is invremented each time an update occurs -- if it doesn't match the one you got when you did your select, you know someone else has changed your data.
Thanks alot, i've read it allready..
I would just like to make sure that there isnt a 'prettier' way of doing it... but i've fallen for the TimeStamp method.. and then doing a 'UPDATE ... WHERE TIMESTAMP = x'
and if it returns 0 then ...
its beutiful, and only ONE database IO required :o)
There are two ways these long-lived "transactions" can be handled - Optimistic locking and Pessimistic locking.
Optimistic Locking involves the keeping of a version number as part of your bean's state. The version is updated (incremented) on every operation that modifies the state - ie on every WRITE operation (reads are unlimited). You can only modify the state of a bean if the version you pass matches the beans current version (ie if no-one has gotten in a write in before you).
Pessimistic locking involves that taking out of a "lock" on the data. No-one can modify the bean state, until the lock is "released". If a user takes out the lock, only that user can update the bean - which releases the lock. However, in this approach, you have to take care of the case where the user forgets to release the lock. You DONT want to allow a situation where a user can go rampant, locking up beans and then dissappearing.
The way to handle this is to have an expiry on the lock.
This sounds complicated, but actually, it is very easily implemented with the timestamp that you speak of.
Taking out a lock involves the setting of a (timestamp) field with value of the current time + timeout. The lock is "on" whenever this value is greater than the current time. The lock is automatically "released" when the current time is greater than the value. When the user that takes out the lock, makes the modification that ends the involvement, the lock is then set to the current time, which in effect means it is released (the next caller will see currentTime > lockTimeStamp.