Re: Lock ACCESS EXCLUSIVE and Select question !

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

On Mon, Feb 28, 2011 at 8:13 PM, David Johnston <polobo(at)yahoo(dot)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(at)postgresql(dot)org
> [mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Andrew Sullivan
> Sent: Monday, February 28, 2011 4:28 PM
> To: pgsql-general(at)postgresql(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>