MySQL Atomic Operations and Table Lock

Question

I have a website where users can buy tickets, but the number of tickets is usually limited and fast. I am trying to implement a escrow system so that the user can click that they need x number of tickets, after which I will put them in a conditional escrow state. This gives them a few minutes to enter their credit card details and complete the purchase.

I have three relevant tables: events, tickets, and escrow. A row in the event table describes the event itself, including the maximum number of tickets available.

The ticket table contains the following:

user_id : user who purchased the tickets

number_of_tickets : how many tickets they purchased

event_id : corresponding event

The escrow table contains the following:

user_id : user in the ticketing process

number_of_tickets : how many tickets do they want

event_id : corresponding event

I am currently making three MySQL queries, one for the maximum tickets, one for the number of tickets sold, and one for the number of tickets already in deposit. 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 multiple instances of the same code can be executed by multiple users at the same time. If one user had to call beginEscrow after another user has already read the number of tickets already in escrow, it is possible that I tried the show.

I use the InnoDB engine for my tables, and I read how to lock a single row using SELECT .... FOR UPDATE , but I am not updating a single row. The beginEscrow function simply inserts a new row into the escrow table. I compute $tickets_in_escrow by reading all the lines with the correct event ID and adding the number of tickets in each of them.

Maybe I'll fix it?

Do I need to lock the entire table?

I cannot be the first to write a escrow system. I searched myself to death, trying to find some kind of textbook on this subject, but crossed out. Any ideas would be helpful.

Thanks!

Answer 1

You are very close to your design, but not quite there.

First of all, your event table should contain the number of tickets still available for your event (in addition to what you want there).

Secondly, your escrow table should have a DATETIME column that indicates when the escrow expires. You must set this value when tickets go into escrow.

Third, the escrow transaction must

• lock the event line.
• read the list of available tickets. (interrupt if this is not enough)
• insert row into escrow table
• update the event line to reduce the available ticket column.
• unlock event string.

Fourth, the end of sale action should delete the escrow line and insert the line of the sold ticket. It's not hard.

Fifth, you need an escrow clearing operation. This is necessary to search for all expired escrow lines (which have an expiration date in the past) and for each of them:

• block the corresponding event line.
• read the number of escort tickets from the escrow table
• delete table row escrow.
• update the event row to increase the available column.
• unlock event string.

The trick is to keep the number of tickets available correctly so that race conditions between users do not resell your event.