Re: Weird (?) happenings with locks and limits?

On Mon, Jul 15, 2002 at 10:19:43AM -0400, Tom Lane wrote:
>Yeah. The syntax is a little misleading, because actually the FOR
>UPDATE lock is the last step. The second guy comes along, finds the
>first row WHERE assignedto is NULL, and then blocks because he can't get
>a FOR UPDATE lock on it. When the first guy commits, the second can
>get a FOR UPDATE lock ... but he now discovers that assignedto isn't
>NULL anymore in that row, so he's not interested in it anymore. And

So it's re-verifying the WHERE clauses after the other branch commits. It
would seem like it would be hard to get correct results after a commit
without re-running the SELECT (in the case where the lock blocks after the
select but before the results are returned).

>try to lock FOR UPDATE that same row. I don't see any good way around
>this, so I'd suggest simplifying matters by not bothering with FOR
>UPDATE. Instead perhaps:

That's a good idea, thanks. I'm also considering having another table
which lists what jobs have been assigned, and having that be a unique
constraint, which would cause other processes requesting the same job to
get a constraint violation and let me re-run the query. On the other hand,
I don't really need concurrency, so locking the table should be fine.

Perhaps the "FOR UPDATE" section of the SELECT documentation should contain
something like:

The lock used by FOR UPDATE may cause some select results to be marked
as no longer valid in cases where another lock has changed related rows.
This may cause some or all of the original SELECT results to become
invalid, producing an artificially small or empty result set.

Thanks,
Sean
--
Follow your dreams. Unless it's the one where you're at work in your
underwear during a fire drill.
Sean Reifschneider, Inimitably Superfluous <jafo(at)tummy(dot)com>
tummy.com - Linux Consulting since 1995. Qmail, KRUD, Firewalls, Python