Как MySQL блокирует таблицы
Описание различных методов блокировки дается в приложении, в разделе Методы блокировки.
В MySQL все блокировки, кроме блокировок таблиц типов InnoDB и BDB, не создают тупиковых ситуаций. Это обеспечивается за счет того, что все необходимые блокировки всегда запрашиваются единожды в начале запроса и блокировка таблиц всегда происходит в одном и том же порядке.
В таблицах типа InnoDB устанавливается блокировка строк, а в таблицах типа BDB - блокировка страниц в процессе выполнения SQL-команд, а не в начале транзакции.
Метод блокирования, используемый в MySQL для блокировок записи (WRITE), работает следующим образом:
Если на данной таблице нет никаких блокировок, то на нее накладывается блокировка записи.
В противном случае запрос на данную блокировку ставится в очередь блокировок записи.
Метод блокирования, используемый в MySQL для блокировок чтения (READ), работает следующим образом:
Если на данной таблице нет блокировок записи, то на нее накладывается блокировка чтения.
В противном случае запрос на данную блокировку ставится в очередь блокировок чтения.
При освобождении текущей блокировки становится возможной следующая блокировка для потоков из очереди блокировок записи, затем для потоков из очереди блокировок чтения.
Это означает, что, если таблица подвергается многочисленным обновлениям, то команды SELECT будут ожидать, пока обновления не закончатся.
Чтобы обойти это в случае, когда для таблицы требуется выполнить много операций INSERT и SELECT, можно внести строки во временную таблицу и время от времени обновлять реальную таблицу записями из временной.
Для этого можно применить следующий код:
mysql> LOCK TABLES real_table WRITE, insert_table WRITE;
mysql> INSERT INTO real_table SELECT * FROM insert_table;
mysql> TRUNCATE TABLE insert_table;
mysql> UNLOCK TABLES;
В некоторых случаях, когда необходимо установить приоритеты извлечения данных, для команд INSERT, UPDATE или DELETE можно указывать опцию LOW_PRIORITY, а для команды SELECT - HIGH_PRIORITY. Тот же результат можно получить, запустив mysqld с --low-priority-updates.
Использование SQL_BUFFER_RESULT тоже способствует уменьшению времени блокировок таблиц. См. раздел Синтаксис оператора SELECT.
Можно также изменить блокировочный код в "mysys/thr_lock.c" - чтобы использовать только одну очередь блокировок. В этом случае блокировки записи и чтения будут иметь одинаковый приоритет, что может оказаться полезным для некоторых приложений.
Вопросы блокирования таблиц
Код блокирования таблиц в 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 можно указать, что все обновления из конкретного потока должны выполняться с низким приоритетом. См. раздел Синтаксис команды code>SET.
Можно указать, что команда SELECT является очень важной, - с помощью атрибута HIGH_PRIORITY. См. раздел Синтаксис оператора SELECT.
Если имеются проблемы при выполнении команд INSERT совместно с SELECT, перейдите на новые таблицы MyISAM, которые поддерживают одновременное выполнение команд SELECT и INSERT.
Если совместно выполняются преимущественно команды INSERT и SELECT, то решить возникающие при этом проблемы иногда помогает атрибут DELAYED для INSERT. См. раздел Синтаксис оператора INSERT.
Если имеются проблемы с командами SELECT и DELETE, то может помочь опция LIMIT для DELETE. См. раздел Синтаксис оператора DELETE.
8 8 8
| |
*
Обратная связь