[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

JDBC Connection Deadlock in J2EE Transaction

Posted on 2007-10-12
9
Medium Priority
?
1,162 Views
Last Modified: 2013-12-19
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.

0
Comment
Question by:handoyog
  • 4
  • 3
7 Comments
 
LVL 2

Accepted Solution

by:
vin_babu earned 2000 total points
ID: 20145941
Its always good to access database thru our ejbs, container takes care of deadlock problems automatically. If you are updating table thru' direct query you can avoid deadlock problem by using ReadWriteLock.
java.util.concurrent.locks.ReentrantReadWriteLock readWriteLock = new ReentrantReadWriteLock(true);

then you can pur writeLock on this before performing any update operation on tables:
getReadWriteLock().writeLock().lock()
which doesn't allow any body else to update the table at the same time.
once opration is done you can release the lock.
Similarly you can put readLock and release it while performing other read operation.
-Vinod
0
 

Author Comment

by:handoyog
ID: 20147350
Did you mean Using ejb as DAO, rather than using POJO as DAO ?
0
 
LVL 2

Expert Comment

by:vin_babu
ID: 20153306
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
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

Author Comment

by:handoyog
ID: 20154222
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
0
 
LVL 2

Expert Comment

by:vin_babu
ID: 20154395
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
0
 

Author Comment

by:handoyog
ID: 20165541
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
0
 
LVL 2

Expert Comment

by:vin_babu
ID: 20167694
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Via a live example, show how to take different types of Oracle backups using RMAN.
Suggested Courses
Course of the Month18 days, 10 hours left to enroll

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question