Про транзакции в SQL?

Question

[Александр Дементеев]

Всем привет!
Подскажите, пожалуйста, правильно ли я понимаю, что если следующий код будет запущен одновременно (прям миллисекунда в миллисекунду) с нескольких клиентов, то ни один из них не будет работать с одной и той же строкой?

$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

[egor_nullptr]

Нет, вы понимаете неправильно. Читайте теорию об уровнях изоляции транзакций.

Comments

[Александр Дементеев]

Ок, спс. т.е. нужно добавить
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
Верно?

Answer 2

[AdvanTiSS]

Различные клиенты могут получить одну и ту же строку пока вы не наложите exclusive lock на строку в первом запросе.
Это можно сделать посредством хинта WITH (UPDLOCK)
Строка будет заблокирована до коммита или отката транзакции, при этом транзакция вероятней всего будет блокировать другие, поскольку первый SELECT не использует индексов.

Answer 3

[Александр Дементеев]

Типа таблица =)

Answer 4

[Melkij]

Если для устранения неоднозначности предположить, что disabled = 'N' только один, либо добавить однозначную сортировку (по PK, например) - то все запросы выстроятся в очередь за одной и той же строкой.

Answer 5

[Александр Дементеев]

Я еще кое-что протестировал: код на гисте.
В test.php нужно присвоить $x значение текущего microtime(true) + несколько секунд и одновременно запустить пару-тройку экземпляров через командную строку. Вот что получается:

Первый экземпляр:

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"
}

Второй (с ошибками, потому, что был только 1 элемент с 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. Старт происходит одновременно S: -> 1393581800.0007
2. Затем первый экземпляр делает выборку 1: -> 1393581800.0167, а второй получает блокировку.
3. Первый выполняет UPDATE 2: -> 1393581800.0497 и тут же слетает блокировка. Поэтому второй экземпляр может теперь выполнить SELECT 1: -> 1393581800.0497.
...

В общем, получается, что весь секрет в FOR UPDATE. Можно смело убирать транзакцию и уровень ее изоляции. Даже больше - если оставить транзакцию и изоляцию, но убрать FOR UPDATE картина становится плачевной.

Все верно? Или я все-таки где-то лапухнулся?