10.11.1 Internal Locking Methods — MySQL 8.4

10.11.1 Внутренние методы блокировки

В этом разделе обсуждается внутренняя блокировка, то есть блокировка, выполняемая внутри самого сервера MySQL для управления конкуренцией за содержимое таблиц несколькими сеансами. Этот тип блокировки является внутренним, потому что он выполняется полностью сервером и не вовлекает другие программы. Для блокировки, выполняемой другими программами на файлах MySQL, см. Раздел 10.11.5, «Внешняя блокировка».

Блокировка на уровне строк

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

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

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

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

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

Блокировка на уровне таблиц

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

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

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

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

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

Если на таблице нет блокировок, поместите на неё блокировку записи.
В противном случае поместите запрос блокировки в очередь блокировок записи.

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

Если на таблице нет блокировок записи, поместите на неё блокировку чтения.
В противном случае поместите запрос блокировки в очередь блокировок чтения.

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

Дополнительная информация об изменении приоритета чтения и записи см. в Разделе 10.11.2, «Вопросы блокировки таблиц».

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

mysql> SHOW STATUS LIKE 'Table%';
+-----------------------+---------+
| Variable_name         | Value   |
+-----------------------+---------+
| Table_locks_immediate | 1151552 |
| Table_locks_waited    | 15324   |
+-----------------------+---------+

Таблицы блокировки Performance Schema также предоставляют информацию о блокировках. См. Раздел 29.12.13, «Таблицы блокировок Performance Schema».

Хранилище данных MyISAM поддерживает одновременные вставки для уменьшения конкуренции между читателями и авторами для данной таблицы: Если у таблицы MyISAM нет свободных блоков посередине файла данных, строки всегда вставляются в конец файла данных. В этом случае вы можете свободно смешивать одновременные INSERT и SELECT операторы для таблицы MyISAM без блокировок. То есть, вы можете вставлять строки в таблицу MyISAM одновременно с тем, как другие клиенты читают из неё. Пробелы могут возникать из-за удаления или обновления строк посередине таблицы. Если есть пробелы, одновременные вставки отключены, но снова включаются автоматически, когда все пробелы заполнены новыми данными. Чтобы контролировать это поведение, используйте переменную системы concurrent_insert. См. Раздел 10.11.3, «Одновременные вставки».

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

Для выполнения многих INSERT и SELECT операций над таблицей t1, когда одновременные вставки невозможны, вы можете вставить строки в временную таблицу temp_t1 и обновить реальную таблицу строками из временной таблицы:

mysql> LOCK TABLES t1 WRITE, temp_t1 WRITE;
mysql> INSERT INTO t1 SELECT * FROM temp_t1;
mysql> DELETE FROM temp_t1;
mysql> UNLOCK TABLES;

Выбор типа блокировки

Как правило, табличные блокировки предпочтительнее блокировок на уровне строк в следующих случаях:

Большинство операторов для таблицы — это чтение.
Операторы для таблицы — это смесь чтений и записей, где записи — это обновления или удаления одной строки, которую можно извлечь с помощью одного чтения по ключу:
```
UPDATE tbl_name SET column=value WHERE unique_key_col=key_value;
DELETE FROM tbl_name WHERE unique_key_col=key_value;
```
SELECT в сочетании с одновременными INSERT операторами и очень небольшим количеством UPDATE или DELETE операторов.
Множество сканирований или GROUP BY операций по всей таблице без каких-либо записей.

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

Варианты помимо блокировки на уровне строк:

Версионирование (например, используемое в MySQL для одновременных вставок), где можно иметь одного автора одновременно с несколькими читателями. Это означает, что база данных или таблица поддерживает различные представления данных в зависимости от времени начала доступа. Другие распространённые термины для этого — «путешествие во времени», «копирование при записи» или «копирование по требованию».
Копирование по требованию во многих случаях превосходит блокировку на уровне строк. Однако в худшем случае оно может использовать гораздо больше памяти, чем использование обычных блокировок.
Вместо использования блокировок на уровне строк можно использовать блокировки на уровне приложения, такие как те, что предоставляются GET_LOCK() и RELEASE_LOCK() в MySQL. Это консультативные блокировки, поэтому они работают только с приложениями, которые сотрудничают друг с другом. См. Раздел 14.14, «Функции блокировки».