На хабре часто обсуждаются принципы работы MySQL. Данный хабратопик посвящен механизмам блокировок, используемым в MySQL. Топик поможет начинающим изучать MySQL и, в некоторой степени, опытным хабралюдям.
Механизм блокирования в MySQL
Одновременный доступ нескольких клиентов к хранилищу данных может приводить к ошибкам различного типа. Например, одновременное чтение одним клиентом и запись другим клиентом одной и той же строки таблицы с большой вероятностью приведет к сбою или чтению некорректных данных. Механизмы блокировок позволяют избежать ситуаций одновременного доступа к данным, регламентируя механизм взаимодействия пользователей между собой.
MySQL от имени одного из клиентов накладывает блокировку на определенный ресурс, при этом другие клиенты ждут освобождения блокировки. Блокировка может быть на уровне таблиц (блокируется таблица) или на уровне строк (блокируются определенные строки таблицы). В механизме хранения MyISAM (используемом по умолчанию) реализована табличная блокировка, а в механизме InnoDB построчная. Построчная блокировка достигается посредством усложнения структуры хранилища: в MyISAM структура файла с данными представляет собой простое перечисление строк таблицы, тогда как хранилище InnoDB структурировано и поддерживает мультиверсионность данных. Поэтому, InnoDB выигрывает в приложениях, в которых происходит многопоточное изменение данных в одну и ту же таблицу, несмотря на необходимые потери на обслуживание более сложного хранилища.
Блокировки бывают двух видов: на чтение и на запись.
- Если A хочет читать данные, то другие клиенты тоже могут читать данные, но никто не может записывать, пока А не закончит чтение (read lock).
- Если А хочет записать данные, то другие клиенты не должны ни читать ни писать эти данные пока А не закончит(write lock).
Блокировка может быть наложена явно или неявно.
- Если клиент не назначает блокировку явным образом, MySQL сервер неявно устанавливает необходимый тип блокировки на время выполнения выражения или транзакции. Например, в случае выполнения оператора SELECT сервер установит READ LOCK, а в случае UPDATE — WRITE LOCK. При неявной блокировке уровень блокировки зависит от типа хранилища данных: для MyISAM, MEMORY и MERGE блокируется вся таблица, для InnoDB — только используемые в выражении строки (в случае, если набор этих строк может быть однозначно определен значениями первичного ключа — иначе, блокируется вся таблица).
- Часто возникает необходимость выполнения нескольких запросов подряд без вмешательства других клиентов в это время. Неявная блоктровка не подходит для этих целей, так как устанавливается только на время выполнения одного запроса. В этом случае клиент может явно назначить, а потом отменить блокировку с помощью выражений LOCK TABLES и UNLOCK TABLES. Явной блокировка всегда блокирует всю таблицу, независимо от механизма хранения.
Использование явных блокировок
В случае явной блокировки можно получить выигрыш производительности за счет однократного назначения блокировки и задержки записи обновленных индексов до на время блокировки. При назначении явной блокировки, указываются имя таблицы и тип блокировки:LOCK TABLES Country READ, City WRITE;Оператор UNLOCK TABLES не имеет аргументов и снимает все блокировки, установленные явно в рамках текущей сессии.
Обратите внимание на следующие особенности явных блокировок:
- Пока клиент удерживает явную блокировку, он не может использовать другие таблицы, поэтому блокировать нужно сразу все что понадобится (одним выражением), так как повторное использование оператора LOCK TABLES отменяет сделанные ранее блокировки.
- Блокировка может быть установлена на представление (VIEW) начиная с версии 5.0.6. Для ранних версий, необходимо установливать блокировку на все таблицы, входящие в представление.
- Разрыв соединения илм завершение сессии автоматически снимает все установленные в рамках данной сессии блокировки.
- Блокировка таблиц может быть нарушена транзакцией и наоборот. START TRANSACTION неявным образом осуществляет UNLOCK TABLES и наоборот LOCK TABLES откатывает незаконченную транзакцию.
- Для установки блокировки требуется иметь привилегии LOCK TABLES и SELECT на каждую блокируемую таблицу.
- Если одна из требуемых таблиц заблокирована в рамках другой сессии, то оператор блокировки не выполнится пока все таблицы не освободятся.
Типы блокировок:
- READ — блокирует таблицу для чтения. Все клиенты могут получать данные одновременно, но никто не может их изменять, даже тот клиент, который установил блокировку.
- WRITE — блокирует таблицу для записи. Только клиент установивший блокировку может получать и изменять данные.
- READ LOCAL — блокирует таблицу для чтения, но позволяет осуществлять вставку данных (INSERT). Применимо только к таблицам MyISAM, которые не имеют дыр, образованных в результате изменения или удаления строк. В этом случае, добавление новых данных производится в конец таблицы. Если таблица имеет дыры, то их можно устранить, используя оператор OPTIMIZE TABLE.
- LOW_PRIORITY WRITE — блокирует таблицу для записи, но во время ожидания блокировки пропускает тех клиентов, которые стоят в очереди на получения блокировки типа READ. Во время ожидания блокировки, новые поступающие запросы на блокировку типа READ также пропускаются вперед, что может потенциально привести к тому, что запись не будет произведена никогда (если всегда есть клиенты в очереди на чтение).
Примечание: системная переменная concurrent_inserts определяет возможность добавления данных в конец таблицы MyISAM, заблокированной для чтения. По умолчанию, эта переменная равна 1, что означает возможность добавления данных при отсутствии в таблице дыр. Таким образом, в случае неявной блокировки, для таблиц MyISAM устанавливается READ LOCAL, а не READ. Значение concurrent_inserts равное 0 запрещает добавление данных параллельно с чтением, а равное 2 разрешает вставку данных в конец таблицы даже при наличии в таблице дыр.
Заключение
В статье рассмотрены лишь основные вопросы использования блокировок в MySQL. Не освещены транзакции, уровни изоляции, advisory lock и др. Напишите в комментариях какие из вопросов вам интересны.