E.4 Методы блокировки

В настоящее время MySQL поддерживает только табличную блокировку для таблиц типов ISAM/MyISAM и HEAP, страничную блокировку для таблиц BDB и строковую блокировку для таблиц InnoDB (see section 5.3.1 Как MySQL блокирует таблицы). Для таблиц MyISAM можно произвольным образом сочетать команды INSERT и SELECT без блокировок, поскольку поддерживается управление версиями (Versioning).

Начиная с версии 3.23.33 имеется возможность анализировать конфликты и конкуренцию блокировок таблиц в системе. Это делается путем проверки переменных Table_locks_waited и Table_locks_immediate.

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

Аргументы в пользу строковой блокировки:

• Меньше конфликтов блокировок при обращении к различным строкам из множества потоков.
• Меньше изменений при откатах.
• Возможна блокировка одной строки на длительное время.

Аргументы против строковой блокировки

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

Блокировки на уровне таблиц лучше, чем блокировки страничного/строкового уровня в следующих случаях:

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

  UPDATE table_name SET column=value WHERE unique_key#
  DELETE FROM table_name WHERE unique_key=#
  

• SELECT с INSERT (и очень мало операций UPDATE и DELETE).
• Выполняется много операций просмотра и группировки GROUP BY на всей таблице без записи.

Другие возможности, кроме строчного/страничного уровня блокирования:

Управление версиями (Versioning), подобно тому, как это делается в MySQL для параллельных вставок. При этом один из пользователей может выполнять операцию записи в то же время, когда несколько пользователей производят чтение. Это означает, что данная база данных/таблица поддерживает различные представления для данных в зависимости от того, когда произошло обращение к ним. Существуют и другие названия этой возможности - перемещение по времени (time travel), метод копирования в момент записи (copy on write) или метод копирования по запросу (copy on demand).

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

Вместо использования блокировок строкового уровня можно применять блокировки уровня приложения (подобно get_lock/release_lock в MySQL). Конечно, такие блокировки годятся только для корректно работающих приложений.

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

Ниже приводится несколько советов по блокировкам в MySQL:

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

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

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

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

Иногда проблемы со скоростью при блокировках таблиц в MySQL удается решить преобразованием ряда таблиц в таблицы типа InnoDB или BDB. See section 7.5 Таблицы InnoDB. See section 7.6 Таблицы BDB или Berkeley_DB.

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