permaculture playing cards*
The moose likes JDBC and the fly likes Avoid Concurrent Update Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


JavaRanch » Java Forums » Databases » JDBC
Bookmark "Avoid Concurrent Update" Watch "Avoid Concurrent Update" New topic
Author

Avoid Concurrent Update

arun raj singh
Greenhorn

Joined: Mar 12, 2012
Posts: 6
Hi,

I have one scenario where in there are many user login to an application. There is one page which fetches data from the database to the logged in user and user can update that page to update the corresponding data in data base. how to avoid a situation like this that one user fetches the data and wait for long time to update it and meanwhile some other user fetches it and updates it? I know that synchronization can help here but it going to hit hard on performance.

Any suggestions would be highly appreciated.

Thanks,
Arun
Rob Spoor
Sheriff

Joined: Oct 27, 2005
Posts: 19697
    
  20

Welcome to the Ranch!

You definitely do not want to use synchronization here. If the first user is not doing anything for hours, the second user would have to wait all that time for even that response to end (it would more likely timeout though).


SCJP 1.4 - SCJP 6 - SCWCD 5 - OCEEJBD 6
How To Ask Questions How To Answer Questions
arun raj singh
Greenhorn

Joined: Mar 12, 2012
Posts: 6
Thanks Rob for reply

It was the first reply for my thread and so very special..

coming to the question.. yes i do not want to use synchronization here.. what could be other way around for it?
Nauman Hasan
Ranch Hand

Joined: Jul 27, 2005
Posts: 34
Hi Arun,

You can investigate Optimistic Locking as a possible solution for this problem.

Regards,
Nauman
Jayesh A Lalwani
Bartender

Joined: Jan 17, 2008
Posts: 2383
    
  28

Not sure if you are using Hibernate, but if you are Hibernate supports automatic optimistic locking.

If you aren't, you will need to implement optimistic locking yourself.

ETA: Technically, you could have a "solution" that is based on pessimistic locking too. You can have a flag in the record that indicates that the record is being edited by another user. Then you can have something in the UI that the user can use to indicate that they are going to edit the document which would set the flag. When another user opens the record for editing, you check the flag and stop the user. Pessimistic locking can create problems. For example, if a user locks a record and then forgets that s/he locked it, you will have a record that is locked forever. You will have to find some way for the user to find his/her locked records, and for an admin to override the lock.

Pessimistic locking works for entities where pessimistic locking works from the business POV, For example, if you have a table that contains a list of tasks that can be done by a any user in a group. Any user in the group can pick a task, but once a user has started working on the task, you don;t want anyone else to work on it. This is a classic case of where pessimistic locking makes sense.

For the most part, unless you have a business reason to do pessimistic locking, you should do optimistic locking
arun raj singh
Greenhorn

Joined: Mar 12, 2012
Posts: 6
Thanks guys for your reply...

I am not using hibernate here. I have to handle it through the servlets and jsp.I am using synchronization right now to handle this but its hitting on performance.Is there no other way out.
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18570
    
    8

The "pessimistic locking" method which you're using just doesn't work well in a web application, as you can see.

The best way to handle the problem is to design a system in which you aren't going to have two people trying to update the same data at the same time. You do this by making only one person responsible for updating each data item. You're still going to have the occasional race condition, for example when somebody updates something which isn't their job, but you can deal with those on an ad-hoc basis after the fact.

However if you're stuck with a situation where many people are trying to update the same data item at the same time, then you're going to have to implement a process like what Jayesh described.
Jayesh A Lalwani
Bartender

Joined: Jan 17, 2008
Posts: 2383
    
  28

Sorry, no easy answer here. You'll have to implement optimistic or pessimistic locking yourself.

If you want to do optimistic locking, add a version number column to the table. When you insert new record set verisonnumber = 1. When you get a record to show to the user, get the version number and store in a hidden form field. When the user submits the form, the version number should be submitted in the update request. In the update request, get the latest version number for the record, and see if it is different from the version number in the request. If it is different, show error to user. If it is same, increment version number and save.
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18570
    
    8

There are other ways to do optimistic locking, but that one's as good as any other.

However as I implied in my earlier reply, sometimes your design makes these locking issues worse. Here's an example: let's suppose you have a Customer table. Now there's a lot of different pieces of information you keep for a customer, like name and address and phone number and credit limit and preferred delivery date and on and on. And let's suppose that your design for updating customer information just displays all the information you have for a customer and invites people to change it.

Then at 9:47 on Tuesday morning somebody tries to change the customer's phone number, and somebody else tries to change the customer's credit limit. Now this wouldn't be a problem if you just updated the columns that the user changed; it becomes a problem if you update all of the fields with the values that came back from the web page. Which is the easier thing to do, so that's probably what happens.

Even that isn't a problem if you break the customer data into groups based on areas of responsibility. Then the sales people get to update the customer's phone number but not his credit limit, and the credit people get to update the customer's credit limit but not his phone number. Then you have two separate transactions which update different sets of columns, and so they don't conflict.

All of which just adds up to "No easy answer here" as Jayesh said.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Avoid Concurrent Update