Почему ваша вставка заблокирована?

Введение

Я думал, что системе нужно только изучить относительно полную логику, но на самом деле это требует большого количества знаний, связанных с блокировкой innodb, и методов блокировки. Перечислять и анализировать процесс блокировки различных сценариев у меня, похоже, нет возможности, также не так много сил проверять достоверность онлайн-выступлений.

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

2. Статус на месте

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

1. Версия базы данных

``mysql> select version();
+-----------+
| version() |
+-----------+
| 5.6.27 |
+-----------+

2. Механизм базы данных

``mysql> show variables like '%engine%';
+----------------------------+--------+
| Variable_name | Value |
+----------------------------+--------+
| default_storage_engine | InnoDB |
| default_tmp_storage_engine | InnoDB |
| storage_engine | InnoDB |
+----------------------------+--------+

Примечание: InnoDB поддерживает транзакции, Myisam не поддерживает транзакции, InnoDB поддерживает блокировки строк и таблиц, Myisam не поддерживает блокировки строк.

3. Уровень изоляции транзакций

``mysql> select @@global.tx_isolation, @@session.tx_isolation, @@tx_isolation;
+-----------------------+------------------------+-----------------+
| @@global.tx_isolation | @@session.tx_isolation | @@tx_isolation |
+-----------------------+------------------------+-----------------+
| REPEATABLE-READ | REPEATABLE-READ | REPEATABLE-READ |
+-----------------------+------------------------+-----------------+

Примечание. Несколько уровней изоляции транзакций: READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE

4. Проверьте состояние гэп-лока

``mysql> show variables like 'innodb_locks_unsafe_for_binlog';
+--------------------------------+-------+
| Variable_name | Value |
+--------------------------------+-------+
| innodb_locks_unsafe_for_binlog | OFF |
+--------------------------------+-------+

innodb_locks_unsafe_for_binlog: значение по умолчанию — 0, что включает блокировку пробелов. Основная функция — контролировать, блокирует ли innodb разрыв. Однако это изменение параметра не влияет на потребность во внешних ключах и уникальных индексах (включая первичные ключи) для блокировки разрыва. Включение уровня изоляции транзакций REPEATABLE-READ innodb_locks_unsafe_for_binlog в значительной степени превратилось в READ-COMMITTED.

см. официальную документацию¹:

By default, the value of innodb_locks_unsafe_for_binlog is 0 (disabled), which means that gap locking is enabled: InnoDB uses next-key locks for searches and index scans. To enable the variable, set it to 1. This causes gap locking to be disabled: InnoDB uses only index-record locks for searches and index scans.

Enabling innodb_locks_unsafe_for_binlog does not disable the use of gap locking for foreign-key constraint checking or duplicate-key checking.

The effect of enabling innodb_locks_unsafe_for_binlog is similar to but not identical to setting the transaction isolation level to READ COMMITTED.

5. Проверьте режим автоматической блокировки

``mysql> show variables like 'innodb_autoinc_lock_mode';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| innodb_autoinc_lock_mode | 1 |
+--------------------------+-------+

innodb_autoinc_lock_mode имеет 3 режима конфигурации: 0, 1, 2, соответствующие «традиционному режиму», «непрерывному режиму», «режиму чередования» соответственно.⁸Традиционный режим: блокировка AUTO-INC на уровне таблицы добавляется к оператору вставки, включающему столбец с автоматическим приращением, и блокировка снимается только после завершения выполнения вставки. Эта политика совместима с версиями до MySQL 5.1. Непрерывный режим: операторы с количеством вставленных строк (включая однострочные и многострочные вставки) могут быть определены заранее, и выделяется непрерывное и определенное значение автоинкремента; для оператора вставки с неопределенным количеством вставленных строк , блокировки таблиц по-прежнему добавляются. В этом режиме транзакция откатывается, и значение автоинкремента не откатывается, то есть содержимое столбца автоинкремента будет прерывистым. Чередующийся режим: несколько операторов SQL одновременно генерируют чередующиеся значения автоинкремента.

Поскольку оператор вставки часто включает процесс блокировки самоувеличивающихся столбцов, он будет включать процесс блокировки AUTO-INC Locks. Чтобы понять процесс блокировки вставки шаг за шагом, в этой статье не обсуждается какая-либо логика блокировки, включающая самовозрастающие столбцы. Эта настройка параметра может появиться в моей следующей статье.

3. Тип блокировки InnoDB²

1. Базовый замок

Базовые блокировки: общие блокировки (общие блокировки: S-блокировки) и эксклюзивные блокировки (эксклюзивные блокировки: X-блокировки)

MySQL позволяет транзакциям с S-блокировками читать строку, а транзакциям с X-блокировками обновлять или удалять строку. Для записей с добавленными S-блокировками другим транзакциям разрешено добавлять S-блокировки, а другим транзакциям не разрешено добавлять X-блокировки; Для записей с X-блокировками другим транзакциям не разрешается добавлять S-блокировки или X-блокировки.

MySQL предоставляет синтаксис для добавления этих двух типов блокировок следующим образом: Добавить блокировку S: выберите...блокировка в режиме общего доступа Добавить блокировку X: выберите... для обновления

2. Замки намерения

InnoDB вводит блокировки по намерениям для поддержки сосуществования мультигранулярности (блокировки таблицы и блокировки строк). Преднамеренные блокировки — это блокировки на уровне таблицы, которые можно разделить на преднамеренные совместные блокировки (блокировки IS) и преднамеренные монопольные блокировки (блокировки IX).

InnoDB supports multiple granularity locking which permits coexistence of row-level locks and locks on entire tables. To make locking at multiple granularity levels practical, additional types of locks called intention locks are used. Intention locks are table-level locks in InnoDB that indicate which type of lock (shared or exclusive) a transaction will require later for a row in that table. There are two types of intention locks used in InnoDB (assume that transaction T has requested a lock of the indicated type on table t): Intention shared (IS): Transaction T intends to set S locks on individual rows in table t. Intention exclusive (IX): Transaction T intends to set X locks on those rows.

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

Before a transaction can acquire an S lock on a row in table t, it must first acquire an IS or stronger lock on t. Before a transaction can acquire an X lock on a row, it must first acquire an IX lock on t.

Основная цель блокировки намерения - разрешить конфликт между блокировкой строки и блокировкой таблицы, который используется для указания того, что «транзакция удерживает блокировку строки или собирается удерживать блокировку».

The main purpose of IX and IS locks is to show that someone is locking a row, or going to lock a row in the table.

Матрица совместимости разделяемых блокировок, эксклюзивных блокировок и блокировок по намерению выглядит следующим образом:

	X	IX	S	IS
X	`冲突`	`冲突`	`冲突`	`冲突`
IX	`冲突`	совместимый	`冲突`	совместимый
S	`冲突`	`冲突`	совместимый	совместимый
IS	`冲突`	совместимый	совместимый	совместимый

считать

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

3. Блокировка ряда

Блокировка записи

Блокировка записи, которая блокирует только одну строку записи индекса. Блокируется одна запись индекса. Блокировка записи всегда блокирует индекс, а не саму запись. Даже если в таблице нет индекса, innodb создаст в фоновом режиме скрытый кластеризованный индекс первичного ключа, а затем скрытый кластеризованный индекс. заблокированный индекс первичного ключа Кластеризованный индекс первичного ключа. Поэтому, когда в sql нет никаких индексов, после каждого кластерного индекса будет добавлена блокировка X. Это похоже на блокировку таблицы, но в принципе должно полностью отличаться от блокировки таблицы.

см. официальную документацию³:

If the table has no PRIMARY KEY or suitable UNIQUE index, InnoDB internally generates a hidden clustered index on a synthetic column containing row ID values. The rows are ordered by the ID that InnoDB assigns to the rows in such a table. The row ID is a 6-byte field that increases monotonically as new rows are inserted. Thus, the rows ordered by the row ID are physically in insertion order.

Гэп-замки

Блокировка диапазона, блокирует только индексный диапазон (открытый диапазон). Блокировка промежутка между записями индекса, до или после записи индекса не включает саму запись индекса.

Блокировки следующего ключа

блокировка записи + блокировка промежутка, левый открытый и правый закрытый интервал.

A next-key lock is a combination of a record lock on the index record and a gap lock on the gap before the index record.

По умолчанию InnoDB работает на уровне изоляции транзакций REPEATABLE READ и с отключенной системной переменной innodb_locks_unsafe_for_binlog.В этом случае InnoDB использует блокировки следующего ключа для поиска и сканирования индекса, что предотвращает фантомные строки.

По умолчанию innodb использует блокировки следующего ключа для блокировки записей. Однако если индекс запроса содержит уникальные атрибуты, Next-Key Lock оптимизирует его и понизит до Record Lock, то есть будет заблокирован только сам индекс, а не диапазон.

Вставьте замки намерения

В Gap Lock есть блокировка намерения вставки, которая генерируется во время операции вставки. Когда несколько транзакций одновременно записывают разные данные в один и тот же интервал индекса, нет необходимости ждать завершения других транзакций, и не происходит ожидания блокировки. Предположим, что есть индекс записи, содержащий значения ключа 4 и 7, и разные транзакции вставляют соответственно 5 и 6. Каждая транзакция будет генерировать блокировку намерения вставки между 4 и 7, чтобы получить монопольную блокировку на вставленную строку, но не будет заблокированы друг с другом, потому что строки данных не конфликтуют.

An insert intention lock is a type of gap lock set by INSERT operations prior to row insertion. This lock signals the intent to insert in such a way that multiple transactions inserting into the same index gap need not wait for each other if they are not inserting at the same position within the gap. Suppose that there are index records with values of 4 and 7. Separate transactions that attempt to insert values of 5 and 6, respectively, each lock the gap between 4 and 7 with insert intention locks prior to obtaining the exclusive lock on the inserted row, but do not block each other because the rows are nonconflicting.

Примечание. Блокировка намерения вставки — это не блокировка намерения, а особый вид блокировки промежутка.

4. Матрица совместимости блокировки строки⁴

	Gap	Insert Intention	Record	Next-Key
Gap	совместимый	совместимый	совместимый	совместимый
Insert Intention	`冲突`	совместимый	совместимый	`冲突`
Record	совместимый	совместимый	`冲突`	`冲突`
Next-Key	совместимый	совместимый	`冲突`	`冲突`

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

Поскольку S-блокировки и S-блокировки полностью совместимы, при оценке совместимости учитываются только три комбинации удерживаемых блокировок и запрошенных блокировок: X, S и S, X и X, X. Кроме того, следует напомнить, что оценка совместимости проводится только для случая, когда строки, участвующие в блокировке, имеют пересечение.

Из анализа матрицы совместимости можно сделать следующие выводы:

Между операциями INSERT не будет конфликтов.
GAP, Next-Key предотвратит вставку.
GAP и Record, Next-Key не будут конфликтовать
Record и Record и Next-Key конфликтуют друг с другом.
Существующая блокировка Insert не блокирует блокировки, готовые к добавлению.

5. АВТО-INC замки

Блокировка AUTO-INC — это специальная блокировка на уровне таблицы, которая возникает, когда происходит транзакционная операция вставки с участием столбца AUTO_INCREMENT.

Официальное объяснение выглядит следующим образом.³:

An AUTO-INC lock is a special table-level lock taken by transactions inserting into tables with AUTO_INCREMENT columns. In the simplest case, if one transaction is inserting values into the table, any other transactions must wait to do their own inserts into that table, so that rows inserted by the first transaction receive consecutive primary key values.

В-четвертых, вставьте процесс блокировки

официальная документация⁶Описание блокировки вставки выглядит следующим образом:

INSERT sets an exclusive lock on the inserted row. This lock is an index-record lock, not a next-key lock (that is, there is no gap lock) and does not prevent other sessions from inserting into the gap before the inserted row.

Prior to inserting the row, a type of gap lock called an insert intention gap lock is set. This lock signals the intent to insert in such a way that multiple transactions inserting into the same index gap need not wait for each other if they are not inserting at the same position within the gap. Suppose that there are index records with values of 4 and 7. Separate transactions that attempt to insert values of 5 and 6 each lock the gap between 4 and 7 with insert intention locks prior to obtaining the exclusive lock on the inserted row, but do not block each other because the rows are nonconflicting.

If a duplicate-key error occurs, a shared lock on the duplicate index record is set. This use of a shared lock can result in deadlock should there be multiple sessions trying to insert the same row if another session already has an exclusive lock.

Простая вставка добавит монопольную блокировку записи индекса, соответствующей вставленной строке.Это блокировка записи, в которой нет пробела, поэтому она не будет блокировать другие сеансы от вставки записей в пробел.

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

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

Мышление: роль вставки замков намерения

Насколько я понимаю, внедрение Insert Intention Locks должно улучшить параллелизм вставки данных. Если нет замков «Insert Intention Locks», вам может понадобиться вместо них использовать «Gap Locks».

Пять, вставьте анализ сценария взаимоблокировки

Далее давайте рассмотрим несколько сценариев взаимной блокировки, связанных со вставкой.

1. Взаимная блокировка, вызванная ошибкой дублирования ключа

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

Структура таблицы

``CREATE TABLE aa (idint(10) unsigned NOT NULL COMMENT 'первичный ключ',namevarchar(20) NOT NULL DEFAULT '' КОММЕНТАРИЙ 'Имя',ageint(11) NOT NULL ПО УМОЛЧАНИЮ '0' КОММЕНТАРИЙ 'возраст',stageint(11) NOT NULL DEFAULT '0' COMMENT 'Количество уровней', PRIMARY KEY (id),UNIQUE KEY udx_name (name),KEY idx_stage (stage)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

данные таблицы

``CREATE TABLE `aa` (`id` int(10) unsigned NOT NULL COMMENT '主键',`name` varchar(20) NOT NULL DEFAULT '' COMMENT '姓名',`age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄',`stage` int(11) NOT NULL DEFAULT '0' COMMENT '关卡数',PRIMARY KEY (`id`),UNIQUE KEY `udx_name` (`name`),KEY `idx_stage` (`stage`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

График исполнения транзакций

T1(36727)	T2(36728)	T3(36729)
begin;	begin;	begin;
вставить в aa значения(6, 'тест', 12, 3);
	вставить в aa значения(6, 'тест', 12, 3);
		вставить в aa значения(6, 'тест', 12, 3);
rollback;
		ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
	Query OK, 1 row affected (13.10 sec)

Если T1 не выполняет откат, а фиксирует, T2 и T3 сообщат об уникальном конфликте ключей: ОШИБКА 1062 (23000): повторяющаяся запись «6» для ключа «PRIMARY».

Использование блокировки транзакции

Перед откатом T1 блокировка каждой транзакции:

mysql> select * from information_schema.innodb_locks;
+--------------+-------------+-----------+-----------+-------------+------------+------------+-----------+----------+-----------+
| lock_id | lock_trx_id | lock_mode | lock_type | lock_table | lock_index | lock_space | lock_page | lock_rec | lock_data |
+--------------+-------------+-----------+-----------+-------------+------------+------------+-----------+----------+-----------+
| 36729:24:3:7 | 36729 | S | RECORD | `test`.`aa` | PRIMARY | 24 | 3 | 7 | 6 |
| 36727:24:3:7 | 36727 | X | RECORD | `test`.`aa` | PRIMARY | 24 | 3 | 7 | 6 |
| 36728:24:3:7 | 36728 | S | RECORD | `test`.`aa` | PRIMARY | 24 | 3 | 7 | 6 |
+--------------+-------------+-----------+-----------+-------------+------------+------------+-----------+----------+-----------+

Примечание. MySQL имеет собственный набор правил для определения того, какие T2 и T3 следует откатить, которые не обсуждаются в этой статье.

журнал взаимоблокировок

------------------------
LATEST DETECTED DEADLOCK
------------------------
2016-07-21 19:34:23 700000a3f000
*** (1) TRANSACTION:
TRANSACTION 36728, ACTIVE 199 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1184, 2 row lock(s)
MySQL thread id 13, OS thread handle 0x700000b0b000, query id 590 localhost root update
insert into aa values(6, 'test', 12, 3)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 24 page no 3 n bits 80 index `PRIMARY` of table `test`.`aa` trx id 36728 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
*** (2) TRANSACTION:
TRANSACTION 36729, ACTIVE 196 sec inserting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1184, 2 row lock(s)
MySQL thread id 14, OS thread handle 0x700000a3f000, query id 591 localhost root update
insert into aa values(6, 'test', 12, 3)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 24 page no 3 n bits 80 index `PRIMARY` of table `test`.`aa` trx id 36729 lock mode S
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 24 page no 3 n bits 80 index `PRIMARY` of table `test`.`aa` trx id 36729 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
*** WE ROLL BACK TRANSACTION (2)

Причина взаимоблокировки

Транзакция T1 успешно вставила запись и получила монопольную блокировку записи (LOCK_X | LOCK_REC_NOT_GAP) по индексу с идентификатором = 6. Сразу после этого транзакции T2 и T3 также начали вставлять записи, запрашивая эксклюзивные блокировки намерения вставки (LOCK_X | LOCK_GAP | LOCK_INSERT_INTENTION); но из-за повторяющихся конфликтов уникальных ключей соответствующие запрошенные эксклюзивные блокировки записей (LOCK_X | LOCK_REC_NOT_GAP) были преобразованы в общие записи. блокировки (LOCK_S | LOCK_REC_NOT_GAP).

T1 выполняет откат, чтобы снять монопольную блокировку записи (LOCK_X | LOCK_REC_NOT_GAP) для индекса с идентификатором = 6, а T2 и T3 запрашивают монопольную блокировку записи (LOCK_X | LOCK_REC_NOT_GAP) для индекса с идентификатором = 6. Поскольку блокировка X и блокировка S являются взаимоисключающими, и T2, и T3 ждут, пока другая сторона освободит блокировку S. Таким образом, возникает тупик.

Если в этом сценарии есть только две транзакции T1 и T2 или T1 и T3, описанная выше тупиковая ситуация не возникнет.

считать

Почему блокировка X, запрошенная транзакцией, должна быть преобразована в блокировку S при обнаружении конфликта повторяющихся первичных ключей? (Более надуманное) личное понимание заключается в повышении эффективности параллелизма вставки с типом блокировки намерения вставки.
Какова роль запроса блокировки намерения вставки перед вставкой? Лично я думаю, что с помощью анализа матрицы совместимости Insert Intention Locks должна уменьшить конфликт блокировок при вставке.

2. Тупик, вызванный конфликтом между GAP и Insert Intention.

Структура таблицы

``CREATE TABLE `t` (`a` int(11) NOT NULL,`b` int(11) DEFAULT NULL,PRIMARY KEY (`a`),KEY `idx_b` (`b`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

данные таблицы

``mysql> select * from t;
+----+------+
| a | b |
+----+------+
| 1 | 2 |
| 2 | 3 |
| 3 | 4 |
| 11 | 22 |
+----+------+

График исполнения транзакций

T1(36831)	T2(36832)
begin;	begin;
select * from t where b = 6 for update;
	select * from t where b = 8 for update;
insert into t values (4,5);
	insert into t values (4,5);
	ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
Query OK, 1 row affected (5.45 sec)

Использование блокировки транзакции

Перед вставкой T2 блокировка каждой транзакции:

mysql> select * from information_schema.innodb_locks;
+--------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+
| lock_id | lock_trx_id | lock_mode | lock_type | lock_table | lock_index | lock_space | lock_page | lock_rec | lock_data |
+--------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+
| 36831:25:4:5 | 36831 | X,GAP | RECORD | `test`.`t` | idx_b | 25 | 4 | 5 | 22, 11 |
| 36832:25:4:5 | 36832 | X,GAP | RECORD | `test`.`t` | idx_b | 25 | 4 | 5 | 22, 11 |
+--------------+-------------+-----------+-----------+------------+------------+------------+-----------+----------+-----------+

журнал взаимоблокировок

------------------------
LATEST DETECTED DEADLOCK
------------------------
2016-07-28 12:28:34 700000a3f000
*** (1) TRANSACTION:
TRANSACTION 36831, ACTIVE 17 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1184, 3 row lock(s), undo log entries 1
MySQL thread id 38, OS thread handle 0x700000b0b000, query id 953 localhost root update
insert into t values (4,5)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 25 page no 4 n bits 72 index `idx_b` of table `test`.`t` trx id 36831 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 80000016; asc ;;
1: len 4; hex 8000000b; asc ;;
*** (2) TRANSACTION:
TRANSACTION 36832, ACTIVE 13 sec inserting
mysql tables in use 1, locked 1
3 lock struct(s), heap size 360, 2 row lock(s)
MySQL thread id 39, OS thread handle 0x700000a3f000, query id 954 localhost root update
insert into t values (4,5)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 25 page no 4 n bits 72 index `idx_b` of table `test`.`t` trx id 36832 lock_mode X locks gap before rec
Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 80000016; asc ;;
1: len 4; hex 8000000b; asc ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 25 page no 3 n bits 72 index `PRIMARY` of table `test`.`t` trx id 36832 lock mode S locks rec but not gap waiting
Record lock, heap no 5 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 80000004; asc ;;
1: len 6; hex 000000008fdf; asc ;;
2: len 7; hex 8d000001d00110; asc ;;
3: len 4; hex 80000005; asc ;;
*** WE ROLL BACK TRANSACTION (2)

Причина взаимоблокировки

Транзакция T1 выполняет оператор запроса и добавляет эксклюзивную блокировку следующего ключа (LOCK_X | LOCK_ORDINARY) для индекса b=6, которая блокирует диапазон индекса idx_b (4, 22). Транзакция T2 выполняет оператор запроса и добавляет эксклюзивную блокировку следующего ключа (LOCK_X | LOCK_ORDINARY) для индекса b=8, которая блокирует диапазон индекса idx_b (4, 22). Поскольку запрошенный GAP совместим с уже удерживаемым GAP, транзакция T2 также может быть успешно заблокирована в диапазоне индексов idx_b (4, 22).

Когда транзакция T1 выполняет оператор вставки, сначала будет добавлена монопольная блокировка с намерением вставки. Поскольку запрошенная блокировка Insert Intention несовместима с существующей блокировкой GAP, транзакция T1 ожидает, пока T2 освободит блокировку GAP. Транзакция T2 выполняет оператор вставки, а также ожидает, пока T1 снимет блокировку GAP. Таким образом, возникает тупик.

Примечание. LOCK_ORDINARY имеет некоторые особенности LOCK_GAP.

Мысль: на каком уровне индекса находится блокировка Insert Intention?

Эта эксклюзивная блокировка добавляется в ПК или во вторичный индекс?

6. Размышление после занятий

Процесс блокировки без первичного ключа При отсутствии PK создается неявный кластеризованный индекс. Как блокировка влияет на этот неявный кластеризованный индекс?
Процесс блокировки составного индекса
Процесс блокировки с несколькими условиями (где условие)
Неявные блокировки и явные блокировки, когда неявная блокировка будет преобразована в явную блокировку?
Если блокировка намерения вставки не блокирует никакие блокировки, необходима ли блокировка? Эффект, который я видел до сих пор, заключается в том, чтобы разбудить ожидающий поток путем блокировки. Но это не значит, что операция вставки может быть выполнена сразу после пробуждения. Нужно еще раз судить, есть ли конфликт блокировок.

7. Дополнительные знания

1. Проверьте уровень изоляции транзакций

SELECT @@global.tx_isolation; SELECT @@session.tx_isolation; SELECT @@tx_isolation;

2. Установите уровень изоляции

УСТАНОВИТЬ [SESSION | GLOBAL] УРОВЕНЬ ИЗОЛЯЦИИ ТРАНЗАКЦИИ {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE} Например: установить уровень изоляции сеансовой транзакции чтение незафиксированного;

3. Просмотр режима механизма auto_increment

показывать переменные типа 'innodb_autoinc_lock_mode';

4. Просмотр статуса таблицы

показывать статус таблицы, например 'plan_branch'\G; показать статус таблицы из теста, например 'plan_branch'\G;

5. Просмотр производительности SQL

show profiles show profile for query 1;

6. Просмотр текущего идентификатора последней транзакции

Каждый раз, когда открывается новая транзакция, записывается идентификатор текущей последней транзакции, который можно использовать для последующего анализа взаимоблокировок. показать состояние движка innodb\G;

7. Просмотр состояния ожидания блокировки транзакции

select from information_schema.innodb_locks; select from information_schema.innodb_lock_waits; select * from information_schema.innodb_trx;

8. Просмотр состояния innodb (включая последние журналы взаимоблокировок)

show engine innodb status;

8. Справочные документы

InnoDB Startup Options and System Variables ↩
AUTO_INCREMENT lock Handing in InnoDB ↩
InnoDB Locking ↩
Является ли Intention Lock блокировкой на уровне таблицы ↩
Clustered and Secondary Indexes ↩
[MySQL] анализ блокировки промежутка/блокировки следующего ключа ↩
Locks Set by Different SQL Statements in InnoDB ↩

Введение

2. Статус на месте

1. Версия базы данных

2. Механизм базы данных

3. Уровень изоляции транзакций

4. Проверьте состояние гэп-лока

5. Проверьте режим автоматической блокировки

3. Тип блокировки InnoDB2

1. Базовый замок

2. Замки намерения

считать

3. Блокировка ряда

Блокировка записи

Гэп-замки

Блокировки следующего ключа

Вставьте замки намерения

4. Матрица совместимости блокировки строки4

5. АВТО-INC замки

В-четвертых, вставьте процесс блокировки

Мышление: роль вставки замков намерения

Пять, вставьте анализ сценария взаимоблокировки

1. Взаимная блокировка, вызванная ошибкой дублирования ключа

Структура таблицы

данные таблицы

График исполнения транзакций

Использование блокировки транзакции

журнал взаимоблокировок

Причина взаимоблокировки

считать

2. Тупик, вызванный конфликтом между GAP и Insert Intention.

Структура таблицы

данные таблицы

График исполнения транзакций

Использование блокировки транзакции

журнал взаимоблокировок

Причина взаимоблокировки

Мысль: на каком уровне индекса находится блокировка Insert Intention?

6. Размышление после занятий

7. Дополнительные знания

1. Проверьте уровень изоляции транзакций

2. Установите уровень изоляции

3. Просмотр режима механизма auto_increment

4. Просмотр статуса таблицы

5. Просмотр производительности SQL

6. Просмотр текущего идентификатора последней транзакции

7. Просмотр состояния ожидания блокировки транзакции

8. Просмотр состояния innodb (включая последние журналы взаимоблокировок)

8. Справочные документы

3. Тип блокировки InnoDB²

4. Матрица совместимости блокировки строки⁴