Re: Locking Question (nowait, UPDATE etc)

<ctcgag_at_hotmail.com> wrote in message
news:20030327145537.582$gY_at_newsreader.com...
> "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?

It's supposed to lock the records. We do it because we want the records locked (so they're not modified while we process them in our client) and because we actually do want the records in the client, for processing. There is only one process that wants to lock the records for this extended period of time.

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

Tried this, it seems the lock doesn't survive the stored procedure that is putting the lock on it, amazingly enough. We can work-around that by doing a simple UPDATE, then select the records.

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

Yes. The code that just wants to do a quick update would be nearly twice as slow if it first did a select for update. Also, we'd have to retrofit all existing code that does updates, not just the one long running process. The long-running process may hold a lock for a couple of minutes and we need other processes to NOT wait for that long when they try to update. Received on Fri Mar 28 2003 - 08:38:28 CST