Re: Lock detection and resolution

On 25 May 2001 06:18:20 -0700, onlyforposting_at_yahoo.com (Vikas Agnihotri) wrote:

>
>'select for update nowait' places a row-exclusive lock on the row,
>right? How can the session release this lock? Only by doing a
>commit/rollback? This is too drastic. Is there any other way?

There is, but it is undocumented and you should not rely on it. Financials used it, IIRC. Dunno if still does.

>
>The issue I have with the 'select for update nowait' is that it is too
>heavy-handed. i.e. it tries to check if a row is locked by trying to
>lock it itself! Seems a little odd.
>

If you follow one of the solutions given by Thomas, it is not heavy-handed at all. The lock will only occur when needed and for a short period of time. It's the approach followed by SQL*Forms.

It has to do with not subverting the RDBMS engine (SQL in ORACLE's case), one of Codd's 12 rules for "true" relational DBMS. You can only use SQL to determine a status or query any system information in a true relational database that is SQL-based. As soon as you open a "back door" and create an operation that doesn't use pure SQL, you throw away the "true relational" definition.

Going back a while, in Codasyl databases handling large volumes of transactions this was also the case. To check a lock you had to lock.

>Isnt there a way to determine if the row is "dirty" without attempting
>to lock it myself?
>
>Or am I completely off the mark?
>

Not at all, it's a common problem in OLTP systems.

Cheers
Nuno Souto
nsouto_at_bigpond.net.au.nospam
http://www.users.bigpond.net.au/the_Den/index.html Received on Fri May 25 2001 - 09:22:32 CDT