Пользователь
0,0
рейтинг
29 сентября 2014 в 13:06

Разработка → Блокировки и уровни изоляции транзакций InnoDB в MySQL

SQL*, MySQL*
Здравствуй, Хабр!
Предлагаю всем желающим вспомнить или познать суть блокировок движка InnoDB в MySQL.


КДПВ: deadlock в исполнении тропической фауны



Базовые понятия


Все, думаю, уже знают, что InnoDB использует блокировки на уровне строк. В зависимости от уровня изоляции транзакции могут блокироваться как строки, попавшие в результирующую выборку, так и все строки, что были просмотрены при поиске. Например, в REPEATABLE READ блокирующий запрос без использования индекса потребует перебора всей таблицы, а следовательно и блокировки всех записей. Посему помни, %username%, правильный выбор индексов напрямую влияет на скорость работы блокировок.

Есть два базовых типа блокировок:
  • shared lock — совместная блокировка, позволяет другим транзакциям читать строку и ставить на нее такую же совместную блокировку, но не позволяет изменять строку или ставить исключительную блокировку.
  • exclusive lock — исключительная блокировка, запрещает другим транзакциям блокировать строку, а также может блокировать строку как на запись, так и на чтение в зависимости от текущего уровня изоляции (о коих ниже).

Если копнуть глубже, то выяснится, что есть еще 2 типа блокировок, назовем их блокировками «о намерениях». Нельзя просто так взять и заблокировать запись в InnoDB. Блокировки intention shared и intention exclusive являются блокировками на уровне таблицы и блокируют только создание других блокировок и операции на всей таблице типа LOCK TABLE. Наложение такой блокировки транзакцией лишь сообщает о намерении данной транзакции получить соответствующую совместную или исключительную блокировку строки.

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

InnoDB накладывает блокировки не на сами строки с данными, а на записи индексов. Та или иная блокировка может накладываться на:
  • record lock — блокировка записи индекса
  • gap lock — блокировка промежутка между, до или после индексной записи
  • next-key lock — блокировка записи индекса и промежутка перед ней

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

Все описанное выше определяется InnoDB неявно, вам нужно лишь представлять, что происходит «под капотом».
О том какие именно запросы накладывают блокировки, можно посмотреть опять же в документации.

Ну и перед тем как перейти к уровням изоляции, рассмотрим понятие согласованного чтения (consistent read).
В момент первого запроса в транзакции создается снэпшот данных БД (т.н. read view), на который не влияют изменения в параллельных транзакциях, но влияют изменения в текущей. Чтение из такого снэпшота называют неблокирующим согласованным чтением. Неблокирующим — потому что для создания такого снэпшота не требуется навешивание блокировок, согласованным — потому что никакие катаклизмы во внешним мире (кроме DROP TABLE и ALTER TABLE) не повлияют на уютный мирок снэпшота. InnoDB можно попросить сделать снэпшот и до первого запроса в транзакции, для этого нужно упомянуть об этом во время старта транзакции — START TRANSACTION WITH CONSISTENT SNAPSHOT.

Уровни изоляции транзакций InnoDB


Уровень изоляции можно изменить запросом SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL.

REPEATABLE READ (значение по умолчанию)

  • Согласованное чтение (SELECT) ничего не блокирует, читает строки из снэпшота, который создается при первом чтении в транзакции. Одинаковые запросы всегда вернут одинаковый результат.
  • Для блокирующего чтения (SELECT… FOR UPDATE/LOCK IN SHARE MODE), UPDATE и DELETE блокировка будет зависит от типа условия. Если условие уникально (WHERE id=42), то блокируется только найденная индексная запись (record lock). Если условие с диапазоном (WHERE id > 42), то блокируются весь диапазон (gap lock или next-key lock).

READ COMMITED

  • Согласованное чтение ничего не блокирует, но каждый раз происходит из свежего снэпшота.
  • Блокирующее чтение (SELECT… FOR UPDATE/LOCK IN SHARE MODE), UPDATE и DELETE блокирует только искомые индексные записи (record lock). Таким образом возможна вставка параллельным потоком записей в промежутки между индексами. Промежутки блокируются (gap lock) только при проверках внешних ключей и дублирующихся ключей. Также блокировки просканированных строк (record lock), не удовлетворяющих WHERE, снимаются сразу же после обработки WHERE.

READ UNCOMMITED (самый слабый уровень)

  • Все запросы SELECT читают в неблокирующей манере. Изменения незавершенной транзакции могут быть прочитаны в других транзакциях, а изменения эти могут быть еще и впоследствии откачены. Это так называемое «грязное чтение» (несогласованное).
  • В остальном все так же, как и при READ COMMITED.

SERIALIZABLE (самый строгий уровень)

  • Аналогичен REPEATABLE READ, за исключением одного момента. Если autocommit выключен (а при явном старте транзакции он выключен), то все простые запросы SELECT неявно превращаются в SELECT… LOCK IN SHARE MODE, если включен — каждый SELECT идет в отдельной транзакции. Используется, как правило, для того чтобы превратить все запросы чтения в SELECT… LOCK IN SHARE MODE, если этого нельзя сделать в коде приложения.


One more thing...


И напоследок пара упомянутых в тексте штук, про которые следует знать.

SELECT… LOCK IN SHARE MODE — блокирует считываемые строки на запись.
Другие сессии могут читать, но ждут окончания транзакции для изменения затронутых строк. Если же в момент такого SELECT'а строка уже изменена другой транзакцией, но еще не зафиксирована, то запрос ждет окончания транзакции и затем читает свежие данные. Данная конструкция нужна, как правило, для того чтобы получить свежайшие данные (независимо от времени жизни транзакции) и заодно убедиться в том, что их никто не изменит.

SELECT… FOR UPDATE — блокирует считываемые строки на чтение. Точно такую же блокировку ставит обычный UPDATE, когда считывает данные для обновления.

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

Например, в сценарии считать → изменить → записать обратно между считать и записать параллельная транзакция может изменить данные, но это изменение будет тут же затерто текущей транзакцией при записи обратно. LOCK IN SHARE MODE в данном примере не даст вклиниться соседней транзакции, ей придется подождать. Заметьте, что в данном случае блокировка будет ставиться дважды, сначала совместная блокировка при чтении, затем исключительная при записи. Так как блокировок две, то есть теоретический шанс проскочить третьей между ними и вызвать deadlock.

Отличие FOR UPDATE в том, что он сразу ставит исключительную блокировку, такую же, как и обычный UPDATE. Таким образом для сценария считать → изменить → записать обратно блокировка будет ставиться только один раз в момент считывания. Такой вариант снизит вероятность возникновения взаимных блокировок.
Александр @MastaEx
карма
25,0
рейтинг 0,0
Реклама помогает поддерживать и развивать наши сервисы

Подробнее
Реклама

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

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

  • +5
    Снова перевод документации на хабре. Но порадуемся за тех, кто программирует по харбрахабру, им будет полезно.
    • +6
      Я наверно еще слишком неопытный разработчик, и у меня не было необходимости разбираться в блокировках, лишь поверхностно почитав. Так вот, мне кажется что даже такие, как Вы выразились «переводы документации» все равно полезны, по крайней мере я углубил хоть и немного свои знания, и при необходимости буду знать куда копать)
    • +9
      Обязательно порадуемся. Только это не перевод документации. Безусловно она тут присутствует в какой-то степени, было бы странно ее не использовать (добавить небылиц от себя?). В доках вся информация раскидана по куче статей, здесь же пересказана в кратком виде с пояснениями и примерами.
  • +1
    Кода-то давно, когда я работал с 1С, была очень хорошая дока с описанием уровней изоляций транзакций, она мне очень нравилась тогда. Я ее себе сохранил.

    Ссылка.

    Много про сам 1С, но понять смысл можно. Кто бы сделал реферат? Так же есть базовое описание что такое блокировка вообще.
  • 0
    SELECT… FOR UPDATE — блокирует считываемые строки на чтение.

    на чтение для обновления? или вообще любое чтение будет заблокировано?
    • 0
      Да, блокируется чтение для обновления и для lock in share mode тоже, т.е. любое блокирующее. Простое неблокирующее чтение (то, что из снэпшота) игнорирует блокировки.
  • –1
    Какое счастье, что когда-то кто-то изобрел MVCC…
    • 0
      select… for update как раз позволяет бороться с этим «счастьем», но иногда да, MVCC бывает полезно
      • 0
        Ну, в большинстве случаев стандартное поведение с откатом и повтором вполне устраивает. Что важнее — оно более интуитивно понятно, и тяжелее выстрелить себе в ногу дедлоком.
        • 0
          Ох, я вас уверяю, MVCC в непривычного человека стреляет куда больнее. Deadlock по крайней мере трудно не заметить.
          • 0
            Ну так и конфликт при записи не заметить тоже очень сложно.

            Если вы имели в виду что-то другое — поясните, пожалуйста.
            • 0
              Пример хорошо описан у Кайта, повторять не буду. Вкратце: человек переходит (например на Oracle) с блокировочника и «ваяет» систему, по привычке держа в голове, что писатели блокируют читателей и наоборот. Получается нехорошо, лечится for update-ом. С подобной ситуацией я сталкивался лично, когда софт одного крупного мобильного оператора писался людьми, до этого плотно работавшими с MS SQL 2000 и имевшими об Oracle отдаленное представление.
              • 0
                Ну это вами же описан классический случай подхода, «не надо RTFM, разберемся по ходу дела». Но я, опять же, не вижу принципиальной разницы с таким же переходом в обратную сторону, когда человек с MVCC переходит на блокирующий сервер и получает дедлок в той ситуации, которая успешно разруливается MVCC. И в том, и в другом случае опасность в том, что на это можно нарваться не сразу, а погодя (в продакшне, например). Но когда нарываешься, то диагностика в обоих случаях вполне однозначная.
                • 0
                  Именно так. MVCC — хороший инструмент (разумеется с собственными накладными расходами), но надо уметь им правильно пользоваться, чтобы не прострелить себе ногу. Высказывания о том какое это «счастье», на мой взгляд, не побуждают к чтению документации и поиску пятен на Солнце. И это плохо.
                  • 0
                    Мне кажется, вы все-таки смотрите слишком однобоко. Если оставить в стороне ситуации типа кто-то переходит с чего-то, и рассмотреть абстрактного разработчика в вакууме, который осваивает это все с нуля, то объяснить ему поведение снэпшотов в MVCC, и как с ними правильно работать, на мой взгляд, в разы проще («всегда будь готов к тому, что записывающая транзакция при коммите может обломиться, и её надо будет повторить»). А вот с блокировками надо писать статьи, подобные этой, где детально разбирается поведение блокировщика — кто там на что берет какой лок — разные уровни изоляции etc.

                    При этом снэпшоты обеспечивают поведение от оптимального до «good enough» в большинстве случаев без каких-либо приседаний вообще. С блокировками, опять же, надо очень внимательно продумавать, какие уровни изоляции где использовать, и как все это будет взаимодействовать друг с другом.
                    • 0
                      В своей жизни я крайне редко встречаю абстрактных разработчиков в вакууме, но зато довольно часто имею дело с поделиями вполне конкретных разработчиков, плохо знакомых с мат. частью. Возможно поэтому я смотрю на этот вопрос несколько однобоко.
                • 0
                  Ну и по поводу «стреляет больнее». Возможность Deadlock-а можно не заметить, но если он случается достаточно часто (а так оно обычно и бывает), это будет заметно и за это разработчика будут долбать, пока он его не исправит. Ошибки проектирования связанные с непониманием MVCC коварнее. Выявить их можно, как правило, только анализируя некорректное поведение системы. И, как правило, такое некорректное поведение бывает связано с чьими-то денежными средствами.
    • 0
      А чего вы человека заминусовали-то? Это ж не сарказм, вроде бы… :-)
      Приложения обычно больше читают, нежели пишут. MVCC позволяет читателям не стоять в очереди и не мешать писателям.
      • 0
        Человека я НЕ заминусовал. Минус на комментарий. Почему — объяснил выше.
  • 0
    Так как блокировок две, то есть теоретический шанс проскочить третьей между ними и вызвать deadlock.

    Я так понимаю, что это происходит из-за того, что чтобы поставить эксклюзивную блокировку FOR UPDATE надо сначала снять блокировку от изменения LOCK IN SHARE MODE. В этот промежуток между двумя блокировками и успевает вклиниться другая транзакция, так? Это актуально только для MySQL или все реляционные БД ведут себя подобным образом?
    • 0
      Верно. В простейшем виде блокировки, это некая очередь. Если блокировке мешает предыдущая, она не отваливается с ошибкой, она просто встает в очередь. Как только мешающая блокировка будет снята, следующая по очереди вступит в силу. Две блокировки — два действия постановки в эту очередь, соответственно между этими действиями может кто-то еще успеть встать в эту очередь между ними.

      В базовом виде, думаю, суть с постановкой в очередь примерно одинакова в различных СУБД, но, полагаю, что у всех есть алгоритмы для предотвращения deadlock'ов, которые работают с этой очередью и могут ее тасовать каким-нибудь хитрым образом. Утверждать не берусь, ибо не знаю.
  • 0
    Господа, а кто-нибудь может привести более-менее внятный реальный пример, когда нужно использовать SERIALIZABLE / SELECT… LOCK IN SHARE MODE? Мне не доводилось сталкиваться с такими случаями и сходу придумать могу. А то в статье рассмотрен пример, когда не надо его использовать, а надо использовать SELECT… FOR UPDATE (с такими случаями я как раз сталкивался).
    • 0
      SERIALIZABLE — это когда сделал на read committed и select for update, протестировал, задеплоил и через год посыпались deadlock-и и срочно стало нужно спасать ситуацию…
    • 0
      Представьте дерево или граф, которые пользуются и в хвост и в гриву :-)
      Нужно вставить дочерний узел. Перед вставкой нужно убедиться в существовании родителя. Делаем SELECT, убеждаемся, делаем вставку и получаем «foreign key constraint fails» — родителя кто-то успел грохнуть. LOCK IN SHARE MODE в данной ситуации не позволит никому потереть родителя до завершения транзакции, а FOR UPDATE будет излишним, так как не даст другим читать родителя.

      SERIALIZEABLE. Вот сваяли вы соц сеть, а с приходом популярности в логах стали появляться ошибки из примера выше. Включаете SERIALIZEABLE и идете рыть код… ну или пиво пить.
      • 0
        Спасибо за развёрнутый пример, про удаление связанных данных не подумал… А для SERIALIZEABLE, как я понял, для неаварийной ситуации в продакшене примера нет, т.к. не существует?
        • 0
          как вариант — обработчик биржевых операций. всё строго по порядку. хотя, не лучший пример.

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