Как решить проблему с обновлением строки, которая заблокирована с помощью SELECT FOR UPDATE в MySQL 8?

Question

Ричард

mysql , php , yii

Как решить проблему с обновлением строки, которая заблокирована с помощью SELECT FOR UPDATE в MySQL 8?

Всем привет! У меня возникла проблема с блокировкой записи в базе данных. Я использую MySQL 8.0.35 с уровнем изоляции транзакций по умолчанию. У меня есть консольный воркер на PHP 8.2 (Yii2), который запускает транзакцию и выполняет команду SELECT FOR UPDATE для блокировки определенной строки таблицы. Вот пример кода:

```sql
start transaction;
select * from Table where <conditions> for update; // Блокируем строку
... // Выполняем операции на PHP
update Table set `balance`=0 where id=XXX // Обновляем заблокированную строку по её id
commit;
```

На первый взгляд, все достаточно просто, но я сталкиваюсь с ситуациями в продакшене, когда эту же строку пытается изменить сторонний сервис. У меня нет доступа к этому сервису, и я не знаю, как он работает, но он иногда успешно изменяет строку, после чего при выполнении моего commit транзакции новое значение затирается.

Чтобы воспроизвести ситуацию локально, я использовал конструкцию SELECT SLEEP(100) внутри транзакции перед её завершением, и пытался обновить ту же строку из консоли, в то время как SLEEP() висел — в результате получил ожидаемую ошибку из-за блокировки. Также вставлял sleep() на этапе PHP перед вызовом $transaction->commit() и запускал обновление строки из другого PHP скрипта — тоже успешно получал сообщение о заблокированной строке.

Обновление записи воркером происходит внутри метода ActiveRecord::afterSave(). Я подозревал, что afterSave() может создавать свою вложенную транзакцию, но во время локального дебага было подтверждено, что всё выполняется в рамках одной транзакции.

Не могу понять, почему происходит такое изменение данных. Какие условия могут привести к перезаписи? Буду признателен за любые советы или направления, куда можно обратить внимание!

10 ноября 2024 г. 13:53

Ответы на вопрос 1

Похожие вопросы

Answer 1

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

Вот несколько возможных причин и направлений для решения:

1. **Дублирование транзакций**: Убедитесь, что никакой другой код не выполняет обновление той же строки одновременно. Проверьте, что другие транзакции не запускаются на вашем стороне, которые могут создавать свои собственные блокировки или проблемы. Убедитесь, что у вас нет места, где несколько отдельных транзакций могут взаимодействовать с одной и той же записью.

2. **Параметры уровня изоляции**: В вашем случае уровень изоляции по умолчанию, а именно `REPEATABLE READ`, это нормальный выбор для работы с блокировками. Однако в некоторых случаях может потребоваться использовать более строгий уровень изоляции, такой как `SERIALIZABLE`. Это может помочь предотвратить ситуацию гонки.

3. **После обновления данных другим процессом**: Если другой процесс действительно модифицирует строку после вашего блока `SELECT FOR UPDATE`, он может сделать это до вашего `commit()`, если он завершает свою транзакцию до того, как ваша транзакция достигает `commit()`. Обратите внимание на порядок операций.

4. **Убедитесь, что lock-ы не "падают"**: Проверьте, возможно ли, что блокировка на строке снята перед завершением вашей транзакции. Это может произойти, если в процессе выполнения вашей транзакции происходит какой-то неявный `commit` или `rollback`.

5. **Возможность вложенных транзакций**: Если в `afterSave` у вас выполняются операции, которые открывают свои собственные транзакции, убедитесь, что они не вмешиваются в вашу основную транзакцию. В yii2, если вы используете стандартные методы ActiveRecord, вложенные транзакции могут повлиять на ваш код. Попробуйте временно отключить `afterSave` или проверить, вызывается ли он несколько раз.

6. **Логи трассировки и отладка**: Включите более детальные логи для проверки блокировки, чтобы убедиться, что вы понимаете, какие операции выполняются на уровне базы данных. Используйте команды для просмотра активных транзакций и блокировок, например, `SHOW ENGINE INNODB STATUS`.

7. **Использование `EXECUTE` для избежать проблем с `afterSave`**: Попробуйте выполнить `UPDATE` через `EXECUTE` в контексте одной и той же транзакции, чтобы избежать потенциального взаимодействия с триггерами или состоянием гонки, если такие операции происходят.

Если после всех проверок проблема сохраняется, вы можете рассмотреть использование механизма контроля версий (например, хранить текущую версию записи и проверять её перед обновлением) для явного предотвращения перезаписи данных. 

Также, если у вас есть возможность, попробуйте провести анализ и улучшить логику работы стороннего сервиса, который взаимодействует с вашей базой данных, если это возможно.