Три алгоритма блокировки строк
Механизм хранения InnoDB имеет три алгоритма блокировки строк, а именно:
- Блокировка записи: диапазон в одной записи строки
- GAP LOCK: блокировка промежутка, блокировка диапазона, но не содержит самой записи
- Next-Key Lock: Gap Lock + Record Lock, блокирует диапазон и блокирует саму запись
Блокировка записи всегда будет блокировать записи индекса.Если механизм хранения InnoDB не устанавливает какой-либо индекс при его создании, механизм хранения InnoDB будет использовать неявный первичный ключ для блокировки.
Next-Key Lock — это алгоритм блокировки, который сочетает в себе Gap Lock и Record Lock.В рамках алгоритма Next-Key Lock innodb использует этот алгоритм блокировки для запросов строк.Например, индекс имеет 4 значения 9, 11, 13 и 20, тогда диапазон индекса, который может быть блокировкой следующего ключа, равен (слева открыт, справа закрыт): (-&,9] (9,11] (13,20] (20,+ и)
Технология блокировки с Next-Key Lock называется Next-Key Locking. Целью этого проекта является решение фантомной проблемы. С помощью этого метода блокировки блокируется не одно значение, а диапазон.
Когда запрошенный индекс содержит уникальный атрибут, механизм хранения innodb оптимизирует блокировку следующего ключа и понизит ее до блокировки записи, то есть блокируется сама запись индекса, а не диапазон. Для уникального индекса добавляется блокировка записи, которая блокирует только саму запись. Но есть и особые случаи, то есть уникальный индекс состоит из нескольких столбцов, и запрос состоит только в том, чтобы найти один из нескольких уникальных столбцов индекса, тогда ситуация блокировки по-прежнему является блокировкой следующего ключа.
DROP TABLE
IF EXISTS t;
CREATE TABLE t (a INT PRIMARY KEY);
INSERT INTO t
VALUES
(1),
(2),
(5);
CREATE TABLE Z (
a INT,
b INT,
PRIMARY KEY (a),
KEY (b)
);
INSERT INTO Z
VALUES
(1, 1),
(3, 1),
(5, 3),
(7, 6),
(10, 8);
Столбец b таблицы z является вспомогательным индексом, если всессия АВыполните следующую инструкцию SQL:
SELECT * FROM Z WHERE b=3 FOR UPDATE;
Очевидно, что в это время оператор SQL запрашивается через столбец индекса b, поэтому для блокировки используется традиционная технология блокировки следующего ключа, а посколькуЕсть два индекса, которые нужно блокировать отдельно.Для кластеризованного индекса блокировка записи добавляется только к тому индексу, столбец которого равен 5. Для вспомогательного индекса добавлена блокировка следующей клавиши, а заблокированный диапазон равен (1,3), важно отметить, что,Механизм хранения InnoDB добавит блокировку пробела к следующему значению ключа вспомогательного индекса, то есть также существует блокировка с диапазоном вспомогательного индекса (3,6). Поэтому, если новыйсессия БЕсли вы запустите следующую инструкцию SQL, она будет заблокирована:
SELECT * FROM Z WHERE a=5 LOCK IN SHARE MODE;
INSERT INTO Z SELECT 4,2;
INSERT INTO Z SELECT 6,5;
Первый оператор SQL не может быть выполнен, поскольку оператор SQL, выполненный в сеансе A, имеет значение столбца a=5 в кластеризованном индексе плюс блокировка X, поэтому выполнение будет заблокировано. Второй оператор SQL, вставка первичного ключа 4, не имеет проблем, но вставленное значение вторичного индекса 2 находится в заблокированном диапазоне (1, 3), поэтому выполнение также будет заблокировано. Третий оператор SQL, вставленный первичный ключ 6 не заблокирован, а 5 не находится между диапазоном (1, 3). Но вставленное значение 5 находится в другом заблокированном диапазоне (3, 6), поэтому его тоже нужно подождать. Следующая инструкция SQL не будет заблокирована и может быть выполнена немедленно:
INSERT INTO Z SELECT 8,6;
INSERT INTO Z SEELCT 2,0;
INSERT INTO Z SELECT 6,7;
Как видно из приведенного выше примера,Роль Gap Lock состоит в том, чтобы предотвратить вставку записей в один и тот же диапазон несколькими транзакциями, что может привести к фантомной проблеме.Например, в приведенном выше примере пользователь в сеансе A заблокировал запись с b=3. Если в это время нет блокировки Gap Lock (3, 6), пользователь может вставить запись с индексом b столбца 3, что заставит пользователя в сеансе A снова выполнить тот же запрос.В результате будут возвращены разные записи. в Призрачной задаче.
Пользователи могут явно отключить Gap Lock следующими двумя способами:
- Установите уровень изоляции транзакции для чтения
- Установите для параметра innodb_locks_unsafe_for_binlog значение 1.
В приведенной выше конфигурации, за исключением Gap Lock, который по-прежнему требуется для ограничений внешнего ключа и проверки уникальности, в других случаях для блокировки используется только Record Lock. Но следует иметь в виду, что указанные выше настройки разрушают изоляцию транзакций, а для репликации могут привести к несогласованности данных master-slave. Кроме того, с точки зрения производительности READ COMMITTED не будет лучше уровня изоляции транзакций по умолчанию READ REPEATABLE.
В механизме хранения InnoDB для операции INSERT проверяется, заблокирована ли следующая запись вставленной записи.Если она заблокирована, запрос не разрешен. Для приведенного выше примера сеанс A заблокировал запись b = 3 в таблице z, то есть был заблокирован диапазон (1, 3).В это время следующие вставки в других сеансах также вызовут блокировку:
INSERT INTO Z SELECT 2,2;
Потому что при вставке записи со значением 2 в столбец вспомогательного индекса b он будет следить за тем, чтобы следующая запись 3 была проиндексирована. И измените вставку на следующее значение, которое может быть выполнено немедленно:
INSERT INTO Z SELECT 2,0;
Наконец, еще раз напоминаем, что для блокировки уникального значения ключа блокировка следующего ключа понижается до блокировки записи, которая существует только в запросе всех уникальных столбцов индекса. Если уникальный индекс состоит из нескольких столбцов, и запрос должен найти один из нескольких столбцов уникального индекса, то запрос на самом деле является запросом типа диапазона, а не запросом типа точки, поэтому механизм хранения InnoDB по-прежнему использует Next-Key Lock. для блокировки.
Решить фантомные проблемы
При уровне изоляции транзакций по умолчанию, а именно REPEATABLE READ, механизм хранения InnoDB принимает Механизм Next-Key Lock для предотвращения фантомной проблемы (фантомной проблемы). Это может отличаться от других баз данных, таких как база данных Oracle, поскольку для решения фантомной проблемы может потребоваться уровень изоляции транзакций SERIALIZABLE.
Фантомная проблема, когдата же транзакцияВ этом случае выполнение одной и той же инструкции SQL дважды подряд может привести к разным результатам, а вторая инструкция SQL может вернуть строки, которых раньше не было.
Этот пример будет продемонстрирован ниже с использованием таблицы t, созданной в предыдущем разделе. Таблица t состоит из трех значений 1, 2 и 5:
DROP TABLE
IF EXISTS t;
CREATE TABLE t (a INT PRIMARY KEY);
INSERT INTO t
VALUES
(1),
(2),
(5);
Если транзакция T1 в это время выполняет следующую инструкцию SQL:
SELECT * FROM t WHERE a> 2 FOR UPDATE;
Обратите внимание, что транзакция T1 в это время не выполняет операцию фиксации, и вышеуказанное должно вернуть результат 5. Если в то же время другая транзакция T2 вставит значение 4, а база данных разрешит эту операцию, то транзакция T1 снова выполнит приведенный выше оператор SQL и получит результаты 4 и 5. Это отличается от результата, полученного в первый раз, и нарушает изоляцию транзакций, то есть текущая транзакция может видеть результаты других транзакций. Процесс показан в таблице 6-13:
Двигатель хранения InnoDB Уровень изоляции транзакции по умолчанию повторяется читать, на этом уровне изоляции, Блокировка, которая использует следующую клавишу для блокировки. И в преданном уровне выделения транзакций, который использует только блокировку записи, поэтому в приведенном выше примере сеанс необходимо установить превышение уровня изоляции транзакции.
также,Пользователи могут проверить уникальность на уровне приложения с помощью механизма Next-Key Lock механизма хранения InnoDB.Например:
SELECT * FROM table WHERE col=xxx LOCK IN SHARE MODE;
If not found any row:
# unique for insert value
INSERT INTO table VALUES (...);
Если пользователь запрашивает значение через индекс и добавляет SLock к строке, то даже если значение запроса отсутствует, он блокирует диапазон, поэтому, если строка не возвращается, вновь вставленное значение должно быть уникальным. У некоторых читателей могут возникнуть вопросы, если во время первой операции SELECT •••LOCK IN SHARE MODE одновременно работает несколько транзакций, то есть ли проблема с этим механизмом проверки уникальности. На самом деле этого не произойдет, потому что в это время это вызовет взаимоблокировку.Успехом будет только операция вставки одной транзакции, а остальные транзакции вызовут ошибки взаимоблокировки, как показано в Таблице 6-14.
Эта статья организована из: «Инсайдер технологии MySQL: механизм хранения InnoDB», второе издание.
Личный публичный аккаунт WeChat:
Персональный гитхаб:
личный блог: