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!
You are very close on your design, but not quite there.
First of all, your event table needs to hold the number of tickets still available for your event (in addition to whatever else you want there).
Second, your escrow table needs to have a DATETIME column indicating when the escrow expires. You need to set that value whenever tickets go into escrow.
Third, the transaction of putting tickets in escrow needs to
Fourth, the action of completing the sale needs to delete the escrow row and insert a sold-ticket row. This isn't hard.
Fifth, you need an escrow cleanup operation. This needs to look for all escrow rows that have expired (that have an expiration date in the past) and, for each one:
The trick is to have the number of available tickets maintained in a way that is interlocked correctly, so race conditions between users don't oversell your event.