Блог

Я запутался в том, как работают блокировки MySQL InnoDB.

Чтобы лучше понять это, я провел приведенный ниже эксперимент. Я использовал Spring boot и JPA для вставки/обновления записей в таблице ниже (хотя этот вопрос не связан с Spring boot или JPA).

Имя таблицы: test

Время ожидания блокировки Innodb: 50 секунд

Хранилище: ITestRepository.java

 @Modifying @Query(value = "update test set val=val 1 where valgt;:val and br_id=:brId", nativeQuery = true) // Just a random update query public void updateVal(Integer val, Integer brId);  

Обслуживание: TestService.java

 @org.springframework.transaction.annotation.Transactional public void saveMany(final int brId) { // brId is always passed as 1  for (int i = 0; i lt; 200; i  ) {  this.testRepository.updateVal(i, brId); // ======gt; LINE: 1  this.testRepository.save(new TestEntity(String.valueOf(i), i, brId)); // ======gt; LINE: 2  try {  Thread.sleep(2000);  } catch (final InterruptedException e) {  e.printStackTrace();  }  } }  @org.springframework.transaction.annotation.Transactional public void saveOne(final int val, final int brId) { // brId is always passed as 2  this.testRepository.updateVal(val, brId); // ======gt; LINE: 3  this.testRepository.save(new TestEntity(String.valueOf(val), val, brId)); // ======gt; LINE: 4 }  

• ПРИМЕЧАНИЕ 1: Таблица всегда усекается перед выполнением любого из приведенных ниже случаев.
• ПРИМЕЧАНИЕ 2: Намеренно, что приведенный выше update запрос ничего не обновит.

Случай 1: Запуск saveMany метода только один раз

В этом случае все работает нормально, и 200 строк вставляются после завершения транзакции без каких-либо ошибок.

Случай 2: saveMany Сначала срабатывает, а затем saveOne один раз

In this case, while saveMany is being executed if we trigger saveOne , saveOne will fail with Lock wait timeout exception after 50 sec, and saveMany will successfully complete once the loop is over.

Case 3: Commenting LINE: 1 amp; LINE: 3 OR commenting LINE: 2 amp; LINE: 4 and Triggering saveMany first, and then saveOne once

In this case, while saveMany is being executed if we trigger saveOne , everything will work fine and both the methods will complete successfully without any exception.

Случай 4: Сначала комментируйте LINE: 1 и запускайте saveMany , а затем saveOne один раз

В этом случае во время saveMany выполнения, если мы запустим saveOne , saveOne произойдет сбой LINE: 3 lock wait timeout , за исключением операции обновления.

Случай 5: Сначала комментируйте LINE: 2 и запускайте saveMany , а затем saveOne один раз

В этом случае во время saveMany выполнения, если мы запустим saveOne , saveOne произойдет сбой LINE: 4 lock wait timeout , за исключением операции вставки.

Из приведенных выше случаев вот мои выводы:

1. Параллельные inserts или updates не блокирующие таблицы
2. Параллельно insert и update заблокирует таблицу (какая бы операция не была запущена первой, она получит блокировку первой)

Я не в состоянии понять, как работают вышеуказанные два вывода. Я имею в виду, что из документации MySQL они утверждают, что вся таблица никогда не блокируется в InnoDB, и только строки блокируются при выполнении inserts или updates . Как вы можете видеть в приведенных выше случаях, br_id всегда разные для обоих методов и, следовательно updates , выполняются для разных наборов строк, тогда также почему lock wait timeout возникает исключение? Кроме того, параллельны inserts или updates не вызывают никаких проблем, как и почему?

ПРАВКА 1:

Если br_id не индексируется, то это работает так, как указано в приведенных выше случаях, но если br_id индексируется, то Deadlock found when trying to get lock; try restarting transaction исключение создается немедленно при выполнении saveOne метода, параллельного saveMany методу.

 update test  set val = ...  where val gt; ...  and br_id = ...  

Поскольку в соответствующих столбцах нет индексов, все строки заблокированы.

Было бы лучше, если бы

 INDEX(br_id)  

или

 INDEX(br_id, val)  

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

Имейте в виду, что некоторые «замки» являются «замками зазора». Это включает в себя блокировку разрыва между индексированными значениями. Иногда это может вызвать сюрпризы. (Я не знаю, имеет ли это отношение к этому обновлению.)

Один из инструментов для просмотра блокировок SHOW ENGINE INNODB STATUS; (поскольку информация там временная, она может не показывать то, что вам нужно).

 BEGIN; UPDATE ... ((spend lots of time before COMMIT)) COMMIT;  

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

Почти всегда InnoDB может видеть, что просто останавливается (до innodb_lock_wait_timeout нескольких секунд), чтобы получить необходимые блокировки.

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

Есть несколько случаев (особенно с «блокировкой пробелов»), когда InnoDB консервативно объявляет тупик, когда его могло и не быть. (Я не знаю, было ли это вашим делом.)