From: Craig James <craig_james@emolecules.com>
To: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject: select for update
Date: Fri, 22 Apr 2011 10:49:54 -0700
Message-ID: <4DB1BFC2.7020806@emolecules.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: craig_james@emolecules.com
Subject: Re: select for update
In-Reply-To: <4DB1BFC2.7020806@emolecules.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox