Re: Lock ACCESS EXCLUSIVE and Select question !

On Mon, Feb 28, 2011 at 03:32:08PM -0500, Alan Acosta wrote:
> 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 !

I think I might be misunderstanding you. As I read the above, you're
using more than one thread on the same connection when a transaction
is open. That is almost certainly a bad idea, if that's what you're doing.

There is no locking mode that blocks the same transaction from using
the table. If so, your transaction wouldn't be able to do anything
with the locked table (including, presumably, release the lock).

I ask again why you think it's a good idea to prevent any other
transaction from writing into the table you're using. I can think of
a couple cases where that would be necessary, but in almost every case
I've seen people do that it's from not understanding database
trasactions. It's almost certainly the wrong thing. If you said more
about what you're trying to do, maybe someone can help you.

A

--
Andrew Sullivan
ajs(at)crankycanuck(dot)ca