Блог

Главная - Вопросы по программированию - Поведение MySQL gap lock не соответствует ожиданиям

Поведение MySQL gap lock не соответствует ожиданиям

#mysql #locking #innodb

#mysql #блокировка #innodb

Вопрос:

Описание проблемы:

В mysql 8.0 я запустил транзакцию и выполнил SELECT * FROM child WHERE id > 1000 FOR UPDATE; (транзакция один), затем я запустил другую транзакцию и выполнил update child set id = id 10 where id = 101; (транзакция два), и в таблице есть строка id = 101 child , но эта транзакция два была заблокирована.

Если я выполню update child set id = id 10 where id = 102; (транзакция три) и id = 102 в таблице не будет строки из, транзакция три не будет заблокирована и может быть выполнена успешно.

Насколько я понимаю, mysql 8.0 gap lock блокирует только строки, идентификаторы которых больше 1000, но во второй транзакции идентификатор строки равен 101 и не превышает 1000, поэтому две транзакции не будут конфликтовать друг с другом. Итак, почему вторая транзакция была заблокирована первой транзакцией?

Дополнительные сведения, как показано ниже :

  1. child Структура таблицы:

CREATE TABLE `child` (n `id` int NOT NULL,n PRIMARY KEY (`id`)n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

  1. Все данные в таблице child :
 mysql root@localhost:test> select * from child;
 ------ 
| id   |
 ------ 
| 90   |
| 101  |
| 105  |
| 106  |
| 109  |
| 111  |
| 1007 |
 ------ 
7 rows in set
  1. Все конфигурации mysql 8.0 по умолчанию.
  2. Две транзакции выполняются параллельно.

Комментарии:

1. затем я начал другую транзакцию , эта транзакция параллельна или вложена?

2. Поведение блокировки будет зависеть от ваших данных, поэтому, пожалуйста, добавьте подробности об этом. В частности: есть ли у вас идентификатор, который находится в промежутке (например, значение от 112 до 1001) в вашей таблице? Если нет, то такое поведение ожидается. (Если вы все еще хотите знать, почему, мы можем уточнить, но потребуется подтверждение ваших данных).

3. Я добавил необходимые детали выше, очень ценю ваш ответ.

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

Ответ №1:

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

Предположим, у вас есть идентификаторы 90, 101, 105, 106, 109, 110 и 1007 в вашей таблице. Обратите внимание, что я изменил вашу примерную строку 111 на 110, так как в противном случае обновление со 101 на 111 завершилось бы неудачно из-за нарушения первичного ключа, прежде чем возникли проблемы с блокировками.

Если MySQL необходимо выдать блокировку WHERE id > 1000 , он не будет (поскольку не может) отслеживать точное значение > 1000 . Вместо этого он возьмет существующий объект, который ближе всего к этому значению, которым в вашем случае является строка с идентификатором 110, и добавит блокировку пробелов, которая покрывает пространство от 110 до 1006. И еще одна блокировка блокировка пробелов в строке с идентификатором 1007, которая покрывает пространство от 1007 и выше.

Да, это занимает больше места, чем необходимо. Но эта блокировка включает в себя необходимое пространство, что является важным аспектом.

Ваш update child set id = id 10 where id = 101 now переместит строку в заблокированное пространство и, таким образом, должен дождаться снятия существующей блокировки. Так что это на самом деле ожидаемое поведение из-за того, как работает MySQL.

Если у вас будет еще одна строка между 112 и 1000, блокировка пробелов может начаться там, и вашей транзакции обновления не придется ждать.

Практически, это проблема, особенно с небольшими таблицами (или с конкретными шаблонами обновления / вставки). Если вы добавляете все больше и больше строк, вероятность того, что случайное обновление попадет в один «перекрытый» пробел, будет ниже.