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 g1IEdFE61388 for ; Mon, 18 Feb 2002 09:39:15 -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 16cowj-0009gl-00 for pgsql-sql@postgresql.org; Mon, 18 Feb 2002 14:39:13 +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 g1IEdDL16952 for ; Mon, 18 Feb 2002 14:39:13 GMT Message-Id: <200202181439.g1IEdDL16952@fullerruss.dsvr.co.uk> Content-Type: text/plain; charset="iso-8859-1" From: Shane Wright Reply-To: me@shanewright.co.uk To: pgsql-sql@postgresql.org Subject: table locking Date: Mon, 18 Feb 2002 14:39:25 +0000 X-Mailer: KMail [version 1.3.2] MIME-Version: 1.0 Content-Transfer-Encoding: 8bit X-Archive-Number: 200202/209 X-Sequence-Number: 6633 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. Thanks -- Shane