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

1. Проблемы

В транзакции: после вставки записи обновите запись в соответствии с полем p.Однако, когда есть параллельная операция, при обновлении возникнет проблема взаимоблокировки, и обновление будет изменено на id, и это будет отлично.

В той же таблице с высокой параллельной транзакцией сначала вставьте запись в транзакцию, а затем обновите эту запись: (1) Если обновление является уникальным индексом, существует исключение; (2) Если обновление является самоувеличивающимся первичным ключом, исключения нет; Голос за кадром: Не обманывайтесь описанием «тупиковой блокировки», будь то проблема взаимоблокировки, проблема блокировки или какая-то другая аномалия и так далее.

2. Тестовые задачи и воспроизведение

2.1 Подготовка данных

create table t (id int(20) primary key AUTO_INCREMENT,cell varchar(20) unique)engine=innodb;

Новая таблица: (1) механизм хранения — innodb, версия MySQL — 5.6; (2) поле id, самоувеличивающийся первичный ключ; (3) поле ячейки, уникальный индекс;

start transaction;
insert into t(cell) values(11111111111);
insert into t(cell) values(22222222222);
insert into t(cell) values(33333333333);
commit;

Вставьте некоторые тестовые данные.

2.2 настройки параметров сеанса

Установите уровень изоляции транзакций на RR (повторяемое чтение).

--设置手动提交
--设置事务隔离级别为RR
set session autocommit=0;
set session transaction isolation level repeatable read;

2.3 Моделирование параллелизма

Несколько сеансов терминала имитируют параллельные транзакции

start TRANSACTION;
INSERT INTO t(cell) VALUES(44444444);
UPDATE t set cell = 123 WHERE cell = 44444444 ;
ROLLBACK;

start TRANSACTION;
INSERT INTO t(cell) VALUES(5555555);
UPDATE t set cell= 456 WHERE cell = 5555555 ;
ROLLBACK;

Откройте два окна в Navicat

Окно A, сначала запустите транзакцию и вставьте запись;
Окно B, снова запустите транзакцию и вставьте запись;
Окно A, измените вставленную запись;
Окно B, также измените вставленную запись;

2.4 Результаты

Странно появилось!

При запуске обновления транзакции 1 произошло ожидание!
При переходе к обновлению транзакции 2 возникла взаимоблокировка, и был автоматически выполнен откат.

3. Запрос

Логично, вставить неконфликтующую запись, а потом изменить эту запись, блокировка строки не должна конфликтовать?
Как может быть разница между уникальным индексом и индексом первичного ключа? Актуально ли это? Это тупик или что-то другое?

3.1 Запрос в соответствии с состоянием show engine innodb

Если вы не можете понять это, давайте сначала посмотрим, что находится в статусе innodb, скопируйте и вставьте его и проверьте:

Видно, что Transaction1 и Transaction2 одновременно блокируют одну и ту же часть, и это locate_mode X rec bur not gap Блокировка записи

Это очень странно.Это не тупик, вызванный гэп-локом.Почему первое обновление ждет, а второе зависает?

не знаю, поищите в другом месте

3.2 Просмотр таблицы innodb_locks

Взглянув на таблицу inndb_locks в библиотеке information_schema, вы увидите, что транзакция 1 и транзакция 2 действительно заблокированы в области данных одновременно, что приводит к ожиданию данных и взаимоблокировке, но в чем причина?

Поэтому снова измените метод, чтобы увидеть:

3.3 SQL-оператор объяснения/описания

Какие! Обнаружены основные проблемы
Почему рядов здесь 6!
Почему мое обновление сканирует всю таблицу? ?
я добавил индекс

Нашел проблему:обновление не пошло на индекс, а зачистило всю таблицу!

В-четвертых, решить проблему

Теперь, когда проблема найдена, давайте посмотрим, как ее решить.Почему обновление не идет в индекс? Затем вернемся и посмотрим на два оператора обновления.

UPDATE t set cell = 123 WHERE cell = 44444444 ;
UPDATE t set cell= 456 WHERE cell = 5555555 ;

Выглядит нормально?

Я устал искать, покинутый и несчастный, наконец, когда я проверил часы, я нашел проблему:

Оглянитесь назад на оператор создания таблицы/структуру таблицы.

create table t (id int(20) primary key AUTO_INCREMENT,cell varchar(20) unique)engine=innodb;

Тип данных поля ячейки имеет тип varchar, и наше обновление записывается как cell = 444444;

Данные не указаны! что привело кобновление не пошло на индекс, а просканировало всю таблицу

Итак, давайте рассмотрим процесс с самого начала:

Когда уровень изоляции транзакций — RR (Repeat Read)
Вставка транзакции 1 генерирует блокировку намерения вставки, а вставка транзакции 2 также генерирует блокировку намерения вставки (не будут блокироваться друг другом, поскольку строки данных не конфликтуют).
В это время транзакция 1 снова выполняет оператор обновления. Поскольку индекс не используется, сканируется вся таблица. Когда сканируются данные, вставленные транзакцией 2, блокировка строки конфликтует с блокировкой намерения вставки, поэтому транзакция 1 должна подождите, пока транзакция 2 освободит намерение вставки, заблокируйте и подождите.
Когда транзакция 2 обновляется, ей также необходимо сканировать всю таблицу, но вся таблица заблокирована обновлением транзакции 1. Транзакция 2 должна дождаться, пока транзакция 2 освободит блокировку строки транзакции 1, которая вставила блокировку намерения, поэтому возникает взаимоблокировка.

Решение очень простое, измените оператор на:

UPDATE t set cell = "123" WHERE cell = "44444444" ;
UPDATE t set cell= "456" WHERE cell = "5555555" ;

Может решить проблему взаимоблокировки/ожидания

В-пятых, проблема расширения

5.1 Сравнение RC и RR

5.1.1 Тесты с историческими данными в таблице

На самом деле, во время теста я также задавался вопросом, не связано ли это с проблемой RR, так почему бы не изменить его на RC?

--将事务隔离级别改为RC
SET TRANSACTION ISOLATION LEVEL REPEATABLE COMMITTED;

После модификации проделайте с ним то же самое:

Находить:Вставка транзакции 1, вставка транзакции 2, обновление транзакции 1 вступает в силу, а обновление транзакции 2 ожидает

В соответствии с проблемами, которые мы обнаружили выше, проанализируйте его:

Когда транзакция 1 обновляется, она также сканирует всю таблицу, но поскольку RC не имеет блокировок пробелов и не вставляет блокировок намерения, обновление транзакции 1 не будет ждать.
Когда транзакция 2 обновляется, ей нужно дождаться фиксации обновления транзакции 1, чтобы снять блокировку, поэтому происходит ожидание.

пришел к выводу:

Там нет блокировки пробелов под RC

5.1.2 Тесты, не содержащие в таблице исторических данных (таблица пуста)

Для сравнения между RC и RR мы продолжили тестировать данные в таблице, удалив данные:

truncate table

Продолжаем делать то же самое с таблицей, результат:

Транзакция 1 все еще ожидает под RR, а транзакция 2 заблокирована.
При RC транзакция 1 и транзакция 2 являются нормальными, и ожидание не происходит.

Причина в том, что при RR данные, вставленные транзакцией 1, видны транзакции 2. Следовательно, при RR, даже если данные очищаются, транзакция 1 по-прежнему блокирует данные, вставленные транзакцией 2.
При RC данные, вставленные транзакцией 1, не могут быть видны для транзакции 2, а данные, вставленные транзакцией 2, не могут быть видны для транзакции 1. Они блокируют только данные, вставленные сами по себе, поэтому они могут успешно выполняться.

5.1.3 Заключение

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

В RC нет блокировки промежутка, а также нет блокировки намерения вставки, принадлежащей замку промежутка.
В RR транзакция данных 2, вставленная транзакцией 1, может быть видна, но транзакция данных 2, вставленная транзакцией 1 в RC, не видна.

5.2 Блокировка промежутка и блокировка вставки намерения в Mysql

В этом процессе мы обнаружили: блокировки строк, блокировки промежутков, блокировки намерения вставки. Среди них разные блокировки генерируются из-за разного поведения, и их значения и использование также различаются:

5.2.1 Гэп-замки

Блокировка диапазона, блокирует только диапазон индекса (открытый диапазон, исключая двусторонние конечные точки).
Блокировка промежутка между записями индекса, до или после записи индекса не включает саму запись индекса. Например, в 1, 2 и 3 возможные значения блокировки гэпа равны (∞, 1), (1, 2), (2, ∞).
Гэп-блокировки можно использовать для предотвращения фантомного чтения и гарантии того, что данные не будут вставлены между индексами.

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

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

5.2.3 Выбор блокировки

Когда мы обрабатываем операторы SQL для выполнения, разные операторы будут выбирать разные блокировки:

Если условие обновления не проходит через индекс, например, выполнение «update test set name="hello" where name="world";", в это время будет выполнено полное сканирование таблицы. При сканировании таблицы любые другие операции обновления должны быть заблокированы, поэтому увеличение блокировки таблицы.
Если условием обновления является поле индекса, но это не уникальный индекс (включая индекс первичного ключа), например, выполните «update test set name="hello" where code=9;", тогда обновление будет использовать Next -Key Lock в это время. Причины использования Next-Key Lock:

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