CODEWITHSUNDEEP
phpmysql
chongzixin
chongzixin

Reputation: 1981

MySQL InnoDB insert and select lock

I created a ticketing system that in its simplest form just records a user joining the queue, and prints out a ticket with the queue number.

When the user presses for a ticket, the following happens in the database

INSERT details INTO All_Transactions_Table
SELECT COUNT(*) as ticketNum FROM All_Transactions_Table WHERE date is TODAY

This serves me well in most cases. However, I recently started to see some duplicate ticket numbers. I cant seem to replicate the issue even after running the web service multiple times myself.

My guess of how it could happen is that in some scenarios the INSERT happened only AFTER the SELECT COUNT. But this is an InnoDB table and I am not using INSERT DELAYED. Does InnoDB have any of such implicit mechanisms?

Upvotes: 0

Views: 87

Answers (1)

Austin
Austin

Reputation: 3318

I think your problem is that you have a race condition. Imagine that you have two people that come in to get tickets. Here's person one:

INSERT details INTO All_Transactions_Table

Then, before the SELECT COUNT(*) can happen, person two comes along and does:

INSERT details INTO All_Transactions_Table

Now both users get the same ticket number. This can be very hard to replicate using your existing code because it depends on the exact scheduling of threads withing MySQL which is totally beyond your control.

The best solution to this would be to use some kind of AUTO_INCREMENT column to provide the ticket number, but failing that, you can probably use transactions to achieve what you want:

START TRANSACTION
SELECT COUNT(*) + 1 as ticketNum FROM All_Transactions_Table WHERE date is TODAY FOR UPDATE
INSERT details INTO All_Transactions_Table
COMMIT

However, whether or not this works will depend on what transaction isolation level you have set, and it will not be very efficient.

Upvotes: 1

Related Questions