exclusive record lock to prevent modif. by other session

[stilltray]

I use a TOraQuery, Delphi 7 on Oracle 10G

I have in a TOraQuery, beside normal select statements like

Code: Select all

select * from table where key=...

defined in SQLLock:

Code: Select all

select * from table where key=.. FOR UPDATE NOWAIT

On before insert and edit, I've defined in Delphi:
(In short)

Code: Select all

try 
  starttransaction
  lockmode=lmlockImmediate  
  Checkmode=cmException
  query.lock
except
  showmessage('Record in use by other session');
end;

Another user/session tries to modify the same data, using the same code as above (same app).

The second session doesn't see a row lock? No message appears.

I see rowlevel locks in Oracle, but not on the table defined in the query...??
(If the first session issues the rowlock I see rowlocks on record(s?) in a different table as supposed.
If the second session issues a rowlock I see more rowlocks, also in a different table as supposed. Sometimes even different from the (wrong) table mentioned by session 1???)

Is this the right method doing so?
If not how to achieve this functionality? ((I can't find an definite answer in the ODAC help or forum.))

[AlexP]

Hello,

I cannot reproduce the problem when performing the steps you provided

Code: Select all

    try
      OraSession1.StartTransaction;
      OraQuery1.lock;
    except
      ShowMessage('Record in use by other session')
    end;

when attempting to lock the record by the second session, Oracle generates the correct error "ORA-00054 "resource busy and acquire with NOWAIT specified". Perhaps, you unlock this record or call commit/rolback transactions before the second user tries to lock the record. Please provide the whole code or send a sample to Alexp*devart*com in order that we could reproduce the problem.

[stilltray]

It's part of a large app, which uses a (large) number of threads.
To separate the problem is a bit difficult, but I'll implement the code also in another app, without all the threads.
If it works ok in the other app, I'll try to separate the functionality in the 'problemapp'.

(With the code as mentioned in the first post, it looks like it (exclusive) locks records of the active thread....)

[AlexP]

Hello,

Maybe, the problem occurs due to that the thread, that blocked the record, has already stopped before the new thread tries to block the same record. And as, depending on settings, either Commit or RollBack occur when destroying a ToraSession instance, Lock of the records is removed.