TQuery waiting

[Grubi]

Following scenario with TOracleQuery:

1.) User a executes update query for record x
2.) User b executes update query for record x

Now the query of user b is waiting until user a commits the transaction.

Is there a way that the query of user b returns immediately with an errormsg.

Changing session isolationlevel does not make any difference.

Thank's for your help.
Regards,
Frank Gruber

 

[Marco Kalter]

You will have to issue a "select ... for update nowait" statement first. This locks the record, and raises an "ORA-00054 resource busy and acquire with NOWAIT specified" exception if the record is locked by another session. After successfully selecting the record for update it is locked by your session, so you can safely update it.

This is what the TOracleDataSet does when updating, deleting or locking a record.

------------------
Marco Kalter
Allround Automations

 

[Grubi]

Ok, meanwhile I found many many post dealing with this issue in GoogleGroups. This seems to be one of the quirks of Oracle itself and has nothing to do with DOA. IMHO other DBMS (Interbase, Firebird, Informix) have better options with regards to concurrency. Would be enough to have a NOWAIT clause for UPDATE queries.

BTW do you know why the isolationlevel serialisation behaves different than stated in the Oracle docs. There it is said that an attempt to update a record which was updated meanwhile by a different TR would result in an errormessage without wait.

Thank's for your answer.
Regards,
Frank.

[This message has been edited by Grubi (edited 20 September 2003).]

[This message has been edited by Grubi (edited 20 September 2003).]