Question

with in do transaction, i defined a label and in this label i am accessing a table with exclusive-lock.and at the end of label i have done all the changes in that table. bt now i am with in transaction block. Now, i tried to access that same table in another session.then it show an error, Table used by another user. So is it possible that, can we release teh table with in transaction,so another user can access it.

For example:

Session 1)

DO TRANSACTION:
  ---
  ---
  loopb:
  REPEAT:
    --
    --
    ---------------------> control is here right now.
  END. /*repeat*/
  -- 
  --
END. /*do transaction*/

Session 2)

I tried to access same table, but it show an error, that table locked by another user.

Answer 1

All those records you touched in the loop using EXCLUSIVE-LOCK will not be available to be locked by another user until the TRANSACTION is complete. There is no getting around this. If the second process needs to lock those records, then all you can do is decrease your TRANSACTION scope in the first process. This is a safety feature so that if an error happens later on in the TRANSACTION, all the changes made during the TRANSACTION will be rolled back. Another way to look at it is if you could release some record locks during a TRANSACTION, you would lose the atomicity (all-or-nothingness) that is part of the definition of a TRANSACTION.

It should be noted that if you don t really need to lock those records in the second process but just need to see their updated value, that is possible. Once the updated records are no longer in the record buffer (or the record lock status is downgraded to a NO-LOCK in the TRANSACTION), they will become limbo locks and you can view their updated values using a NO-LOCK. To make the last record in the loop become a limbo lock, you can either do this

FIND CURRENT tablerecord NO-LOCK.

Or this, if you do not need to access the record buffer any longer:

RELEASE tablerecord.

Answer 2

Other sessions can do a "dirty read" of the record using NO-LOCK. But they will not be able to lock it or update it until the transaction is committed (or rolled back). And that won t happen until the repeat block iterates or you leave it.

友情链接