SQLinfo.ru - Все о MySQL Фестиваль РИТ++ 2018

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

Дата: 3.05.2018

Данная статья является переводом статьи Мартина Ханссона.

В 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;

Или отменить покупку, сделав ROLLBACK.

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

START TRANSACTION;
SELECT * FROM seats WHERE seat_no IN (3, 4) FOR UPDATE;
SELECT object_name, index_name, lock_type, lock_mode, lock_data
FROM performance_schema.data_locks WHERE object_name = 'seats';
 
+-------------+------------+-----------+-----------+-----------+
| object_name | index_name | lock_type | lock_mode | lock_data |
+-------------+------------+-----------+-----------+-----------+
| seats       | NULL       | TABLE     | IX        | NULL      |
| seats       | PRIMARY    | RECORD    | X         | 3         |
| seats       | PRIMARY    | RECORD    | X         | 4         |
+-------------+------------+-----------+-----------+-----------+

Здесь мы видим, что эксклюзивная (Х) блокировка была взята для записей, представляющих места с номерами 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:

SELECT object_name, index_name, lock_type, lock_mode, lock_data
FROM performance_schema.data_locks WHERE object_name = 'seats';
+-------------+------------+-----------+-----------+-----------+
| object_name | index_name | lock_type | lock_mode | lock_data |
+-------------+------------+-----------+-----------+-----------+
| seats       | NULL       | TABLE     | IX        | NULL      |
| seats       | PRIMARY    | RECORD    | X         | 3, 5      |
| seats       | PRIMARY    | RECORD    | X         | 3, 6      |
| seats       | PRIMARY    | RECORD    | X         | 4, 5      |
| seats       | PRIMARY    | RECORD    | X         | 4, 6      |
+-------------+------------+-----------+-----------+-----------+
5 rows in set (0.00 sec)

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

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, показывающий, что получение разделяемой блокировки находится в состоянии ожидания.

SELECT object_name, lock_type, lock_mode, lock_data, lock_status FROM performance_schema.data_locks;
+-------------+-----------+-----------+------------------------+-------------+
| object_name | lock_type | lock_mode | lock_data              | lock_status |
+-------------+-----------+-----------+------------------------+-------------+
| seats       | TABLE     | IX        | NULL                   | GRANTED     |
| seats       | RECORD    | X         | 3, 5                   | GRANTED     |
| seats       | RECORD    | X         | 3, 6                   | GRANTED     |
| seats       | RECORD    | X         | 4, 5                   | GRANTED     |
| seats       | RECORD    | X         | 4, 6                   | GRANTED     |
| seat_rows   | TABLE     | IS        | NULL                   | GRANTED     |
| seat_rows   | RECORD    | S         | 5                      | GRANTED     |
| seat_rows   | RECORD    | S         | 6                      | GRANTED     |
| seat_rows   | RECORD    | S         | 12                     | WAITING     |
| seat_rows   | TABLE     | IX        | NULL                   | GRANTED     |
| seat_rows   | RECORD    | X         | 10                     | GRANTED     |
| seat_rows   | RECORD    | X         | supremum pseudo-record | GRANTED     |
| seat_rows   | RECORD    | X         | 11                     | GRANTED     |
| seat_rows   | RECORD    | X         | 12                     | GRANTED     |
| seat_rows   | RECORD    | X         | 13                     | GRANTED     |
| seat_rows   | RECORD    | X         | 14                     | GRANTED     |
| seat_rows   | RECORD    | X         | 15                     | GRANTED     |
| seat_rows   | RECORD    | X         | 16                     | GRANTED     |
| seat_rows   | RECORD    | X         | 17                     | GRANTED     |
| seat_rows   | RECORD    | X         | 18                     | GRANTED     |
| seat_rows   | RECORD    | X         | 19                     | GRANTED     |
| seat_rows   | RECORD    | X         | 20                     | GRANTED     |
+-------------+-----------+-----------+------------------------+-------------+
22 rows in set (0.00 sec)

Можно смешивать получение монопольных и разделяемых блокировок для произвольного числа таблиц. Забавно, что внутренний механизм для реализации этого был и ранее, но отсутствовал подходящий синтаксис (была возможность указать или 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 в других сценариях:

  • SKIP LOCKED очень удобен в случае многопоточных приложений, пытающихся найти следующие N строк в таблице, которые нуждаются в обработке.
  • Вы можете использовать NOWAIT, если не ожидаете заблокированных строк, и ваша бизнес-логика не имеет смысла в случае наличия таковых.
  • Вы можете использовать в одном запросе и SKIP LOCKED и NOWAIT (естественно, если не пытаетесь применить их оба к одной и той же таблице).

Удачи и спасибо за использование MySQL!

Дата публикации: 3.05.2018

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

Статьи :
 Установка и настройка MySQL
 Коды ошибок в MySQL
>Программирование в MySQL
>Оптимизация производительности
 Кодировка символов в MySQL
 Хранение данных в MySQL
 MySQL Cluster
См. также:
 Оптимизация производительности MySQL
 Онлайн-курс по оптимизации MySQL
 Услуги по оптимизации MySQL