Hibernate db2 dealock

I am currently developing a product using Hibernate, DB2 and JBoss and I am experiancing deadlock The difficult part of the problem is that it locks on a various tables at different times. I am using TRANSACTION_REPEATABLE_READ as my transaction isolation level, and Spring Framework utilities to fit into our DAO pattern. Our datamodel is fairly complex and our transactions are fairly long (2-4 sec on current hardware/test env). Short of dumping all of our code on the forum, I just wanted to ask if anybody has had deadlock with db2/hibernate/jboss/spring, and if/how they fixed it.

Thanks,
Aaron

(I understand that this may not be enough information to provide a concrete resolution, I just wanted to get some suggestions for where to look.)

Ref: https://forums.hibernate.org/viewtopic.php?f=1&t=925392&start=0

 

My code was somewhat like this.

view plaincopy to clipboardprint?

1. mymethod(){   
2.    DAO.find(where something = something)   
3.    DAO.delete(passTheId)   
4.    DAO.insert(newOrUpdatedValue)   
5. }  

mymethod(){ DAO.find(where something = something) DAO.delete(passTheId) DAO.insert(newOrUpdatedValue) }

mutiple threads in different transactions calling this method were deadlocking after the first few completed successfully.

since the first query was "find", successive threads had to wait for their predecessors to complete as the predecessors had acquired "read" locks. so the first few threads completed as they were within timeout limits, but other succeeding threads were not so lucky.

[This is MY assumption of what was happening]

Simple soln: Make the method synchronized if its not too much of a performance hit

 

 

Hibernate in WAS 5.x - fixes possible deadlock problem

I know that a Hibernate application should use only one Hibernate session in a J2EE transaction (one hibernate session per application transaction). Anyway if you have already built a J2EE application that opens and closes hibernate sessions several times in one J2EE/JTA application transaction, then you should read this posting.

WebSphere 5.0/5.1 gives a different physical database connection for the same transaction if you call DataSource.getConnection() in different SLSB EJBs in some cases (at least when there is concurrent transactions going on). The database doesn't recognize that the different physical database connections belongs to the same transaction. This causes a lot of problems in database transaction isolation. For example, it will cause a deadlock if another EJB modifies some rows and later in the same transaction, another EJB tries to fetch these modified rows. WebSphere will also use different physical database connections for each Hibernate session (in a complex application this might be 10 connections for 1 transaction!).

Read this article to understand what it means:

http://www.ibm.com/developerworks/websphere/library/techarticles/0404_tang/0404_tang.html

It also has the solution. WebSphere will only return the same physical database connection if you use the WebSphere specific API to fetch the connection from the DataSource. WebSphere will return the same physical database connection if the contents of JDBCConnectionSpec objects match (It compares the new connection's JDBCConnectionSpec to the one of the existing connection.).

example code for fetching the JDBC Connection:

   JDBCConnectionSpec connSpec = WSRRAFactory.createJDBCConnectionSpec();    connSpec.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);    if (user != null || pass != null) {       connSpec.setUserName(user);       connSpec.setPassword(pass);       }    Connection connection=((WSDataSource)ds).getConnection(connSpec); 

Are you performing several update/insert/delete ?? If you are then set :: 
connection.setAutoCommit(false); // We want to do commit manually only after all the update/delete/insert are OK
 Once all the queries have executed, perform connection.commit(); // No exceptions so update/delete/insert OK so commit,and then close the connection. This is what i did !!