Re: Lock ACCESS EXCLUSIVE and Select question !

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
>