Re: select for update

Поиск
Список
Период
Сортировка
Искать

Re: select for update

От:
Tom Lane <tgl@sss.pgh.pa.us>
Дата:
Craig James  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

Re: select for update

От:
Tom Lane <tgl@sss.pgh.pa.us>
Дата:
Craig James  writes:
> On 4/22/11 1:58 PM, Tom Lane wrote:
>> Craig James  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

Re: select for update

От:
"Kevin Grittner" <Kevin.Grittner@wicourts.gov>
Дата:
Craig James  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

select for update

От:
Craig James <craig_james@emolecules.com>
Дата:
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

Re: select for update

От:
Craig James <craig_james@emolecules.com>
Дата:
On 4/22/11 1:58 PM, Tom Lane wrote:
> Craig James  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
>

Re: select for update

От:
Craig James <craig_james@emolecules.com>
Дата:
On 4/22/11 8:17 PM, Tom Lane wrote:
> Craig James  writes:
>> On 4/22/11 1:58 PM, Tom Lane wrote:
>>> Craig James   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
FAQ