19 Jun 2014

InnoDB: транзакции и блокировки

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

InnoDB следует принципам ACID (Atomicity, Consistency, Isolation, Durability). В рамках обсуждаемой темы нас интересуют два принципа: атомарность и изоляция, которые обеспечиваются в основном за счет транзакций и блокировок.

Транзакции

Для обеспечения атомарности любая последовательность операций выполняется в рамках своей транзакции, т.е. все изменения выполняемые транзакцией либо применяются - COMMIT, либо откатываются - ROLLBACK. По-умолчанию каждая новая сессия работает в режиме автоматического коммита. В этом режиме любое SQL-выражение либо будет автоматически закоммичено (при отсутсвии ошибок), либо будет откатано назад (при возникновении ошибки поведение коммита и роллбека зависит от типа ошибки, см. Error Handling). Чтобы выполнять последовательность операций без автокоммита, можно либо воспользоваться выражениями START TRANSACTION / BEGIN, либо выключить автокоммит с помощью выражения SET autocommit = 0. При выключенном автокоммите транзакция будет открыта до тех пор пока явно не выполнится COMMIT или ROLLBACK, которые завершат текущую тразакцию и начнут новую. При выполнении COMMIT или ROLLBACK все блокировки установленные внутри транзакции снимаются.

Изоляция транзакций в InnoDB реализована в соответсвии со стандартом SQL-92 в виде четырех уровней: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE. По-умолчанию используется REPEATABLE READ, т.к. он обычно является более подходящим под требования, чем остальные. Реже используется READ COMMITED, например в ситуациях когда нужно повысить concurrency и можно немного забить на изоляцию. READ UNCOMMITED и SERIALIZABLE используются редко, т.к. это крайности: либо никакой изоляции, либо полная изоляция. Пользователь может изменить уровень изоляции для текущей сессии или для всех новых сессий с помощью выражения SET TRANSACTION. Перед тем как рассмотреть что из себя представляют эти уровни изоляции, стоит кратко пояснить что чтение бывает двух типов: консистентное неблокирующее (Consistent Nonlocking Reads - обычные SELECT) и блокирущее (Locking Reads - SELECT ... FOR UPDATE и SELECT ... LOCK IN SHARE MODE). О типах чтения и блокировках поговорим подробнее чуть позже. Итак, рассмотрим что же из себя представляют уровни изоляции транзакций:

  1. READ UNCOMMITTED. Самый низкий уровень изоляции. При этом уровне изоляции для неблокирующих чтений возможны ситуации когда будут использованы ранние версии данных. Такие чтения будут неконсистентны, их еще называют “грязным чтением”. В остальном этот уровень работает так же как READ COMMITED.
  2. READ COMMITED. При этом уровне изоляции каждое неблокирующее чтение работает со своим собственным свежим снимком данных, в том числе и в рамках одной транзакции. При выполнении блокирующих чтений, UPDATE и DELETE будут блокироваться только индексные записи (record lock) и не будут блокираться интервалы (gaps). Поэтому данный уровень изоляции позволяет вставлять новые строки в интервалы перед индексными записями. Такие строки называют фантомными. Уровень REPEATABLE READ решает данную проблему.
  3. REPEATABLE READ. При этом уровне изоляции каждое неблокирующие чтение (так же как и при READ COMMITED) работает со своим снимком данных, но снимок этот не обновляется в рамках одной транзакции, а используется тот что был получен при первом чтении. Т.е. каждое неблокирующее чтение в рамках одной транзакции является консистентным по отношению к остальным. Поведение блокирующего чтения, UPDATE и DELETE зависит от уникальности индекса и уникальности условия поиска. Когда и индекс и условие поиска уникально - блокируется только найденная индексная запись и не блокируется интервал перед ней. В остальных случаях InnoDB будет блокировать некоторый индексный интервал, используя так называемую интервальную блокировку gap lock или блокировку следующего ключа next-key lock. Блокировки интервалов и следующего ключа позволяют избежать фантомных чтений (Phantom Problem).
  4. SERIALIZABLE. Этот уровень аналогичен REPEATABLE READ, но InnoDB будет неявно преобразовывать все неблокирующие чтения (SELECT), в блокирующие (SELECT ... LOCK IN SHARE MODE) если автокоммит выключен. В случае если автокоммит включен, неблокирующее чтение будет единственным оператором в транзакции, т.е. понятно что транзакция будет read only, соответственно не имеет смысла блокировать записи такой транзакцией.

Блокировки

В InnoDB блокировки происходят на уровне строк, что позволяет различным транзакциям работать с одними и теми же таблицами одновременно. В случае когда транзакция пытается изменить строку, в которую уже вносит изменение другая транзакция, происходит блокировка, т.е. ожидание пока первая транзакция не закончит работу с изменяемой строкой. Любой вид блокировки строк - это по сути блокировка индексов. В случае если в таблице не объявленны уникальные индексы, InnoDB создаст скрытый ключ в виде номера строки (подробнее см. Clustered and Secondary Indexes) и будет вешать блокировки на них. Посмотреть какие индексы есть в таблице можно с помощью команды SHOW INDEX FROM <table name>.

Существует три типа блокировок:

  1. блокировка индексной записи (record lock) - такая блокировка происходит, если условие запроса ограничивает только одну запись уникального индекса (unique index); например, если в таблице t поле c1 является уникальным индексом и существует запись для которой с1 = 10, то при выполнении блокирующего чтения SELECT * FROM t WHERE c1 = 10 FOR UPDATE InnoDB установит блокировку на этот индекс и не допустит чтобы другая транзакция вставила, обновила или удалила строку с полем с1 = 10; если выполнить тот же запрос, но записи с полем с1 = 10 (а соответственно и записи индекса) не будет существовать, то это уже будет блокировка интервала
  2. блокировка интервала (gap lock) - происходит когда блокируется интервал между индексными записями, интервал до первой индексной записи или интервал после последней индексной записи; допустим что в таблице есть две строки для которых с1 = 10 и c1 = 20, т.е. индекс содержит значения 10 и 20; интервалами будут являтся следующие отрезки: (минус бесконечность, 10), (10, 20), (20, плюс беконечность); если мы выполним запрос на блокирующее чтение несуществующей пока записи SELECT * FROM t WHERE c1 = 15 FOR UPDATE, то будет блокирован интервал от 10 до 20, но не включительно, т.е. обновить граничные записи можно, можно даже их удалить, а вот вставка новой строки в этот интервал будет блокирована; еще один интересный пример: если выполнить предыдущий запрос на блокирующее чтение строки, но таблица t будет пуста, то заблокируется интервал, размером во все индексное пространство, т.е. вся таблица
  3. блокировка следующего ключа (next-ket lock) - комбинация блокировок индексных записей и блокировок интервалов; возьмем предыдущий пример, но выполним другой запрос: SELECT * FROM t WHERE c1 > 15; в данном случае помимо индекса со значением 20 заблокируются также интервалы (10, 20) и (20, плюс бесконечность); при этом строку с индексом 10 можно изменять, т.к. она не блокируется; в общем случае блокируемых индексных интервалов и индексных записей может быть гораздо больше, все зависит от условий блокирующего запроса

Таким образом видно, что InnoDB использует блокироки интервалов и блокировки следующего ключа так, чтобы полностью покрыть условие поиска блокирующего запроса и при этом заблокировать наименьшее возможное количество индексного пространства. Рассмотрим как блокировки интервалов и блокировки следующих ключей решают проблему фантомных чтений (Phantom Problem). Фантомное чтение - это когда в рамках одной транзакции возвращается разные результаты. Возьмем последний пример и допустим что для текущей транзакции установлен уровень изоляции READ UNCOMMITTED или READ COMMITED. Выполним блокирующее чтение SELECT * FROM t WHERE c1 BETWEEN 12 AND 18 и получим пустое множество. Теперь в другой транзакции (с любым уровнем изоляции) выполним запрос на вставку строки: INSERT INTO t (c1) VALUES (15). В первой транзакции повторим то же самое блокирующее чтение и в результате получим фантомную строку с полем c1 = 15, вставленную второй транзакцией. Если попробовать провернуть все то же самое но установив для первой транзакции уровень изоляции REPEATABLE READ или SERIALIZABLE, то при попытке вставки строки второй транзакцией она будет блокирована, т.к. первая транзакция заблокирует индексный интервал (10, 20). Таким образом блокирующее чтение будет возвращать один и тот же результат (пустое множество), независимо от действий других транзакций. Блокировка следующего ключа избавляет от фантомных чтений аналогично, с той лишь разницей что в этих блокировках могут участвовать несколько индексных записей и интервалов.

Режимы работы блокировок

InnoDB реализует блокировки строк в двух режимах: чтения (shared (S) lock) и записи (exclusive (X) lock).

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

Если транзакция T1 обладает блокировкой чтения на строку r, то запрос от второй транзакции T2 на блокировку той же строки r обрабатывается следующим образом:

  1. запрос на блокировку чтения может быть выдан сразу же, т.е. в результате обе транзакции T1 и T2 будут обладать блокировками чтения на строку r
  2. запрос на блокировку записи не может быть выдан до тех пор, пока транзакция T1 не освободит блокировку чтения на строку r

Если транзакция T1 обладает блокировкой записи на строку r, то запрос от второй транзакции T2 на блокировку любого режима будет приостановлен до тех пор пока транзакция T1 не освободит блокировку записи на строку r.

Для поддежки различной гранулярности блокировки данных, в InnoDB существуют так называемые целевые блокировки (intention locks), которые работают не на уровне строк, а на уровне таблиц. Целевые блокировки предназначены для того чтобы обозначить намерение транзакции получить в будующем блокировки чтения или записи на некоторые строки в данной таблице. Если транзакция собирается получить в таблице только блокировки чтения, то это целевая блокировка чтения (intention shared (IS) lock). Если транзакция намерена получить блокировки записи, то это целевая блокировка записи (intention exclusive (IX) lock). Например, выражение SELECT ... LOCK IN SHARE MODE установит на таблицу целевую блокировку чтения (IS lock), а выражение SELECT ... FOR UPDATE установит целевую блокировку записи (IX lock).

Перед тем как установить блокировку чтения (S lock) на строку в таблице, транзакция должна сначала установить целевую блокировку чтения (IS lock) или блокировку уровнем строже на эту таблицу. Перед тем как получить блокировку записи (X lock) на строку в таблице, транзакция должна сначала установить целевую блокировку записи (IX lock) на эту таблицу. Совместимость блокировок представлена в таблице ниже:

X IX S IS
X Конфликт Конфликт Конфликт Конфликт
IX Конфликт Совместимо Конфликт Совместимо
S Конфликт Конфликт Совместимо Совместимо
IS Конфликт Совместимо Совместимо Совместимо

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

Целевые блокировки блокируют только запросы на всю таблицу, например LOCK TABLES ... WRITE. Основное предназначение целевых блокировок - это обозначить что транзакция уже блокирует или собирается заблокировать строки в таблице.