To resolve the issue mentioned here.
We are creating and using 2 same JDBC Singleton Connections(Regular, Proxy).
- But by doing so we are facing deadlock when we try to use both connections consecutively on same table for doing multiple inserts and updates.
- When this happens, I cannot run any queries from the DB tool (Aqua Data Studio) as well.
- My assumption is that it waits indefinitely for other connection to release lock.
Note: We are not dealing with multi-threading here.
Issue:
// Auto Commit false
// Singelton
Connection connection = getConnection(); //same
// Auto Commit true
// // Singelton
Connection proxyConnection= getConnection(); //same
PreparedStatement ps = null;
try{
connection.setAutoCommit(false);
//Step 1
String sql = getQuery();
ps = proxyConnection.prepareStatement(sql);
ps.executeUpdate();
.
.
//Step 2
// if I don't execute this step everything works fine.
sql = getTransctionQuery();
ps = connection.prepareStatement(sql);
ps.executeUpdate();
.
.
//Step 3
sql = getQuery();
ps = proxyConnection.prepareStatement(sql);
ps.executeUpdate(); // this line never completes (if Step 2 runs)
}catch(){
connection.rollback(); //Doesn’t rollback step 1 and understandably step 2.
}
finally{
connection.close(); //cleanup code
proxyConnection.close();
}
Question:
- How to resolve this issue?
- How to make sure different connections, though they are creating using same class loader, won't lock database/table.
Thanks
