Re: Lock ACCESS EXCLUSIVE and Select question !

But i have a new problem there, i have no rows for update, i create new rows
when the seats are sold, cause rarely the database knows the capacity of the
bus, number of seats, even some bus doesn't have seat number 4 for example
:p. So i cannot SELECT FOR UPDATE no existent rows, but i still need to lock
in some way in order to check if seat number X was already sold or is free !

Alan Acosta

On Mon, Feb 28, 2011 at 5:21 PM, Andrew Sullivan <ajs(at)crankycanuck(dot)ca>wrote:

> On Mon, Feb 28, 2011 at 05:13:11PM -0500, Alan Acosta wrote:
> > I really appreciate your help Andrew, and yep, i already starto to feel
> some
> > pain lol. I suppose is true but is better to ask, SELECT FOR UPDATE is
> > faster than LOCK ?
>
> SELECT FOR UPDATE locks the row you're trying to lock. So it's
> "faster" in that it's not completely serialized on one person's
> actions.
>
> As I understand your application, you have a list of seats. You want
> to sell every seat, and you want to make sure that each seat is sold
> no more than once. But you also want people to be able to see whether
> a seat is sold yet, and it would be good if more than one person can
> buy a (different) seat at the same time.
>
> If you do SELECT FOR UPDATE on the seat that the person is selecting
> (or, if you assign them, I guess ORDER BY random LIMIT 1), then you
> lock that seat from being sold while the customer is deciding, but
> other customers could buy a different seat.
>
> What you're doing instead right now is locking the whole table,
> thereby preventing any seat from being sold (or, at the moment, even
> looked at) while one customer is deciding.
>
> It is very rare that you want to be making explicit table locks in a
> database application: you're foiling your transaction manager. It is
> sometimes necessary, but in this case it probably isn't. What you're
> really doing is pretending that the database is like a file on the
> filesystem, and you need to flock it. That's not how transactional
> databases work, and that's the pain you're feeling.
>
> A
>
> --
> Andrew Sullivan
> ajs(at)crankycanuck(dot)ca
>
> --
> 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
>