Avoid a concurrent insert


I have a problem on my website. It turns out that I have a packs table which has a availability column. A pack will be available as long as the availability is > 0 . A user can reserve an available pack, upon payment with Stripe.

This form will make an INSERT in the database in the reservations table, checking before there are available packs .

When doing an INSERT I have a trigger that performs an UPDATE on the packs table by subtracting the < strong> availability in 1 unit.

However, when booking a pack, if you enter N requests at the same time, N INSERT , these will trigger N UPDATES and availability will end up being -N values. As many as reservations have entered at the same time.

The question is: How can I avoid an INSERT being done concurrently? That is to say, when a petition arrives, do not enter any more.

I am interested in doing it for PHP code since I want to avoid that the payment is made by the client in case the availability of the pack is 0. p>

Thank you.

asked by Valdemore 03.04.2018 в 23:09

2 answers


What you need is a LOCK. This is an example taken from the documentation:

LOCK TABLES trans READ, customer WRITE;
SELECT SUM(value) FROM trans WHERE customer_id=some_id;
UPDATE customer
  SET total_value=sum_from_previous_statement
  WHERE customer_id=some_id;
answered by 04.04.2018 / 00:11

Under my point of view you should only show available packs to the client in case there is already no show (hide them), another point would be that before doing the insert first make a select that is, first check if the pack or packs are available only there, send them to make an insert and then only collect them there !! that is, you would have 3 methods:

  • check if there are available packs
  • insert the pack or pack selected by the client
  • charge the packs that would be an option from my point of view !! luck !!
answered by 03.04.2018 в 23:29