CODEWITHSUNDEEP
javajdbctransactionsoracle11gdistributed-transactions
Krishnan
Krishnan

Reputation: 175

Lock, process and release lock in jdbc

Thats my requirement - to lock a database record, process it and release it

Environment - weblogic 10.3 Database - Oracle 11g Datasources - multiple XA recources involved Tx mgr - JTA

Here are the results of the experiments I have done so far:

Experiment 1 - Rely on read uncommitted

  1. Read the db record
  2. Lock the record by id in another table, as part of the global JTA transaction
  3. 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.

Experiment 2 - Lock record in local transaction

  1. Read the db record
  2. Lock the record by id in another table, as a separate local transaction
  3. 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 - Need help here

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.

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.

Thanks a lot in advance.

Upvotes: 0

Views: 4214

Answers (1)

Laurent Legrand
Laurent Legrand

Reputation: 1144

We have an utility class that implements roughly what you describe in experiment 2:

Prerequisite: having a dedicated table for the lock

On lock phase, a new connection is created; a INSERT INTO is performed on the lock table.

On unlock phase, a rollback on the connection is performed regardless of the execution of the business logic.

It is used like a java.util.concurrent.locks.Lock:

Lock lock = new Lock(...);
lock.lock();
try {

    // your business logic
} finally {
   lock.unlock();
}

It works on websphere / oracle.

Note that if you use JPA, there is a built-in support for entity locking.

Upvotes: 1

Related Questions