Reputation: 33
Big problem...
I'm implementing an online ticket sale system in PHP and MySQL. I have a table called "block_of_tickets", or something like that...
This table looks like:
+-----------+------------+--------------+--------------+--------------+ | idblock | block_name | total_tickets| block_gender | idblock_pair | +-----------+------------+--------------+--------------+--------------+ | 1 | Block 1- M | 100 | MALE | 2 | +-----------+------------+--------------+--------------+--------------+ | 2 | Block 1- F | 100 | FEMALE | 1 | +-----------+------------+--------------+--------------+--------------+
Where:
Note: There are also other columns, like "price", etc.
Here is the (big) problem:
When there is a "idblock_pair", it means that both block of tickets will share the same total_tickets (available tickets), so both cells must have exactly the same value in this case. As you can see in the example above, block 1 points to block 2 and vice-versa.
Lots of people buy lots of tickets in (almost) the same time, which means that each sold ticket must decrement 1 in the "total_tickets" field, for both cells.
Database Normalization can solve this. However, it would lose a lot in performance.
I'm almost sure that I should use "SELECT... FOR UPDATE"... but I don't know how, since it's the same table, and a "deadlock" can occur...
How to solve this problem? Do I have to use Triggers? Procedures? Do I have to use the PHP processing (and transactions) to solve this?
In the example below, one ticket were sold, and now I'm decrementing the total_tickets by 1:
START TRANSACTION; SELECT * FROM block_of_tickets WHERE idblock in (1,2) FOR UPDATE; UPDATE block_of_tickets SET total_tickets = (total_tickets - 1) WHERE idblock in (1,2); COMMIT;
Is this a nice solution?
Upvotes: 0
Views: 68