There is one script. It runs several times, often several instances of the script are executed at the same time. It is necessary for the script to take the line in which in_use = 0, put in_use = 1 to it, process it and set it back in_use = 0. Naturally, no other instance of the script should get the same string. That is, it does not work out that the same line is processed simultaneously.

Task:

  1. Lock the table so that no one can read and write from it, in general
  2. Select a row from this table, and set this field to in_use = 1, set the lock flag
  3. Unlock

Tried to do a WRITE lock, but other threads can read the table. I made a READ lock, then I cannot write to the table. How to do both - I do not know.

PS There is a table for example tasks. Each copy of the script should take a free task, mark what it took, process it, and remove all locks. And at the moment when the script takes the task, no other should overtake him. That is, at the moment of SELECT, it is necessary that other processes wait for UNLOCK TABLES

$pdo->exec('LOCK TABLES links WRITE'); $sql = " SELECT hash FROM links WHERE in_use!=1 AND site=:site AND ( parsed is null OR DATEDIFF(CURDATE(),parsed)>=:revisit ) LIMIT 1 FOR UPDATE "; $statement = $pdo->prepare($sql); $statement->execute( [ ':site' => $site['name'], ':revisit' => static::$revisit, ] ); if ($statement instanceof PDOStatement && $statement->rowCount() == 1) { $hash = $statement->fetchAll(); $hash = $hash[0]['hash']; $pdo->exec("UPDATE links set in_use=1 where hash='" . $hash . "'"); } else { $hash = false; } $pdo->exec('UNLOCK TABLES'); return $hash;

The code reads from the table Primari Key, puts the line in_use = 1. In another method, it is processed and after that put in_use = 0

2 answers 2

I have not worked with SQL for a long time and I can be mistaken, but in general the task looks like this:

  1. (Optional) The running process reads the value in the table. If he sees that in_use == 1 , he immediately terminates the work.
  2. The process executes the query UPDATE <table> SET in_use = true WHERE id = <id> AND in_use = false . Here, perhaps, it is necessary to make clarifications on ISOLATION LEVEL, but, as far as I understand, two processes cannot update the same record at any isolation level (correct if this is not the case).
  3. The process analyzes the number of updated rows. 1 - lock successfully captured, 0 - someone had time before.
  4. The process removes the lock in the same way (it is better if it also uses a query with the condition in_use = true , so as not to remove someone else's lock, if suddenly for some reason the block was removed manually; you can also add the owner field, to write a random string to the process could only remove its block).

Ideally, you also need to add a certain date, which will allow intercepting the blocking of the deceased process (after a rationally long time, for example, a day), in order to avoid unpleasant cases of endless blocking.

You can see the perfect case for implementing such a lock on cassandra, the mentioned timestamp turns there during the recording life.

  • In this case, in_use = 1 will be illogical in this case. I will explain my task. There is a table for example tasks. Each copy of the script should take a free task, mark what it took, process it, and remove all locks. And at the moment when the script takes the task, no other should overtake him - s4urp8n
  • @ s4urp8n he cannot get ahead in my sentence, the one who is ahead of him gets the number of updated lines = 1, any other gets the number of updated lines = 0. - etki
  • Added code to the question - s4urp8n
  • @ s4urp8n Combine SELECT and UPDATE into one query, this will eliminate the situation when another query executes the same SELECT between the SELECT and UPDATE. All problems are no more. :) - Yaant
  • @Yaant if you mean to send two requests in a row in one package, then the guarantees you describe are unlikely to exist. - etki

After a lot of digging, the solution was simple. To provide the necessary locks and atomicity, it was enough to make queries in a transaction:

  1. We make SELECT hash ... WHERE in_use=0 ... FOR UPDATE block strings for writing and reading
  2. fetchAll() - get data in php (now we have $ hash)
  3. If there is data, then we do UPDATE ... SET in_use=1 ... WHERE hash=:hash (We substitute our $ hash here)
  4. commit() transaction