X-Original-To: pgsql-sql-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id D18B09FB0D1 for ; Wed, 26 Jul 2006 16:25:55 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 35423-05 for ; Wed, 26 Jul 2006 16:25:45 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from floppy.pyrenet.fr (news.pyrenet.fr [194.116.145.2]) by postgresql.org (Postfix) with ESMTP id 866569FA64B for ; Wed, 26 Jul 2006 16:25:45 -0300 (ADT) Received: by floppy.pyrenet.fr (Postfix, from userid 106) id 8C4BD30E16; Wed, 26 Jul 2006 21:25:37 +0200 (MET DST) From: "Andrew Hammond" X-Newsgroups: pgsql.sql Subject: Re: Rows with exclusive lock Date: 26 Jul 2006 12:25:31 -0700 Organization: http://groups.google.com Lines: 26 Message-ID: <1153941931.290674.125300@b28g2000cwb.googlegroups.com> References: <20060722144218.GH5125@surnet.cl> <20060723161050.GC32458@surnet.cl> Mime-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable X-Complaints-To: groups-abuse@google.com In-Reply-To: User-Agent: G2/0.2 X-HTTP-UserAgent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.8.0.4) Gecko/20060608 Ubuntu/dapper-security Firefox/1.5.0.4,gzip(gfe),gzip(gfe) Complaints-To: groups-abuse@google.com Injection-Info: b28g2000cwb.googlegroups.com; posting-host=207.219.45.62; posting-account=cSUlcA0AAABWACaPjo2Z9C0GBLTio512 To: pgsql-sql@postgresql.org X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.013 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200607/320 X-Sequence-Number: 25894 Martin Marques wrote: > On Sun, 23 Jul 2006, Alvaro Herrera wrote: > > > Martin Marques escribi=F3: > >> > >> After the SELECT FOR UPDATE other transactions can still see the locked > >> rows. I want a read/write lock, so no one can access does rows. > > > > SELECT FOR UPDATE acquires an exclusive lock, but other transactions > > must try to acquire a lock on the rows as well, or they won't be locked. > > You can try using SELECT FOR SHARE (new as of 8.1) if you want some > > transactions to hold shared (read) locks. > > Sorry for not getting it clear the first time. > > What I want is something like "LOCK table IN ACCESS EXCLUSIVE MODE", but > at row level. Well... you could change your other selects to use FOR UPDATE as well, even if they're not going to be updating (but this block concurrent reads). You're probably better to take Alvaro's suggestion above and use SELECT FOR SHARE. Drew