Уровень изолированности REPEATABLE READ. Почему так происходит?

Question

[Артём]

Кратко.
1. Обнаружил в проекте несогласованность данных
2. Сделал транзакции с уровнем изолированности REPEATABLE READ
3. Проблема не решилась

Теперь немного подробнее.
Классическая задача из главы по Транзакциям, есть магазин, куда пользователь выставляет свой товар. Контрагент покупает выставленный товар.
Почему-то возникает ситуация, при которой уже купленный товар, либо снятый с продажи, покупает еще один пользователь.

Почему так происходит?
Может ли, на это как то влиять, подготовленные запросы?
Использую расширение pdo

Отмена лота

<?php
// shop.php пример
...
$pdo->begintransaction();

if (isset($remove)) 
{
  $que = "SELECT `id` FROM `shops` WHERE `product_id` = ? AND `status` = ''";
  $pre = $pdo->prepare($que);
  $pre->execute([$product]);
  $res = $pre->fetchColumn();

  if ($res)
  {
    $que = "UPDATE `shops` SET `status` = 'Отозвано' WHERE `id` = $res";
    $pdo->exec($que);
  }
}

$pdo->commit();
...
?>

Покупка лота

<?php
// buy.php пример
...
$pdo->begintransaction();
if (isset($product))
{
  $que = "SELECT `id` FROM `shops` WHERE `product_id` = ? AND `status` = ''";
  $pre = $pdo->prepare($que);
  $pre->execute([$product]);
  $res = $pre->fetchColumn();

  if ($res)
  {
    $que = "UPDATE `shops` SET `status` = 'Продано' WHERE `id` = $res";
    $pdo->exec($que);
  }
}
$pdo->commit();
...
?>

Может пригодится

Сервер: Localhost via UNIX socket
Версия сервера: 8.0.23-0ubuntu0.20.04.1 - (Ubuntu)
Кодировка сервера: UTF-8 Unicode (utf8mb4)

nginx/1.18.0
Версия клиента базы данных: libmysql - mysqlnd 7.4.3
Версия PHP: 7.4.3

Answer 1

[Артём]

Как оказалось SELECT вовсе не блокирует запись, и она может быть изменена другой транзакцией.

Решение мне подсказали следующее:
1. Обновить запись с нужным ID и статусом
2. Проверить affected rows

Как то так:

<?php
// buy.php пример
...
$pdo->begintransaction();
if (isset($product))
{
	$que = "UPDATE `shops` SET `status` = 'Продано' WHERE `id` = ? AND `status` = ''";
	$pre = $pdo->prepare($que);
    $pre->execute([$product]);

	if ($pre->rowCount())
	{
		// Товар куплен
	}

	else 
	{
		// Статус был изменен другой транзакцией, либо това не найден
	}
}
$pdo->commit();
...
?>

Comments

[Ромзес Панагиотис]

https://dev.mysql.com/doc/refman/8.0/en/innodb-loc...

Answer 2

[Владимир]

REPEATABLE READ и так в mysql по умолчанию так что у вас скорее всего ничего не поменялось
возможно вам стоит использовать READ COMMITTED
без кода самих транзакций сказать сложно почему так происходит

Comments

[Артём]

Не понимаю, что вам даст, формальный пример, моей транзакции, но так и быть дополнил вопрос.

[Владимир]

Артём, эээ вы вобще про mysql транзакции читали ? ваша реализация не будет работать.
у вас покупка должна происходить одной транзакцией в которой проверяется наличие и дальше выполняется апдейт.
поэтому гуглите "mysql transaction" и читайте документацию.

[Артём]

Владимир, в моём примере я так и делаю, разве нет?

[Владимир]

Артём, Вы правы я плохо смотрел (тк как пхп и мне было лень вчитываться
pdo реализует нужные вещи но есть нюанс в вашем случае нужно блокирывать строку с продуктом в которую вы будете вносить изменения, что бы другая транзакция её не обновила. у вас какраз происходит то что описанно в документации:
If you query data and then insert or update related data within the same transaction, the regular SELECT statement does not give enough protection. Other transactions can update or delete the same rows you just queried.
взято отсюда - https://dev.mysql.com/doc/refman/8.0/en/innodb-loc... обычно применяют select for update

[Артём]

Владимир, SELECT FOR UPDATE в простеньком примере выше прокатит, в моем же случае он окажется очень прожорливым. Я выбрал более подходящее решение.