Lock ACCESS EXCLUSIVE and Select question !

From: Alan Acosta <zagato(dot)gekko(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Lock ACCESS EXCLUSIVE and Select question !
Date: 2011-02-28 17:43:58
Message-ID: AANLkTi=NVnE0oNBr4d0TfWw7PXLduiVpu9R91WT9Ln==@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi everyone !

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, this is blocking my
SELECT, and this is what i want, but, deadlocks start to showing more and
more in my logs when SELECTS failt to get his Share Lock mode.

My question is, can i use a lower mode for example "EXCLUSIVE", letting
Select commands to read the table but avoiding that select reads the new
rows inside my transaction ?

For example:

- lock table ticks with "some mode"
- start to inserting new rows in ticks
- meanwhile another thread: select * from ticks (but this select not
bring me the new rows)
- end of transaction

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
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.

Any help is very welcome ^_^

Cheers,
Alan Acosta

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Andrew Sullivan 2011-02-28 18:10:19 Re: Lock ACCESS EXCLUSIVE and Select question !
Previous Message Adrian Klaver 2011-02-28 17:20:46 Re: Permission denied when inserting