JDBC transaction dead-lock solution required?
- by user49767
It's a scenario described my friend and challenged to find solution. He is using Oracle database and JDBC connection with read committed as transaction isolation level. In one of the transaction, he updates a record and executes selects statement and commits the transaction. when everything happening within single thread, things are fine. But when multiple requests are handled, dead-lock happens.
Thread-A updates a record.
Thread B updates another record.
Thread-A issues select statement and waits for Thread-B's transaction to complete the commit operation.
Thread-B issues select statement and waits for Thread-A's transaction to complete the commit operation.
Now above causes dead-lock. Since they use command pattern, the base framework allows to issue commit only once (at the end of all the db operation), so they are unable to issue commit immediately after select statement.
My argument was Thread-A supposed to select all the records which are committed and hence should not be issue. But he said that Thread-A will surely wait till Thread-B commits the record. is that true?
What are all the ways, to avoid the above issue? is it possible to change isolation-level? (without changing underlying java framework)
Little information about base framework, it is something similar to Struts action, their each and every request handled by one action, transaction begins before execution and commits after execution.
