Skip site navigation (1) Skip section navigation (2)

Re: Lock ACCESS EXCLUSIVE and Select question !

From: Alan Acosta <zagato(dot)gekko(at)gmail(dot)com>
To: Andrew Sullivan <ajs(at)crankycanuck(dot)ca>, pgsql-general(at)postgresql(dot)org
Subject: Re: Lock ACCESS EXCLUSIVE and Select question !
Date: 2011-02-28 20:32:08
Message-ID: AANLkTi=WN_H=6uF+6+aJh=n398XDWJa6ArYV-YAW8=gg@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-general
Andrew, thank you very much for reply !

I already update my bookmark for 8.3 which is my current version,
http://www.postgresql.org/docs/8.3/static/explicit-locking.html, i see new
things here, like a comparative table.

Yep, seems like ACCESS EXCLUSIVE is a bad idea, at least now :p ! i check
your recommendation about to use SHARE mode, but in
http://www.postgresql.org/docs/8.3/static/explicit-locking.html i see that
SHARE mode doesn't lock against itself, so, another thread using the same
mode will be able to access the tables for update ! or i'm reading bad *Table
13-2. Conflicting lock modes*. Meanwhile i understand well which mode to use
in which case i reduce my lock level to EXCLUSIVE, which lock against itself
but let SELECT to do his job !

Cheers,
Alan Acosta


On Mon, Feb 28, 2011 at 1:10 PM, Andrew Sullivan <ajs(at)crankycanuck(dot)ca>wrote:

> On Mon, Feb 28, 2011 at 12:43:58PM -0500, Alan Acosta wrote:
>
> > I'm using lock with ACCESS EXCLUSIVE in several of my tables to assure
> that
> > only one process write in those tables at same time
>
> Why are you doing that?  It sounds like a bad idea to me.
>
> But anyway, I believe that the SHARE lock (which is what CREATE INDEX
> uses) ought to work.  It should prevent any concurrent data
> alterations in the table.
>
> Also,
>
> > May be a little newbie question, but i cannot find this answer in
> > http://www.postgresql.org/docs/8.1/static/explicit-locking.html or
> similar
>
> those are the docs for 8.1.x.  Note that it was EOL'd last November:
>
> http://wiki.postgresql.org/wiki/PostgreSQL_Release_Support_Policy
>
> > pages, i really want to know if new rows inserted in an open transaction
> > will be read it by another threads or this new rows are invisible no
> matter
> > the mode of the transaction.
>
> Rows inserted by an uncommitted transaction are invisible to everyone
> else until the transaction commits.  Postgres doesn't have dirty
> reads.  If you have an open transaction and you look at a table where
> another transaction has committed, then you will or will not see the
> resulting rows depending on whether you are in READ COMMITTED or
> SERIALIZABLE isolation mode, respectively.
>
> A
>
> --
> Andrew Sullivan
> ajs(at)crankycanuck(dot)ca
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

In response to

Responses

pgsql-general by date

Next:From: Andrew SullivanDate: 2011-02-28 20:35:13
Subject: Re: Full Vacuum/Reindex vs autovacuum
Previous:From: Jason LongDate: 2011-02-28 19:48:30
Subject: Re: Full Vacuum/Reindex vs autovacuum

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group