wood burning stoves*
The moose likes JSP and the fly likes Record Locking in multi-user Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


JavaRanch » Java Forums » Java » JSP
Bookmark "Record Locking in multi-user" Watch "Record Locking in multi-user" New topic
Author

Record Locking in multi-user

kwwong wong
Ranch Hand

Joined: Feb 15, 2002
Posts: 56
I am developing an application using Oracle database. The application has several client. The application has a page list all records in the table. When user click the row, it will goto edit page for user update.
The problem is when two user edit the same record at the same time, the last user will overwrite the previous user.
Is it need to add a timestamp in the table and every time user edit the record, I save the timestamp in the edit page. And then when the user click the update button to update the record, I will compare the timestamp before update or using a timestamp as one of the key when update?
But I hope that before the user enter to the edit page, I can check that whether the record is updated by another user. When it is, I can prevent the user enter to the edit page for that record. How to implement this mechanism when using JSP/Servlet/JavaBean only ?
Thanks
Hartmut Ludwig
Ranch Hand

Joined: Aug 31, 2002
Posts: 51
The problem with your method is, that the user is tricked somehow if he enters the edit page, does some editing, goes for a cup of coffee and returns to submit. If in the meantime another user has just entered the edit page without doing anything the work of the first one is obsolete.
No good idea.
A better soulution would be to lock the record right when somebody enters the edit page. Others who want to enter the edit page will then immediately get the info "this record is already in use - try later". But how do we manage this locking-mechanism? Since HTTP is a stateless protocol it is not too easy to find out if anybody is still working on a record or just swiched off the Computer in the middle of a session.
One possible solution could be the following. If somebody enters the edit page this users Session ID is written into the database for this record. It will later be removed if the user submits the changed data. If the user just leaves the session will expire at some time (depending in how the server is configured).
If somebody else tries to edit the same record the JSP first checks if there is a session active for the SessionID that is saved in this record. If not so - the record is open for use again and the new users SessionID is saved instead of the old one.
I don't know how to exactly implement this solution with JSP but I know it works for PHP since we implemented it for some kind of newsticker on our companies website
hope it helps
Hartmut
James Carman
Ranch Hand

Joined: Feb 20, 2001
Posts: 580
Originally posted by kwwong wong:
I am developing an application using Oracle database. The application has several client. The application has a page list all records in the table. When user click the row, it will goto edit page for user update.
The problem is when two user edit the same record at the same time, the last user will overwrite the previous user.
Is it need to add a timestamp in the table and every time user edit the record, I save the timestamp in the edit page. And then when the user click the update button to update the record, I will compare the timestamp before update or using a timestamp as one of the key when update?
But I hope that before the user enter to the edit page, I can check that whether the record is updated by another user. When it is, I can prevent the user enter to the edit page for that record. How to implement this mechanism when using JSP/Servlet/JavaBean only ?
Thanks

You can use a simple "optimistic locking" mechanism. You need to add a "version" column to the database table(s) in question. This will be a simple integer column. When an edit request comes in, it must pass the version number with it. If the version number passed in matches the version number in the database, the edit can proceed. Now, you must change the version number somehow. Some folks just increment the number. I, however, don't like that approach and opt for random numbers (so that a client can't just keep retrying with larger and larger version numbers). Anyway, this is the typical optimistic locking strategy. Hope this helps. :-)


James Carman, President<br />Carman Consulting, Inc.
Hartmut Ludwig
Ranch Hand

Joined: Aug 31, 2002
Posts: 51
Originally posted by James Carman:

When an edit request comes in, it must pass the version number with it. If the version number passed in matches the version number in the database, the edit can proceed.


How do you solve the problem with simultaneous access? First come first served, the version number is changed and the second person that also has done some editing won't be able to save it and won't know why... I think it's better to tell that the record is locked so the person can try later.
sl
Hartmut
James Carman
Ranch Hand

Joined: Feb 20, 2001
Posts: 580
Originally posted by Hartmut Ludwig:

How do you solve the problem with simultaneous access? First come first served, the version number is changed and the second person that also has done some editing won't be able to save it and won't know why... I think it's better to tell that the record is locked so the person can try later.
sl
Hartmut

You can throw an OptimisticLockingException or something like that, so that the webapp knows what's going on. At least that's what I've done in the past.
kwwong wong
Ranch Hand

Joined: Feb 15, 2002
Posts: 56
Thanks for response my post. Hartmut Ludwig, I have a problem when using your method. If user A go to edit record A but without update the record, and then he go to edit record B. At this time, record A and B also has the session ID for user A. On the same time, if user B try to edit the record A, because user A session alive at this time, user B cannot enter to edit page for record A. The record A is locked until the user A's session died.

To solved the problem, can I add a table to record the user's last update record ? So that I can check that the user A is currently not edit record A and I can permit user B to edit the record A.
Hartmut Ludwig
Ranch Hand

Joined: Aug 31, 2002
Posts: 51
Originally posted by kwwong wong:
If user A go to edit record A but without update the record, and then he go to edit record B. At this time, record A and B also has the session ID for user A. On the same time, if user B try to edit the record A, because user A session alive at this time, user B cannot enter to edit page for record A. The record A is locked until the user A's session died.

I understand the problem. It acutally works as designed, since user A is editing both records, so it is necessary to lock both, to avoid that other users mess it up.
But this is no good solution, cause one user could block several records at the same time.
To avoid this issue I would do the following:
First of all: is it really necessary for one user to work on two records at the same time?
Usually it is not and so you can just forbid a user to work on more then one record at the time.
This means: If user A did not finish work on record A it is not allowed to open another record. User A has to submit the changed data to the database first. The record is updated and the session ID of user A is removed from the record. The record is now free for others to use.
But how do we keep the Users from editing more than one record at the time? This is actually quite simple. The only thing you have to implement is to check if the Session ID of the current user is already contained in the database. This proofs if the user has another browserwindow open working on another record. In this case you show an error message like "You first have to submit record 'A' before you are allowed to open another record".
I think this will do. Does anybody else have a better idea?
cheers
Hartmut
James Cook
Greenhorn

Joined: Jun 13, 2002
Posts: 11
The session ID idea is not so good if the user's web browser crashes whilst editing. This is because the session is persisted to the database and could still be registered until it expires (this is normally programmed for when the browser exits, but may not be the case if the browser has terminated abnormally). Also you need to ensure the webserver does not issue a new session if the user comes off the page and back onto it whilst updating.
Optimistic locking (i.e. update counters etc.) is a much better approach for non-realtime updating. It suffers from the disadvantage if there is a lot of frequent editing, that another user could have updated the record before you have managed to commit the changes. This then requires you to reread the record (to pick up the new update counter) and re-enter the change. Random counters are not reliable since the generator may pick a number which has already been used. Incrementing the counter by 1 each time is much better and also gives free statistics on how often the record has been updated. Note that add operations always put a new entry on with update counter = 0, and that the check is ONLY done on update. In this way, you do not have to create lock systems (with potential dead locks). However, this does not work very well for realtime apps, because in this case you want the changes to be cumulative (e.g. booking airline seats), and constantly re-reading the record is counterproductive. In this case you want a lock for editing, but you should try to optimise it (depending on your needs) so that the lock is only taken out for the duration of the update and not whilst the user is thinking about what data to change.


James Cook<br />Sun Certified Java 2 Developer
kwwong wong
Ranch Hand

Joined: Feb 15, 2002
Posts: 56
My project is realtime application and the user can only edit one record at the same time. I have an edit page for user edit the record and with the "Update" and "Cancel" button. I follow Hartmut's method and when user click the "Update" button, I update the record with null sessionID. When user click the "Cancel" button, I only update the record's sessionID with null value. However, when the user exit the current edit page without click any button, i.e. using backpage button in browse or the menu I provided. In this case, I have no opportunity to remove the sessionID. When the user go to another page and then also exit the page without click any button. He can lock many record (in clude another tables) at the same time.
So I think that I need to add a table to record the user's last update record. The table has three column like:
sessionID table_name record_ID
But using this method, every updating need to update at least two tables.
Have any suggestion ?
Hartmut Ludwig
Ranch Hand

Joined: Aug 31, 2002
Posts: 51
Originally posted by kwwong wong:
However, when the user exit the current edit page without click any button, i.e. using backpage button in browse or the menu I provided. In this case, I have no opportunity to remove the sessionID.

That's right and this is the main backdraw in the locking method. But I told you a solution for this: if the session has expired the locked records will automatically be free again.
How is this?
Because if any user tries to edit a record and there is a session ID already listed in it this does not necesserily mean that the record is locked. First it has to be checked if the session ID is really in use on the server. With the PHP-solution we did this was quite easy to establish. Every session has a serialized session-object in a specified directory on the webserver. The name of the serialized file is the same like the session ID. So if user B comes to edit a record of user A whose Browser crashed, there are two opportunities:
1. The webserver still finds a session file with the ID found in the record.
So the session is still active (means that the browser crashed just a little while ago). In this case the record is still locked. You can reduce the severity of this problem by reducing the session timeout, but this could annoy users on the other hand.
2. The webserver found out that the session is gone and invalidates it. The record is free to use again, because there is no session file for the session ID listed in the record.
This means that the user switched off the computer in the middle of the session, or browser crashed or something like that. So user B has not to care about the Session ID that is listed in the record and just overrides it with it's own. Now the record is locked for his session and user B won't encounter any problems.
You see - no deadlock is possible. It is only possible that because of a crashed browser (quite rare case by the way) or a lame user that leaves the browser open for hours a record is locked until session-timeout - right until the server finds out, that there is no activity of this user and invalidates the session.

When the user go to another page and then also exit the page without click any button. He can lock many record (in clude another tables) at the same time.

Nope! Not with the solution I depicted in my last posting. Because every user is only allowed to edit one record at the time. If user B wants to edit record C the following happens:
1. It is checked if user B is already editing any other record. (Select for users session ID)
2. If resultset is empty (users session ID is not listed) goto Point 4
3. If users session ID is listed user gets a message "you are not allowed to edit more than one record" -> END.
4. Record 'C' is read
5. Does it contain a Session ID of another user?
6. if not goto point 9
7. if yes we check if the Session ID is valid or not
8. if it is valid user gets a message "please come back later" -> END.
9. user B's Session ID is written into the record
10. Now the user can edit the record (and nobody else)
11. User saves record, and the Session ID is removed from the record
Again - this is a solution that works great with a PHP application we implemented. The concept has to work with JSP as well. The only problem I see is: how do I find out if another users session exists if I know it's Session ID. In PHP it's easy: The session exists if the serialized session file exists. I don't know how it works in other Webapplication servers. May differ from server to server.
Kwwong: I don't think that you need another table and two inserts if you follow this procedure.
BTW. I would be glad to hear if you find out how to validate sessions of other users. This is the weak point in my concept.
good luck
Hartmut
Hartmut Ludwig
Ranch Hand

Joined: Aug 31, 2002
Posts: 51
And here is a implementation of the solution I talked about. It's just a small prototype as proof of my concept.
It consists of 2 classes:
  • A servlet named "UpdateLockedTable" which containts the acutal code to disply or edit records
  • A class called "SessionLockManager" (Servlet 2.3) that implements HttpSessionBindingListener to control locks and free them if a session expires

  • I hope the code will explain itself with all the comments. Here is the code for the servlet:

    The servlet makes use of the SessionLockManager. Here's the code

    Finally you need a small HTML-Page to call the servlet:

    OK. That's it. I have tested it successfully.
    To get it up and running just follow the explanations in the javadoc. Don't forget to set right username and password in the JDBC-Connect URLs.
    If you find any bugs or constellations where it won't work as designed - pease inform me...
    cheers
    Hartmut
    [ September 15, 2002: Message edited by: Hartmut Ludwig ]
    kwwong wong
    Ranch Hand

    Joined: Feb 15, 2002
    Posts: 56
    In multi-table environment, is it need to pass the table name as parameter in SesionLockManager's methods ?
    Hartmut Ludwig
    Ranch Hand

    Joined: Aug 31, 2002
    Posts: 51
    Hi, Kam Wah!
    Yes, indeed, you need to pass the name of the table you refer to in the following methods of the SessionLockManager.
    isDoubleEdit(key, id)
    isRecordLocked(key, id)
    lockRecord(key, id)
    unlockRecord(id)
    I recommend that you overload them to:
    isDoubleEdit(tableName, key, id)
    isRecordLocked(tableName, key, id)
    lockRecord(tableName, key, id)
    unlockRecord(tableName, id)
    and use the variable tableName in the SELECT- and UPDATE-Statements instead of the hardcoded "test" I used so far.
    good luck and let me know if it works.
    cheers
    Hartmut
    kwwong wong
    Ranch Hand

    Joined: Feb 15, 2002
    Posts: 56
    Hello, Hartmut,
    I tried and it is works. However in the valueUnbound method, I need to scan all the tables with the sessionID to unlocks all locks of the according session.
    I have another ideal to implement the locking mechanism. Can I store the locking information in ServletContext instead of tables column ?
    E.g. Create a collection of locking objects and stored it in ServletContext. The locking object has the attributes like (String table_name, String id, String session_id). The SessionLockManager also provide the methods
    isDoubleEdit(tableName, key, id)
    isRecordLocked(tableName, key, id)
    lockRecord(tableName, key, id)
    { add the locking object to the collection }
    unlockRecord(tableName, id)
    { remove the locking object from the collection }. But I don't know how to implement the collection. Using Collection , Vector, List or implement by myself ?
    Please comment. Thanks.
    kam wah
    Hartmut Ludwig
    Ranch Hand

    Joined: Aug 31, 2002
    Posts: 51
    great that it works for you.
    Originally posted by kwwong wong:
    However in the valueUnbound method, I need to scan all the tables with the sessionID to unlocks all locks of the according session.

    Hmm. More than one table at the time. Well - this can really cause a lot of unnecessary requests to the Database.
    I have another ideal to implement the locking mechanism. Can I store the locking information in ServletContext instead of tables column ?

    Why not - brilliant idea. I see only three tiny drawbacks:
    1. You have no real persistance in the locks (e.g. if the server fails, gets rebooted or something)
    2. You won't be able to use the locks to block other applications accessing the database without using your webapp.
    3. You have to deal with some probs that might occur due to synchronous access. You have to impelment a synchronized solution. We did not need to do that with the database, cause the JDBC-Driver and the Database itself did it for us.
    But all this is not a big problem - so your approach is better since it is much quicker and causes less load for the CPU.
    But I don't know how to implement the collection. Using Collection , Vector, List or implement by myself ?

    Oh that's the easy part! You could use any of the mentioned approaches. Maybe for our needs the synchronizedMap() from the collections API (java.util.Collections
    in Java 1.4) would fit best. It allows to search the collection by a specified key and avoids duplicate keys (which won't occur in our program anyway).
    The key for the collection would consist of the combination of databasekey and tablename.
    So... create an Object LockMap which extends java.util.AbstractMap and add the features you
    mentioned. Create an Instance of it in the init() Method of the servlet as synchronizedMap...
    Map myLocks = Collections.synchronizedMap(new LockMap());
    Add it to the servlets Context. Now you can do the locking with this object instead of the database. The code itself could look somewhat like this:

    ATTENTION: I haven't tested this at all (just to keep some work left for you)
    But I honestly think it goes into the right direction and I hope it helps.
    BTW: I never worked with the Collection API before, but it seems like it's quite useful.
    good luck
    Hartmut
     
    GeeCON Prague 2014
     
    subject: Record Locking in multi-user