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)

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

По умолчанию 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, используемое для запроса. Другие транзакции не могут вставлять или изменять строку, которая соответствовала бы условию запроса.