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

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

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

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

Например, SELECT ... LOCK IN SHARE MODE устанавливает 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 создает скрытый кластеризованный индекс и использует этот индекс для блокировки записей. См. Раздел 14.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 или включите переменную системы innodb_locks_unsafe_for_binlog (которая сейчас устарела). В этом случае блокировка замочной скважины отключена для поисков и сканирования индексов и используется только для проверки ограничений внешних ключей и проверки дублирующихся ключей.

Также есть другие последствия использования уровня изоляции READ COMMITTED или включения innodb_locks_unsafe_for_binlog. Блокировки записей для строк, не соответствующих условиям, освобождаются после того, как 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 использует блокировки следующего ключа для поиска и сканирования индексов, что предотвращает «фантомные строки» (см. Раздел 14.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 управляет алгоритмом, используемым для блокировки автоинкремента. Она позволяет выбрать, как скомпрометировать между предсказуемыми последовательностями значений автоинкремента и максимальной конкурентностью для операций вставки.

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

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

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

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