Re: Lock ACCESS EXCLUSIVE and Select question !

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