Обсуждение: select for update

I thought I understood "select ... for update," but maybe not.

We have a number of separate databases and a unique integer identifier that's supposed to be global across all
databases. A single "archive" database is used to issue the next available ID when a process wants to create a new
object. The sequence of operations goes like this (pseudo-code): 

   /* start a transaction *
   begin;

   /* see if an objectid has been returned for re-use */
   select objectid from archive where db_id is null limit 1 for update

   /* no ID available?  issue a new one */
   if (objectid is null)
     new_id = select nextval('object_id_sequence')
     insert into archive(objectid, db_id) values('new_id', 'new_id')

   /* ID available? claim it */
   else
     update archive set db_id = this_db_id where objectid

   commit

The problem is that very occasionally the same ID will be issued twice.  I don't see how this can be.  Doesn't the "for
update"guarantee that no other process can claim that same row? 

Thanks,
Craig

Craig James <craig_james@emolecules.com> wrote:

> The sequence of operations goes like this (pseudo-code):

> The problem is that very occasionally the same ID will be issued
> twice.  I don't see how this can be.  Doesn't the "for update"
> guarantee that no other process can claim that same row?

I don't see a flaw in the pseudo-code (other than an apparent typo
on the insert).  Can you show the actual code?

-Kevin

Craig James <craig_james@emolecules.com> writes:
>    select objectid from archive where db_id is null limit 1 for update

The interaction between LIMIT and FOR UPDATE changed in 9.0 ... what
PG version are you using?

            regards, tom lane

On 4/22/11 1:58 PM, Tom Lane wrote:
> Craig James<craig_james@emolecules.com>  writes:
>>     select objectid from archive where db_id is null limit 1 for update
> The interaction between LIMIT and FOR UPDATE changed in 9.0 ... what
> PG version are you using?
8.4.4

thanks,
Craig
>             regards, tom lane
>

Craig James <craig_james@emolecules.com> writes:
> On 4/22/11 1:58 PM, Tom Lane wrote:
>> Craig James<craig_james@emolecules.com>  writes:
>>> select objectid from archive where db_id is null limit 1 for update

>> The interaction between LIMIT and FOR UPDATE changed in 9.0 ... what
>> PG version are you using?

> 8.4.4

Well, note what it says in the 8.4 SELECT reference page:

                Caution

    It is possible for a SELECT command using both LIMIT and FOR
    UPDATE/SHARE clauses to return fewer rows than specified by
    LIMIT. This is because LIMIT is applied first. The command
    selects the specified number of rows, but might then block
    trying to obtain a lock on one or more of them. Once the SELECT
    unblocks, the row might have been deleted or updated so that it
    does not meet the query WHERE condition anymore, in which case
    it will not be returned.

I think what's probably happening to you is you're getting a NULL not
because there isn't a matching row, but because somebody is updating the
first matching row underneath you and then the LIMIT prevents finding
any other matches.  However, that pseudo-code is too pseudo to tell
whether this theory is correct.

(9.0 handles these situations in a less unintuitive fashion, btw.)

            regards, tom lane

On 4/22/11 8:17 PM, Tom Lane wrote:
> Craig James<craig_james@emolecules.com>  writes:
>> On 4/22/11 1:58 PM, Tom Lane wrote:
>>> Craig James<craig_james@emolecules.com>   writes:
>>>> select objectid from archive where db_id is null limit 1 for update
>>> The interaction between LIMIT and FOR UPDATE changed in 9.0 ... what
>>> PG version are you using?
>> 8.4.4
> Well, note what it says in the 8.4 SELECT reference page:
>
>                 Caution
>
>     It is possible for a SELECT command using both LIMIT and FOR
>     UPDATE/SHARE clauses to return fewer rows than specified by
>     LIMIT. This is because LIMIT is applied first. The command
>     selects the specified number of rows, but might then block
>     trying to obtain a lock on one or more of them. Once the SELECT
>     unblocks, the row might have been deleted or updated so that it
>     does not meet the query WHERE condition anymore, in which case
>     it will not be returned.
>
> I think what's probably happening to you is you're getting a NULL not
> because there isn't a matching row, but because somebody is updating the
> first matching row underneath you and then the LIMIT prevents finding
> any other matches.  However, that pseudo-code is too pseudo to tell
> whether this theory is correct.
Thanks, it sounds like this is exactly what's happening.  It happens very rarely (a few times per month), so this makes
sense.

I think I just need a two-step approach:

   $object_id = $dbh->selectrow_array("select min(objectid) from archive where db_id is null");
   if ($object_id) {
     $db_id = $dbh->selectrow_array("select db_id from archive where objectid = $object_id for update");
     ... double check that db_id is still NULL, repeat if someone else grabbed it...
   }
> (9.0 handles these situations in a less unintuitive fashion, btw.)
We'll be migrating soon, thanks.

Craig