Received: from smtp-relay.noc.dsvr.net (smtp-relay.noc.dsvr.net [212.69.192.4]) by postgresql.org (8.11.3/8.11.4) with ESMTP id g1IFgZE00798 for ; Mon, 18 Feb 2002 10:42:35 -0500 (EST) (envelope-from me@shanewright.co.uk) Received: from [212.69.206.2] (helo=fullerruss.dsvr.co.uk) by smtp-relay.noc.dsvr.net with esmtp (Exim 3.16 #1) id 16cpw7-000Dwu-00; Mon, 18 Feb 2002 15:42:39 +0000 Received: from there (host213-122-81-43.in-addr.btopenworld.com [213.122.81.43] (may be forged)) by fullerruss.dsvr.co.uk (8.11.3/8.11.1) with SMTP id g1IFgcE27761; Mon, 18 Feb 2002 15:42:38 GMT Message-Id: <200202181542.g1IFgcE27761@fullerruss.dsvr.co.uk> Content-Type: text/plain; charset="iso-8859-1" From: Shane Wright Reply-To: me@shanewright.co.uk To: Bruce Momjian Subject: Re: table locking Date: Mon, 18 Feb 2002 15:42:51 +0000 X-Mailer: KMail [version 1.3.2] Cc: pgsql-sql@postgresql.org References: <200202181534.g1IFYDP19471@candle.pha.pa.us> In-Reply-To: <200202181534.g1IFYDP19471@candle.pha.pa.us> MIME-Version: 1.0 Content-Transfer-Encoding: 8bit X-Archive-Number: 200202/217 X-Sequence-Number: 6641 Hi Bruce, I've had a go with that, but its proving quite complicated because of the number of combinations. The main problem is that the initial SELECT doesn't match any rows (it isnt supposed to), so I can use SELECT .. FOR UPDATE or anything. The exact thing is this... 1 - generate an ID randomly 2 - SELECT in the table to see if a row already has that ID 3 - if it does, loop back and generate another 4- if not, insert the new row with that ID So, I need to block other processes doing that same thing or they can (and do) pick the same ID and it gets duplicated (or one fails because of a UNIQUE constraint). I know it's not the best method for generating IDs, but it works relatively well given that I cant use sequences (gives away too much info about whats in the system), and I dont want to use oids (not portable, and issues on the oids surviving across databases). -- Shane On Monday 18 Feb 2002 3:34 pm, Bruce Momjian wrote: > Shane Wright wrote: > > Hi Bruce > > > > Thanks for the information (and the book link, straight in the bookmarks > > and remembered for an Amazon search...) > > > > Anyway, the chapter was helpful, but I'm still stuck. SERIALIZABLE looks > > like it would do the job, but I don't want any queries to fail/rollback - > > I'd rather they stalled on SET TRANSACTION or LOCK TABLE until the first > > was complete. > > > > Is this possible? > > > > I am also assuming that even with SERIALIZABLE, concurrent SELECTs can > > proceed unhindered? > > Actually, I think the default READ COMMITTED will work fine for you. > Backends will block waiting for your write, then continue when you > commit, and reads will not be affected. I am not totally sure what you > are testing, so I suggest starting two psql sessions and trying it.