MySQL 5.1 поддерживает блокировку на уровне таблицы для таблиц MyISAM и MEMORY, блокировку на уровне страницы для таблиц BDB и InnoDB. блокировки на уровне строк. Во многих случаях на основе обучения можно угадать, какой тип блокировки лучше всего подходит для приложения, но обычно трудно определить, лучше ли один тип блокировки, чем другой. Все зависит от приложения, разные части приложения могут требовать разных типов блокировки. Чтобы определить, хотите ли вы использовать механизм хранения с блокировкой на уровне строк, вам следует посмотреть, что делает ваше приложение и какое сочетание операторов выбора и обновления оно использует. Например, большинство веб-приложений выполняют множество операций выбора и небольшое количество удалений, только обновление значений ключей и лишь несколько вставок конкретных таблиц. Базовая настройка MySQL MyISAM хорошо настроена.
В MySQL для механизмов хранения, использующих блокировку на уровне таблицы, при блокировке таблицы взаимоблокировок не будет. Для этого всегда запрашиваются все необходимые блокировки сразу в начале запроса и всегда блокируется таблица в том же порядке.
Для WRITE метод блокировки таблицы, используемый MySQL, работает следующим образом:
◆ Если на таблице нет блокировки, установите на нее блокировку записи.
◆ В противном случае поместите запрос на блокировку в очередь блокировки записи.
Для READ метод блокировки, используемый MySQL, работает следующим образом:
◆ Если в таблице нет блокировки записи, установите на нее блокировку чтения.
◆ В противном случае поместите запрос на блокировку в очередь блокировки чтения.
Когда блокировка снята, ее могут получить потоки в очереди блокировки записи, а затем потоки в очереди блокировки чтения.
Это означает, что если у вас много обновлений в таблице, оператор SELECT будет ждать, пока обновлений не останется.
Если инструкции INSERT не конфликтуют, вы можете свободно смешивать параллельные инструкции INSERT и SELECT для таблицы MyISAM без блокировки.
InnoDB использует блокировку строк, а BDB использует блокировку страниц. При использовании обоих механизмов хранения возможны взаимоблокировки. Это связано с тем, что InnoDB автоматически получает блокировки строк, а BDB получает блокировки страниц во время обработки оператора SQL, а не при запуске транзакции.
Преимущества блокировки на уровне строк:
· При доступе к разным строкам во многих потоках возникает лишь несколько конфликтов блокировок.
· Откат с небольшим количеством изменений.
· Одна строка может быть заблокирована на длительное время.
Недостатки блокировки на уровне строк:
· Занимает больше памяти, чем блокировка на уровне страницы или таблицы.
· При использовании на больших частях таблицы медленнее, чем блокировка на уровне страницы или таблицы, поскольку необходимо получить больше блокировок.
· Если вы часто выполняете операции GROUP BY для большей части ваших данных или вам приходится часто сканировать всю таблицу, это будет значительно медленнее, чем другие блокировки.
· С помощью блокировки высокого уровня вы также можете легко масштабировать свое приложение, поддерживая различные типы блокировки, поскольку стоимость блокировки меньше, чем блокировка на уровне строки.
Блокировки таблиц имеют приоритет над блокировками на уровне страниц или строк, если:
· Большинство операторов в таблице используются для чтения.
· Чтение и обновление со строгими ключами, вы можете обновить или удалить строку, которую можно извлечь с помощью одного ключа чтения:
• UPDATE tbl_name SET columns = value WHERE unique_key_col = key_value
• DELETE FROM tbl_name WHERE unique_key_col = key_value;· SELECT в сочетании с
key_value;
параллельные операторы INSERT и очень мало операторов UPDATE или DELETE.
· Во всей таблице выполняется множество операций сканирования или GROUP BY без каких-либо операций записи.
Опции, которые отличаются от блокировки на уровне строки или страницы:
· Управление версиями (например, метод, используемый в MySQL для параллельных вставок), где может одновременно выполняться одна операция записи и множество операций чтения. Это означает, что база данных или таблица поддерживают разные представления данных в зависимости от того, когда начинается доступ. Другими распространенными терминами являются «отслеживание времени», «копирование при записи» или «копирование по требованию».
· Во многих случаях репликация по требованию имеет приоритет над блокировкой на уровне страницы или строки. Однако в худшем случае может использоваться больше памяти, чем при использовании обычной блокировки.
· В дополнение к блокировке на уровне строк вы можете использовать блокировку на уровне приложения, например GET_LOCK() и RELEASE_LOCK() в MySQL. Это консультативные блокировки, и они будут работать только в хорошо работающих приложениях.
Чтобы достичь максимальной скорости блокировки, MySQL использует блокировку таблиц (а не блокировку страниц, строк или столбцов) для всех механизмов хранения, кроме InnoDB и BDB. Для таблиц InnoDB и BDB MySQL использует блокировку таблиц только в том случае, если вы явно блокируете таблицу с помощью LOCK TABLES, если вы не используете LOCK TABLES, поскольку InnoDB использует автоматическую блокировку на уровне строк, а BDB использует блокировку на уровне страниц для обеспечения изоляции транзакций;
Но для больших таблиц блокировка таблиц лучше, чем блокировка строк, для большинства приложений, но имеет некоторые недостатки. Блокировка таблицы позволяет нескольким потокам одновременно читать таблицу, но если поток хочет выполнить запись в таблицу, он должен сначала получить монопольный доступ. Во время обновления все остальные потоки, желающие получить доступ к таблице, должны дождаться завершения обновления.
Обновления таблиц обычно считаются более важными, чем извлечение таблиц, поэтому им уделяется более высокий приоритет. Это должно гарантировать, что действие по обновлению таблицы не может остановиться, даже если в таблице есть активная активность SELECT.
Блокировка таблицы может вызвать проблемы, например, когда поток ожидает, поскольку жесткий диск заполнен и для того, чтобы поток мог обработать, должно быть свободное место. В этом случае все потоки, желающие получить доступ к рассматриваемой таблице, также переводятся в состояние ожидания, пока не освободится больше места на жестком диске.
Блокировка таблиц также проблематична в следующих ситуациях:
· Клиент отправляет длительный запрос.
· Затем другой клиент обновляет ту же таблицу. Клиент должен дождаться завершения SELECT.
· Другой клиент выполняет еще один оператор SELECT для той же таблицы. Поскольку UPDATE имеет более высокий приоритет, чем SELECT, оператор SELECT ожидает завершения UPDATE и завершения первого SELECT.
Ниже описаны некоторые методы, позволяющие избежать или уменьшить конфликты, вызванные блокировками таблиц:
· Попробуйте ускорить выполнение оператора SELECT. Для этого вам, возможно, придется создать несколько сводных таблиц.
· Запустите mysqld с параметром --low-priority-updates. Это даст всем операторам, которые обновляют (изменяют) таблицу, более низкий приоритет, чем инструкции SELECT. В этом случае второй оператор SELECT в предыдущей ситуации будет выполнен перед оператором UPDATE, не дожидаясь завершения первого SELECT.
· Вы можете использовать оператор SET LOW_PRIORITY_UPDATS=1, чтобы указать, что все обновления в определенном соединении должны иметь низкий приоритет.
· Вы можете использовать атрибут LOW_PRIORITY, чтобы присвоить определенному оператору INSERT, UPDATE или DELETE более низкий приоритет.
· Вы можете использовать атрибут HIGH_PRIORITY, чтобы придать определенному оператору SELECT более высокий приоритет.
· Запустите mysqld, указав низкое значение для системной переменной max_write_lock_count, чтобы заставить MySQL временно повысить приоритет всех операторов SELECT, ожидающих таблицу после завершения определенного количества вставок. Это позволяет установить блокировку READ после определенного количества блокировок WRITE.
· Если у вас возникли проблемы с INSERT в сочетании с SELECT, переключитесь на использование новых таблиц MyISAM, поскольку они поддерживают одновременные операции SELECT и INSERT.
· Если вы смешиваете вставки и удаления в одной таблице, INSERT DELAYED будет вам очень полезен.
· Если у вас возникли проблемы со смешиванием операторов SELECT и DELETE в одной таблице, вам может помочь опция LIMIT в DELETE.
· Использование SQL_BUFFER_RESULT для операторов SELECT может помочь сократить время блокировки таблицы.
· Код блокировки в mysys/thr_lock.c можно изменить, чтобы использовать одну очередь. В этом случае блокировки записи и блокировки чтения будут иметь одинаковый приоритет, что может быть полезно для некоторых приложений.
Вот несколько советов, касающихся блокировки таблиц в MySQL:
· Если вы не смешиваете обновления с выборками, требующими проверки множества строк в одной и той же таблице, вы можете выполнять параллельные операции.
· Вы можете использовать ТАБЛИЦЫ БЛОКИРОВКИ для повышения скорости, поскольку многие обновления за одну блокировку выполняются намного быстрее, чем обновления без блокировок. Также может помочь разделение содержимого таблицы на несколько таблиц.
· Если вы столкнулись с проблемами скорости при блокировке таблиц в MySQL, вы можете преобразовать таблицу в таблицу InnoDB или BDB для повышения производительности.