Re: Lock detection and resolution

A copy of this was sent to onlyforposting_at_yahoo.com (Vikas Agnihotri) (if that email address didn't require changing) On 25 May 2001 06:18:20 -0700, you wrote:

>Thomas Kyte <tkyte_at_us.oracle.com> wrote in message news:<tucrgtc0blq1sbindn85n13lpea5vcp3l4_at_4ax.com>...
>>
>
>[select ... for update nowait]
>
>Thanks for the great explanation.
>
>This is what I thought, but I have another question.
>
>'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?
>

that is the only way. If you feel that is "too drastic" postpone the select for update to be IMMEDIATELY PRIOR to the update. There you would be "optimistically hoping that no one else updates the row"

>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.
>

no, not at all. My logic was:

read the data out with no locks. Let the user look at them.

As soon as the user decides "hey, I'm going to modify this row" -- you lock it for them. This does not prevent others from reading, only from writing (or expressing their intention to write).

When you commit -- lock goes away.

the other alternative was:

read the data out with no locks. let the user look at them.

let the user work on a copy of the data. No locks yet.

User says "save work", you then select for update (re-read to avoid the LOST UPDATE) and then update. Locks are held for a small period while you actually update the data. locks are gone as soon as you commit.

It is upto you. Many extremely large scale applications (all apps built with Oracle forms for example) use option 1 -- it is their natural mode of operation.

>Isnt there a way to determine if the row is "dirty" without attempting
>to lock it myself?
>

No, and even if there was -- it wouldn't tell you anything. It could tell you that as 12:01:01.000 the row was not locked -- but at 12:01:01.001 (a split instant later) the row could become locked. Asking "is row X locked" without locking it doesn't tell you anything. The split INSTANT after you ask the question -- the answer can change.

If you are worried about holding locks too long -- use option2 (although you might make your end users "mad" because they spent all of that time working on a row only to find out that someone else was working on it -- and hence their update cannot take place due to the lost update effect)

>Or am I completely off the mark?
>
>Thanks,
>Vikas

--
Thomas Kyte (tkyte_at_us.oracle.com) Oracle Service Industries
Howtos and such: http://asktom.oracle.com/ http://asktom.oracle.com/~tkyte/
Oracle Magazine: http://www.oracle.com/oramag
Opinions are mine and do not necessarily reflect those of Oracle Corp