0

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:

  1. idblock: The id (primary key) of the block of tickets.
  2. block_name: The name of the block. In the example I have a "Block 1- M" and "Block 1- F" to represente the "Block 1 - Male" and "Block 1 - Female", respectively.
  3. total_tickets: the total of available tickets
  4. block_gender: the gender of the block of tickets
  5. idblock_pair: the block wich is pair of the current block.

Note: There are also other columns, like "price", etc.

Here is the (big) problem:

  1. 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.

  2. Lots of people buy lots of tickets in (almost) the same time, wich 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? Proccedures? 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?

Lightness Races in Orbit
  • 378,754
  • 76
  • 643
  • 1,055
Carlos Souza
  • 33
  • 4
  • 1
    If I understand, then why not the simple update `UPDATE block_of_tickets SET total_tickets = total_tickets-1 WHERE idblock = solded_ticket_id OR idblock_pair = solded_ticket_id` ? – Autista_z Apr 19 '17 at 07:14
  • "**idblock_pair**" or "**idblock**" will never be the "**solded_ticket_id**" ... The solded tickets are stored in another table, with the "**idblock**" and "**iduser**" as the foreign keys. The problem is the database concurrency - many users disputing the same resource. If two people buy two tickets in the same time, it some situations it can result in a database inconsistency between the two cells. – Carlos Souza Apr 19 '17 at 07:37

0 Answers0