I tried to read about the lock in MySQL, but the topic still remains completely incomprehensible.

The essence of the problem:

Table with the following columns: id - Index, Primary, Autoincrement; id_order - Order Id; name - the name of the ordering person

The bottom line is that id increases without my intervention, but I have to prescribe the next id_order myself.

Procedure:

  1. Take the last id_order
  2. We perform calculations in PHP.
  3. Add to the table a row with id_order , equal to either the last + 1 or 0.

The bottom line is that with 2 simultaneous orders, you get 2 new entries with the same id_order .

As I understand it, SELECT ... FOR UPDATE will not help me here, as it will unlock the last record immediately after it receives the order_id.

Ie, in theory, I need to lock the record with the last id_order , perform all the actions I need, and then unlock it.

I can't figure out how to do this.

Please no suggestions for changing the table structure. Yes, crookedly done initially.

  • Given that you have an insert, not an update, then of course select for update will not help. I am afraid only complete blocking of the table. Or it can revise the logic of the php code and perform all actions with a single SQL query - Mike
  • Choose what you like best. The first is that one user has the procedure to lock the record with the last id_order, perform all the actions I need, and then unlock it is not over, the rest are refused. Or secondly, the appearance of “holes” in the numbering by this field is potentially possible, but no one is waiting for anyone, and then you can close the holes. - Akina
  • Holes are unacceptable. Failure, too, is an online store, after all. - user64675
  • And what, there is no separate team to first lock. And then separately unlock? - user64675
  • You would describe the current logic in php. when 0 is taken when next id In 90% of cases, all logic is quietly written in one request. If, of course, the decision does not depend on any external data that is obtained only after select from the database - Mike

1 answer 1

Since in fact you do not need to block reading before the appearance of a new record, but only need to block the competitive reading of the last id_order to increment the counter, then select .. for update will be enough.

 begin; select id_order from tablename where /**/ order by id_order limit 1 for update insert ... commit;

A lock on the for update line (as well as other transactional locks) is taken only for the duration of the entire transaction and is released upon commit or rollback.

Competitive select id_order .. for update changing the table will see and return after the commit of that transaction a new id_order

Or you can go to optimistic locks. Hang a unique index on id_order , get id_order without blocking, make +1 and try to write a new line. If the DBMS has said the duplicate key, go back to the id_order reading step and repeat. Of course, it will work disgustingly with high competitiveness. But if conflicts are extremely rare and are considered as an exception to the rule, then it will work.

  • Stupid question - can I split a transaction from a PHP point of view? Those. Sanachala begin and select for update, then miscalculations in PHP and as the second insert and commit request? - user64675
  • Hanging a unique index on id_order impossible. id already unique - user64675
  • @ user64675 There is only one primary key in the table. But there can be as many unique indices as possible - Mike
  • The variant with begin..commit seems to me the most successful. The question is whether it can be divided into 2 requests (from the point of view of PHP). Otherwise there is no point in this - user64675
  • On the PHP side for transaction management, there are generally separate API methods PDO::beginTransaction , PDO::commit and PDO::rollBack . Between - any number of any requests on this connection will be in this transaction. - Shallow