Пользователь
0,0
рейтинг
9 декабря 2008 в 16:34

Разработка → Блокировки в MySQL

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

Механизм блокирования в MySQL


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

MySQL от имени одного из клиентов накладывает блокировку на определенный ресурс, при этом другие клиенты ждут освобождения блокировки. Блокировка может быть на уровне таблиц (блокируется таблица) или на уровне строк (блокируются определенные строки таблицы). В механизме хранения MyISAM (используемом по умолчанию) реализована табличная блокировка, а в механизме InnoDB построчная. Построчная блокировка достигается посредством усложнения структуры хранилища: в MyISAM структура файла с данными представляет собой простое перечисление строк таблицы, тогда как хранилище InnoDB структурировано и поддерживает мультиверсионность данных. Поэтому, InnoDB выигрывает в приложениях, в которых происходит многопоточное изменение данных в одну и ту же таблицу, несмотря на необходимые потери на обслуживание более сложного хранилища.

Блокировки бывают двух видов: на чтение и на запись.
  1. Если A хочет читать данные, то другие клиенты тоже могут читать данные, но никто не может записывать, пока А не закончит чтение (read lock).
  2. Если А хочет записать данные, то другие клиенты не должны ни читать ни писать эти данные пока А не закончит(write lock).
Блокировка может быть наложена явно или неявно.
  1. Если клиент не назначает блокировку явным образом, MySQL сервер неявно устанавливает необходимый тип блокировки на время выполнения выражения или транзакции. Например, в случае выполнения оператора SELECT сервер установит READ LOCK, а в случае UPDATE — WRITE LOCK. При неявной блокировке уровень блокировки зависит от типа хранилища данных: для MyISAM, MEMORY и MERGE блокируется вся таблица, для InnoDB — только используемые в выражении строки (в случае, если набор этих строк может быть однозначно определен значениями первичного ключа — иначе, блокируется вся таблица).
  2. Часто возникает необходимость выполнения нескольких запросов подряд без вмешательства других клиентов в это время. Неявная блоктровка не подходит для этих целей, так как устанавливается только на время выполнения одного запроса. В этом случае клиент может явно назначить, а потом отменить блокировку с помощью выражений LOCK TABLES и UNLOCK TABLES. Явной блокировка всегда блокирует всю таблицу, независимо от механизма хранения.

Использование явных блокировок


В случае явной блокировки можно получить выигрыш производительности за счет однократного назначения блокировки и задержки записи обновленных индексов до на время блокировки. При назначении явной блокировки, указываются имя таблицы и тип блокировки:LOCK TABLES Country READ, City WRITE;Оператор UNLOCK TABLES не имеет аргументов и снимает все блокировки, установленные явно в рамках текущей сессии.

Обратите внимание на следующие особенности явных блокировок:
  • Пока клиент удерживает явную блокировку, он не может использовать другие таблицы, поэтому блокировать нужно сразу все что понадобится (одним выражением), так как повторное использование оператора LOCK TABLES отменяет сделанные ранее блокировки.
  • Блокировка может быть установлена на представление (VIEW) начиная с версии 5.0.6. Для ранних версий, необходимо установливать блокировку на все таблицы, входящие в представление.
  • Разрыв соединения илм завершение сессии автоматически снимает все установленные в рамках данной сессии блокировки.
  • Блокировка таблиц может быть нарушена транзакцией и наоборот. START TRANSACTION неявным образом осуществляет UNLOCK TABLES и наоборот LOCK TABLES откатывает незаконченную транзакцию.
  • Для установки блокировки требуется иметь привилегии LOCK TABLES и SELECT на каждую блокируемую таблицу.
  • Если одна из требуемых таблиц заблокирована в рамках другой сессии, то оператор блокировки не выполнится пока все таблицы не освободятся.
Типы блокировок:
  • READ — блокирует таблицу для чтения. Все клиенты могут получать данные одновременно, но никто не может их изменять, даже тот клиент, который установил блокировку.
  • WRITE — блокирует таблицу для записи. Только клиент установивший блокировку может получать и изменять данные.
  • READ LOCAL — блокирует таблицу для чтения, но позволяет осуществлять вставку данных (INSERT). Применимо только к таблицам MyISAM, которые не имеют дыр, образованных в результате изменения или удаления строк. В этом случае, добавление новых данных производится в конец таблицы. Если таблица имеет дыры, то их можно устранить, используя оператор OPTIMIZE TABLE.
  • LOW_PRIORITY WRITE — блокирует таблицу для записи, но во время ожидания блокировки пропускает тех клиентов, которые стоят в очереди на получения блокировки типа READ. Во время ожидания блокировки, новые поступающие запросы на блокировку типа READ также пропускаются вперед, что может потенциально привести к тому, что запись не будет произведена никогда (если всегда есть клиенты в очереди на чтение).
Примечание: системная переменная concurrent_inserts определяет возможность добавления данных в конец таблицы MyISAM, заблокированной для чтения. По умолчанию, эта переменная равна 1, что означает возможность добавления данных при отсутствии в таблице дыр. Таким образом, в случае неявной блокировки, для таблиц MyISAM устанавливается READ LOCAL, а не READ. Значение concurrent_inserts равное 0 запрещает добавление данных параллельно с чтением, а равное 2 разрешает вставку данных в конец таблицы даже при наличии в таблице дыр.

Заключение


В статье рассмотрены лишь основные вопросы использования блокировок в MySQL. Не освещены транзакции, уровни изоляции, advisory lock и др. Напишите в комментариях какие из вопросов вам интересны.

Кросспост Блокировки в MySQL с Webew.
retvizan @retvizan
карма
39,0
рейтинг 0,0
Реклама помогает поддерживать и развивать наши сервисы

Подробнее
Спецпроект

Самое читаемое Разработка

Комментарии (18)

  • +5
    Мне очень интересны транзакции

    Долгое время в какой-то мере успешно боремся с Lock и Deadlock в многопоточных highload скриптах, работающих с InnoDB — уверен, что можно бороться эффективнее)
    • +2
      Обязательно напишу, только это будет, наверное, после нового года.
    • +1
      Если возможно, попробуйте упростить транзакции. Если нет, то попробуйте сделать структуру таблиц такую, что SELECT и UPDATE будут обращаться к разным таблицам, в основном.
      • +2
        А можно с этого места и поподробнее?
        • +1
          Блокировки возникают чаще всего из-за того, что тот, кто читает ждет того, кто пишет. Причем в Innodb это на уровне строк. Бывает, что некоторые колонки в таблице часто обновляются, но редко читаются. Самый типичный пример UPDATE news SET viewcount = viewcount+1 WHERE id=987; В этом примере достаточно перенести статистику просмотров в другую таблицу и на таблице news почти не останется апдейтов, что дает выйгрыш (и эффективное использование кэша). Если хотите подробнее, опишите конкретную ситуацию на форуме SQLinfo.ru.
          • +2
            Да, довольно часто встреченщь такие вещь.

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

            спасибо за урл, буду обращатся по мере надобности ;)

      • 0
        В InnoDB все проще. Вступает в силу механизм транзакций и версионность.
        Чтение в большинстве случаев и не блокируется. Просто во время чтения вы и не увидите изменений, вызванных текущими транзакциями. Просто будете читать старую версию.
        В мане все описано неплохо InnoDB and TRANSACTION ISOLATION LEVEL + Consistent Non-Locking Read.
        • 0
          Все будет так, если одни транзакции только пишут, а другие — только читают. Если транзакция считывает данные (возможно не одну строку, а информацию, основанную на многих строках таблицы, не идентифицируемых по первичному ключу), а потом выполняет запись, то в зависимости от уровня изоляции блокировка будет при записи или при выполнении COMMIT.
    • +1
      Если Вы действительно занимаетесь хайлоад проектами, то данная статья вам должна быть абсолютно бесполезной, т.к. LOCK TABLES это самое большое зло, которое и врагу не пожелаешь, и если вы его используете где-то, вам стоит многое в жизни переосмыслить
      • +1
        Lock tables я и не использую, используя транзакции и лок строк в InnoDB
  • +2
    Возможно, еще было бы неплохо снабдить примерами эту статью, ну и следующие тоже, чтобы можно было запустить у себя и протестировать.

    А, вообще, спасибо :)
  • +3
    Собственно замечания к статье.

    Описание механизма LOCK TABLES это конечно круто, но кому это реально нужно?

    Если уже писать статью о блокировках, то нужно писать их в контексте транзакций и 4х степеней изоляции транзакций, в контексте InnoDB чтаблиц с их MVCC, по крайней мере объяснить почему в MyISAM есть только убогий LOCK TABLE и сразу сказать почему его не стоит использовать особенно в веб приложениях.

    LOCK TABLE часто используется в нетранзакционных сторидж енжинах для создания псевдо-транзакций, т.к. он реализован на уровне сервера, а не на уровне сторидж енжина. Я даже видел как некоторые использовали LOCK TABLE для InnoDB!!!

    В общем то, о чем реально нужно было писать
    SELECT… LOCK IN SHARE MODE
    SELECT… FOR UPDATE
    в транзакциях

    • +2
      Спасибо за ценный комментарий.

      Вообще-то в черновом варианте даже написал про FOR UPDATE и LOCK IN SHARE MODE, но потом решил что в отрыве от InnoDB, а соответственно и транзакций с их уровнями изоляций это будет не в тему. Всё-таки эта статья предназначена для новичков, чтобы было понимание, что такое блокировки и с чем их едят, а уже потом можно рассматривать особенности различных механизмов хранения.
  • +1
    сегодня читал про блокировку файлов в пхп при работе с ними, и сразу задался вопросом «А есть ли что-то подобное в МуСКУЛе?».
    спасибо=) в мемориз=)
    • +1
      Если вы читали про блокировки файлов в пхп, то вам стоит почитать про блокировки в SQLight там это действительно тоже LOCK на файловом уровне, а в MySQL есть средства гораздо более грамотной блокировки.
    • 0
      Кому интересно…
      Блокировка файлов (в php & perl) на запись реализована наподобие «LOW_PRIORITY WRITE», т.е. читающие процессы пропускаются вперёд. Хотя это можно и предотвратить путём некоторого механизма блокировки дополнительных файлов, но обычно это требуется, если «читателей» на порядок больше «писателей» при большой интенсивности чтения-записи.
  • 0
    Спасибо. Теперь я понял, почему INSERT DELAYED отсутствует, да и вообще не нужен в InnoDB (=
  • 0
    Столкнулся с проблемой поиска причин длительных блокировок (у меня InnoDB)
    Почерпнул информацию из этой статьи:
    www.xaprb.com/blog/2006/07/31/how-to-analyze-innodb-mysql-locks/

    Если вкратце:
    mysql не показывает, какие блокировки установлены на данный момент (возможно в будущих версиях появятся инструменты для этого, или уже появились, но я о них не знаю).
    Однако, косвенно можно оценить, кто запросил блокировку, командой SHOW ENGINE INNODB STATUS (лучше SHOW ENGINE INNODB STATUS \G, чтобы было более читаемо)
    В выводе есть список транзакций, и если какая-то из них висит долгое время, то можно предположить, что в этой транзакции и кроется проблема, там же будет id mysql-потока, который можно искать в show processlist и в бинлогах.

Только зарегистрированные пользователи могут оставлять комментарии. Войдите, пожалуйста.