Carlos Souza
Carlos Souza

Reputation: 33

How to synchronize cell values in the same table?

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 represent 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 which 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, 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

Answers (0)

Related Questions