Обсуждение: 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