asked on

JDBC Connection Deadlock in J2EE Transaction

This problem happens at the development using NetBeans 5.5 and Oracle 10g, Fedora

One Session Bean is engaging in a REQUIRED transaction
Inside, this Session Bean is using one DAO to update 1 parent table and another 4 DAO to update to 4 children tables

An insert to Parent Table is putting a Lock Mode 3 to the parent table in the Database, and also put Lock Mode 2 to all the children

Object Lock Mode Session ID
------------------------------------------------------------------------------
ParentTable 3 152
ChildTable1 2 152
ChildTable2 2 152
ChildTable3 2 152
ChildTable4 2 152

First Child DAO updates its respective Lock Mode Table to 3
Object Lock Mode Session ID
------------------------------------------------------------------------------
ParentTable 3 152
ChildTable1 3 152
ChildTable2 2 152
ChildTable3 2 152
ChildTable4 2 152

Second Child DAO SHOULD updates its respective Lock Mode Table to 3, But It Doesn't
Object Lock Mode Session ID
------------------------------------------------------------------------------
ParentTable 3 152
ChildTable1 3 152
ChildTable2 2 152
ChildTable3 2 152
ChildTable4 2 152
ParentTable 3 159
ChildTable2 3 152

Now it is a Deadlock.
An insert in the ChildTable2 will try to place a lock in the parent table

The cause is : Insert on Parent Table and ChildTable1 is using the same Connection Object,
however, when Connection Object is obtained in DAO2 ( for ChildTable2 ), The Connection Object is different, therefore it is as if there are2 different Oracle Sessions trying to lock the same Row

Note that The Connection Object is obtained from DataSource, and at the end of execution, The connection is closed ( which will remain in the pool for the next request, because J2EE Container hasn't issue COMMIT or ROLLBACK )
The Prepared Statement as well as ResultSet has been closed as well

Questions :
1. What makes JDBC Connection physically closes ( No Exception being thrown here )
2. How can I prevent that from happening ?
3. Help me solve this case
4. I've been trying to Re-Create the case, using the same code, into another App, only in this app, The parent table has only 4 fields, and so do the children. The problem did not happen here.

ASKER CERTIFIED SOLUTION

vin_babu

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

handoyog

ASKER

Did you mean Using ejb as DAO, rather than using POJO as DAO ?

vin_babu

Yes I meant using EntityBeans to update the table... If you have all the code written already then you can use the locks which I have mentioned to avoid deadlock... I have avoided deadlock using ReadWriteLock when I had to update tables thru' update queries directly instead of EJBs.
-Vinod

handoyog

ASKER

I see. I avoid using Entity Beans from the ground up

If I use this lock, then this lock is in my bottom of my application layer before touching the database. But the locking I mentioned above happens automatically at database level, which is still out of my control

How do I propagate this lock to the database then ?

Are there methods in the JDBC that I can execute to obtain your suggestions ?

And on what object do I issue getReadWriteLock() ?
Before executing PreparedStatement.executeUpdate() ?
PreparedStatement and Connection object does not have this method

vin_babu

You doesn't need to apply this lock on object. You can do write lock using:
java.util.concurrent.locks.ReentrantReadWriteLock readWriteLock = new ReentrantReadWriteLock(true);
then you can put writeLock on this before performing any update operation on tables:
getReadWriteLock().writeLock().lock()
which puts lock on the table, basically it wont allow anybody to read/write into tables
then you can release lock once updation is over using
getReadWriteLock().writeLock().unLock()

Similarly you can put readLock also, which tells that no one can update this table until readlock is released.
I think you can use these lock before executing PreparedStatement.executeUpdate()
-Vinod

handoyog

ASKER

Do you have more information where can I study about this Lock ?
Reference or book
I'm not so sure if just calling lock() will put a lock in the database, since there is no way an object lock at Application Layer JVM would propagate automatically at the Database which is in different JVM

vin_babu

have a look at this link:
http://java.sun.com/j2se/1.5.0/docs/api/java/util/concurrent/locks/ReentrantReadWriteLock.html