Re: Locking Question (nowait, UPDATE etc)

"Johannes Eggers" <jeggers_at_tetrix.com> wrote:
> I hope this question makes sense:
>
> We have some code (in stored procedures) that selects data FOR UPDATE
> NOWAIT, and it behaves as excepted.

How is it expected to behave, and why does it do this?

> Other code (running in a separate
> transaction) attempts to update/modify the data using standard DELETE
> FROM table WHERE... or UPDATE table SET ... syntax. That code waits on
> the FOR UPDATE lock indefinitely (i.e. until the other transaction is
> rolled back or committed). We need a mechanism whereby the UPDATE or
> DELETE statement would fail, but it appears that the nowait option is not
> available for the UPDATE or DELETE syntax.

Then do the select for update no wait on this, first.

> For performance reasons, we
> can NOT do another select-for-update-nowait prior to running the actual
> UPDATE or DELETE, this would just be way too slow.

Have you tried it? What would it slow down that the other select for update isn't already slowing down? How long would you expect it to hold the locks for?

Xho

-- 
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service              New Rate! $9.95/Month 50GB