Re: Lock ACCESS EXCLUSIVE and Select question !

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 !