How to set exclusive transaction in mysql?

Question

MySQL

A small example, not a real situation, but just to understand the task:
File test1.php

mysqli_query($connect, 'START TRANSACTION');
mysqli_query($connect, 'SELECT @var:=value FROM userinfo WHERE id=1');
sleep(10);
mysqli_query($connect, 'UPDATE userinfo SET [email protected] + 1 WHERE id=1');
mysqli_query($connect, 'COMMIT');

File test2.php

mysqli_query($connect, 'START TRANSACTION');
mysqli_query($connect, 'SELECT @var:=value FROM userinfo WHERE id=1');
mysqli_query($connect, 'UPDATE userinfo SET [email protected] + 1 WHERE id=1');
mysqli_query($connect, 'COMMIT');

There are two transactions. The bottom line is that they must read the value of the field (value), increase it by 1 and write the result back to this field. The problem is that these transactions do not block each other, but are executed in parallel. In the first file (test1.php), between reading and writing a value, there is a sleep(10) function that makes the program wait 10 seconds before performing the next operations. If you first run the test1.php file, and then immediately run test2.php, then as a result the value field will increase by only one. This is logical, because the first file will first read the value and then sleep for 10 seconds, while the second file will also read this value and immediately increase it by 1. After 10 seconds. wakes up the first file that only knows the old value and knows nothing about the new one. Increase it by 1 and update the data. That is, as a result of the execution of both files, the value will increase by 1, and not by 2. I need the transactions to be executed one by one. That is, until the first is executed, the second will not be able to access the lines used in the first transaction. How can the standard behavior be changed so that transactions are executed sequentially one after another. I cannot change the isolation level of the transaction (the hoster does not allow it). Yes, and this would not solve the problem, because even with the SERIALIZABLE level, the second file will stupidly give an error that the records are busy and stop its execution, and will not queue up, as I need. How can the standard behavior be changed so that transactions are executed sequentially one after another. I cannot change the isolation level of the transaction (the hoster does not allow it). Yes, and this would not solve the problem, because even with the SERIALIZABLE level, the second file will stupidly give an error that the records are busy and stop its execution, and will not queue up, as I need. How can the standard behavior be changed so that transactions are executed sequentially one after another. I cannot change the isolation level of the transaction (the hoster does not allow it). Yes, and this would not solve the problem, because even with the SERIALIZABLE level, the second file will stupidly give an error that the records are busy and stop its execution, and will not queue up, as I need.

Answer 1

You already had a question on this topic, you were answered quite clearly that you need to use mutexes.