File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes Servlets and the fly likes JDBC & Web Environment Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

Win a copy of Java Interview Guide this week in the Jobs Discussion forum!
JavaRanch » Java Forums » Java » Servlets
Bookmark "JDBC & Web Environment" Watch "JDBC & Web Environment" New topic

JDBC & Web Environment

Mujir Shaikh

Joined: May 18, 2002
Posts: 10
Hi Folks,
I have a requirement that I acquire locks on Rows while a user is updating some fields on a Web Page (Intranet). Now the Issue is that this DB Lock should be removed only after he finishes his operations on 5-6 pages i.e. I acquire Locks on DB thru setting Transaction Isolation Levels and my DB Transaction is spread across 5-6 HTTP Requests!! How do I go about this?
One idea is to put the Connection Object in HttpSession and commit the Transaction after the 5-6 Pages. But Connection Object is not Serializable, so in case of Server Failure, safe fail-Over is not possible.
Any ideas on this guys?
Kyle Brown
Ranch Hand

Joined: Aug 10, 2001
Posts: 3892
Long transactions are always, always, always a bad idea. Instead, why don't you look into something like an optimistic locking scheme using timestamps or overqualified updates. That way you can fetch back all the data and store it internally in the HttpSession until the user is done. Then you try to update the rows and if any of them have changed, you simply abandon the update and let the user try again. That's a MUCH better option than trying to hold locks across 6 screens of user think time! Think about what can happen in a web application
(1) Users can walk away
(2) Requests can get lost or reissued
In both of those cases, what happens to the status of your locks? How do you intend on releasing them in the case of (1)? The answer is DON'T DO THAT!!! Don't try to use pessimistic locking -- use optimistic locking instead.
P.S. For a good description of optimistic locking schemes, see Martin Fowler's Enterprise Architecture Patterns

Kyle Brown, Author of Persistence in the Enterprise and Enterprise Java Programming with IBM Websphere, 2nd Edition
See my homepage at for other WebSphere information.
Mujir Shaikh

Joined: May 18, 2002
Posts: 10
Your suggestion is absolutely correct Kyle. I thought of the same things before deciding to move onto Pessimitic Locking.
The first option I thoguht was managing the locking programmatically (timestamps/flags). But the stage of the project I am in, I cannot afford to spend time on coding (some things r beyond our control, business matters!).
Easiest way out was to use Pessimistic Locks (not much of work, but def. a big problem & bad design, if the user session times out, or server goes down).
But I was just thinking if it is possible to do this cleanly using Pessimistic Locks. For eg. something like we start a service on another machine which takes the SessionId and returns the Connection Object (no need for Connectiomn Object to be Serializable and our problem of Server Failure is solved). But then if this one Machine returning the Connection Object goes down, its a problem.
Any similar ideas/suggestions? I would still try to convince the Client to give time to implement Optimistic Locking, but if it isnt possible, any ideas?
Paul Stevens
Ranch Hand

Joined: May 17, 2001
Posts: 2823
The Java Ranch has thousands of visitors every week, many with surprisingly similar names. To avoid confusion we have a naming convention, described at JavaRanch's naming policy. . We require names to have at least two words, separated by a space, and strongly recommend that you use your full real name. Please change to a new name which meets the requirements.
I agree. Here's the link:
subject: JDBC & Web Environment
It's not a secret anymore!