Re: Lock ACCESS EXCLUSIVE and Select question !

My threads use each one different conecctions, so the transactions are
different, may be my bad English doesn't help to much, sorry for that !

My application is trying to generate a numbered place for a client inside a
bus, and to avoid to sell the place number "5" to two people, so i need to
avoid that two sellers to sell the same place to same time, when i start my
project, i read about table lock and choose ACCESS EXCLUSIVE, cause blocks
everything, in that time seems safe :p, but now i have more and more sellers
and the application is throwing a lot deadlocks in simple SELECTs, i check
my logs and notice that was because ACCESS EXCLUSIVE is taking a little more
time now, and deadlocks arise !

In,

*Table 13-2. Conflicting lock modes*
Requested Lock ModeCurrent Lock ModeACCESS SHAREROW SHAREROW EXCLUSIVESHARE
UPDATE EXCLUSIVESHARESHARE ROW EXCLUSIVEEXCLUSIVEACCESS EXCLUSIVEACCESS
SHARE XROW SHARE XXROW EXCLUSIVE XXXXSHARE UPDATE EXCLUSIVE
XXXXXSHARE XX XXXSHARE ROW EXCLUSIVE XXXXXXEXCLUSIVE XXXXXXXACCESS
EXCLUSIVEXXXXXXXX
I can see that ACCESS EXCLUSIVE and EXCLUSIVE blocks each other on
different transactions at different threads, but SHARE don't, or i'm reading
bad the table ? I need only one process insert or update my tables in my
transaction, no matter how many i have.

How can i know which mode is better to block in which case ?

Cheers,
Alan Acosta

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

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