*
The moose likes JDBC and the fly likes How to achive Optimistic Locking for  an Application Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "How to achive Optimistic Locking for  an Application " Watch "How to achive Optimistic Locking for  an Application " New topic
Author

How to achive Optimistic Locking for an Application

Ravi Kiran Va
Ranch Hand

Joined: Apr 18, 2009
Posts: 2234

Hi ,

I am using JSP , Servlets for my Front End , Sessions Beans as Controller and JDBC for doing backend operations

Please let me know how is it possible to achive Optimistic Locking for my Application .

Thanks in advance .

Save India From Corruption - Anna Hazare.
Stephan Mueller
Ranch Hand

Joined: May 05, 2010
Posts: 50
Google is your friend.
From http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/topic/com.ibm.db2.luw.apdv.java.doc/doc/c0052062.html it looks to me as if this
depends on your dbms vendor, hence it depends on the JDBC driver you are using. You'd be more lucky if you read the driver manual
or tell us the DBMS you are using, so people who are working with this driver could help you.


1. Make it run - 2. Make it run correctly - 3. Make it pretty OR fast/small - 4. ??? - 5. Profit
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

Yes it is possible.

You shouldn't need to depend on any database or driver feature to implement optimistic locking. Have a search through this (or the ORM forum) for how to do it.


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Ravi Kiran Va
Ranch Hand

Joined: Apr 18, 2009
Posts: 2234

Thanks for the link , which shows nice information about Optimistic Locking with respect to JDBC Applications .

Optimistic locking is a technique that applications can use to release locks between SELECT and UPDATE or DELETE operations. If the selected rows change before that application updates or deletes them, the UPDATE or DELETE operation fails. Optimistic locking minimizes the time during which a given resource is unavailable for use by other transactions.


But can you please explain me what does the above mean ??

Thanks in advance.
Ireneusz Kordal
Ranch Hand

Joined: Jun 21, 2008
Posts: 423
Ravi Kiran V wrote:
But can you please explain me what does the above mean ??

Hi,

To prevent data corruption in cases when two sessions (users) try to modify the same table row, you must lock records.
Consider a simple case:
1. User A retrieves a record (for example containing customers data ) and displays it on the screen.
2. User B retrieves the same record and displays it on the screen.
3. User A modifies the address (street number), updates and commits changes ....
... but here data displayed on the screen of user B is not atomagically refreshed !!!
4. User B modifies the address (telephone number), updates and commits changes.
After a while user A retrieves the same row again and sees that his changes are lost
- street number is the same as it was before the update.

This situation is called 'lost update'.

Standard database automatic locking mechanism (database locks records on delete/update, unlock it on commit/rollback)
will not protect you from such kind of errors, you must apply some strategy to prevent this.
And here optimistic or pessimistic locking methods are used.

In pessimistic method the record is locked before the user A modifies values on the screen (for example user A clicks "update" button
which locks the row), and the lock is held until user A saves (commits) his changes to the database.
When user B in the same time will try to change data, it will get error 'record is locked'.
This method has some drawbacks - if user A locks a record, and then leaves his computer and goes to the lunch,
the record remains locked and no one can make changes to this row.

In the second method - optimistic locking - the row is not locked, but an application retrieves data (the row content),
holds old and new data in the memory ('new' - means data entered by the user), and in the end when user makes his changes
(he clicks "save changes" button), the application locks the row, retrieves current row content and then compare it
to the 'old' data stored in the memory
- if the current record content in the database is the same as record data in the memory, it means that nobody
has changed the row since the time the application retrieved data, and it is safe to store changed (new) data in the database.
And, in this case, new record content is updated and commit to the database, and in the end the row is unlocked.
But if this comparison is negative (row content and 'old' data in the memory differ) - it means that someone other
has changed data in the meantime, and we cannot save our changes to the database
because we can overwrite changes made by the other user.
So the application display the message 'Sorry, other user has changed the row - you must retrieve modified data
and start entering your changes from the beginning'.
In this method the row is locked only in very short time ('locking granularity' is low), but sometimes users must do their work twice.

Hope this helps and is clear enough ... my english is probably horrible
Ravi Kiran Va
Ranch Hand

Joined: Apr 18, 2009
Posts: 2234

Thanks Ireneusz for your time , you have explained the concepts very clearly especially when the Locking is needed generally and pessimistic locking concepts .

Coming to Optimistic Locking , there are many comparisions that re to be done before doing any updation , Are these things should be taken care by the developer or Database Engine takes care of this things ??

One more question please .

How can we know what Mechanism of locking , does a specific Database does by default ??

Waiting for your replies .

Thanks in advance .
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336


Coming to Optimistic Locking , there are many comparisions that re to be done before doing any updation , Are these things should be taken care by the developer or Database Engine takes care of this things ??

Yes, a versioning field needs checked. This is done by the developer.


How can we know what Mechanism of locking , does a specific Database does by default ??

Optimisitc locking does not (explicitly) use any kind of database locking (as Ireneusz says).
Ravi Kiran Va
Ranch Hand

Joined: Apr 18, 2009
Posts: 2234

Thanks Paul .
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: How to achive Optimistic Locking for an Application
 
Similar Threads
How to assign a javascript variable to java varisble?
How EJB handles DB Locking ?
Synchronizing MDBs
After customer selects seat then stays idle for a minute or two
Optimistic Or Pessimistic locking .