Рано или поздно любой разработчик или администратор СУБД, имеющий дело с MySQL, сталкивается с проблемой блокировок. Всё дело в природе MySQL как системы с конкурентным доступом на чтение/запись. Я расскажу о видах блокировок в MySQL, их преимуществах и недостатках, о проблемах, которые они вызывают, а также дам полезные советы по обнаружению и способам борьбы с блокировками.

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

Краткая аналогия с блокировками баз данных

Представим, что вы разрабатываете программу для совместного редактирования текстовых документов, наподобие Google Docs. Вы закончили работу над первой версией и обнаружили, что ваши пользователи, работающие одновременно в программе, перетирают изменения друг друга. Вы решаете добавить в базу данных какое-нибудь булево значение is_locked, которое будет принимать значение true, если документ открыт пользователем. И если в этом случае другой пользователь попытается редактировать документ, ему придется подождать, пока первый не закончит свою работу. Такая схема будет работать, но не позволит поддерживать одновременное редактирование документа, поскольку только один пользователь сможет изменять документ в текущий момент времени.

Чтение документа, на первый взгляд, не имеет проблем. Что плохого, когда несколько пользователей читают документ? Однако что произойдет, если кто-нибудь из администраторов решит удалить документ, который открыт у пользователей? То есть даже чтение документов требует некоторых мер предосторожности.

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

Конкурентный доступ и виды блокировок

Конкурентный доступ является одной из главных проблем в системах управления базами данных. Что делать, когда несколько клиентов пытаются изменить одни и те же данные? А что если один из клиентов пытается получить данные, изменяемые другим в этот же момент времени? Можно привести много подобных примеров. Для разрешения этой проблемы в MySQL используется два типа блокировок: разделяемые (shared locks) и монолитные (exclusive locks). Их ещё называют блокировками на чтение и на запись. Блокировки на чтение являются разделяемыми, или неблокирующими. Это означает, что множество клиентов может читать запись в одно и то же время. Блокировки на запись являются эксклюзивными, то есть они не дадут другим клиентам захватывать блокировки на чтение/запись, поскольку это единственный безопасный способ гарантировать наличие единственного клиента, записывающего в данный момент времени.

Табличные блокировки

MySQL использует табличные блокировки для подсистем хранения MyISAM, MEMORY, MERGE, но вы можете явно заблокировать таблицу в любой подсистеме хранения командой LOCK TABLES, о которой мы поговорим ниже.

Табличная блокировка аналогична примеру выше. Когда клиент хочет записать что-нибудь в таблицу, он захватывает монолитную блокировку (exclusive lock) ко всей таблице. Остальные клиенты должны дождаться снятия блокировки. Если в текущий момент нет клиентов, которые изменяют данные, то все клиенты, читающие их, захватывают разделяемую блокировку (shared lock), которая не будет конфликтовать с другими блокировками на чтение.

Сервер MySQL содержит две очереди табличных блокировок, называемые read lock queue (очередь блокировок на чтение) и write lock queue (очередь блокировок на запись). Очередь блокировок на запись имеет приоритет перед очередью блокировок на чтение, то есть, если очередь блокировок на запись не пуста, то сервер MySQL будет освобождать её, пока она не опустеет, и только потом приступит к очереди блокировок на чтение. Это поведение можно изменить, запустив сервер MySQL с флагом --low-priority-updates, тогда очередь блокировок на чтение получит более высокий приоритет.

Проанализировать конкуренцию за блокировку таблиц можно командой

SHOW STATUS LIKE ‘Table%’.

Переменная Table_locks_immediate показывает количество раз, когда запрос на табличную блокировку был удовлетворен моментально. А Table_locks_waited показывает, сколько раз требовалось ожидать применения блокировки. Если это значение велико, то у вас проблемы с производительностью.

Преимущества табличных блокировок:

1. Требуют относительно небольшого объема памяти.

2. Быстрая работа при блокировке большого количества таблиц.

3. Быстрая работа при операциях GROUP BY или при полном сканировании таблиц.

4. Хорошо подходят для приложений, в которых данные изменяются редко.

5. Отсутствие взаимоблокировок (deadlocks), к которым мы еще вернемся.

Недостатки табличных блокировок:

1. Отсутствие параллелизма для операций изменения таблиц.

2. Из-за монолитной блокировки на запись все остальные операции вынуждены будут дожидаться окончания записи, в том числе выражения типа SELECT.

3. Операции на изменение должны дождаться, пока текущая блокировка на чтение не освободится.

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

Пользовательские блокировки

Пользовательскую блокировку можно получить с помощью функции GET_LOCK(key, timeout), где key — произвольный строковый ключ длиной до 64 символов, а timeout — таймаут в секундах. Отрицательное значение таймаута означает бесконечное время ожидания.

SELECT GET_LOCK(‘key’, 10);

Пользовательская блокировка применяется не к строке или таблице, это монолитная блокировка произвольного строкового значения. По-сути, именованный мьютекс. Вы задаете строку (ключ), которую хотите заблокировать, и указываете таймаут.

Снять пользовательскую блокировку можно явно командой RELEASE_LOCK(key). Второй случай снятия — неявно при завершении сеанса, в том числе аварийном. И третий случай — по истечении таймаута.

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

Преимущества пользовательских блокировок:

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

2. Позволяют реализовать блокировку на уровне приложения.

3. Позволяют реализовать подобие транзакций в тех движках, в которых они отсутствуют.

4. Имеют меньшие издержки, чем транзакции.

Недостатки пользовательских блокировок:

1. Небезопасно использовать с репликацией на основе SQL-выражений (покомандная репликация).

2. Из-за возможности вызвать сколько угодно команд GET_LOCK в рамках одного сеанса есть опасность применить слишком много блокировок в цикле вашей программы или, например, командой INSERT INTO … SELECT GET_LOCK(t1.col_name) FROM t1.

3. Такие блокировки не снимаются при фиксации или откате транзакций.

4. До версии 5.7 команда GET_LOCK имела очень опасное для ваших данных поведение, если вы применяли её к ключу, который уже был заблокирован этой командой.

Глобальные блокировки

Глобальная блокировка закрывает на чтение все открытые таблицы для всех баз данных. Применить её можно только с помощью команды FLUSH TABLES WITH READ LOCK. Это удобно для создания резервной копии. Однако стоит иметь в виду два момента:

1. Команда конфликтует с другими табличными блокировками.

2. Команда вызовет снятие всех предыдущих блокировок, вызванных командой LOCK TABLES, как будто вы выполнили команду UNLOCK TABLES.

Снять глобальную блокировку можно командой UNLOCK TABLES.

Преимущества глобальных блокировок:

1. Очень удобный способ получения резервной копии.

2. Эту команду используют как трюк для быстрого alter таблиц через подмену .frm-файлов.

3. Не так затратна, как полная остановка сервера, так как бОльшая часть информации остается кешированной в памяти и сервер «прогрет».

4. Используется для восстановления на конкретный момент времени.

Недостатки глобальных блокировок:

1. Требует более глубоких знаний администрирования MySQL. Не используйте эту команду, если не знаете, что делаете. Это может привести к глобальной блокировке вашей базы данных на очень долгое время.

2. На получение глобальной блокировки может уйти довольно много времени, поскольку команда ждет завершения всех операторов SELECT. В это время все остальные запросы будут вынуждены простаивать, неизвестно как долго.

3. В случае с таблицами MyISAM при большом объеме избыточного кеша придется ждать еще больше, так как ему необходимо сбросить блоки ключей на диск.

Построчные блокировки

Блокировки на уровне строк доступны в подсистеме хранения Archive, InnoDB и её форке XtraDB от Percona. Построчные блокировки реализуются только подсистемами хранения, а не сервером MySQL. 

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

Например, если наш запрос изменяет поле balance в таблице Users, то мы можем заблокировать только это поле, чтобы дать возможность другому запросу читать данные first_name, last_name и age. Однако если другая транзакция захочет прочитать поле balance, ей придется подождать снятия блокировки.

Преимущества построчных блокировок:

1. Обеспечивают лучшее управление конкурентным доступом.

2. Позволяют одновременно выполнять несколько изменений одного ресурса (если они не конфликтуют друг с другом).

3. Позволяют заблокировать одну строку на долгое время.

4. Меньше конфликтов блокировок при доступе к разным строкам.

Недостатки построчных блокировок:

1. Влекут максимальные издержки.

2. Приводят к взаимоблокировкам (deadlocks).

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

4. Работают намного медленней при полном сканировании таблицы или для группировок GROUP BY.

Работа с блокировками

Табличные блокировки и команда LOCK TABLES

Табличные блокировки бывают явные и неявные. Явная захватывается командой LOCK TABLES. Синтаксис следующий:

LOCK TABLES table_name1 {lock_type}, table_name2 {lock_type}...table_name3 {lock_type};

где lock_type — тип блокировки.

lock_type: {
  READ [LOCAL] | WRITE
}

READ означает применение табличной блокировки на чтение, WRITE — на запись.

Особенности блокировки READ [LOCAL]:

• Клиент, удерживающий блокировку, может читать из таблиц (но не писать).

• Другие клиенты могут читать из таблиц без явной READ блокировки.

• Другие клиенты могут применять READ блокировку одновременно с вами.

• Модификатор LOCAL для движка MyISAM разрешает другим клиентам выполнять конкурентные INSERT-вставки во время удержания блокировки. Подробнее можно прочитать здесь.

• Для InnoDB READ LOCAL то же самое, что и READ.

Особенности блокировки WRITE:

• Клиент, удерживающий блокировку, может читать и писать в таблицу.

• Другие клиенты не могут читать или изменять что-то в таблицах, поскольку блокировка монолитная.

• Запросы на блокирование таблицы другими клиентами будут отклонены, пока удерживается WRITE-блокировка.

• У WRITE есть модификатор LOW_PRIORITY, который с версии 5.6 не имеет никакого эффекта. 

Правильный способ сделать LOCK/UNLOCK TABLES при использовании транзакционных движков, таких как InnoDB, — это начинать транзакцию с SET autocommit = 0 (а не со START TRANSACTION), затем применить блокировку LOCK TABLES и не выполнять UNLOCK TABLES, пока транзакция не будет зафиксирована явно.

SET autocommit = 0;
LOCK TABLES drivers WRITE, clients READ;
<--делаем что-нибудь-->
COMMIT;
UNLOCK TABLES;

Почему этот способ правильный? Потому, что команда LOCK TABLES неявно фиксирует транзакцию и если переменная autocommit будет в значении 1/ON, то после вызова LOCK TABLES INNODB неявно зафиксирует транзакцию. Такая ситуация может легко привести к взаимоблокировке  (deadlock).

При использовании LOCK TABLES неявная блокировка таблиц применяется в двух случаях:

1. Таблицы, которые используются в триггерах, также будут заблокированы.

2. Все таблицы, связанные ограничениями внешних ключей, будут неявно заблокированы на чтение (LOCK TABLES READ). Для каскадных обновлений таблицы будут заблокированы на запись (LOCK TABLES WRITE).

Чем явные блокировки отличаются от неявных? На внутреннем уровне их структура одинакова, и управляются они одним и тем же кодом. На внешнем уровне разница лишь в том, что явные блокировки управляются с помощью команд LOCK TABLES и UNLOCK TABLES.

Однако всё это актуально для движка MyISAM. Табличные блокировки для других подсистем хранения ведут себя магическим образом. Когда сервер MySQL применяет и снимает неявные блокировки, он сообщает об этом подсистеме хранения, а та конвертирует эти блокировки, исходя из своих собственных потребностей.

Добавлю еще несколько моментов, которые стоит держать в голове при работе с командой LOCK TABLES:

• Вы не можете работать с таблицами, которые не перечислены в LOCK TABLES.

LOCK TABLES users WRITE;
INSERT INTO users (name) VALUES (‘Вася’); // успех
INSERT INTO drivers (name) VALUES (‘Петя’); // ошибка!
UNLOCK TABLES;

Error: Table 'drivers' was not locked with LOCK TABLES

Исключение — таблица INFORMATION_SCHEMA.

• LOCK TABLES неявно снимает все блокировки таблиц, удерживаемых в текущем сеансе.

• Если вы используете псевдоним таблицы, то должны явно обращаться к этому псевдониму после LOCK TABLES.

LOCK TABLES drivers as d WRITE;
SELECT * FROM drivers;
UNLOCK TABLES:

Error: Table 'drivers' was not locked with LOCK TABLES

То же самое правило относится и к псевдониму в выражениях после LOCK TABLES.

LOCK TABLES drivers WRITE;
SELECT * FROM drivers as d;
UNLOCK TABLES:

Error: Table 'd' was not locked with LOCK TABLES

Работа с блокировками на уровне строк в InnoDB и транзакции

Множественная блокировка гранулярности

Блокировки на уровне строк тесно связаны с транзакционным механизмом. Как я уже писал выше, блокировки делятся на разделяемые (shared locks) и монолитные (exclusive locks). Назовем их S и X соответственно.

Если транзакция T1 применила разделяемую блокировку к строке r, то запрос от второй транзакции T2 на блокировку этой строки обрабатывается по правилу:

• Запрос T2 на S-блокировку может быть удовлетворен. Обе транзакции удерживают блокировку строки r.

• Запрос T2 на X-блокировку не может быть удовлетворен. Транзакция T2 ожидает снятия блокировки T1.

Если транзакция T1 захватила монолитную блокировку к строке r, то запрос от второй транзакции T2 на любую (S или X) блокировку этой строки не может быть удовлетворен. Транзакция T2 ожидает снятия блокировки T1.

Но такой механизм не мог бы удовлетворить потребностям систем с большим конкурентным доступом, поскольку в таких системах также присутствуют табличные блокировки, которые будут конфликтовать с блокировками строк, и это может стоить нам потери параллелизма. Для решения этой проблемы в InnoDB была добавлена поддержка множественной блокировки гранулярности (Multiple granularity locking).

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

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

Теперь, когда стало проще блокировать файлы и записи, как InnoDB определяет, можно ли заблокировать корневой узел? Как вариант — поиск по дереву, но это сводит на нет саму идею блокировки с множественной гранулярностью. Более эффективный способ получить эту информацию — ввести новый режим: блокировку с намерением.

Блокировки с намерением (Intention locks)

Блокировки с намерением, или преднамеренные блокировки — это блокировки на уровне таблицы, которые сообщают подсистеме хранения о том, какой тип блокировки (shared или exclusive) позже потребуется транзакции для строки в таблице. По-сути, это указание движку на тот факт, что где-то есть обычные блокировки.

Так же как обычные блокировки делятся на shared и exclusive, преднамеренные делятся на разделяемые преднамеренные (intention shared locks) и монолитные преднамеренные блокировки (intention exclusive locks). Назовем их IS и IX соответственно.

В документации MySQL приводится следующая таблица совместимости блокировок:

Общие правило преднамеренных блокировок звучит так:

1. Прежде чем получить S-блокировку для строки r в таблице t, транзакция сначала должна получить IS или более сильную блокировку для таблицы t.

2. Прежде чем получить X-блокировку для строки r в таблице t, транзакция сначала должна получить IX-блокировку для таблицы t.

Если какой-то поток уже удерживает монопольную блокировку таблицы, то достаточно будет проверить только совместимость между монолитной и преднамеренной, а не проверять совместимость блокировки строки и таблицы. Это ускоряет проверку конфликтов.

Блокировки намерений не конфликтуют друг с другом, но блокировки таблиц конфликтуют как с IS, так и с IX, и наоборот. Не существует простого способа обнаружить конфликты блокировок на уровне строк и таблиц, но легко обнаружить конфликт между блокировками таблиц и преднамеренными блокировками.

Синтаксис блокировок с намерением чрезвычайно простой:

SELECT … FOR SHARE [NOWAIT | SKIP LOCKED] — намерение установить IS-блокировку.

SELECT … FOR UPDATE [NOWAIT | SKIP LOCKED] — намерение установить IX-блокировку.

Пара примеров, демонстрирующих совместимость на практике:

Пример 1:

# Транзакция 1 (IS)
SELECT id, name FROM drivers FOR SHARE;

# Транзакция 2 (S)
LOCK TABLES drivers READ;
SELECT * FROM drivers;
UNLOCK TABLES;

В этом случае транзакция 2 ничего не ждет, поскольку IS- и S-блокировки совместимы.

Пример 2:

# Транзакция 1 (IX)
SELECT id, name FROM drivers FOR UPDATE;

# Транзакция 2 (S)
LOCK TABLES drivers READ;
SELECT * FROM drivers;
UNLOCK TABLES;

Здесь транзакция 2 будет дожидаться окончания работы транзакции 1, поскольку IX- и S-блокировки конфликтуют между собой.

Оба типа преднамеренных блокировок имеют необязательные флаги NOWAIT и SKIP LOCKED. Обычно, если ваша транзакция хочет заблокировать строку, то она дожидается окончания текущих блокировок. Но если в запросе имеются один из этих флагов, то транзакция не будет дожидаться снятия блокировки и сразу же попробует захватить её сама. С флагом NOWAIT вы получите ошибку, если строка уже будет заблокирована. А в случае SKIP LOCKED заблокированная строка будет удалена из выборки.

Работа флагов на примере:

Пример с NOWAIT.

# Транзакция 1
START TRANSACTION;  
SELECT id, name FROM drivers WHERE id = 1 FOR UPDATE;
# Транзакция 2
START TRANSACTION;
SELECT id, name FROM drivers WHERE id = 1 FOR UPDATE NOWAIT;

Error: Statement aborted because lock(s) could not be acquired immediately and NOWAIT is set.

Пример со SKIP LOCKED.

# Транзакция 1
START TRANSACTION;  
SELECT id, name FROM drivers WHERE id = 1 FOR UPDATE;

# Транзакция 2
START TRANSACTION;
SELECT * FROM drivers FOR UPDATE SKIP LOCKED;

Примечания по работе с блокировками намерений:

• При блокировке строки (строк) SELECT … FOR SHARE, если эта строка (строки) были изменены транзакцией, которая еще не зафиксирована, ваш запрос будет ожидать фиксации транзакции, чтобы получить актуальные данные.

• До MySQL 8.0.22 SELECT … FOR SHARE требует SELECT-привилегий и, по крайней мере, одну привилегию из DELETE, LOCK TABLES, UPDATE. Начиная с 8.0.22 достаточно только привилегии SELECT.

• Во время блокировки SELECT … FOR UPDATE для записей индекса, обнаруженных при поиске, блокируются строки и все связанные с индексом записи, как если бы вы выполняли UPDATE этих строк. В этот момент все транзакции, которые пытаются изменить эти строки или выполнить SELECT … FOR SHARE этих записей, будут ждать снятия блокировки. Также эти записи будут заблокированы для SELECT уровне изоляции SERIALIZABLE, поскольку на этом уровне все выражения SELECT неявно приводятся к SELECT … FOR SHARE.

Пример 1:

# Транзакция 1
START TRANSACTION;  
SELECT id, name FROM drivers WHERE id > 5 FOR UPDATE;
COMMIT;

# Транзакция 2
START TRANSACTION;
UPDATE drivers SET balance = balance + 50 WHERE id > 5;
COMMIT;

Пример 2:

# Транзакция 1
START TRANSACTION;  
SELECT id, name FROM drivers WHERE id > 5 FOR UPDATE;
COMMIT;

# Транзакция 2
START TRANSACTION;
SELECT id, name FROM drivers WHERE id > 5 FOR SHARE;
COMMIT;

Пример 3:

# Транзакция 1
START TRANSACTION;  
SELECT id, name FROM drivers WHERE id > 5 FOR UPDATE;
COMMIT;
# Транзакция 2
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; 
START TRANSACTION; 
SELECT id, name FROM drivers WHERE id > 5;
COMMIT;

Во всех примерах транзакция 2 ожидает снятия блокировки всех записей индекса, которые попадают под условие id > 5.

• SELECT … FOR UPDATE требует SELECT-привилегий и, по крайней мере, одну привилегию из DELETE, LOCK TABLES, UPDATE.

• Фиксация или откат транзакции снимает все блокировки SELECT … FOR SHARE и SELECT … FOR UPDATE.

• SELECT … FOR SHARE является заменой устаревшему выражению LOCK IN SHARE MODE (хотя последнее еще присутствует в MySQL для обратной совместимости).

• SELECT … FOR SHARE и SELECT … FOR UPDATE не блокируют таблицы в подзапросах.

# В этом примере строки из таблицы blocked_drivers не блокируются

SELECT id, name 
FROM drivers 
WHERE id = (SELECT driver_id FROM blocked_drivers) 
FOR UPDATE;

# А в этом блокируются
SELECT id, name 
FROM drivers 
WHERE id = (SELECT driver_id FROM blocked_drivers FOR UPDATE) 
FOR UPDATE;

Хочу еще раз обратить ваше внимание, что преднамеренные блокировки НЕ являются реальными блокировками строк, а являются лишь блокировками таблиц с УКАЗАНИЕМ того, какой тип блокировки позже потребуется для строки. Конкретные же стратегии блокировок строк мы рассмотрим прямо сейчас.

Блокировки записей (Record Locks)

Record lock есть ни что иное, как блокировка записи индекса. Тема индексов выходит за рамки этой статьи, напомню только, что в InnoDB существуют кластерные индексы. Обычно это Primary Key-таблицы, но, если он не определен, то используется unique index. Если нет и его, то InnoDB сам создаст скрытый кластерный индекс. Также в InnoDB есть вторичные (secondary) индексы, которые хранят, помимо всего прочего, значение кластерного индекса. Во время блокировки записи блокируется запись первичного и вторичного индекса.

Добавим индекс по колонке car_id в нашей таблице Drivers, и теперь запрос

SELECT id, name 
FROM drivers 
WHERE car_id = 5
FOR UPDATE;

заблокирует индекс и не позволит другим транзакциям, пытающимся вставить/изменить/удалить записи в индексе, подходящим под условие car_id = 5.

Команда SHOW ENGINE INNODB STATUS, о которой мы поговорим позже, покажет нам две блокировки:

RECORD LOCKS index drivers_car_id_index of table drivers trx id 2653 lock_mode X

RECORD LOCKS index PRIMARY of table locks.drivers trx id 2653 lock_mode X locks rec but not gap

Первая — блокировка записей во вторичном индексе, вторая — в кластерном.

Блокировки промежутка (Gap Locks)

Этот вид строковой блокировки блокирует записи:

A. Между записями индекса:

SELECT id, name, car_id 
FROM drivers 
WHERE car_id BETWEEN 3 AND 7
FOR UPDATE;

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

SELECT id, name, car_id 
FROM drivers 
WHERE car_id = 4
FOR UPDATE;

SHOW ENGINE INNODB STATUS;

TRX HAS BEEN WAITING 3 SEC FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 2 page no 5 n bits 80 index drivers_car_id_index of table drivers trx id 3151 lock_mode X waiting

B. До записи индекса:

SELECT id, name, car_id 
FROM drivers 
WHERE car_id < 5
FOR UPDATE;

Промежуток индексов, подходящих под условие car_id < 5, будет также заблокирован.

C. После записи индекса:

SELECT id, name, car_id 
FROM drivers 
WHERE car_id > 5
FOR UPDATE;

Пример аналогичен предыдущему, только условие меняется на car_id > 5.

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

Блокировки промежутков не конфликтуют между собой, то есть один и тот же промежуток может быть заблокирован двумя разными транзакциями, причем они могут применять к одному промежутку одновременно S- и X-блокировку. Для этого вида блокировки нет разницы между разделяемыми и монолитными, поскольку они решают одну и ту же задачу: предотвращают потенциальные нарушения целостности из-за одновременных вставок. 

Если вы понизите уровень изоляции транзакции до READ COMMITED, то блокировки промежутков больше не будут блокировать индексные записи, потому что на таком уровне изоляции допускается существование фантомов. В этом случае блокировки промежутков будут использованы только для проверки ограничений внешнего ключа и проверки дубликатов ключа.

Блокировки следующего ключа (Next-Key Locks)

Блокировка следующего ключа — это комбинация блокировки индекса и промежутка. Идея в том, что блокируется не только запись индекса, но и промежуток перед ней. 

Предположим, у нас есть три записи в индексе (10, 20, 30), тогда мы имеем четыре интервала возможных значений (-inf...10], (10...20], (20...30], (30…+inf), где круглая скобка означает исключение точки из интервала, а квадратная — включение. Для последнего интервала блокируется промежуток над наибольшим значением в индексе до записи supremum, который является псевдо-записью, имеющим значение больше любого другого в индексе. Например, блокируя ключ 30, мы блокируем и диапазон (20...30], чем добиваемся консистентности.

Когда мы говорим о таком типе блокировки, мы подразумеваем уровень изоляции SERIALIZABLE, который требует отсутствия новых промежутков при повторном сканировании.

Блокировки намерений вставок* (Insert intention locks)

*возможно, на русский язык название этого типа блокировки можно перевести более корректно, но беглый поиск в сети ничего не выдал. Если у вас есть хороший перевод названия, напишите, пожалуйста, в комментариях.

Этот тип блокировки является разновидностью блокировки промежутка, но для операций вставки. Промежуток будет заблокирован до вставки новой записи в индекс, это сигнализирует о намерении вставить запись таким образом, что другие операции вставки в этот промежуток не конфликтуют друг с другом (если, конечно, вы не вставляете запись в одну и ту же позицию).

Для примера давайте добавим пару новых записей в нашу таблицу:

INSERT INTO drivers (name, car_id, balance) VALUES (‘new1’, 14, 100);
INSERT INTO drivers (name, car_id, balance) VALUES (‘new2’, 17, 100); 

Теперь в первой транзакции сделаем запрос:

START TRANSACTION;
SELECT * FROM drivers WHERE car_id > 13 FOR UPDATE;

Вторая же транзакция пытается вставить новую запись в наш промежуток car_id > 13:

INSERT INTO drivers (name, car_id, balance) VALUES (‘new2’, 15, 100);

Команда SHOW ENGINE INNODB STATUS покажет нам следующую информацию:

RECORD LOCKS space id 2 page no 5 n bits 80 index drivers_car_id_index of table drivers trx id 3165 lock_mode X locks gap before rec insert intention waiting

Record lock, heap no 10 PHYSICAL RECORD: n_fields 2; compact format; info bits 0

0: len 4; hex 80000011; asc     ;;

1: len 4; hex 8000000b; asc     ;;

Однако вторая транзакция не будет блокировать другие намерения вставок в этот промежуток и они могут сосуществовать бесконфликтно.

Блокировки AUTO-INC (AUTO-INC Locks)

AUTO-INC является блокировкой на уровне таблицы для инкрементирования первичного ключа во время вставок новых записей в таблицу. Есть три алгоритма увеличения первичного ключа, которые контролируются переменной innodb_autoinc_lock_mode. Значения могут быть 0, 1 или 2 (по-умолчанию в MySQL 8). Подробнее об алгоритмах можно почитать здесь.

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

Проблемы блокировок и способы решения

Существует три вида проблем с которыми вы можете столкнуться: конфликт за блокировку, долгосрочные блокировки и взаимоблокировки (deadlocks). Я постараюсь привести хоть и тривиальные, но понятные примеры.

Конфликт за блокировку

Конфликт возникает тогда, когда множеству сеансов постоянно требуется доступ к одной и той же блокировке. Такую ситуацию еще называют горячей блокировкой (Hot Locks). Каждый сеанс удерживает блокировку небольшое количество времени и снимает её. Это создает ситуацию наподобие выезда с парковки, когда множеству водителей для выезда нужно отсканировать чек об оплате, чтобы шлагбаум поднялся. Причём шлагбаум только один. Проблема незаметна при низком трафике (или при низком параллелизме), но по мере увеличения трафика возникает узкое место.

Конфликты за блокировку ограничивают масштабируемость. По мере увеличения параллелизма пропускная способность системы не увеличивается, а может даже и снижаться. Также такая ситуация приводит к увеличению нагрузки на ЦП.

Пример 1.

На сайте требуется записывать все хиты для каждой страницы (даже от одного и того же пользователя), и вы решаете хранить это значение в таблице базы данных. Каждый раз, когда делается запрос к странице, это значение увеличивается на единицу. Пока что ваш сайт не особо популярен и вы не чувствуете проблем, но когда им стали пользоваться миллионы людей, страницы стали загружаться медленно, а то и вообще падать с таймаутом.

Пример 2.

Для бухгалтерской отчетности нужно вычислять идентификатор следующего потенциального заказа. Вы храните это значение в отдельной таблице и после каждого заказа вычисляете его с помощью формулы last_order_id + 1. По мере роста нагрузки вам приходится слишком часто блокировать как таблицу заказов, так и таблицу, хранящую следующий идентификатор, пока он вычисляется.

Способы решения.

Как бы банально это ни звучало, но для решения проблемы горячих блокировок стоит просто не допускать горячих блокировок. Старайтесь не использовать критически важную функциональность совместно с механизмом постоянного блокирования строки/таблицы.

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

• Используйте более быстрое хранилище, например, Redis.

• Для различных счетчиков, где допустима потеря части данных, может хорошо помочь использование систем очередей, чтобы вести подсчет асинхронно.

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

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

• Уменьшайте время удержания такой блокировки с помощью оптимизации алгоритма программного кода или обнаружения активности, которая без надобности защищена блокировкой.

• Вместо табличной блокировки используйте InnoDB и детализированную блокировку на уровне строк.

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

Долгосрочные блокировки

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

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

Пример.

Рассмотрим тот же пример с заказами, описанный выше. Добавим к нему условие, что мы имеем запускаемую по расписанию задачу, которая берет следующий ID заказа и составляет большой и сложный отчет в течение 15 минут. Никакие другие заказы не могут быть обработаны, пока отчет не будет составлен. Ситуацию усугубляет тот факт, что пользователи не могут оформить заказ и думают, что система вышла из строя. Они пытаются создать новый заказ, отправляя новые запросы в базу данных. В результате пользователи исчерпывают все потоки соединения с базой данных, из-за чего система не может отвечать на запросы, даже не связанные с заказами.

Способы решения.

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

• Исключите взаимодействие с пользователем во время блокировки.

• Понизьте уровень изоляции транзакции.

• Проверьте, что у вас нет «спящих» потоков: это подключения к базе, которые ничего не делают. Например, PHP-скрипт, подключился, начал выполнять транзакцию, а затем продолжил работу, и в этот момент ваш поток MySQL находится в состоянии SLEEP. Еще хуже, если ваш код инициализирует постоянное соединение с базой, которое будет активно даже после завершения работы скрипта.

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

• Попробуйте использовать несколько коротких транзакций вместо одной долгой.

• Убедитесь, что вы закрываете все соединения, а также что все транзакции, начатые со START TRANSACTION, завершаются COMMIT/ROLLBACK.

Долгосрочные блокировки, как правило, представляют более значительную угрозу, чем конфликт блокировок. Они могут остановить работу большой части системы или даже всю её целиком. Блокировки, задействованные в этом сценарии, могут быть недостаточно «горячими», но если они становятся еще и «горячими», катастрофы не избежать.

Взаимоблокировки (Deadlocks)

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

Пример 1.

Самый простой случай взаимоблокировки:

Пример 2.

Взаимоблокировка из трех транзакций:

Способы решения.

Хорошей новостью является то, что InnoDB умеет самостоятельно обнаруживать взаимоблокировки и выбирает транзакцию-«жертву», которую откатит. Учитывайте этот момент в своих приложениях и будьте готовы выполнить транзакцию еще раз, если она откатывается из-за взаимоблокировки. Но если это стало частой проблемой, то вот список того, что стоит предпринять:

• Определите причину последней взаимоблокировки с помощью SHOW ENGINE INNODB STATUS.

• Активируйте флаг отладки взаимоблокировок innodb_print_all_deadlocks. Когда он включен, MySQL будет записывать информацию обо всех взаимоблокировках в журнал ошибок. Не забудьте отключить флаг после отладки, чтобы не создавать лишних накладных расходов.

• Не забывайте повторно выполнять транзакцию, которая была выбрана «жертвой».

• Минимизируйте свои транзакции, сделайте их максимально короткими. Избегайте взаимодействия с пользователями и обращений к внешним сервисам во время транзакции.

• Для транзакций SELECT … FOR UPDATE/SHARED попробуйте понизить уровень изоляции.

• Всегда выполняйте одни и те же наборы операций в одном и том же порядке.

• Подберите оптимальные индексы для своих таблиц, чтобы запросы сканировали как можно меньше индексных записей.

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

• Старайтесь избегать блокировок таблиц с помощью LOCK TABLES, но если совсем ничего не помогает, то LOCK TABLES становится одним из вариантов сериализации транзакций. Только не забудьте установить флаг autocommit в значение 0 по причине, о которой я писал выше. Цена, которую вам придется заплатить в данном случае, — это уменьшение скорости отклика базы данных.

• Еще одним способом сериализации транзакций является создание искусственного семафора в виде таблицы с одной единственной строкой. Каждая транзакция должна будет получить блокировку этой строки перед выполнением своих основных функций.

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

Как видите, множество советов по решению всех видов проблем, связанных с блокировками, повторяются. Всё дело в том, что блокировки являются проблемами не базы данных, а организации вашего приложения. 

Стоит добавить, что если ваши транзакции начали периодически отваливаться с ошибкой “Lock wait timeout exceeded”, значит ваше приложение столкнулось с проблемой конфликта за блокировку и/или долгосрочными блокировками. В этом случае может помочь увеличение времени ожидания снятия блокировки транзакцией с помощью изменения переменной innodb_lock_wait_timeout (по умолчанию 50 секунд).

SET innodb_lock_wait_timeout = {количество_секунд} 

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

Отладка блокировок

Для определения, какие запросы сейчас заблокированы и почему, можно использовать следующий запрос:

SELECT 
	r.trx_id waiting_trx_id,
	r.trx_mysql_thread_id waiting_thread,
	r.trx_query waiting_query,
	b.trx_id blocking_trx_id,
	b.trx_mysql_thread_id blocking_thread,
	b.trx_query blocking_query
FROM performance_schema.data_lock_waits w
INNER JOIN information_schema.innodb_trx b 
ON b.trx_id = w.blocking_engine_transaction_id
INNER JOIN information_schema.innodb_trx r
	ON r.trx_id = w.requesting_engine_transaction_id;

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

# блокирующий запрос
SELECT id, SLEEP(30) FROM drivers FOR UPDATE;

# эти два запроса ждут снятия блокировки 
SELECT name FROM drivers FOR UPDATE;

SELECT car_id FROM drivers FOR UPDATE;

Результат отладочного запроса будет следующий

где:

waiting_trx_id — ID ожидающей транзакции;

waiting_thread — ID ожидающего потока MySQL;

waiting_query — запрос, ожидающий снятия блокировки;

blocking_trx_id — ID блокирующей транзакции;

blocking_thread — ID блокирующего потока MySQL;

blocking_query — блокирующий запрос.

Если всё плохо и вам нужно срочно прибить блокирующий запрос, то можно воспользоваться командой KILL {blocking_thread}. Для данного примера это будет выглядеть так:

KILL 47;

Чуть более подробный, но примерно такой же результат можно увидеть просто выполнив команду:

SELECT * FROM sys.innodb_lock_waits;

Например:

# блокирующий запрос
START TRANSACTION;
SELECT * FROM drivers FOR UPDATE;

# этот запрос ждет снятия блокировки 
SELECT name FROM drivers FOR UPDATE;

# отладочный запрос
SELECT * FROM sys.innodb_lock_waits;

****** 1.row ******

-- wait_started: 2021-12-03 09:50:18

-- wait_age: 00:00:34

-- wait_age_secs: 34

-- locked_table: ‘locks’.drivers

-- locked_table_schema: locks

-- locked_table_name: drivers

-- locked_table_partition: NULL

-- locked_table_subpartition: NULL

-- locked_index: PRIMARY

-- locked_type: RECORD

-- waiting_trx_id: 3253

-- waiting_trx_started: 2021-12-03 09:50:18

-- waiting_trx_age: 00:00:34

-- waiting_trx_rows_locked: 1

-- waiting_trx_rows_modified: 0

-- waiting_pid: 53

-- waiting_query: SELECT name FROM drivers FOR UPDATE;

-- waiting_lock_id: 140500149771672:2:4:25:140500057267936

-- waiting_lock_mode: X

-- blocking_trx_id: 3252

-- blocking_pid: 52

-- blocking_query: NULL

-- blocking_lock_id: 140500149772528:2:4:25:140500057274160

-- blocking_lock_mode: X

-- blocking_trx_started: 2021-12-03 09:50:16

-- blocking_trx_age: 00:00:36

-- blocking_trx_rows_locked: 11

-- blocking_trx_rows_modified: 0

-- sql_kill_blocking_query: KILL QUERY 52

-- sql_kill_blocking_connection: KILL 52

Однако в ряде случаев в колонке blocking_query будет значение NULL. Такое происходит, когда сеанс переходит в неактивное состояние, то есть не делает ничего. Для примера я начну транзакцию и не буду выполнять COMMIT:

# блокирующий запрос
START TRANSACTION;
SELECT * FROM drivers FOR UPDATE;

# эти два запроса ждут снятия блокировки 
SELECT name FROM drivers FOR UPDATE;

SELECT car_id FROM drivers FOR UPDATE;

Результат отладочного запроса

Запрос SELECT car_id FROM drivers FOR UPDATE; блокирует сразу два запроса. Один из них мы видим, но второй в значении null. То же самое мы видим в колонке blocking_query и для запроса SELECT name FROM drivers FOR UPDATE;. Это происходит потому, что блокирующий запрос после начала транзакции и выборки SELECT не делает ничего. Чтобы узнать, что это за блокирующий запрос, стоит проделать следующее:

С помощью нужного blocking_thread получить идентификатор процесса блокирующей транзакции:

SELECT blocking_pid 
FROM sys.innodb_lock_waits 
WHERE blocking_trx_id = {blocking_thread}

С помощью полученного blocking_pid получить идентификатор потока:

SELECT THREAD_ID 
FROM performance_schema.threads 
WHERE PROCESSLIST_ID = {blocking_pid}

С помощью полученного THREAD_ID получить текст запроса:

SELECT THREAD_ID, SQL_TEXT 
FROM performance_schema.events_statements_current 
WHERE THREAD_ID = {THREAD_ID}

Если запрос не предоставил достаточную информацию и в SQL_TEXT вы получили что-то типа SELECT @@session.transaction_isolation, то можно запросить историю, чтобы посмотреть последние 10 команд:

SELECT THREAD_ID, SQL_TEXT 
FROM performance_schema.events_statements_history 
WHERE THREAD_ID = {THREAD_ID} 
ORDER BY EVENT_ID;

Один запрос для всего вышеперечисленного:

SELECT THREAD_ID, SQL_TEXT FROM performance_schema.events_statements_history
WHERE THREAD_ID = (
   SELECT THREAD_ID
   FROM performance_schema.threads
   WHERE PROCESSLIST_ID = (
       SELECT blocking_pid
       FROM sys.innodb_lock_waits
       WHERE blocking_trx_id = {blocking_thread} LIMIT 1
   )
)
ORDER BY EVENT_ID;

Еще один способ узнать о блокировках в системе — это воспользоваться командой SHOW ENGINE INNODB STATUS;. Но предварительно нужно установить переменную innodb_status_output_locks в значение ON.

SET GLOBAL innodb_status_output_locks = ON;
SHOW ENGINE INNODB STATUS;

Не забудьте отключить эту переменную после отладки, так как InnoDB создает еще один поток для записи это информации.

После выполнения команды можно прочитать информацию о блокировках в разделе TRANSACTIONS. Стандартный вывод включает список активной и ожидающих транзакций и информацию о заблокированных таблицах и строках, а также о режиме блокировки.

Заключение

Блокировки являются обычным явлением при работе с базами данных. Не стоит воспринимать их как что-то плохое. Напротив, блокировки — это необходимый компромисс для обеспечения целостности ваших данных в системах с конкурентным доступом.

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

Литература и источники

1. High Performance MySQL (by Baron Schwartz, Peter Zaitsev, Vadim Tkachenko)

2. https://dev.mysql.com/

3. https://stackoverflow.com/

4. https://www.percona.com/

5. https://www.methodsandtools.com/archive/archive.php?id=83