Received: from candle.pha.pa.us (216-55-132-35.dsl.san-diego.abac.net [216.55.132.35]) by postgresql.org (8.11.3/8.11.4) with ESMTP id g1IFGJE87535 for ; Mon, 18 Feb 2002 10:16:19 -0500 (EST) (envelope-from pgman@candle.pha.pa.us) Received: (from pgman@localhost) by candle.pha.pa.us (8.11.6/8.10.1) id g1IFGF517803; Mon, 18 Feb 2002 10:16:15 -0500 (EST) From: Bruce Momjian Message-Id: <200202181516.g1IFGF517803@candle.pha.pa.us> Subject: Re: table locking In-Reply-To: <200202181439.g1IEdDL16952@fullerruss.dsvr.co.uk> To: me@shanewright.co.uk Date: Mon, 18 Feb 2002 10:16:15 -0500 (EST) CC: pgsql-sql@postgresql.org X-Mailer: ELM [version 2.4ME+ PL96 (25)] MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset=US-ASCII X-Archive-Number: 200202/211 X-Sequence-Number: 6635 Shane Wright wrote: > Hi > > I'm having trouble getting my head round postgres's locking. I've read > through all the docs but cant seem to find the right thingthat does what I > want... > > > Basically, there are a few tables in my system where a SELECT is done prior > to an INSERT to avoid duplicating certain data and so forth. > > So, I want to lock the table for the transaction that does the stuff above so > no more than one can run at a time (essentially making it SERIALIZABLE). > But, I dont want to block other readers from SELECTing from the table at the > same time... > > This is the classic multiple readers, single writer problem yes? I'm sure > its doable, but I cant work out which locking / transaction isolation levels > to use. See my chapter on transactions. I think you want SERIALIZABLE transaction mode. http://www.postgresql.org/docs/awbook.html -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026