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 22:13:11
Message-ID: AANLkTikeU_Og9NQb1J6YPXycZW5WSGHf93-EmEu4sLB5@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-general
I really appreciate your help Andrew, and yep, i already starto to feel some
pain lol. I suppose is true but is better to ask, SELECT FOR UPDATE is
faster than LOCK ?

Thanks for the recommendations, i will check them ^_^

Cheers,
Alan Acosta


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

> On Mon, Feb 28, 2011 at 04:12:30PM -0500, Alan Acosta wrote:
>
> > 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 !
>
> Ah.  Well, then, yeah, I think you're going to have some pain.  See more
> below.
>
> > *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,
>
> Share does not, but it does block other writes.  See the text in the
> manual:
>
>    SHARE
>
>    Conflicts with the ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE
>    ROW EXCLUSIVE, EXCLUSIVE, and ACCESS EXCLUSIVE lock modes. This
>    mode protects a table against concurrent data changes.
>
> But I still don't think that's going to scale.
>
> I think what you probably want is to SELECT FOR UPDATE the row you're
> aiming to update later.  Alternatively, you could use some sort of
> pessimistic locking strategy using either a field on the row or an
> advisory lock.  For the latter, see the manual.  For the former, it's
> something like this:
>
>    - create a sequence seq.
>
>    - add an integer column newcol (with a default of 0) to your
>      table.
>
>    - when you select, make sure you include newcol.  Suppose it's
>      value is 0 in the row you want.
>
>    - when you sell the seat, UPDATE the row SET newcol =
>      nextval('seq') WHERE newcol = _previous_newcol_value [and some
>      other criteria, like the seat number or whatever]
>
>    - now, either you affect some number of rows >0, which means you
>      made a sale, or else 0 rows are affected (because some other
>      transaction sold this seat at the same time).  In the latter
>      case, you have to try a new seat.
>
> Hope that helps,
>
> 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 22:21:42
Subject: Re: Lock ACCESS EXCLUSIVE and Select question !
Previous:From: Andrew SullivanDate: 2011-02-28 21:28:04
Subject: Re: Lock ACCESS EXCLUSIVE and Select question !

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