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@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Andrew Sullivan Sent: Monday, February 28, 2011 4:28 PM To: pgsql-general@xxxxxxxxxxxxxx Subject: 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 ! -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
