Concurrent Transactions causing a deadlock

ernst_pluess · **Posted:** Thu Jan 29, 2004 3:34 am

Hi All

We're currently in the process of investigating Hibernate in order to get rid of Entity Beans. One major concern is, whether Hibernate does proper transaction handling. Therefore I did the following test:

My business classes consist of a parent class Wertpapier and a child class Kurs. A Wertpapier can contain many Kurses.

I have a method creating a Wertpapier and two Kurses, and then Hibernate persists them into our Oracle DB.
I have a second method which does a find for Wertpapiers and deletes them. Since I specified the one-to-many relation as cascade-all, the Kurses are deleted as well.
Both methods do open and close again their own session and transaction.

Create Method:

Code:

    Session aSession = null;
    Transaction t = null;

    try {
      aSession = HibernateUtil.getInstance().getMySessionFactory().openSession();
      t = aSession.beginTransaction();

      Wertpapier w = new Wertpapier();
      w.setNlsKey(NLS_1);
      w.setValor(7);

      Kurs k1 = new Kurs();
      k1.setBetrag(123.5f);
      k1.setWaehrung("CHF");
      k1.setZeitstempel(GregorianCalendar.getInstance().getTime());
      k1.setWertpapier(w);
      w.addKurs(k1);

      Kurs k2 = new Kurs();
      k2.setBetrag(321);
      k2.setWaehrung("USD");
      k2.setZeitstempel(GregorianCalendar.getInstance().getTime());
      k2.setWertpapier(w);
      w.addKurs(k2);

      aSession.save(w);
      t.commit();

      t = aSession.beginTransaction();

      List wps = aSession.find("from Wertpapier as wp where wp.nlsKey = ?", NLS_1, Hibernate.STRING);

      assertTrue(1 <= wps.size());
      assertEquals(NLS_1, ((Wertpapier) wps.get(0)).getNlsKey());

      t.commit();
    } catch (HibernateException e) {
      if (t != null) {
        t.rollback();
      }
    } finally {
      aSession.close();
    }

Cleanup method:

Code:

    Session aSession = null;
    Transaction t = null;
    
    try {
      aSession = HibernateUtil.getInstance().getMySessionFactory().openSession();
      t = aSession.beginTransaction();
      
      List wps = aSession.find(
          "from Wertpapier as wp where wp.nlsKey in (?, ?)",
          new Object[]{NLS_1, NSL_2},
          new Type[]{Hibernate.STRING, Hibernate.STRING}
      );
      
      for (Iterator iter = wps.iterator(); iter.hasNext();) {
        aSession.delete(iter.next());    
      }
      
      t.commit();
    }
    catch (HibernateException e) {
      if (t!=null) {
        t.rollback();
      }
    }
    finally {
      aSession.close();
    }

Now I started 10 threads. Each thread calls the two methods 10 times.

Most of the time this test works fine, but every now and then I get the following exceptions:

Code:

[java] ERROR 28.01 15:24:07,894 til.JDBCExceptionReporter [java.sql.SQLException: ORA-00060: Deadlock beim Warten auf Ressource festgestellt
     [java] ]  '1'
     [java] WARN  28.01 15:24:07,894 til.JDBCExceptionReporter [SQL Error: 0, SQLState: null]  '1'
     [java] ERROR 28.01 15:24:07,894 til.JDBCExceptionReporter [java.sql.SQLException: ORA-00060: Deadlock beim Warten auf Ressource festgestellt
     [java] ]  '1'
     [java] ERROR 28.01 15:24:07,894 til.JDBCExceptionReporter [Could not execute JDBC batch update]  '1'
     [java] java.sql.SQLException: java.sql.SQLException: ORA-00060: Deadlock beim Warten auf Ressource festgestellt

     [java]    at weblogic.jdbc.rmi.SerialStatement.executeBatch(SerialStatement.java:450)
     [java]    at net.sf.hibernate.impl.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:54)
     [java]    at net.sf.hibernate.impl.BatcherImpl.executeBatch(BatcherImpl.java:118)
     [java]    at net.sf.hibernate.impl.SessionImpl.executeAll(SessionImpl.java:2311)
     [java]    at net.sf.hibernate.impl.SessionImpl.execute(SessionImpl.java:2266)
     [java]    at net.sf.hibernate.impl.SessionImpl.flush(SessionImpl.java:2187)
     [java]    at net.sf.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:61)
     [java]    at ch.postfinance.bom.dummy.WertpapierServerTest.cleanup(WertpapierServerTest.java:75)
     [java]    at ch.postfinance.bom.dummy.WertpapierServerTest.setUp(WertpapierServerTest.java:39)
     [java]    at org.apache.cactus.AbstractTestCase.runBareServerTest(AbstractTestCase.java:233)
     [java]    at org.apache.cactus.server.AbstractWebTestCaller.doTest(AbstractWebTestCaller.java:149)

.....

     [java] ERROR 28.01 15:24:08,003 ibernate.impl.SessionImpl [Could not synchronize database state with session]  '1'
     [java] net.sf.hibernate.JDBCException: Could not execute JDBC batch update
     [java]    at net.sf.hibernate.impl.BatcherImpl.executeBatch(BatcherImpl.java:125)
     [java]    at net.sf.hibernate.impl.SessionImpl.executeAll(SessionImpl.java:2311)
     [java]    at net.sf.hibernate.impl.SessionImpl.execute(SessionImpl.java:2266)
     [java]    at net.sf.hibernate.impl.SessionImpl.flush(SessionImpl.java:2187)
     [java]    at net.sf.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:61)
     [java]    at ch.postfinance.bom.dummy.WertpapierServerTest.cleanup(WertpapierServerTest.java:75)
     [java]    at ch.postfinance.bom.dummy.WertpapierServerTest.setUp(WertpapierServerTest.java:39)
     [java]    at org.apache.cactus.AbstractTestCase.runBareServerTest(AbstractTestCase.java:233)
 
.....


     [java] Caused by: 
     [java] java.sql.SQLException: java.sql.SQLException: ORA-00060: Deadlock beim Warten auf Ressource festgestellt
     [java]    at weblogic.jdbc.rmi.SerialStatement.executeBatch(SerialStatement.java:450)
     [java]    at net.sf.hibernate.impl.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:54)
     [java]    at net.sf.hibernate.impl.BatcherImpl.executeBatch(BatcherImpl.java:118)
     [java]    ... 33 more

I'm not sure what this means. Is this Oracles way of telling me, that there's optimistic locking conflict. That would be fine for me. Or is there something I'm doing wrong?

Any help is appreciated
Ernst

christian · **Posted:** Thu Jan 29, 2004 3:52 am

Thats an Oracle specific problem, I'd say. Hibernate is not doing anything special here: Each TX block you execute starts a new database transaction, executes some SQL SELECT, INSERT and DELETE statements and then commits the transaction.

I think Oracle locks the whole table for each transaction and you have a very high likelyhood of collision with 30 concurrent transactions doing exactly the opposite of each other. There is not much Hibernate (or even your handcoded JDBC) can do about that. If you really like to get this (not very realistic) testcase working, you may have to use explicit shared row locks (SELECT ... FOR UPDATE, see Hibernate LockMode) or even lock the table manually.

See:

http://www.wisc.edu/drmt/oratips/sess004.html
http://www.ebullen.demon.co.uk/tech_doc ... locks.html

ernst_pluess · **Posted:** Thu Jan 29, 2004 1:30 pm

Hi All

Thanks for the hints so far.

After adding a session.lock with LockMode.UPGRADE command before I start deleting the objects, now I do get an
StaleObjectStateException [An operation failed due to stale data].

If I got everything right this exception should be thrown in case of an optimistic locking conflict. But the documentation (chapter 17.5) says, that a session.lock with LockMode.UPGRADE should provide pessimistic locking.

Optimistic Locking
My understanding of the topic is, that this is how Hibernate works by default. If I don't need one of the things described in the manual in chapter 17.3 I don't have to do anything special.
Shouldn't the dead lock error from Oracle be wraped into an StaleObjectStateException? Or is it at least fair to assume, that there's an optimistic locking conflict if I catch it?

Pessimistic Locking
I do unterstand, that it may require some extra work to get my example reliably working. I tried to exclusively lock the tables right after creating the session. Unfortunately this didn't help at all. What else can I do?

For completness here's the code of the "delete" method:

Code:

    Session aSession = null;
    Transaction t = null;
    
    try {
      aSession = HibernateUtil.getInstance().getMySessionFactory().openSession();

      Connection aConnection = aSession.connection();
      
      // lock the tables exclusively
      PreparedStatement aStatement = aConnection.prepareStatement("LOCK TABLE FIPO_BOM_PRODUKT IN EXCLUSIVE MODE");
      aStatement.execute();
      aStatement.close();
      aStatement = aConnection.prepareStatement("LOCK TABLE FIPO_BOM_KURS IN EXCLUSIVE MODE");
      aStatement.execute();
      aStatement.close();
      
      t = aSession.beginTransaction();
      
      List wps = aSession.find(
          "from Wertpapier as wp where wp.nlsKey in (?, ?)",
          new Object[]{NLS_1, NSL_2},
          new Type[]{Hibernate.STRING, Hibernate.STRING}
      );

      // force hibernate to do pesimistic locking according to the manual (chapter 17.5)      
      for (Iterator iter = wps.iterator(); iter.hasNext();) {
        Wertpapier element = (Wertpapier)iter.next();
        aSession.lock(element, LockMode.UPGRADE);
        Set kurse = element.getKurs();
        for (Iterator iterator = kurse.iterator(); iterator.hasNext();) {
          Kurs element2 = (Kurs)iterator.next();
          aSession.lock(element2, LockMode.UPGRADE);          
        } 
      }
      
      for (Iterator iter = wps.iterator(); iter.hasNext();) {
        aSession.delete(iter.next());    
      }
      
      t.commit();
    }
    catch (HibernateException e) {
      if (t!=null) {
        t.rollback();
      }
    }
    finally {
      aSession.close();
    }

Thanks in advance for any help
Ernst

mwoelke · **Joined:** Fri Mar 03, 2006 11:37 am **Posts:** 3

hello,
i got exactly the same problem with jboss using hibernate as ejb 3.0 persistence provider. i investigated the problem and found, that it can be solved creating an index for the foreign key in the dependent table. Unfortunately i dont know how to make hibernate generate this index. its easy to reproduce this behavior using sqlplus. u just have to try to delete two Wertpapier records, concurrently from two sessions.

session 1:
delete from kurs where wertpapier_id = 4711;

session 2:
delete from kurs where wertpapier_id = 4712;

session 1:
delete from Wertpapier where id = 4711;

session 2:
delete from Wertpapier where id = 4712;

this causes a deadlock in oracle. if you create an index on wertpapier_id everything works fine. Oracle alway expects indexes on foreign keys, as long as there are foreign key constraints defined.
see http://www.akadia.com/services/ora_lock ... guide.html (chapter: Referential Integrity Locks)

IMHO this should be solved by hibernate, i guess the right place is somewhere below the Oracle9Dialect facade, but thats just a rough guess. Please, you guys from hibernate, make a statement to this. We are currently working on a framework for future projects based on ejb 3, and we urgently need some solution to this. I would do it myself, but first i would need some feedback on my analysis, as well as maybe some hints on where to make these changes.

regards, milan wölke