I have a website where users can purchase tickets, but ticket quantities are usually limited and go quickly. I'm trying to implement an escrow system such that a user can click that they want x number of tickets, at which point I will put them in an escrow state. That gives them several minutes to enter their credit card information and complete the purchase.
I have three relevant tables: events, tickets, and escrow. A row in the events table describes the event itself, including the maximum number of tickets available.
The tickets table holds the following:
user_id: the user that purchased the tickets
number_of_tickets: how many tickets they purchased
event_id: the relevant event
The escrow table holds the following:
user_id: the user in the process of purchasing tickets
number_of_tickets: how many tickets they want
event_id: the relevant event
Currently, I do three MySQL queries, one for the max tickets, one for the number of tickets sold, and one for the number of tickets already in escrow. Then I calculate:
$remaining_tickets = $max_tickets - $tickets_sold - $tickets_in_escrow;
if ($remaining_tickets >= $tickets_desired)
{
beginEscrow($user_id, $event_id, $tickets_desired);
}
else
{
echo "Error: not enough ticket remain.";
}
My problem is that it is possible for more than one user to be executing this code at the same time. If one user were to call beginEscrow
after another user had already read the number of tickets already in escrow, it's possible that I'll oversell the show.
I'm using the InnoDB engine for my tables, and I've read how to lock a single row by using SELECT .... FOR UPDATE
, but I'm not updating a single row. The beginEscrow
function will just insert a new row into the escrow table. I calculate $tickets_in_escrow
by reading all rows with the correct event id and adding up the number of tickets in each of them.
Maybe I'm going about it all wrong?
Do I need to lock the entire table?
I can't be the first one to write a ticket escrow system. I've googled myself to death trying to find some tutorial on this kind of thing, but struck out. Any ideas would be helpful.
Thanks!