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