Re: Lock ACCESS EXCLUSIVE and Select question ! - Mailing list pgsql-general
| From | Alan Acosta |
|---|---|
| Subject | Re: Lock ACCESS EXCLUSIVE and Select question ! |
| Date | |
| Msg-id | AANLkTinxqmbgTBzstSaMgdJDbQE61T78-E9LXpC77Atr@mail.gmail.com Whole thread Raw |
| In response to | Re: Lock ACCESS EXCLUSIVE and Select question ! ("David Johnston" <polobo@yahoo.com>) |
| Responses |
Re: Lock ACCESS EXCLUSIVE and Select question !
(Andrew Sullivan <ajs@crankycanuck.ca>)
|
| List | pgsql-general |
Hi everyone, thanks for all your advice, i will take then in mind ^_^, yep it was a little difficult to know which seats i can sell, but it was one of the client request, some business constraints don't let me know how many seats have an specific bus even 5 minutes before departure, sometimes i know sometimes i don't, even sometimes when i know i have to change on fly this capacity, for example my bus crash just before departure, so i have to use a default averaged capacity. A human must have the final word about which bus departure, so the software must be very very open to changes.
Meanwhile, i reduce my lock level and even the CPU load is now lower LOL, is fantastic, thanks for your help, clients are now working better and faster than before ^_^, i still have a lot of to read about postgres.
Alan Acosta
Meanwhile, i reduce my lock level and even the CPU load is now lower LOL, is fantastic, thanks for your help, clients are now working better and faster than before ^_^, i still have a lot of to read about postgres.
Alan Acosta
On Mon, Feb 28, 2011 at 8:13 PM, David Johnston <polobo@yahoo.com> wrote:
As mentioned SELECT FOR UPDATE is likely your best option. As for an
algorithm if you can find an airline or sporting event case study those two
domains have this problem solved already. Barring that the following comes
to mind.
Create a record for every "seat" that needs to be sold.
You can list all unreserved seats at a given point in time then at the time
of attempted reservation you re-SELECT but this time with FOR UPDATE and
then immediately mark the seat as reserved (and when it was reserved).
Establish a policy that reservations last for "X minutes" and, using
application code, reset the reservation to OPEN if that time elapses.
If the application needs to restart you can scan the table for the
reservation time and reset any that have already expired while loading back
into memory all those that are still valid.
It really isn't that different than dispatching tasks to handlers (which is
what I do) and the FOR UPDATE works just fine. I recommend using a pl/pgsql
function for implementation. Return a reservationID if the seat has been
reserved for a specific user or return null if it could not be reserved.
You also have access to "RAISE" events. Alternatively, you could output a
multi-column row with a Boolean true/false as one of the fields for
"reservation made" and have other message field for cases where it was not
made.
David J.
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Andrew Sullivan
Sent: Monday, February 28, 2011 4:28 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] 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 !--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
pgsql-general by date: