MySQL 8.0.1: использование SKIP LOCKED и NOWAIT

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

В качестве иллюстрации будет использоваться система бронирования билетов на хоккейный матч.

Процесс бронирования

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

Ожидается, что система бронирования временно задержит мои места, пока я не завершу заказ (или не откажусь от него). Аналогично, я не рассчитываю, что при выборе мест мне будут показаны места, которые отмечены другими пользователями.

Такую ситуацию можно было обработать в MySQL и ранее, имея метаданные, связанные с каждым местом (проданный, доступный, ожидающий), а также таймаут для того, когда ‘ожидающие’ места должны стать доступными снова. Но начиная с MySQL 8.0.1 есть лучший (и более простой) способ…

SKIP LOCKED

В MySQL 8.0.1 был введен модификатор SKIP LOCKED, использующийся для не детерминистического чтения из таблицы с пропуском строк, заблокированых другими пользователями. Это можно использовать в нашей системе бронирования для пропуска отложенных заказов. Например:

CREATE TABLE seats (
seat_no INT PRIMARY KEY,
booked ENUM('YES', 'NO') DEFAULT 'NO'
);

# генерируем 100 строк для примера
INSERT INTO seats (seat_no)
WITH RECURSIVE my_cte AS
(
SELECT 1 AS n
UNION ALL
SELECT 1+n FROM my_cte WHERE n<100
)
SELECT * FROM my_cte;

Если я хочу забронировать места №2 и №3, то достаточно выполнить:

START TRANSACTION;
SELECT * FROM seats WHERE seat_no BETWEEN 2 AND 3 AND booked = 'NO'
FOR UPDATE SKIP LOCKED;

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

Далее я могу или оформить покупку на выбранные мной места:

UPDATE seats SET booked = 'YES' WHERE seat_no BETWEEN 2 AND 3;
COMMIT;

Иными словами, комбинация FOR UPDATE и SKIP LOCKED действует следующим образом: строки, заблокированные другими пользователями, в результирующий набор не попадают, а на выбранные строки ставится эксклюзивная блокировка. В этом можно убедиться, используя performance_schema.data_locks (также доступна с 8.0.1):

START TRANSACTION;
SELECT * FROM seats WHERE seat_no IN (3, 4) FOR UPDATE;

Здесь мы видим, что эксклюзивная (Х) блокировка была взята для записей, представляющих места с номерами 3 и 4. Если таблица имеет первичный ключ, то в столбце lock_data содержится его значение. Не правда ли удобно? Также мы видим, что блокировка намерения (IX) взята на всю таблицу. Но не волнуйтесь, эта блокировка совместима с блокировками чтения. Для получения более подробной информации про блокировки InnoDB читайте документацию.

Совет: Если вы не видите никаких строк в performance_schema, это может быть потому, что вы забыли выполнить START TRANSACTION. Блокировки удерживаются на время транзакции, поэтому так важно не использовать в этих примерах автоматическую фиксацию. Указание ROLLBACK или COMMIT также приведет к тому, что в таблицах performance_schema будет пусто.

Выборочная блокировка таблиц, входящих в запрос

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

DROP TABLE IF EXISTS seats;
CREATE TABLE seat_rows ( row_no INT PRIMARY KEY, cost DECIMAL );
CREATE TABLE seats (
seat_no INT NOT NULL,
row_no INT NOT NULL,
booked ENUM('YES', 'NO') DEFAULT 'NO',
PRIMARY KEY (seat_no, row_no)
);

# генерируем 20 рядов по 100 мест в каждом
INSERT INTO seats (seat_no, row_no)
WITH RECURSIVE my_seats AS
(
SELECT 1 AS n
UNION ALL
SELECT 1+n FROM my_seats WHERE n<100
), my_rows AS
(
SELECT 1 AS n
UNION ALL
SELECT 1+n FROM my_rows WHERE n<20
)
SELECT * FROM my_seats, my_rows;

# добавляем информацию о стоимости каждого ряда

INSERT INTO seat_rows (row_no, cost)
WITH RECURSIVE my_rows AS
(
SELECT 1 AS n
UNION ALL
SELECT 1+n FROM my_rows WHERE n<20
)
SELECT n, 100-(n*2) FROM my_rows;

Теперь для блокировки строк только в таблице seats мы добавляем часть OF в конструкцию FOR UPDATE:

START TRANSACTION;
SELECT seat_no, row_no, cost
FROM seats s JOIN seat_rows sr USING ( row_no )
WHERE seat_no IN ( 3,4 ) AND sr.row_no IN ( 5,6 )
AND booked = 'NO'
FOR UPDATE OF s SKIP LOCKED;

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

Проверим через performance_schema.data_locks, что заблокированы только строки в таблице seats:

Теперь несколько надуманный пример. Предположим, я хочу продавать билеты таким образом, чтобы максимизировать плотность посетителей для больших панорамных снимков, которые появятся на телевидении (на самом деле акула капитализма хочет в первую очередь распродать самые дорогие билеты/ прим переводчика/). Для этого я объявлю большую часть мест заблокированными и буду освобождать их для продажи по мере необходимости. Сначала установим блокировку на следующие строки:

START TRANSACTION;
SELECT * FROM seat_rows WHERE row_no >= 10 FOR UPDATE;

Затем изменим предыдущий запрос, который ищет доступные места, так чтобы он устанавливал разделяемую блокировку (shared lock) на ряды. Так как разделяемая блокировка совместима с другими разделяемыми блокировками, то в следующем примере места на рядах с первого по девятый будут доступны для продажи, а с десятого и далее нет.

START TRANSACTION;

# Attempt to book available row (works)
SELECT seat_no
FROM seats JOIN seat_rows USING ( row_no )
WHERE seat_no IN (3,4) AND seat_rows.row_no IN (5,6)
AND booked = 'NO'
FOR UPDATE OF seats SKIP LOCKED
FOR SHARE OF seat_rows;

# Attempt to book row on hold (lock waits)
SELECT seat_no
FROM seats JOIN seat_rows USING ( row_no )
WHERE seat_no IN (3,4) AND seat_rows.row_no IN (12)
AND booked = 'NO'
FOR UPDATE OF seats SKIP LOCKED
FOR SHARE OF seat_rows;

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

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

NOWAIT

SKIP LOCKED это действие, которое механизм хранения выполняет при обнаружении заблокированной строки, состоящее в том, чтобы пропустить её и перейти к передаче следующей строки в результирующий набор. Иногда, это именно то, что нам нужно, а в другой ситуации - нет.

Рассмотрим предыдущий пример в предположении, что монопольная блокировка (Х) на row_no >= 10 удерживается без намерения освободить её в ближайшее время. Возможно, в этом случае будет лучше, если запрос не станет ожидать освобождения блокировки, а сразу завершится с ошибкой:

START TRANSACTION;

# Попытка получить блокировку, немедленно
# вернуть ошибку, если это невозможно

SELECT seat_no
FROM seats JOIN seat_rows USING ( row_no )
WHERE seat_no IN (3,4) AND seat_rows.row_no IN (12)
AND booked = 'NO'
FOR UPDATE OF seats SKIP LOCKED
FOR SHARE OF seat_rows NOWAIT;

Без указания NOWAIT запрос ожидал бы innodb_lock_wait_timeout секунд (по умолчанию 50) при попытке получить разделяемую блокировку на seat_rows. NOWAIT предписывает сразу завершить выполнение запроса и вернуть ошибку:

ERROR 3572 (HY000): Do not wait for lock.

Заключение

Синтаксис, позволяющий управлять поведением при наличии блокировок, был одной из самых востребованных функций со стороны сообщества, и мы рады представить его вам в новой версии. В статье использование новых возможностей показано на примере бронирования мест, но вы, наверное, уже видите как можно применять SKIP LOCKED и NOWAIT в других сценариях:

© Все права на данную статью принадлежат порталу SQLInfo.ru. Перепечатка в интернет-изданиях разрешается только с указанием автора и прямой ссылки на оригинальную статью. Перепечатка в бумажных изданиях допускается только с разрешения редакции.