Re: Lock ACCESS EXCLUSIVE and Select question !

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
>