This discussion is archived
0 Replies Latest reply: Jun 14, 2007 6:50 PM by 807605 RSS

How to prevent deadlock

807605 Newbie
Currently Being Moderated
I'm working on a series of servlets that allow manipulation of a database, and I need to be able to prevent deadlock or hanging if data is being changed by someone besides the user.

If this is done within the program, I can prevent it by committing the changes soon after a change, not allowing uncommitted changes to exist and keeping the database free from changes, however, if somone were to manually open the database on the server side, I'd want the client to be unable to write any new data.

I attempted to set the transaction isolation level to serializable to prevent it from doing anything, but the client side will just hang if I say, try to delete the same data entry from sqlplus, and then the servlet.

I've read up on the stuff, and this should be a simple matter to limiting the access rights of the client program, but I'm not sure how to proceed since setting the transaction isolation level didn't work;

Here's the method I use to set the transation level, which seems to happen fine, but a simultaneous delete causes the servlet to hang (which I just fix by doing a rollback on the server side, but I can't rely on this in actual implementation).

I've looked around quite a bit and read the documentation, but the solution to this problem seems to be evading me, any help would be greatly appreciated.
     public void setIso(String lvl)
     {
          try
          {
               st = con.prepareStatement("SET TRANSACTION ISOLATION LEVEL " + lvl);
               res = st.executeQuery();
               C12_Log.write(0, "SQL: SET TRANSACTION LEVEL " + lvl);
          }
          catch (SQLException s)
          {
               C12_Log.write(1, s.toString() + s.getErrorCode() + s.getSQLState());
          }
          finally
          {
               try
               {
                    st.close();
               }
               catch (Exception e)
               {
                    C12_Log.write(1, e.toString() + e.getMessage());
               }
          }
     }