aspose file tools*
The moose likes JDBC and the fly likes Locking Oracle Record Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Locking Oracle Record" Watch "Locking Oracle Record" New topic
Author

Locking Oracle Record

Steve Hill
Greenhorn

Joined: Oct 06, 2004
Posts: 9
Hi,

I have searched the forum and don't seem to find anything that helps me out...

I know how the lock an record in Oracle (Select ... FOR UPDATE)

My question is how to maintain the lock across multiple HTTP requests
ie/ HTTP request to query/display the Record (lock record here)
HTTP request to update the Record (update/release the lock)

The application is servlet based and one servlet handles the query of the data as well as the update of the data. So the servlet generates the Update HTML form with a 'Save Changes' button. When the user presses the button a parameter is passed indicating a update request. The servlet then process the update of data to the Database and then redraws the Update HTML form.
So if I lock the record on the first request, how can I maintain the lock so on the recond request (update) the record is still locked... As you can see, I need to do this, since I will not know if two users have the Update HTML form up and could overwrite each others data.

Thanks in advance
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 61658
    
  67

My question is how to maintain the lock across multiple HTTP requests


This is not a design I would recommend in the web environment.

What if the user who locks the record goes to lunch? has a heart attack? is escorted from the building for improper usage of the copy machine? Your record could stay locked for a long time, gumming up the works.

I'm not a DB expert, but there are patterns out there to deal with this sort of situation. One I have seen used fairly often is a "dirty write" pattern that detects if an update to a record has occurred between the time the data was fetched and the time it is being written back.

I'm sure there are many more. Perhaps those that have more experience with large mutli-user database applications could chime in with patterns that they have seen in use.


[Asking smart questions] [Bear's FrontMan] [About Bear] [Books by Bear]
Steve Hill
Greenhorn

Joined: Oct 06, 2004
Posts: 9
Hi Bear Bibeault,

Thanks for your responce... I am not locked into this design... I am willing to lock the record just prior to the update (which in fact happens anyway - Oracle)... My concern is that if I do this, and two user have the same record up in there browswers, the first person updates no problem... When the second person updates, Who can i tell that the record is dirty and display a message to the user to 'Re-query'...

I guess that is my real challenge to not let users corrupt data by overwriting...

Again, Thanks a million in advance
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 61658
    
  67

Generally, in a dirty write scenario, when a "dirty write" is detected, the user is informed that the update failed because the record had been modified. Depending upon your requirements, you could give them the option to over-write the intervening update, or even (more sophisticated) merge their changes with the conflicting changes.
Adeel Ansari
Ranch Hand

Joined: Aug 15, 2004
Posts: 2874
specifically in oracle the second person have to wait until the transaction of the first person, who executed a update on the same record, gets committed or rollbacked.

No chance of dirty updates. you can try it through sql plus.

i dont think that you need to give an appropriate message to the user for this. because i think it is the game of some milli seconds or a second after that the request would be fulfilled.

plz dont use select FOR UPDATE in a web app or do this for admin only.
[ October 07, 2004: Message edited by: adeel ansari ]
Steve Hill
Greenhorn

Joined: Oct 06, 2004
Posts: 9
Thanks everyone for you replies... I have more questions...

I understand Oracle updates so no dirty updates... My question is, if two users have queried the same record in a web application... The first user updates the record... When the second user tries to update the record I do not want this to happen, since the two user does not have the most current changes, so therefore it would overwrite the first persons record... I have been reading on transaction isolation levels... Still no clear on these...
Is this a JDBC thing???
What does Oracle 9i support for Transaction Isolation???

Thanks in advance
/Steve
Adeel Ansari
Ranch Hand

Joined: Aug 15, 2004
Posts: 2874
after commit or rollback issued by the first transaction(user), mean transaction is over the record is no more a dirty record or rollbacked. so, the second user can obviously change the record.

Now why you want this to happen?? why you dont want the second user update the record, while the first user is done?? haven't got you properly.
Steve Hill
Greenhorn

Joined: Oct 06, 2004
Posts: 9
Hi,

Let me try to explain this again... I understand that when the first transaction is completed by a commit or rollback... But when the second user issues the update it will NOT have the most current record...

2 web users
- First web user queries record
- Second web user queries the SAME record as first web user
- First web user updates some fields in the record and commits
- The second web user now has a dirty record, which when he changes
some fields (completely different from the first user field changes)
the update will overwrite the first user updates...

How are people handling this issue on the web... Oracle Forms will not allow you to update a record that has changed on the database, without first requerying the record to get the most current record...

I fully understand how the Oracle DB handles transactions, my question is related to a web application (java servlets) and JDBC... I hope this clarifies the issue more clearly...

Thanks in advance,
/Steve
Adeel Ansari
Ranch Hand

Joined: Aug 15, 2004
Posts: 2874
hm now i got you thanx.

i think it is not a problem if the second user updates the record. see possibilities. there are two.

- second user will make the same changes to that record, which is already be made by the first one.

- second user will make some changes in that record completely different from the changes made by first user.

In both the possibilties there is no harm.

Even then if you want to suppress the second user from updating record you have to do it on your own. you have to check all the fields there inside. if newer than user have, notify user that you are goin to change the record which has already been changed and show the current values.
Steve Hill
Greenhorn

Joined: Oct 06, 2004
Posts: 9
Hi adeel,

Thanks for the response...

Your second Scenario would be a problem, atleast for me...

When the java code ready to perform the update, it simply takes all field values from the HTML page and updates the entire row... This means that if all columns in the table are being updated... So if user one has performed some updates, then when user two updates, user one changes will be overwritten... Is there not a java servlet solution somewhere to avoid this type of collision...

Thanks in advance
/Steve
Adeel Ansari
Ranch Hand

Joined: Aug 15, 2004
Posts: 2874
you know you are talking about something like this.

I inserted a record then somebody, granted by equivalent rights, deleted my record. I again inserted that record and that buddy again deleted my record. So, I just went to admin that please revoke him/her delete rights atleast. then came back and inserted that record again. Now its Ok.

have you got the solution, mate.
Steve Hill
Greenhorn

Joined: Oct 06, 2004
Posts: 9
Hi,

I wish it was that easy... Its not a insert, delete issue... Its an update issue, which user(s) need the ability to update records...

Has anyone any experience they can share with regards to this issue...

Im sure this very issue has been addressed by someone out there... Any help would be greatly appreciated...
Adeel Ansari
Ranch Hand

Joined: Aug 15, 2004
Posts: 2874
anyways if you are really desperate to do this then i think that is the only solution to do this, as i mentioned earlier.

"you have to check all the fields there inside. if newer than user have, notify user that you are goin to change the record which has already been changed and show the current values."

any other options would greatly appreciated.
Steve Hill
Greenhorn

Joined: Oct 06, 2004
Posts: 9
Hi adeel,

Thanks for the responce... Its not that I am desperate, but what are some other alternatives...

The java code builds the update statement, it updates all values from the HTML page to the database, regardless if the values have changed...
ie. Table Employee
Column Name
Address 1
City
Original Record
Joe Smith
123 South St.
SoHo

User 1 queries record and displays HTML page with results
- changes Name to John Smith
- no change in address or city

User 2 queries record and displah HTML page with results
- changes Address to 456 North St.
- no change in name or city

User 1 submits changes and commits so current record is
Current Record
John Smith
123 South St.
SoHo

User 2 submits changes and commits so current record is
Current Record
Joe Smith
456 North St.
SoHo

So User 2 update has overwritten the name change from User 1...

Im not sure how much clearer I can be here in explaination

Thanks in advance,
/Steve
Daniel Hedrick
Greenhorn

Joined: Aug 08, 2003
Posts: 10
Steve,

It seems that one possibility would be to include a version key in the update. If the version that is supplied in the update is not the same that's in the database (which is updated every commit) then the update would fail, and allow the client to refresh the data...

Something like:

1. User A loads record Foo,v1
2. User B loads record Foo,v1
3. User B makes changes, commits record Foo,v1 which becomes Foo,v2
4. User A attempts to commit Foo,v1 gets a version-out-of-sync error/exception.

This is effectively a non-locked dirty write exception. Instead of the more widely used, "last one to the database wins" this is more of a "first one to the database wins".

Good luck!

-daniel

[DLH: edited for clarity]
[ October 09, 2004: Message edited by: Daniel Hedrick ]

There are 10 types of people in this world...<br />Those who understand binary, and those who don't.
Adeel Ansari
Ranch Hand

Joined: Aug 15, 2004
Posts: 2874
yeah sounds like a nice solution. but incase there are more than two users. suppose there are 100 users using application. 12 are viewing the same record. one updated the record in the DB. then you have to mark all the 11 users to v2, right. the problems might be.

- tracing the users who are viewing the same record.
- performance lack, because every time you have to do extra stuff.
- you have to maintain the log somewhere that, which users are on the same page.
- and more . . .


isn't it better to check at the time of update. just match the previous values from the DB values. if same then set the new values else get the new value of DB and show to the user with an appropriate message. it will save you from management complexity.

just a thought
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

What Daniel Hedrick is describing is an Optimistic Locking pattern (in case you need to put a name to it), and its the way you would normally solve this sort of problem. The things you are worried about adeel ansari aren't really a problem:
  • You don't need to trace the users that have selected a record - only increment the version when a use begins a transaction which involves an update
  • There will be a hit on performance, but there is no way round that. Checking for different values in code will cause extra work too (imagine the volume of code required to check a 200 attribute entity).
  • what page the user is on doesn't come into it - only that they are perfoming a transaction which involves an update.

  • Other than the extra work needed, the only real issue is you might generate an application in which users get kicked back from updating things a lot. Usually you can plan round this though.


    JavaRanch FAQ HowToAskQuestionsOnJavaRanch
    Adeel Ansari
    Ranch Hand

    Joined: Aug 15, 2004
    Posts: 2874
    thanx Paul, I agreed. I need some more explaination on the following.

    suppose. first user queried that particular record, then second user did that too. now both are seeing the same record. now first user updated that record. that update incremented a version with one, say v2. now where we keep this version attribute.

    A. in the session of second user
    B. in the session of first user
    C. application wide, for all users
    D. or somewhere else. and if some elsewhere then would it be Static or instance specific.

    thanx in advance.
    Srinivasa Raghavan
    Ranch Hand

    Joined: Sep 28, 2004
    Posts: 1228
    Think about this solution, have a couple of columns in the table Sys_creation_Date & Sys_Update_Date.
    The first one gives the date & time when the record is created.
    The later gives the date & time when the record gets updated.

    So when users are quering the database they 'll have the Sys_Update_Time from the record set, so when Updating the the record check for the Sys_update_time that u have & the SysUpdate_time in DB....

    This might be a work around and not the direct sln..

    Srini
    [ October 11, 2004: Message edited by: srini vasan ]

    Thanks & regards, Srini
    MCP, SCJP-1.4, NCFM (Financial Markets), Oracle 9i - SQL ( 1Z0-007 ), ITIL Certified
    Paul Sturrock
    Bartender

    Joined: Apr 14, 2004
    Posts: 10336


    now where we keep this version attribute

    We keep it as a property of the object describing the record.
    Daniel Hedrick
    Greenhorn

    Joined: Aug 08, 2003
    Posts: 10
    Adeel:

    isn't it better to check at the time of update. just match the previous values from the DB values.


    Yes. I think we're saying the same thing. But instead of doing something like:



    I think:


    would work.

    also...

    where *would* we keep this version attribute?


    It doesn't need to be anything complex. An additional column in any tables is probably sufficient. It would probably need an index or clustered index upon it, along with the primary key.

    It would be the responsibility of the business object's model to keep track of the version key and verify its integrity (ie it matches what's in the db) when an update (db write) occurs.

    Now... as to your possible solution, Srini...

    have a couple of columns in the table Sys_creation_Date & Sys_Update_Date


    One major hurdle we've come up against is that small tables with fast hardware can actually cause the JDBC writes to appear to be at the same time. Eventhough the db might have the ability to provide the chrono resolution necessary, often JDBC just couldn't keep up.

    The versioning (or Optomistic Locking, as Paul put it) has really saved us quite a bit of time and energy.

    Good luck!

    -daniel
    Adeel Ansari
    Ranch Hand

    Joined: Aug 15, 2004
    Posts: 2874
    thanx Daniel,

    Ofcourse we can handle it on our own as i said earlier that there would be a kind of management required.

    Happy Scenerio:

    - user A fetched the record
    - user B fetched the same record
    - user A updated the record, our logic works here. we put the Version = 2
    - Now user B also tried to update the same record.
    - update query failed because user B is having version = 1. didn't pass the condition
    - Now on query failure we fetch the same record an show it to user B along appropriate message.

    all fine and happy till now.

    Other scenerio: here our version is going to be increase everytime. though i didnt like that. but

    - user A fetched the record
    - user B fetched the same record
    - user A updated the record, our logic works here. we put the Version = 2
    - user C fetched the record and got the version = 2
    - Now user B tried to update the same record.
    - update query failed because user B is having version = 1. didn't pass the condition
    - Now on query failure we fetch the same record an show it to user B along appropriate message.
    - Now user C also tried to update the same record. and updated the record successfully. we put the Version = 3 this time.
    - Now user B tried to update the same record.
    - update query failed because user B is having version = 2. didn't pass the condition
    - Now on query failure we fetch the same record an show it to user B along appropriate message.


    i have sympathy with user B

    I was confused because i wanted to work around with just v1 and v2 and again and again. haven't thought to involve DB to apply some logic.

    anyways no doubt nice solution. but we cant help user B.
    Moreover, This way we can keep the log that how many times that record is updated, An update History.

    be the first one, be the third one, but never be the second one
    [ October 11, 2004: Message edited by: adeel ansari ]
    Steve Hill
    Greenhorn

    Joined: Oct 06, 2004
    Posts: 9
    Hi,

    Thanks to everyone who responded... I guess the real answer to my question is, there is no packaged solution... This must be custom coded and handled by the application...

    Although I am still unsure how or where to store the increment value... The application, (which was handed to me) does not have a data layer per say... When the query executes, a class (DBObject.java) is instaniated, query executed and object is closed... DBObject is responsilbe for getting a connection (connection pool), executing SQL statements, commit and rollback.

    So would I store the increment value in the session object???

    Thanks in advance,
    Adeel Ansari
    Ranch Hand

    Joined: Aug 15, 2004
    Posts: 2874
    no steve we are talking about something else. just have a look here.


    your table would have 1 more column representing the version of the record. and i think the discussion above would give some more elaboration that how could you go for this.

    for more, we are still here with you.
    [ October 12, 2004: Message edited by: adeel ansari ]
    Steve Hill
    Greenhorn

    Joined: Oct 06, 2004
    Posts: 9
    Hi,

    Yes I understand I would need a column added to my table(s)... But When I first query the record, I should store the version #, so when the update occurs, I would then requery the record and check the version # to what I have stored...

    So would I store the version # in the session object?

    Thanks in advance,
    Adeel Ansari
    Ranch Hand

    Joined: Aug 15, 2004
    Posts: 2874
    it would be there in your ValueObject or Bean. and your VO would work in request scope.
     
    Consider Paul's rocket mass heater.
     
    subject: Locking Oracle Record