InnoDB реализует стандартную блокировку на уровне строк, где существуют два типа блокировок, и .

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

Если транзакция T1 держит эксклюзивную (X) блокировку на строке r, запрос от другой транзакции T2 на блокировку любого типа на r не может быть удовлетворён сразу. Вместо этого, транзакции T2 необходимо подождать, пока транзакция T1 освободит свою блокировку на строке r.

InnoDB поддерживает блокировку с множественной гранулярностью, что позволяет сосуществовать строчным и табличным блокировкам. Например, оператор, такой как LOCK TABLES ... WRITE, получает эксклюзивную блокировку (X блокировка) на указанной таблице. Для практического применения блокировки на нескольких уровнях гранулярности, InnoDB использует . Блокировки намерений — это блокировки на уровне таблицы, указывающие, какой тип блокировки (общая или эксклюзивная) транзакция требует позже для строки в таблице. Существует два типа блокировок намерений:

Например, SELECT ... FOR SHARE устанавливает IS блокировку, а SELECT ... FOR UPDATE устанавливает IX блокировку.

Протокол блокировки намерений следующий:

Совместимость типов блокировок на уровне таблицы суммируется в следующей матрице.

Блокировка предоставляется запрашивающей транзакции, если она совместима с существующими блокировками, но не если она конфликтует с существующими блокировками. Транзакция ожидает, пока конфликтующая существующая блокировка не будет освобождена. Если запрос на блокировку конфликтует с существующей блокировкой и не может быть удовлетворён, так как это приведет к , возникает ошибка.

Блокировки намерений ничего не блокируют, кроме полных запросов к таблице (например, LOCK TABLES ... WRITE). Основное назначение блокировок намерений — показать, что кто-то блокирует или собирается заблокировать строку в таблице.

Данные транзакции для блокировки намерений отображаются аналогично следующему в выводе SHOW ENGINE INNODB STATUS и мониторе InnoDB:

TABLE LOCK table `test`.`t` trx id 10080 lock mode IX

Блокировка записи — это блокировка записи индекса. Например, SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE; предотвращает любую другую транзакцию от вставки, обновления или удаления строк, где значение t.c1 равно 10.

Блокировки записей всегда блокируют записи индекса, даже если таблица определена без индексов. В таких случаях InnoDB создаёт скрытый кластеризованный индекс и использует этот индекс для блокировки записей. См. Раздел 17.6.2.1, «Кластеризованные и вторичные индексы».

Данные транзакции для блокировки записи отображаются аналогично следующему в выводе SHOW ENGINE INNODB STATUS и мониторе InnoDB:

RECORD LOCKS space id 58 page no 3 n bits 72 index `PRIMARY` of table `test`.`t`
trx id 10078 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 00000000274f; asc     'O;;
 2: len 7; hex b60000019d0110; asc        ;;

Замок щели — это замок на щели между записями индекса или замок на щели перед первой или после последней записи индекса. Например, SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE; предотвращает другие транзакции от вставки значения 15 в столбец t.c1, независимо от того, было ли такое значение уже в столбце, поскольку щели между всеми существующими значениями в диапазоне заблокированы.

Щель может охватывать одно значение индекса, несколько значений индекса или даже быть пустой.

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

Замки щелей не нужны для операторов, которые блокируют строки, используя уникальный индекс для поиска уникальной строки. (Это не включает случай, когда условие поиска включает только некоторые столбцы многостолбцового уникального индекса; в этом случае блокировка щели происходит.) Например, если столбец id имеет уникальный индекс, следующий оператор использует только блокировку записи индекса для строки со значением id равным 100, и не важно, вставляют ли другие сеансы строки в предшествующую щель:

SELECT * FROM child WHERE id = 100;

Если id не индексирован или имеет не уникальный индекс, оператор блокирует предшествующую щель.

Стоит также отметить, что разные транзакции могут удерживать конфликтующие замки на щели. Например, транзакция A может удерживать общий замок щели (замок щели S) на щели, в то время как транзакция B удерживает эксклюзивный замок щели (замок щели X) на той же щели. Причина, по которой разрешены конфликтующие замки щелей, заключается в том, что если запись удаляется из индекса, замки щелей, удерживаемые на записи различными транзакциями, должны быть объединены.

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

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

Также есть другие последствия использования уровня изоляции READ COMMITTED. Замки записей для строк, не соответствующих условию, освобождаются после того, как MySQL оценил условие WHERE. Для UPDATE операторов InnoDB выполняет «полупоследовательный» чтение, так что возвращает последнюю подтвержденную версию MySQL, чтобы MySQL мог определить, соответствует ли строка условию WHERE оператора UPDATE.

Замок следующей ключа — это комбинация замка записи на записи индекса и замка щели на щели перед записью индекса.

InnoDB выполняет блокировку на уровне строк таким образом, что при поиске или сканировании индекса таблицы он устанавливает общие или эксклюзивные замки на найденные записи индекса. Таким образом, блокировки на уровне строк на самом деле являются блокировками записей индекса. Замок следующей ключа на записи индекса также влияет на «щель» перед этой записью индекса. То есть, замок следующей ключа — это замок записи индекса плюс замок щели на щели, предшествующей записи индекса. Если у одного сеанса есть общий или эксклюзивный замок на записи R в индексе, другой сеанс не может вставить новую запись индекса в щель непосредственно перед R в порядке индекса.

Предположим, что индекс содержит значения 10, 11, 13 и 20. Возможные замки следующей ключа для этого индекса покрывают следующие интервалы, где круглая скобка обозначает исключение конечной точки интервала, а квадратная скобка обозначает включение конечной точки:

(negative infinity, 10]
(10, 11]
(11, 13]
(13, 20]
(20, positive infinity)

Для последнего интервала замок следующей ключа блокирует щель над наибольшим значением в индексе и псевдозаписью «супремум», имеющей значение выше любого значения, фактически присутствующего в индексе. Супремум — это не реальная запись индекса, поэтому, фактически, этот замок следующей ключа блокирует только щель, следующую за наибольшим значением индекса.

По умолчанию InnoDB работает в уровне изоляции транзакций REPEATABLE READ. В этом случае InnoDB использует замки следующей ключа для поисков и сканирования индексов, что предотвращает фиктивные строки (см. Раздел 17.7.4, «Фиктивные строки»).

Данные транзакции для замка следующей ключа выглядят аналогично следующему в SHOW ENGINE INNODB STATUS и выводе монитора InnoDB:

RECORD LOCKS space id 58 page no 3 n bits 72 index `PRIMARY` of table `test`.`t`
trx id 10080 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 00000000274f; asc     'O;;
 2: len 7; hex b60000019d0110; asc        ;;

Замок намерения вставки — это тип замка щели, устанавливаемый операциями INSERT перед вставкой строки. Этот замок сигнализирует о намерении вставить таким образом, что несколько транзакций, вставляющих в одну и ту же щель индекса, не должны ждать друг друга, если они не вставляют в одно и то же место в щели. Предположим, что есть записи индекса со значениями 4 и 7. Отдельные транзакции, которые пытаются вставить значения 5 и 6 соответственно, каждый блокируют щель между 4 и 7 с замками намерения вставки перед получением эксклюзивного замка на вставленную строку, но не блокируют друг друга, потому что строки не конфликтуют.

Следующий пример демонстрирует транзакцию, принимающую замок намерения вставки перед получением эксклюзивного замка на вставленную запись. В примере участвуют два клиента, A и B.

Клиент A создает таблицу, содержащую две записи индекса (90 и 102), а затем запускает транзакцию, которая устанавливает эксклюзивный замок на записи индекса с идентификатором больше 100. Эксклюзивный замок включает замок щели перед записью 102:

mysql> CREATE TABLE child (id int(11) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB;
mysql> INSERT INTO child (id) values (90),(102);

mysql> START TRANSACTION;
mysql> SELECT * FROM child WHERE id > 100 FOR UPDATE;
+-----+
| id  |
+-----+
| 102 |
+-----+

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

mysql> START TRANSACTION;
mysql> INSERT INTO child (id) VALUES (101);

Данные транзакции для замка намерения вставки выглядят аналогично следующему в SHOW ENGINE INNODB STATUS и выводе монитора InnoDB:

RECORD LOCKS space id 31 page no 3 n bits 72 index `PRIMARY` of table `test`.`child`
trx id 8731 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000066; asc    f;;
 1: len 6; hex 000000002215; asc     " ;;
 2: len 7; hex 9000000172011c; asc     r  ;;...

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

Переменная innodb_autoinc_lock_mode управляет алгоритмом, используемым для блокировки автоинкремента. Она позволяет вам выбрать, как обменять предсказуемые последовательности значений автоинкремента и максимальную одновременность для операций вставки.

Дополнительную информацию см. в Разделе 17.6.1.6, «Обработка AUTO_INCREMENT в InnoDB».

InnoDB поддерживает SPATIAL индексирование столбцов, содержащих пространственные данные (см. Раздел 13.4.9, «Оптимизация пространственного анализа»).

Для обработки блокировки для операций, включающих SPATIAL индексы, блокировка следующей ключа не подходит для поддержки уровня изоляции транзакций REPEATABLE READ или SERIALIZABLE. В многомерных данных нет абсолютного порядка, поэтому неясно, какой ключ является «следующим».

Для обеспечения поддержки уровней изоляции для таблиц с SPATIAL индексами InnoDB использует предикатные замки. SPATIAL индекс содержит минимальные ограничивающие прямоугольники (MBR) значений, поэтому InnoDB обеспечивает согласованное чтение по индексу, устанавливая предикатный замок на значение MBR, используемое для запроса. Другие транзакции не могут вставить или изменить строку, которая соответствовала бы условию запроса.