handoyog
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
-Vinod
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.executeU pdate() ?
PreparedStatement and Connection object does not have this method
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.executeU
PreparedStatement and Connection object does not have this method
You doesn't need to apply this lock on object. You can do write lock using:
java.util.concurrent.locks .Reentrant ReadWriteL ock readWriteLock = new ReentrantReadWriteLock(tru e);
then you can put writeLock on this before performing any update operation on tables:
getReadWriteLock().writeLo ck().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().writeLo ck().unLoc k()
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.executeU pdate()
-Vinod
java.util.concurrent.locks
then you can put writeLock on this before performing any update operation on tables:
getReadWriteLock().writeLo
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().writeLo
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.executeU
-Vinod
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
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
have a look at this link:
http://java.sun.com/j2se/1.5.0/docs/api/java/util/concurrent/locks/ReentrantReadWriteLock.html
http://java.sun.com/j2se/1.5.0/docs/api/java/util/concurrent/locks/ReentrantReadWriteLock.html
ASKER