Hello!

Tell me, please, how can I get the value of folder2 in the table and then immediately increment this value by +1. It is important that when a large number of simultaneous requests are received, the values ​​of folder2 do not repeat.

+----+---------+--------+ | id | folder1 | folder2| +----+---------+--------+ | 1 | 235 | 13 | +----+---------+--------+

I would do a php request like this:

  $sql_query1 = "SELECT folder2 from tbl_name"; $sql_query2 = "UPDATE tbl_name SET folder2 = folder2 + 1"

But what if it takes some time between $ sql_query1 and sql_query2, and other users will also run the script. Then some number of users will have a coincidence of folder2 values.

How can this be avoided?

Thank!

  • block the table for writing when someone works with it .. but it will slow down the work in general when there are many users - Volodymyr
  • @Volodymyr Is it possible somehow with one request? Does SQL server execute queries synchronously or asynchronously? Those. it is important to understand all requests are queued. Or does each request work independently of the other? - Pavel
  • There will be no matches. But in the first query, you can get an incorrect value. And why do you need all this? What are you trying to achieve? - Anton Shchyrov
  • @AntonShchyrov, I am trying, but unsuccessfully, to develop a system for cataloging pictures on the server. The user uploads files to the server. For the user, I will create a directory based on the value of folder2. Those. he downloaded the files, I got the value of folder2 and immediately set the value for the next user, etc. - Pavel
  • one
    @Pavel You need a separate table folders with your incremental primary key - Anton Shchyrov 4:52

1 answer 1

You can use the table lock:

 $sql_query1 = "LOCK TABLE folder2 WRITE"; $sql_query2 = "SELECT folder2 from tbl_name"; $sql_query3 = "UPDATE tbl_name SET folder2 = folder2 + 1" $sql_query4 = "UNLOCK TABLES";

In this case, even if a SQL query is launched between query2 and query3 by another user (connection), he will wait until the user (connection) who has installed it is unlocked by a query in query4.

If the table is of the InnoDB type, then you can put a lock on a row, which will be less expensive in terms of resources, but with this question you should better understand more by reading about "InnoDB Transactions".