Вопрос или проблема
Я хочу заблокировать определенную строку в MySQL (InnoDB), так чтобы эта строка не могла быть прочитана другим соединением.
начать транзакцию;
Выбрать яблоки из фруктов, где яблоки="золотые";
зафиксировать;
Теперь я хочу применить эксклюзивную блокировку
эксклюзивная блокировка
Тип блокировки, который предотвращает любую другую транзакцию от блокировки той же строки.
https://dev.mysql.com/doc/refman/5.5/en/innodb-locking.html#innodb-shared-exclusive-locks
Я предполагаю, что блокировка применяется, тогда второе соединение, пытающееся получить доступ к той же строке, должно ждать, пока первое соединение освободит блокировку с помощью “commit;”
Мой вопрос: как мне настроить мой SQL-запрос, чтобы применить эту блокировку? Или это происходит автоматически с началом транзакции?
Я пытался найти хороший пример и не нашел.
НАЧАТЬ ТРАНЗАКЦИЮ;
Выбрать яблоки
из фруктов
где яблоки="золотые"
ДЛЯ ОБНОВЛЕНИЯ;
...
ЗАФИКСИРОВАТЬ;
Инструкция SELECT ... FOR UPDATE сначала блокирует строку, а затем возвращает ее вам.
Кто-то другой, пытающийся сделать то же самое с той же строкой в то же время, должен будет подождать ваш COMMIT или ROLLBACK, и затем они получат строку… с любыми изменениями, которые вы сделали.
Если у вас нет намерения изменять строку, просто хотите убедиться, что никто другой не сможет этого сделать, вы можете использовать SELECT ... LOCK IN SHARE MODE.
Обратите внимание, что в любом случае это технически не “строка”, на самом деле блокируется индексная запись, но в InnoDB “все является индексом” (даже таблица без индексов на самом деле все равно имеет один индекс, тихо созданный системой), так что конечный результат будет тем же.
Ответ или решение
Эксклюзивная блокировка на уровне строки в MySQL: подробный пример
В этом ответе мы рассмотрим, как осуществить эксклюзивную блокировку на уровне строки в MySQL с использованием механизма хранения данных InnoDB. Эксклюзивная блокировка позволяет одной транзакции изменять или читать строку, предотвращая при этом доступ других транзакций к той же строке до момента освобождения блокировки.
Что такое эксклюзивная блокировка?
Эксклюзивная блокировка (exclusive lock) — это тип блокировки, который предотвращает любые другие транзакции от получения блокировки на ту же строку. Это означает, что другие операции, которые пытаются изменить или заблокировать ту же строку, будут ждать завершения текущей транзакции.
Пример использования эксклюзивной блокировки
Давайте рассмотрим, как можно применить эксклюзивную блокировку на уровне строки с помощью SQL-запроса.
- Запустим транзакцию.
- Выполним выборку с эксклюзивной блокировкой с помощью
SELECT ... FOR UPDATE. - Завершим транзакцию.
Вот пример SQL-кода:
START TRANSACTION;
SELECT apples
FROM fruit
WHERE apples = 'golden'
FOR UPDATE;
-- Здесь вы можете произвести изменения с выбранной строкой, например:
-- UPDATE fruit SET ... WHERE apples = 'golden';
COMMIT;Пояснение
-
START TRANSACTION: Эта команда инициирует новую транзакцию, в которой все изменения будут временными до момента вызова
COMMIT. -
SELECT … FOR UPDATE: Этот запрос выбирает строки из таблицы
fruit, используя условие, чтоapplesравно'golden'. Важное замечание: эта команда одновременно блокирует выбранные строки на запись, предотвращая другие транзакции от доступа к этим строкам, пока текущая транзакция не будет разрешена (COMMITS или ROLLS BACK).
Поведение других транзакций
Если в это время другая транзакция попытается выполнить запрос на блокировку той же строки (например, используя SELECT ... FOR UPDATE или UPDATE), она будет ждать, пока первая транзакция завершится. Это важный аспект механизма блокировок в MySQL, который помогает избежать конфликтов при одновременной работе с базой данных.
Альтернативный подход
Если у вас нет намерения изменять данные, но вам всё равно необходимо, чтобы никто другой не изменял строку, вы можете использовать конструкцию SELECT ... LOCK IN SHARE MODE. Это создаст совместную блокировку, которая также предотвратит эксклюзивные блокировки другими транзакциями, пока ваша транзакция активна.
Заключение
Использование эксклюзивной блокировки — это мощный инструмент для управления конкурентным доступом к данным в MySQL. С помощью команды SELECT ... FOR UPDATE вы можете эффективно контролировать доступ к строкам таблиц, обеспечивая безопасность и целостность данных в многопользовательской среде. Важно помнить, что всякий раз, когда вы работаете с блокировками, нужно внимательно следить за порядком исполнения транзакций, чтобы избежать взаимных блокировок и других проблем с производительностью.
Если у вас есть дополнительные вопросы или требуется уточнение по работе с транзакциями и блокировками, не стесняйтесь обращаться.