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 <pol...@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-ow...@postgresql.org > [mailto:pgsql-general-ow...@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 >
