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@xxxxxxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
