Database deadlock with READ_UNCOMMITED transaction isolation

avadhuta · **Joined:** Fri Aug 12, 2005 3:55 am **Posts:** 11

Hallo.
I use Hibernate 3.0 with MS SQL Server 7 within web application. It have two main parts: the HTML-page generator and the client-server part for data update. In both parts I use a standart sequence with "open session, start transaction, do some read or write, submit transaction, close session". For each session I explicitly setup FlushMode to COMMIT.

But, when I try to add/update some rows in database, while they accessed by web users, I got a deadlock in the MS SQL Server 7 database. And this lock can hang several hours, before I can see it. I have supposed, such problem appear because of transaction isolation level, and I change it to READ_UNCOMMITED. But this do not affect the problem.

In documentation I read about automatic pessimistic locking in Hibernate, and could not find any traces of possibility to influence on this actions. And on this forum I read about absence of the pessimistic locking implementation in SybaseDialect. With this I do not have any reasonable suggestion how to solve this problem.
Help! Help! Help!

adamgibbons · **Joined:** Sat Apr 21, 2007 11:01 pm **Posts:** 144

Can you post your hibernate config file and example code of how your handelling the session? ^_^

damnhandy · **Posted:** Sat Apr 28, 2007 10:08 am

I'm not clear on how your application is set up, but it sounds like you have a web application and you are performing 2 transactions in the same HTTP Request? Please correct me if I am mistaken. It would also be very helpful if you could post a code snippet which can illustrate how both sessions are being utilized.

Ryan-

avadhuta · **Joined:** Fri Aug 12, 2005 3:55 am **Posts:** 11

Here is Hibernate config file:

Code:

<?xml version="1.0" encoding="UTF-8"?>
<hibernate-configuration>
<session-factory>
<property name="connection.driver_class">com.jnetdirect.jsql.JSQLDriver</property>
<property name="connection.url">jdbc:JSQLConnect://192.168.1.1:4433/database=MvsSite</property>
<property name="connection.username">sa</property>
<property name="connection.password"></property>
<property name="hibernate.cglib.use_reflection_optimizer">true</property>
<property name="show_sql">false</property>
<property name="use_outer_join">true</property>
<property name="hibernate.connection.isolation">1</property>
<property name="dialect">org.hibernate.dialect.SybaseDialect</property>
<property name="transaction.factory_class">org.hibernate.transaction.JDBCTransactionFactory</property>
<property name="hibernate.c3p0.max_size">10</property>
<property name="hibernate.c3p0.min_size">2</property>
<property name="hibernate.c3p0.timeout">5000</property>
<property name="hibernate.c3p0.max_statements">1000</property>
<property name="hibernate.c3p0.validate">false</property>
<mapping resource="com/avadhuta/stardragon/common/jdo/DeletedObject.hbm.xml"/>
<mapping resource="com/mvs/site/entity/News.hbm.xml"/>
<!-- and so on -->
</session-factory>
</hibernate-configuration>

I can't show exact code, because it very compicated and divided on several blocks. Here is main idea:

Code:

    Session session=sessionFactory.createSession();
    Transaction transaction;
    try{
      transaction=session.beginTransaction();
    } catch(Exception ex){
      session.close();
      throw ex;
    }

    /////////////////////// do something clever //////////////////////////
    boolean commit=true; // or false if was some problem like exception

    try{
      if(transaction.wasCommitted() == false && transaction.wasRolledBack() == false){
        if(commit){
          transaction.commit();
        }else{
          transaction.rollback();
        }
      }
    }catch(Exception ex){
      session.connection().rollback();
      throw ex;
    }finally{
      session.close();
    }

I have my own server-side program and Jetty web server as plugin module. Any operations on server go through this code to obtain access to database. And any operation use only one session. I think, problem consist in simultaneous access to table. When I add/update rows, some web-users can load a page, that show a content of this rows. I store binary data in my table, and it may take much time to complete transaction. (Maximum 1 second.)

I just suggested, that READ_UNCOMMITED isolation level make impossible to lock table in any way. But factual situation show, it was wrong idea.

avadhuta · **Joined:** Fri Aug 12, 2005 3:55 am **Posts:** 11

Help! Help! Help! T__T
What can it be?

dharmendra.pandey · **Posted:** Wed May 02, 2007 6:21 am

HI avadhuta,

}catch(Exception ex){
session.connection().rollback(); throw ex;
}finally{
session.close();
}

Why are you doing session.connection().rollback(); dose it make sense .

instead of using this i would prefer to use Hibernateutil.java to handle all these .Use it

avadhuta · **Joined:** Fri Aug 12, 2005 3:55 am **Posts:** 11

Quote:

Why are you doing session.connection().rollback(); dose it make sense .

I don't think, it have any relations with my problem. I suppose, exception can occur because of some errors in Hibernate code. And I just make another attempt to rollback transaction for safety reason.

When I got deadlock, I do not have any exceptions. My program just "buzz".