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

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

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

SELECT * FROM parent WHERE NAME = 'Jones' FOR SHARE;

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

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

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

Чтобы реализовать чтение и инкремент счётчика, сначала выполните блокирующее чтение счётчика с помощью 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 просто извлекает информацию об идентификаторе (специфичную для текущего подключения). Он не обращается ни к одной таблице.

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

Чтобы избежать ожидания, пока другие транзакции освободят блокировки строк, NOWAIT и SKIP LOCKED опции могут быть использованы с SELECT ... FOR UPDATE или SELECT ... FOR SHARE высказываниями с блокирующим чтением.

NOWAIT и SKIP LOCKED применяются только к блокировкам на уровне строк.

Запросы, использующие NOWAIT или SKIP LOCKED, не безопасны для репликации на уровне запросов.

Следующий пример демонстрирует NOWAIT и SKIP LOCKED. Сессия 1 начинает транзакцию, которая получает блокировку строки на одной записи. Сессия 2 пытается выполнить блокирующее чтение на той же записи с опцией NOWAIT. Так как запрашиваемая строка заблокирована сессией 1, блокирующее чтение возвращается немедленно с ошибкой. В сессии 3 блокирующее чтение с SKIP LOCKED возвращает запрошенные строки, за исключением строки, заблокированной сессией 1.

# Session 1:

mysql> CREATE TABLE t (i INT, PRIMARY KEY (i)) ENGINE = InnoDB;

mysql> INSERT INTO t (i) VALUES(1),(2),(3);

mysql> START TRANSACTION;

mysql> SELECT * FROM t WHERE i = 2 FOR UPDATE;
+---+
| i |
+---+
| 2 |
+---+

# Session 2:

mysql> START TRANSACTION;

mysql> SELECT * FROM t WHERE i = 2 FOR UPDATE NOWAIT;
ERROR 3572 (HY000): Do not wait for lock.

# Session 3:

mysql> START TRANSACTION;

mysql> SELECT * FROM t FOR UPDATE SKIP LOCKED;
+---+
| i |
+---+
| 1 |
| 3 |
+---+