5.3.2 Вопросы блокирования таблиц

Код блокирования таблиц в MySQL не создает тупиковых ситуаций.

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

Для таблиц типов InnoDB и BDB в MySQL табличная блокировка используется только в случае, если данная таблица явно блокируется командой LOCK TABLES. Мы вообще не рекомендуем применять LOCK TABLES для упомянутых типов таблиц, поскольку для таблицы InnoDB используется автоматическая блокировка строкового уровня, а для таблиц BDB - блокировка страничного уровня. Это делается, чтобы гарантировать изоляцию транзакций.

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

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

Поскольку обновление обычно считается более важной операцией, чем SELECT, то все команды, производящие обновления таблицы, имеют более высокий приоритет, чем команды извлечения данных. Такой алгоритм гарантирует, что обновления не зависнут в случае, если для некоторой таблицы выполняется большое количество тяжелых запросов (этот порядок действий можно изменить, используя LOW_PRIORITY с командой обновления или HIGH_PRIORITY с командой SELECT).

Начиная с версии MySQL 3.23.7 можно использовать переменную max_write_lock_count, чтобы заставить MySQL временно предоставить всем командам SELECT, ожидающим доступ к таблице, более высокий приоритет после заданного числа вставок в таблицу.

Табличную блокировку, однако, нецелесообразно использовать в случае следующего сценария:

Клиент запускает SELECT, требующий длительного времени для выполнения.
Затем другой клиент запускает команду UPDATE на используемой таблице. Этот клиент будет ожидать, пока SELECT не закончит свою работу.
Другой клиент запускает еще одну команду SELECT на той же таблице. Поскольку UPDATE имеет более высокий приоритет, чем SELECT, то эта команда SELECT будет ждать, пока UPDATE не закончит свою работу. Кроме того, вторая команда SELECT будет также ждать, пока не завершится первая команда SELECT!
Поток ждет ситуации заполненного диска. В таком случае все потоки, которые хотят получить доступ к проблемной таблице, будут переведены в состояние ожидания до тех пор, пока не освободится немного дискового пространства.
Ниже представлены некоторые возможные решения данной проблемы:

Постарайтесь заставить команды SELECT выполняться быстрее. Возможно, для этого необходимо будет создать сводные таблицы.
Запустите mysqld с --low-priority-updates. Этим вы назначите всем командам обновления таблицы более низкий приоритет, чем у команды SELECT. Тогда последняя команда SELECT в предыдущем сценарии будет выполняться перед командой INSERT.
Конкретным командам INSERT, UPDATE или DELETE можно назначить более низкий приоритет с помощью атрибута LOW_PRIORITY.
Запустите mysqld с небольшим значением max_write_lock_count, чтобы разрешить блокировки чтения (READ) после определенного количества блокировок записи (WRITE).
SQL-командой: SET SQL_LOW_PRIORITY_UPDATES=1 можно указать, что все обновления из конкретного потока должны выполняться с низким приоритетом. See section 5.5.6 Синтаксис команды SET.
Можно указать, что команда SELECT является очень важной, - с помощью атрибута HIGH_PRIORITY. See section 6.4.1 Синтаксис оператора SELECT.
Если имеются проблемы при выполнении команд INSERT совместно с SELECT, перейдите на новые таблицы MyISAM, которые поддерживают одновременное выполнение команд SELECT и INSERT.
Если совместно выполняются преимущественно команды INSERT и SELECT, то решить возникающие при этом проблемы иногда помогает атрибут DELAYED для INSERT. See section 6.4.3 Синтаксис оператора INSERT.
Если имеются проблемы с командами SELECT и DELETE, то может помочь опция LIMIT для DELETE. See section 6.4.6 Синтаксис оператора DELETE.