This is a simplified description of the performance, but .. There is a table with a list of goods. It is necessary one by one to find the goods corresponding to a certain condition, to perform operation A on it and to write 'A' in the operation_done column. After that, calculate all products with operation_done = 'A' and carry out operation B. The problem is that the scripts executing the operations A and B are started by the crown and an overlay may occur. How to make so that operation B "waited" access to the table, while operation A is performed?

PDO beginTransaction () I do not think that solves the problem.

  • And why do you think that the transaction will not be the way out? - cheops
  • In my case, for each following operation A, a SELECT is done .... WHERE operation_done! = 'A'. That is, I need to commit a transaction every time and as a result, the meaning of the transaction disappears - phpcoding
  • Ideally, there would be such an algorithm: 1) in script A at the beginning of the lock, table 2) perform operation A 3) unlock table 4) the same with operation B. - phpcoding
  • UPDATE TABLE ... WHERE operation_done = 'A' - not an exit? Or do you need to set operation_done = 'A' for all records and only then start operation B? - Vladimir Martyanov
  • Yes, yes, only after this operation B to run. Or vice versa, operation A - phpcoding

2 answers 2

This is the usual task of synchronizing the work of two threads. Usually solved by creating some kind of "entity" that is available to both threads.

In your case, one of the options will be to create an additional table with one record which will be changed by process “A” at the beginning of its work and at the end. And the process "B" will check the status of this "flag"

You can also add some status field to your existing table. The process "A" before starting work puts in the field the value of "start" - the usual UPDATE by the very condition with which it makes the sample. When processing a recording, the process "A" writes the status "stop" in this field.

The process "B" in turn checks if there is a status field in the table with the value "start". If there is such a field, then process "A" has not yet completed its work, and process "B" must be waited.

  • And if you use LOCK TABLES? - phpcoding
  • @phpcoding can and lock. It all depends on your task. I gave a more universal solution. It will work if the processing by process "A" is started not in one time, but several. And allows the rest of the scripts to work with the database without any problems. In the case of LOCK - you will stop all the rest of the work with this database. - newman
  • Will work with the base or with the specific table (s) stop, which is volatile? - phpcoding
  • @phpcoding locked table. Respectively work only with the table will stop. Read the documentation - everything is written there. - newman
  • @phpcoding Since it is not written in this answer. because stream B can start working in the middle between checking the state of a flag with stream A and writing a new flag value. We need to use the lock table or lock individual records (for innodb) mysql.ru/docs/man/InnoDB_locking_reads.html - Mike

If you really need to ban other actions at the time of the operation, use a table lock or a named lock. This is their purpose.

I recommend named lok. Since you do not need to suspend all work, but only specific operations. So let them "fight" for one name.