Блокировка записи MySQL для обновления

В ситуации, когда несколько пользователей одновременно пытаются изменить одну и ту же запись в базе данных, возникает проблема, известная как “гонка условий” или “race condition”.

Одним из способов управления этой ситуацией является использование “блокировок”. Блокировка позволяет ограничить доступ к определенной записи базы данных на время ее изменения.

Существуют два основных типа блокировок:

Общие блокировки (shared locks), которые позволяют многим транзакциям читать (но не изменять) одну и ту же запись.
Эксклюзивные блокировки (exclusive locks), которые позволяют только одной транзакции изменять запись.

В MySQL вы можете использовать оператор SELECT … FOR UPDATE, чтобы установить эксклюзивную блокировку на записи, которые вы собираетесь обновить. Это гарантирует, что другие транзакции не смогут изменять эти записи до тех пор, пока ваша транзакция не завершится.

Вот пример использования блокировки в PHP с MySQLi:

$mysqli = new mysqli("localhost", "my_user", "my_password", "world");

/* проверка соединения */
if ($mysqli->connect_errno) {
    printf("Не удалось подключиться: %s\n", $mysqli->connect_error);
    exit();
}

$mysqli->query("START TRANSACTION");

$query = "SELECT * FROM myTable WHERE id = 1 FOR UPDATE";
$mysqli->query($query);

$query = "UPDATE myTable SET myField = 'newValue' WHERE id = 1";
$result = $mysqli->query($query);

if(!$result){
    /* Если произошла ошибка, отменяем транзакцию */
    $mysqli->query("ROLLBACK");
} else {
    /* Если все в порядке, подтверждаем изменения */
    $mysqli->query("COMMIT");
}

$mysqli->close();

$mysqli = new mysqli("localhost", "my_user", "my_password", "world");

/* проверка соединения */

if ($mysqli->connect_errno) {

printf("Не удалось подключиться: %s\n", $mysqli->connect_error);

exit();

}

$mysqli->query("START TRANSACTION");

$query = "SELECT * FROM myTable WHERE id = 1 FOR UPDATE";

$mysqli->query($query);

$query = "UPDATE myTable SET myField = 'newValue' WHERE id = 1";

$result = $mysqli->query($query);

if(!$result){

/* Если произошла ошибка, отменяем транзакцию */

$mysqli->query("ROLLBACK");

} else {

/* Если все в порядке, подтверждаем изменения */

$mysqli->query("COMMIT");

}

$mysqli->close();

В этом примере мы начинаем транзакцию, затем выбираем запись, которую хотим обновить, и блокируем ее. Затем мы выполняем обновление. Если все прошло успешно, мы подтверждаем транзакцию и снимаем блокировку. Если произошла ошибка, мы откатываем транзакцию, что также снимает блокировку.

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

Еще одним важным моментом является то, что использование блокировок может привести к “взаимной блокировке” или “deadlock”, когда две транзакции блокируют друг друга и ни одна из них не может продолжить выполнение. MySQL автоматически обнаруживает такие ситуации и откатывает одну из транзакций, чтобы разрешить взаимную блокировку.

Также можно рассмотреть использование оптимистического контроля конкуренции (Optimistic Concurrency Control, OCC), который предполагает, что конфликты между транзакциями редки, и поэтому вместо блокировки данных перед началом транзакции система может проверять, не произошло ли изменений в данных во время выполнения транзакции перед ее завершением. Это обычно достигается путем добавления к каждой записи поля с версией или временем последнего обновления и проверки этого значения перед коммитом транзакции.

Оставьте комментарий Отменить ответ