Re: Lock ACCESS EXCLUSIVE and Select question ! - Mailing list pgsql-general

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@crankycanuck.ca