6.4. Проблемы, связанные с блокировками

6.4. Проблемы, связанные с блокировками

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

В главах 1 и 2 было отмечено, что в таблицах с типом InnoDB блокировка выполняется на уровне отдельных строк. Для таблиц MyISAM построчная блокировка не поддерживается, и при необходимости таблица блокируется целиком. Преимуществом построчной блокировки является возможность параллельного редактирования данных множеством пользователей. Однако табличная блокировка выполняется быстрее и требует меньшего количества оперативной памяти. Табличные блокировки оказываются предпочтительными в следующих случаях:

• таблица используется главным образом для чтения данных;

• основными операциями в таблице являются запросы и добавление строк, а изменение и удаление данных происходит редко; в этом случае можно выполнять чтение и запись параллельно (об этом будет подробно рассказано ниже);

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

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

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

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

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

LOCK TABLES <Основная таблица> WRITE,

<Вспомогательная таблица> WRITE;

INSERT INTO <Основная таблица>

(SELECT * FROM <Вспомогательная таблица>);

DELETE FROM <Вспомогательная таблица>);

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

• Другое решение состоит в присвоении значения 2 системной переменной concurrent_insert. Такая настройка позволяет выполнять запросы и добавление строк одновременно: если таблица используется для чтения, новые строки записываются в конец таблицы. При этом пустые блоки, образующиеся при удалении строк, не заполняются новыми строками, так что таблицу полезно периодически дефрагментировать с помощью команды OPTIMIZE TABLE, о которой говорилось в разделе 6.1 «Оптимизация структуры данных».

• Уменьшите значение переменной max_write_lock_count, чтобы разрешить чередование блокировок записи и чтения. Данная переменная определяет количество блокировок записи, после применения которых допускается выполнение блокировок чтения, несмотря на наличие ожидающих своей очереди блокировок записи (значение по умолчанию – 102 4).

• Снизьте приоритет всех операций записи, установив значение ON переменной low-priority-updates. При таком значении к таблице в первую очередь будут применяться блокировки чтения, и только в их отсутствие – блокировки записи.

UPDATE LOW_PRIORITY Customers SET phone='444-25-27' WHERE id=536;

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

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

• Если необходимо сочетать массовое удаление строк и выполнение запросов, рекомендуется удалять строки поэтапно, используя параметр LIMIT команды DELETE (см. раздел 2.6 «Изменение данных»).

• Повысим приоритет отдельных запросов, включив в текст запроса параметр HIGH_PRIORITY.

SELECT HIGH_PRIORITY * FROM Customers WHERE rating>1000;

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

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