aspose file tools*
The moose likes JDBC and the fly likes How to keep database safe from multiple operations? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of EJB 3 in Action this week in the EJB and other Java EE Technologies forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "How to keep database safe from multiple operations?" Watch "How to keep database safe from multiple operations?" New topic
Author

How to keep database safe from multiple operations?

Joshua Cloch
Ranch Hand

Joined: Apr 27, 2006
Posts: 95
Hey guys!

Here is the problem: I have implemented a website for internal use. The users may edit the same line in database concurrently, which may cause collision.

However, I can set a lock on the particular line before edit operation. Do you think this is the only solution?

In some extream case, what can I do if the users require the lock at the same time? Is there any idea?

Thanks a lot!


truehh@hotmail.com
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 39547
    
  27
One solution would be to keep a "last modified at" field for that row, which gets updated each time the row is written to. Then, if an editing operation is started, carry the value of that field in the web page. Upon submit, retrieve the field from the DB, and compare its value to the one that was submitted along with the new data from the web page. If the value in the DB is later than the one from the submit, then the data was modified by someone else during the edit operation, and the edit should be rejected.


Ping & DNS - updated with new look and Ping home screen widget
Joshua Cloch
Ranch Hand

Joined: Apr 27, 2006
Posts: 95
That is really a good idea,however,what if there are two or more users retrieve the field at the same time and, submit the latest data at the same time too?
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

This is a problem with this implementation of "optimistic locking" (which is what Ulf is describing), albeit one that will occur only very, very rarely. If you use a Timestamp datatype for your versioning field you use such a small unit that it becomes very unlikely that the threads will end up with the same value. If this does happen you will get lost updates - since one will overwite the other. It is better practice to use a numeric datatype for your versioning field and increment it every time your application starts an transaction that will update the record.

Have a google for "optimistic locking" if you want to find out more.


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Joshua Cloch
Ranch Hand

Joined: Apr 27, 2006
Posts: 95
Thanks guys,

Your ideas are very useful in my application--which is not very big.

Still, I am curious about the giant website,like ebay, amazon, how do they solve this kind of problem: say, there are more than two users operating exactly the same in an auction. It may happen, although not that often.
Allen Mathew Williams
Greenhorn

Joined: Jan 30, 2007
Posts: 8
Hi,
In addition to optimized locking, have you considered synchronizing the part of your code that actually updated the database, then , at any given time only one thread may access the method, though this can slow your app, if the db is responding slow or if the connection fails, it may solve your issue. ( provided you use it carefully)

Allen.
Justin Chu
Ranch Hand

Joined: Apr 19, 2002
Posts: 209
    
    1
For transaction that crosses multiple web requests, I'd suggest implementing your own row level locking.

Create a database transaction to obtain a lock.

TX starts (make sure isolation level is TRANSACTION_REPEATABLE_READ)
"SELECT lock FROM table WHERE rowid = 12354"
if (lock == null){
// update to set lock
} else {
// fail to obtain lock
}
TX ends

I've only use this method for critical things such as implementing a content management system and the user needs to hold on to a row for extended amount of time.
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

Originally posted by Allen Mathew Williams:
Hi,
In addition to optimized locking, have you considered synchronizing the part of your code that actually updated the database, then , at any given time only one thread may access the method, though this can slow your app, if the db is responding slow or if the connection fails, it may solve your issue. ( provided you use it carefully)

Allen.


Generally, this is not a good idea. All RDBMSs are designed to handle multiple, concurrent connections. Synchronizing access turn your database into a single user resource (from the point of view of your application anyway), and why do that? Also it does nothing to prevent another application updating the database at the same time so will not guarentee you won't get lost updates.
 
wood burning stoves
 
subject: How to keep database safe from multiple operations?
 
Similar Threads
which lock should be used here ?
Lock the database
Some questions on "Bodgitt and Scarper"
How to persist a Collection using JDBC?
Bodgitt and Scarper : Data class