Record Locking in multi-user

[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

[Hartmut Ludwig]

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]

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. :-)

[Hartmut Ludwig]

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]

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]

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]

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]

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.

[kwwong wong]

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]

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]

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:

import java.io.PrintWriter;
import javax.servlet.http.*;
import java.sql.*;
/**
 * UpdateLockedTable is a small Servlet to demonstrate a
 * Locking-mechanism on Servlets.
 * It has been tested with MySQL and Tomcat 4.x
 * but any other combination of a RDBMS and Servlet 2.3
 * capable application server should be working as well.
 * The Database used for testing is created as follows:
 *
 * CREATE TABLE test (
 *    primary_key int(11) NOT NULL auto_increment,
 *    name varchar(20) NOT NULL default '',
 *    phone varchar(20) NOT NULL default '',
 *    lock varchar(40) NOT NULL default '',
 *    PRIMARY KEY  (primary_key)
 * )
 *
 * INSERT INTO test VALUES (1, 'Foo', '42', '');
 * INSERT INTO test VALUES (2, 'Bar', '23', '');
 *
 * IMPORTANT! note that this code uses the auto_increment
 * feature of MySQL - if you don't use MySQL you will have
 * to implement this by hand).
 * To make things easier we use a single attribute primary
 * key to clearly identify records. The attribute 'lock' is
 * used to save the users session id so this record will be
 * locked for this particular user.
 * Until timeout is reached the user is the only person who
 * can edit the locked record and other users that try to
 * access it will be blocked. To avoid that one user locks
 * many records it is only allowed to edit one record at the
 * time.
 * If the user saves or timeout of session is reached the
 * SessionLockManager will immediately remove the lock from
 * the table and other users can access it again.
 *
 * Feel free to use this code for your own projects.
 *
 * Creation date: (14/09/2002 12:47:12)
 * @author: [e-mail]
 */
public class UpdateLockedTable extends HttpServlet {
private int timeout; //seconds until timeout of the session
private java.lang.String connectString = 
"jdbc:mysql://localhost/test?user=dbusername&password=dbpassword";
/**
 * This is the main code of the Servlet processing all HTTP-Requests
 * coming from the calling page or the Servlet itself.
 * If Parameter 'save' is set the given record is saved to the database
 * It is possible that a user has taken the record, but did not submit it
 * until timeout is reacht. If another user has locked the record in the
 * meantime the first user is not allowed to save its changes.
 * Choose the timeout-seconds high enough to avoid trouble with this effect.
 *
 * If Parameter 'edit' is set the record that belongs to the key-number is
 * to be edited. The system makes shure that the user is not editing another
 * record or the record is blocked by another user.
 * After that the record is locked and can be edited until timeout.
 * If user is ready and submits data the lock will be removed.
 * If the timeout happens before the data is submitted the record is free
 * again. If it has not been locked by somebody else in the meantime the
 * user can still submit it's data.
 *
 * Creation date: (14/09/2002 12:32:49)
 * @param request Object that encapsulates the request to the servlet
 * @param response Object that encapsulates the response from the servlet
 */
public void doGet(HttpServletRequest request, HttpServletResponse response) 
throws javax.servlet.ServletException {
    try {
        SessionLockManager lockManager = new SessionLockManager();
        response.setContentType("text/html");
        PrintWriter out = response.getWriter();
        String key;
        // get existing session or create one
        HttpSession session = request.getSession(true);
        session.setMaxInactiveInterval(this.timeout);
        session.setAttribute("locks", lockManager);
        // evaluate given parameters
        if (request.getParameter("key") != null) {
            key = request.getParameter("key");
        } else {
        // use 1 as default if somebody calls the servlet directly
            key = "1";
        }
        out.println("<html><head><title>Edit Data</title></head><body>");
        if (request.getParameter("save") != null) {
            // user pushed the submit-button and wants to save...
            System.out.println("User wants to save");
            if (lockManager.isRecordLocked(key, session.getId())) {
                out.println("Another user edits the record you wish to access - ");
                out.println("please come back later<br>");
            } else {
                Connection con = DriverManager.getConnection(this.connectString);
                Statement stmt = con.createStatement();
                String query = "UPDATE test SET test.name='"+request.getParameter("name");
                String query += "', test.phone='"+request.getParameter("phone")+"' ";
                String query += "WHERE primary_key='" + key + "'";
                System.out.println("Executing: " + query);
                int affectedRows = stmt.executeUpdate(query);
                if (affectedRows == 1) {
                    out.println("Record has been successfully saved<br>");
                } else {
                    out.println("Something went wrong saving the record<br>");
                }
                stmt.close();
                con.close();
            }
        } else {
            // user wants to edit...
            // check if user is already editing another record
            System.out.println("User wants to edit");
            if (lockManager.isDoubleEdit(key, session.getId())) {
                out.println("You are not allowed to edit more then one record at the time<br>");
            } else {
                if (lockManager.isRecordLocked(key, session.getId())) {
                    out.println("Another user edits the record - please come back later<br>");
                } else {
                    if (lockManager.lockRecord(key, session.getId())) {
                        out.println("<b>Record " + key + " is locked for " + timeout + " sec.<br>");
                        out.println("<form action='UpdateLockedTable'>");
                        Connection con = DriverManager.getConnection(this.connectString);
                        Statement stmt = con.createStatement();
                        String query = "SELECT test.primary_key, test.name, test.phone FROM test ";
                        String query += "WHERE primary_key='" + key + "'";
                        System.out.println("Executing: " + query);
                        ResultSet rs = stmt.executeQuery(query);
                        if (rs.next()) {
                            out.println("<input type='hidden' name='key' value='");
                            out.println(rs.getString(1) + "'>");
                            out.println("<table><tr><td>Name </td>")
                            out.println("<td><input type='text' name='name' value='");
                            out.println(rs.getString(2) + "'></td></tr>");
                            out.println("<tr><td>Phone number </td> ;) ;
                            out.println("<td><input type='text' name='phone' value='");
                            out.println(rs.getString(3) + "'></td></tr>");
                            out.println("<tr><td colspan='2'>");
                            out.println("<input type='submit' name='save' value='save'>");
                            out.println("</td></tr></table></form>");
                        } else {
                            out.println("There is no record with the requested key.<br>");
                        }
                        rs.close();
                        stmt.close();
                        con.close();
                    } else {
                        out.println("It was not possible to lock this record - does it exist?<br>");
                    }
                }
            }
        }
        out.println("</body></html>");
    } catch (Throwable e) {
        e.printStackTrace(System.out);
    }
}
/**
 * GET and POST requests do not need to be treated differently in this
 * servlet so the request is simply redirected to the doGet() Method
 *
 * Creation date: (14/09/2002 12:32:49)
 * @param request Object that encapsulates the request to the servlet
 * @param response Object that encapsulates the response from the servlet
 */
public void doPost(HttpServletRequest request, HttpServletResponse response) 
                   throws javax.servlet.ServletException, java.io.IOException {
doGet(request, response);
}
/**
 * This method is called once during initialisation of
 * the servlet and sets the timeout for session invalidation
 * to the appropriate level
 *
 * Creation date: (14/09/2002 12:32:49)
 */
public void init() {
    this.setTimeout(30);
    try {
        Class.forName("org.gjt.mm.mysql.Driver").newInstance();
    } catch (Throwable e) {
        System.out.println("The following SQLException occured:\n");
        e.printStackTrace(System.out);
    }
}
/**
 * Timeout defines the time in seconds which
 * the Servlet waits until the session is invalidated
 * and the SessionLockManager removes any locks for this
 * SessionID
 *
 * Creation date: (14/09/2002 12:34:17)
 * @param newTimeout int
 */
public void setTimeout(int newTimeout) {
this.timeout = newTimeout;
}
}

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

  
import java.io.*;
import java.sql.*;
import javax.servlet.*;
import javax.servlet.http.*;
/**
 * This Class is a Implementation of HttpSessionBindingListener.
 * it is used within the UpdateLockedTable Servlet to keep track
 * of the locking mechanism.
 * The Listener keeps track of Sessions and removes the locks from
 * the Database if a Session gets invalidated.
 *
 * Feel free to use this code for your own projects.
 *
 * Creation date: (14/09/2002 11:30:37)
 * @author: [e-mail]
 */
public class SessionLockManager implements HttpSessionBindingListener {
private java.lang.String connectString = 
"jdbc:mysql://localhost/test?user=dbusername&password=dbpassword";
/**
 * Constructor of SessionLockManager
 * Creation date: (13/09/2002 22:08:00)
 */
public SessionLockManager() {
    try {
        Class.forName("org.gjt.mm.mysql.Driver").newInstance();
    } catch (Throwable e) {
        System.out.println("The following SQLException occured:\n");
        e.printStackTrace(System.out);
    }
}
/**
 * This method checks if the current user is already editing any other record
 * this is done by searching the locks of the table for the users SessionID
 *
 * Creation date: (11/09/2002 22:43:13)
 * @return boolean
 * @param key java.lang.String
 * @param id java.lang.String
 */
public boolean isDoubleEdit(String key, String id) {
    boolean returnValue = false;
    try {
        Connection con = DriverManager.getConnection(this.connectString);
        Statement stmt = con.createStatement();
        String query = "SELECT * FROM test WHERE test.lock='" + id + "' ";
        String query += "AND test.primary_key != '" + key + "'";
        System.out.println("Executing: " + query);
        ResultSet rs = stmt.executeQuery(query);
        // if there is anything in the resultset the user is editing another record
        returnValue = rs.next();
        rs.close();
        stmt.close();
        con.close();
    } catch (Throwable e) {
        System.out.println("The following SQLException occured:\n");
        e.printStackTrace(System.out);
    }
    return returnValue;
}
/**
 * This method checks if the record the user wants to update is locked.
 * There are two conditions that have to be true to state that a
 * record is locked by another user:
 * <li>First: There must be a sessionID in the lock field
 * <li>Second: This sessionID must be different from the users SessionID
 *
 * Creation date: (11/09/2002 22:43:03)
 * @return boolean
 * @param key java.lang.String
 * @param id java.lang.String
 */
public boolean isRecordLocked(String key, String id) {
    boolean returnValue = false;
    try {
        String lock = null;
        Connection con = DriverManager.getConnection(this.connectString);
        Statement stmt = con.createStatement();
        String query = "SELECT test.lock FROM test WHERE primary_key='" + key + "'";
        System.out.println("Executing: " + query);
        ResultSet rs = stmt.executeQuery(query);
        if (rs.next()) {
        // there is a lock entry in the record
        lock = rs.getString(1);
            if (lock.equals("") || lock.equals(id)) {
            // if lock is empty or contains the users own id
            // it is not locked for this user
                System.out.println(key+" is free to edit!<br>");
                returnValue = false;
            } else {
                System.out.println(key+" is locked by user of session "+lock+"<br>");
                returnValue = true;
            }
        }
        rs.close();
        stmt.close();
        con.close();
    } catch (Throwable e) {
        System.out.println("The following SQLException occured:\n");
        e.printStackTrace(System.out);
    }
    return returnValue;
}
/**
 * This method will lock the record with the given key
 * for the user with the given Session ID
 *
 * Creation date: (13/09/2002 21:10:03)
 * @return boolean
 * @param key java.lang.String
 * @param id java.lang.String
 */
public boolean lockRecord(String key, String id) {
    boolean returnValue = false;
    try {
        Connection con = DriverManager.getConnection(this.connectString);
        Statement stmt = con.createStatement();
        String query = "UPDATE test SET test.lock = '"+id+"' ";
        String query += "WHERE test.primary_key = '" + key + "'";
        System.out.println("Executing: " + query);
        int affectedRows = stmt.executeUpdate(query);
        // there must be exactly one row affected or something went wrong
        returnValue = (affectedRows == 1);
        stmt.close();
        con.close();
    } catch (Throwable e) {
        System.out.println("The following SQLException occured:\n");
        e.printStackTrace(System.out);
    }
    return returnValue;
}
/**
 * This method will release any lock with
 * the given Session ID
 *
 * Creation date: (13/09/2002 21:10:12)
 * @return boolean
 * @param key java.lang.String
 */
public boolean unlockRecord(String id) {
    boolean returnValue = false;
    try {
        Connection con = DriverManager.getConnection(this.connectString);
        Statement stmt = con.createStatement();
        String query = "UPDATE test SET test.lock = '' WHERE test.lock = '" + id + "'";
        System.out.println("Executing: " + query);
        int affectedRows = stmt.executeUpdate(query);
        // there must be a row affected or something went wrong
        returnValue = (affectedRows != 0);
        stmt.close();
        con.close();
    } catch (Throwable e) {
        System.out.println("The following SQLException occured:\n");
        e.printStackTrace(System.out);
    }
    return returnValue;
}
/**
 * This method is called if a session is created
 *
 * Creation date: (13/09/2002 21:15:47)
 * @param event HttpSessionBindingEvent
 */
public void valueBound(HttpSessionBindingEvent event) {
    System.out.println(event.getSession().getId() + " has been created");
}
/**
 * This method is called if a session is released
 * it unlocks all locks of the according session
 *
 * Creation date: (13/09/2002 21:15:47)
 * @param event HttpSessionBindingEvent
 */
public void valueUnbound(HttpSessionBindingEvent event) {
    System.out.println(event.getSession().getId() + " is released");
    if (unlockRecord(event.getSession().getId())) {
        System.out.println("Locks have been removed.");
    } else {
        System.out.println("No locks have been found.");
    }
}
}

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

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>Choose Record</title>
</head>
<body>
    <form action="servlet/UpdateLockedTable">
        Enter the key of the record you want to edit<br>
        <input type='text' name='key'><br>
        <input type='submit' name='go' value='go'>
    </form>
</body>
</html>

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]

In multi-table environment, is it need to pass the table name as parameter in SesionLockManager's methods ?

[Hartmut Ludwig]

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]

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]

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:

import java.util.Collections;
import java.util.HashMap;
import java.util.Map;
import java.util.Set;
/**
 * Implementation of a LockMap to preserve 
 * Information about the locked records
 * 
 * @author [e-mail]
 * 
 */
public class LockMap extends java.util.AbstractMap {
/**
 * @see java.util.AbstractMap#entrySet()
 */
public Set entrySet() {
Map map = Collections.synchronizedMap(new HashMap());
Set set = map.keySet();
return set;
}
/**
 * Method lockRecord.
 * puts the SessionID of the user as lock on the record with 
 * the given key in the given table
 * 
 * @param tableName the name of the table containing the record
 * @param key the primary key defining the record
 * @param id the sessionID of the user.
 */
public void lockRecord(String tableName, String key, String id) {
// add the locking object to the collection 
this.put(tableName + "_" + key, id);
}
/**
 * Method isRecordLocked.
 * checks if the record with the given key in the given table is
 * locked by someone else but the current user defined by SessionID
 * 
 * <li>returns true if record is locked by another user
 * <li>returns false if record is free to use
 * 
 * @param tableName the name of the table containing the record
 * @param key the primary key defining the record
 * @param id the sessionID of the user.
 */
public boolean isRecordLocked(String tableName, String key, String id) {
String compareString = tableName + "_" + key;
boolean returnValue = false;
if (this.containsKey(compareString)) {
 returnValue = this.get(compareString).equals(id) ? false : true;
}
return returnValue;
}
/**
 * Method unlockRecord.
 * @param tableName the name of the table containing the record
 * @param key the primary key defining the record
 */
public void unlockRecord(String tableName, String key) { 
//remove the locking object from the collection
this.remove(tableName + "_" + key);
}
}

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