MySQL atomic operations and table locking

Question

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!

Answer 1

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

1. lock the event row.
2. read the available tickets column. (abort if not enough are available)
3. insert a row in the escrow table
4. update the event row to decrement the available tickets column.
5. unlock the event row.

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:

1. lock the corresponding event row.
2. read the number of escrowed tickets from the escrow table
3. delete the escrow table row.
4. update the event row to increment the available tickets column.
5. unlock the event row.

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.