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

]In article <bgmccracken.9.003B160B_at_amoco.com> bgmccracken_at_amoco.com (Bill G. McCracken) writes:
]]Problem is this:
]]We have transactional systems (a helpdesk for one app), that use the
]]row-locking technique (SELECT .... FOR UPDATE ... NOWAIT) to "hold"
]]the record so that another cannot update the current updating record(s), and
]]notifies the second user that someone is trying to update the original row(s)
]]via the error return code of the locked record. But I cannot tell who
]]has the lock on the original record!!
]

We have the EXACT same problem. I have also asked Oracle support for a year or so for a solution, the answer: "There is no solution".

In article <James.Lawrence.70.000B3AB3_at_epamail.epa.gov>, James.Lawrence_at_epamail.epa.gov (Lawrence James) says:
]
]Take a look at the CATBLOCK>SQL script you got from Oracle, maybe something
]useful in there. Good luck, Lawrence....
]
Not good enough, not anywhere near a solution. Consider again:

]]We have transactional systems (a helpdesk for one app), that use the
]]row-locking technique (SELECT .... FOR UPDATE ... NOWAIT) to "hold"
]] [...]
]]The second user would like to know who is actually modifying the record
]]at that time, in case they need to get at it, or notify them when they are
]]'out' of the row(s).
]
The second user is not blocked and therefore the CATBLOCK script is useless.

]]My solution is to build a "app.LOCK" table, that basically holds all this info,
]]and not really use the "FOR UPDATE" process, but check this table first.
]]But that's only good for procs, and custom apps.
]

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

]]Holiday wish: Dear Oracle, please provide us a way to lock records, and know
]]who's in them when they're being updated...it's a very simple request!!
]

I can not agree more. Please make it possible to do this the _right_ way.

And again:

]]I'm not talking about locking the entire table, which yes, you can then check
]]to see who's got the lock, these are high transactional table(s), that multiple
]]users have locks on various rows at a time....think of a helpdesk situation.

/Johan Received on Wed Dec 21 1994 - 08:50:23 CET