Re: Lock ACCESS EXCLUSIVE and Select question !

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