Hello.

There is a script in which certain actions are performed with fields in the database:

  1. Extract from table A data for row B and field C - the value of D = 0.

  2. For a while something to consider.

  3. new_D = D + 1.

  4. The update to table A, the data for row B and field C are the value of new_D.

If this script is called 10 times at the same time, will the requests in MySQl be queued or will Phase 1 and 4 be executed at stage 2 with other parallel calls?

It does not happen that 10 parallel calls will lead to the fact that in the table there will be D = 1, not 10?

If there is no strictly formed queue for the execution of all script requests, then give info on how to work with this case correctly.

I came across the use of LOCK TABLES, and the thought appeared that the script was a curve ...

Thanks in advance for your help.

  • one
    If you don’t need to calculate the D value, then maybe you should just do it: UPDATE A SET C = C + 1 WHERE B = ... Then after each update the value will increase by one and it doesn’t matter in which order the requests come. - BOPOH
  • Thanks for the answer. I have to, as I wrote, with a delay between 1 and 4. - trans
  • one
    So who bothers to update later? What is the problem? This approach can cause difficulties if you need to update based on current data, but if you just need to increase the counter (as shown in the example), so increase it. Another blocking option - SELECT ... FOR UPDATE - seems to block only the necessary rows, and you can continue working with the rest of the lines (which is better than full blocking through LOCK TABLES ) - BOPOH
  • It is necessary to update on current and constantly changing data, and not just a counter - in the example I made +1 for the sake of simplicity. Answer the question if it’s not difficult: At the beginning of the script, take a value from the table (SELECT) - pause randomly for 3-5 seconds - at the end of the script increased by 1 (UPDATE var = '$ new_var'). If a script is called 10 times in parallel, will the table have a value of 1 or 10? - trans
  • Here, the principle of "who is the last one and dad", i.e. the table will be from 1 to 10. Therefore, you need to keep track of who will be the last. Use ...FOR UPDATE - should help, or come up with a different approach. The main thing is that the rest of the requests were waiting for their turn. - BOPOH

1 answer 1

Transactions?

  • I re-read the transactions and select foe update - everything is crooked in some way, where the tables are locked and unlocked, somewhere, with complex queries, some other surprises come up, with which you need to mess around and deal with. I decided to make a single-value reference table - the status of the script execution. I lock the file at the beginning of the file again through this table, at the end I unlock it. If 50 people break into one file, they are put in pseudo-timing (waiting for 1 sec and the script re-checks the locking table). Maybe the solution is wooden, but he avoided new hemorrhoids with transactions and for update. - trans
  • one
    Oh, and this is how everyone tries to fashion his own bicycle :) - Stanislav Komar
  • 2
    @ Vasily Panferov> in case of complicated requests, some other surprises come out, which you need to tinker with and then deal with. Programming it is - etki