Re: Row-locking on "FOR UPDATE" processes - Oracle support pls read.

In article <3d8mnv$lfl_at_astrakan.astrakan.se>

           jna_at_astrakan.se "Johan Andersson" writes:

: I have an idea, not implemented though, along the lines of using a
: DBMSpipe within a trigger on the table to a special session which could
: write lock information somewhere else, thereby circumventing the COMMIT
: demand. It might work, but it would be a nasty workaround...
:

Another idea (also not implemented): how about having a go with the DBMS_LOCK package: something along the lines of:

   When the row is to be updated use allocate_unique() to    translate table_name||rowid (caters for clusters) into an    x_mode user-defined lock, and take it out with NOWAIT.

   When another user fails to lock the row, then they do the    same allocate_unique and use the v$lock view to see which    sessions etc.

One possible advantage of this method over the 'create a table of app_locks' implemented by another poster is that locks are guaranteed to disappear when a session terminates; applications which create their own locking methods by using tables sometimes manage to leave rogue rows behind.

Unfortunately, this approach, like yours, cannot be applied through triggers, as there is no 'on lock' table-trigger. (I am assuming here that the application would want to go through the Forms type approach of 'select ... select for update nowait ... update'; rather than leaping straight into an update.)

From an efficiency point of view, my interpretaion of the notes and code of the allocate_unique() function is that it is likely to be the most costly part of the process (it inserts into tables, selects sequences etc, and does a commit): if circumstances permit, it would be more efficient to translate the table/rowid into a 'lockid' (an integer between 0 and 1073741823) using a home-brew function.

-- 
Jonathan Lewis