title: Lock, process and release lock in jdbc
body: <p>Thats my requirement - to lock a database record, process it and release it</p>
  
  <p>Environment - weblogic 10.3
  Database - Oracle 11g
  Datasources - multiple XA recources involved
  Tx mgr - JTA</p>
  
  <p>Here are the results of the experiments I have done so far:</p>
  
  <p><strong>Experiment 1 - Rely on read uncommitted</strong></p>
  
  <ol>
  <li>Read the db record</li>
  <li>Lock the record by id in another table, as part of the global JTA transaction</li>
  <li>Process the record
  A second transaction which tries to lock the same record will fail, will drop the record.
  But for this to work the RDBMS should allow dirty reads.
  Unfortunately Oracle does not support read uncommitted isolation level.</li>
  </ol>
  
  <p><strong>Experiment 2 - Lock record in local transaction</strong></p>
  
  <ol>
  <li>Read the db record</li>
  <li>Lock the record by id in another table, as a separate local transaction</li>
  <li>Process the record and delete the record when the transaction commits successfully
  A second transaction which tries to lock the same record will fail, will drop the record. This approach is based on committed data, should work fine.
  Here is the problem - Since the lock transaction and the global parent are different, if the processing fails rolling back the main transaction, I should compensate by rolling back the lock transaction, which I do not know how to do - <strong>Need help here</strong></li>
  </ol>
  
  <p>If Iam not able to rollback the record locking transaction, would have to write some dirty logic around the record locking code. I dont prefer this.</p>
  
  <p>This appears to be a very common requirement. I would like to know how you guys handle this elegantly.
  Does Oracle support in any way making uncommitted updates visible to all transactions.</p>
  
  <p>Thanks a lot in advance.</p>