14.7.2.4 Locking Reads — MySQL 5.7

14.7.2.4 Блокирующие чтения

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

SELECT ... LOCK IN SHARE MODE

Устанавливает блокировку в режиме совместного доступа на любых строках, которые считываются. Другие сеансы могут читать строки, но не могут их изменять до завершения вашей транзакции. Если какие-либо из этих строк были изменены другой транзакцией, которая еще не завершена, ваш запрос ожидает завершения этой транзакции, а затем использует последние значения.
SELECT ... FOR UPDATE

Для записей индекса, которые встречаются при поиске, блокирует строки и все связанные записи индексов так же, как если бы вы выдали утверждение UPDATE для этих строк. Другие транзакции блокируются от обновления этих строк, от выполнения SELECT ... LOCK IN SHARE MODE или от чтения данных в определенных уровнях изоляции транзакций. Постоянные чтения игнорируют любые блокировки, установленные на записях, которые существуют в представлении чтения. (Старые версии записи не могут быть заблокированы; они реконструируются путем применения UPDATE к кэшированной копии записи.)

Эти клаузы полезны в основном при работе с данными в виде древовидных или графовых структур, как в одной таблице, так и в нескольких. Вы обходите узлы или ветви дерева от одной точки к другой, сохраняя право вернуться и изменить любые из этих значений «“указателей”.

Все блокировки, установленные запросами LOCK IN SHARE MODE и FOR UPDATE, снимаются при подтверждении или откате транзакции.

Примечание

Блокирующие чтения возможны только при отключенном автоматическом подтверждении (либо начав транзакцию с помощью START TRANSACTION, либо установив autocommit в 0).

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

SELECT * FROM t1 WHERE c1 = (SELECT c1 FROM t2) FOR UPDATE;

Чтобы заблокировать строки в таблице t2, добавьте оператор блокирующего чтения в подзапрос:

SELECT * FROM t1 WHERE c1 = (SELECT c1 FROM t2 FOR UPDATE) FOR UPDATE;

Примеры блокирующих чтений

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

Сначала используйте постоянное чтение, чтобы запросить таблицу PARENT и проверить, существует ли родительская строка. Можно ли безопасно вставить дочернюю строку в таблицу CHILD? Нет, потому что другой сеанс может удалить родительскую строку в промежутке между вашим SELECT и вашим INSERT, не сообщив вам об этом.

Чтобы избежать этой потенциальной проблемы, выполните запрос SELECT с использованием LOCK IN SHARE MODE:

SELECT * FROM parent WHERE NAME = 'Jones' LOCK IN SHARE MODE;

После того, как запрос LOCK IN SHARE MODE вернет родительскую 'Jones', вы можете безопасно добавить дочернюю запись в таблицу CHILD и подтвердить транзакцию. Любая транзакция, пытающаяся получить эксклюзивную блокировку в соответствующей строке в таблице PARENT, ожидает завершения ваших операций, то есть, пока данные во всех таблицах не будут в согласованном состоянии.

Рассмотрим другой пример: поле счётчика целых чисел в таблице CHILD_CODES, используемое для назначения уникального идентификатора каждой добавленной дочерней строке в таблице CHILD. Не используйте ни постоянное чтение, ни чтение в режиме совместного доступа для чтения текущего значения счётчика, потому что два пользователя базы данных могут видеть одинаковое значение счётчика, и возникает ошибка дублирования ключа, если две транзакции пытаются добавить строки с тем же идентификатором в таблицу CHILD.

Здесь LOCK IN SHARE MODE не является хорошим решением, потому что если два пользователя прочитают счётчик одновременно, по крайней мере один из них столкнется с тупиком при попытке обновить счётчик.

Для реализации чтения и инкрементации счётчика сначала выполните блокирующее чтение счётчика с помощью FOR UPDATE, а затем увеличьте счётчик. Например:

SELECT counter_field FROM child_codes FOR UPDATE;
UPDATE child_codes SET counter_field = counter_field + 1;

Запрос SELECT ... FOR UPDATE читает самые последние доступные данные, устанавливая эксклюзивные блокировки на каждую прочитанную строку. Таким образом, он устанавливает те же блокировки, что и запрос SQL UPDATE на строки.

Предыдущее описание — всего лишь пример того, как работает запрос SELECT ... FOR UPDATE. В MySQL задача генерации уникального идентификатора на самом деле может быть выполнена с помощью одного обращения к таблице:

UPDATE child_codes SET counter_field = LAST_INSERT_ID(counter_field + 1);
SELECT LAST_INSERT_ID();

Запрос SELECT просто извлекает информацию об идентификаторе (специфичную для текущего подключения). Он не обращается ни к одной таблице.