About transactions in SQL?

Question

MySQL

Hello!
Tell me, please, do I understand correctly that if the following code is run simultaneously (just a millisecond to a millisecond) from several clients, then none of them will work with the same line?

$mysqli = new mysqli("localhost","root",null,"tmp");

$sql = "START TRANSACTION";
$result = $mysqli->query($sql);

$sql = "SELECT * FROM test WHERE disabled = 'N' LIMIT 1 FOR UPDATE";
$result = $mysqli->query($sql);	

$id = mysqli_fetch_assoc($result)['id'];
$sql = "UPDATE test SET disabled = 'Y' WHERE id = $id";
$result = $mysqli->query($sql);	

$sql = "SELECT * FROM test WHERE id = $id";	
$result = $mysqli->query($sql);
var_dump( mysqli_fetch_assoc($result) );

$sql = "COMMIT";
$result = $mysqli->query($sql);	

$mysqli->close();

Answer 1

No, you misunderstand. Read the theory about transaction isolation levels.

Answer 2

Different clients may receive the same row as long as you don't place an exclusive lock on the row in the first request.
This can be done with the WITH (UPDLOCK) hint. The row
will be locked until the transaction is committed or rolled back, and the transaction will most likely lock others because the first SELECT does not use indexes.

Answer 3

Table type =)

Answer 4

If, to eliminate the ambiguity, we assume that there is only one disabled = 'N', or if we add an unambiguous sorting (by PK, for example), then all requests will line up in a queue for the same line.

Answer 5

I tested something else: the code on the gist .
In test.php, you need to set $x to the value of the current microtime(true) + a few seconds and simultaneously run a couple of instances via the command line. Here's what happens:
First instance:

S: -> 1393581800.0007
1: -> 1393581800.0167
2: -> 1393581800.0497
3: -> 1393581800.0507
array(3) {
  ["id"]=>
  string(2) "10"
  ["name"]=>
  string(5) "Sonya"
  ["disabled"]=>
  string(1) "Y"
}

Second (with errors, because there was only 1 element with disabled = 'N'):

S: -> 1393581800.0007
1: -> 1393581800.0497
2: -> 1393581800.0507
string(146) "You have an error in your SQL syntax; check the manual that corresp
onds to your MySQL server version for the right syntax to use near '' at line 1"

3: -> 1393581800.0517
string(146) "You have an error in your SQL syntax; check the manual that corresp
onds to your MySQL server version for the right syntax to use near '' at line 1"

PHP Warning:  mysqli_fetch_assoc() expects parameter 1 to be mysqli_result, bool
ean given in W:\OpenServer\domains\mydomain.local\tmp\test.php on line 41
NULL

1. Start happens at the same time S: -> 1393581800.0007
2. Then the first instance makes a fetch 1: -> 1393581800.0167, and the second one acquires the lock.
3. The first performs UPDATE 2: -> 1393581800.0497and the lock immediately flies. Therefore, the second instance can now perform a SELECT 1: -> 1393581800.0497.
...
In general, it turns out that the whole secret is in FOR UPDATE. You can safely remove the transaction and its isolation level. Even more - if you leave the transaction and isolation, but remove FOR UPDATEthe picture becomes deplorable.
All right? Or have I screwed up somewhere?