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?
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. Kyle P.S. For a good description of optimistic locking schemes, see Martin Fowler's Enterprise Architecture Patterns
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?
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. Thanks.