Yep i already have those columns and unique constraint, my issue isn't sell the seat two times, i was a lot of paranoiac about that and use a lock mode to restricted for that.
I will check if i can create rows for seats before sell and use update, so i can use SELECT FOR UPDATE and not use insert, having to lock the whole table to check if a seat is free or sold, but i have to solve several issues in the meantime, for example, to sell a seat for one month in future, will i have to create every single seat(row) in database to be able to know if is free or sold, and other stuff, even so, seems a new and good idea for me, is the first time i heard about SELECT FOR UPDATE.
Another question !, can i combine SELECT FOR UPDATE and LOCK command on different tables at the same transaction ?
Alan Acosta
On Tue, Mar 1, 2011 at 9:33 AM, Andrew Sullivan
<ajs@crankycanuck.ca> wrote:
On Tue, Mar 01, 2011 at 09:23:49AM -0500, Alan Acosta wrote:
> 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.
This still sounds a little odd to me, but there is another way to do
it, and someone suggested it in this thread. If you're doing this
only with INSERT, then you just need to find some combination of
columns that needs to be unique (one of which is obviously the seat
number). Then you'll get a unique violation when two people try to
insert the same data, and someone will lose.
Your application could catch this in a savepoint and try again with a
different seat number.