Skip site navigation (1) Skip section navigation (2)

Re: SELECT FOR UPDATE and LIMIT 1 behave oddly

From: Josh Berkus <josh(at)agliodbs(dot)com>
To: Neil Conway <neilc(at)samurai(dot)com>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-bugs(at)postgresql(dot)org
Subject: Re: SELECT FOR UPDATE and LIMIT 1 behave oddly
Date: 2004-10-15 16:09:47
Message-ID: 200410150909.47890.josh@agliodbs.com (view raw or flat)
Thread:
Lists: pgsql-bugs
Tom, Neil,

> > Au contraire: every row that gets locked will be returned to the client.
> > The gripe at hand is that the number of such rows may be smaller than
> > the client wished, because the LIMIT step is applied before we do the
> > FOR UPDATE step

As I said, I think this can be taken care of with a doc patch.    The truth is 
that FOR UPDATE LIMIT is not really terribly useful (it will still block 
outer queries to that table with the same LIMIT clause, so why not lock the 
whole table?).   I propose that I add this sentence to the Docs:

--------------
Please not that, since LIMIT is applied before FOR UPDATE, rows which 
disappear from the target set while waiting for a lock may result in less 
than LIMIT # of rows being returned.   This can result in unintuitive 
behavior, so FOR UPDATE and LIMIT should only be combined after significant 
testing.
---------------

Here's a question, though, for my education:  It's possible to query "Please 
lock the first row which is not already locked" by including pg_locks, 
pg_class and xmax in the query set.    Tom warned that this could result in a 
race condition.   If the query-and-lock were a single statement, how would a 
race condition result?  How could I test for it?

-- 
Josh Berkus
Aglio Database Solutions
San Francisco

In response to

Responses

pgsql-bugs by date

Next:From: Oliver ElphickDate: 2004-10-16 17:35:16
Subject: Re: SELECT FOR UPDATE and LIMIT 1 behave oddly
Previous:From: Neil ConwayDate: 2004-10-15 05:37:10
Subject: Re: SELECT FOR UPDATE and LIMIT 1 behave oddly

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group