I'm looking to do something similar to a feature found on
Ticketmaster.com, where you select your seats at a venue, and then you
have two minutes in which to take or leave them.
QUESTION 1a. Inside (or just after) the same query that searches for
available seats, I need to SIMULTANEOUSLY mark those seats as "on
hold".
I've only read about, but not yet used MySQL transactions, and wonder
if this simultaneous "search-and-hold" would/could be done in a
transaction (of multiple queries) or if possible inside just one
query? Either way, how would I do this "search-and-change" of data?
(i.e. changing fields in the rows that are returned by the query.)
QUESTION 1b. So the seats have been put on hold. But by putting seats
on hold, the user may leave them - by either going over their two
minutes, or just navigating away, closing their browser, or even the
browser/computer crashing etc. So I can't set a seat on hold as a
binary yes/no flag, which may never get cleared if the user goes away
abruptly.
I also can't lock a table for two minutes, which is way too long, and
I can't have an automated script scanning all the time through all
tickets to see if they were on hold, but were never taken, and make
them available again.
So I was thinking, place the seats on hold by attaching to them a time
two minutes into the future. During the next two minutes, these "on
hold" seats are passed over by other user's searches because the
current time is still before the "hold-until-time" - but if they never
get purchased, then they become available again after two minutes.
What do people think of this, and what methods have you used in
similar situations?
And so, if the user likes (and then pays for) this set of seats, I can
just go through all the seats and mark them as permanently taken.
QUESTION 2. While I'm here asking time-related questions, how can I
determine in a MySQL query if a certain time falls between two others,
because of the dilemma arising due to the circular nature of times
i.e. any time can occur both inside AND outside of two times. I want
rows to be selected, say when a certain event happens between 9:00 PM
and 6:00 AM, and not sure if something like the following would work
in all cases:
SELECT stuff from table WHERE ($time > start_time) AND ($time <
end_time)
Thanks in advance!
Simon.