I'm confused, with levels of locks, isolation. I have MySQL 5.7, InnoDB engine.

Here are some scenarios:

1) From the table can read (select) several threads at the same time, the same rows

2) From the table can read (select) several threads at the same time, different lines

3) From the table, they can read (select), make certain calculations, and then overwrite (update) several threads at the same time, but each stream has an individual row

4) From the table they can read (select), make certain calculations, and then overwrite (update), several streams simultaneously, one and the same line

5) From the table they can read (select), make certain calculations, and then overwrite (update), several threads simultaneously, the entire table

6) From the table can read (select) range, add (insert) a line, several threads simultaneously

What blockings should I arrange? I do all the requests separately, that is, I read, then the calculations (the calculations take almost no time 0.001-0.002 sec), then I update with another request

  • there are no problems with simple readings. First, ask yourself what the readers should see or should not see if at this time you are going to change the line. usually the reader sees the previous state, and then they see the new state does not cause any problems. problems can be only if someone read the line, and then, based on the data updates it. Here for these rare cases there is select for update . In general, it is impossible to answer your question. Explicit locks are needed extremely rarely and a decision can be made only in each specific case - Mike
  • there is a balance, the player during the game changes it (reads / counts / writes), then the operator decided to withdraw credits: I read the balance in my variable, check that the balance was more than the withdrawal amount, reduce the balance, save to the table, how to make the player not intervene In the process and the operator waited until the player finishes his? - ArtGrek13
  • And changes in the balance of a particular player are frequent? - Mike
  • one
    This is one request. instead of if condition directly in where balance-10>=0 . select is not needed, because update itself will receive the current value and change it when balance=balance-10 is written. If the resulting balance under this condition in where is less than zero, the query will not find the appropriate rows and will not change anything. checking the number of updated lines you will see 0 and the litter did not have enough money (because there are no errors) - Mike
  • one
    Now I’ve checked it and I didn’t like it ... First you need to start the begin transaction. The lock then lives until you give a commit or rollback. (do not forget to disconnect autocommit on the connection after each operation). Opened two windows with MySQL. in one I give begin and select for update. in another: just select works. select for update hangs and waits until the first window completes the transaction. But I didn’t like the fact that reading for update of another record in the table also hangs, because the index is blocked and something tells me that this is not treated in MySQL - Mike

0