Эксклюзивная блокировка строки блокировки MySQL, блокировка пробела, блокировка почтового индекса

MySQL

MySQL InnoDB поддерживает три типа блокировки строк

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

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

  • Блокировка следующей клавиши: комбинация блокировки строки и блокировки промежутка называется блокировкой следующей клавиши.

По умолчанию InnoDB работает на уровне изоляции повторяющегося чтения и блокирует строки данных в форме Next-Key Lock, что может эффективно предотвратить возникновение фантомного чтения. Блокировка следующего ключа представляет собой комбинацию блокировки строки и блокировки промежутка.Когда InnoDB сканирует запись индекса, она сначала добавит блокировку строки (блокировка записи) к записи индекса, а затем добавит блокировку промежутка (блокировка промежутка) к промежутку. с обеих сторон индексной записи. После добавления блокировки промежутка другие транзакции не могут изменять или вставлять записи в этот промежуток.

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

  • Когда вам нужно выполнить операцию записи (вставить, обновить, удалить, выбрать для обновления) над фрагментом данных в таблице, вам необходимо сначала получить монопольную блокировку (X-блокировку) записи, которая называется блокировкой строки. .
create table x(`id` int, `num` int, index `idx_id` (`id`));
insert into x values(1, 1), (2, 2);

-- 事务A
START TRANSACTION;
update x set id = 1 where id = 1;

-- 事务B
-- 如果事务A没有commit，id=1的记录拿不到X锁，将出现等待
START TRANSACTION;
update x set id = 1 where id = 1;

-- 事务C
-- id=2的记录可以拿到X锁，不会出现等待
START TRANSACTION;
update x set id = 2 where id = 2;
  • Для уровня изоляции InnoDB RR в приведенном выше примере SQL показаны характеристики блокировок строк: «Блокировка определенной строки не разрешает модификацию», но блокировки строк основаны на индексах таблицы. Если используется поле num (столбец без индекса). в условии where это будет производить другое явление:
-- 事务A
START TRANSACTION;
update x set num = 1 where num = 1;

-- 事务B
-- 由于事务A中num字段上没有索引将产生表锁，导致整张表的写操作都会出现等待
START TRANSACTION;
update x set num = 1 where num = 1;

-- 事务C
-- 同理，会出现等待
START TRANSACTION;
update x set num = 2 where num = 2;

-- 事务D
-- 等待
START TRANSACTION;
insert into x values(3, 3);

Гэп-лок

В MySQL выбор называется чтением моментального снимка и не требует блокировки, а вставка, обновление, удаление и выбор для обновления называются текущими операциями чтения, которые должны быть заблокированы для данных. «Чтение» в фантомном чтении относится к текущему чтению. .

Уровень изоляции транзакций RR разрешает фантомное чтение, но уровень InnoDB RR позволяет избежать фантомного чтения через Gap-блокировки.

Условия для создания блокировок пробелов (на уровне изоляции транзакций RR)

  • Используйте обычную блокировку индекса
  • Используйте многостолбцовый уникальный индекс
  • Блокировка нескольких строк с уникальным индексом

Gap Lock для уникальных индексов

тестовая среда

MySQL，InnoDB，默认的隔离级别（RR）

техническая спецификация

CREATE TABLE `test` (
  `id` int(1) NOT NULL AUTO_INCREMENT,
  `name` varchar(8) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

данные

INSERT INTO `test` VALUES ('1', '小罗');
INSERT INTO `test` VALUES ('5', '小黄');
INSERT INTO `test` VALUES ('7', '小明');
INSERT INTO `test` VALUES ('11', '小红');

Приведенные выше данные будут генерировать скрытые пробелы

(-infinity, 1] (1, 5] (5, 7] (7, 11] (11, +infinity]

Используются только блокировки записей, блокировки пробелов не генерируются.

/* 开启事务1 */
BEGIN;
/* 查询 id = 5 的数据并加记录锁 */
SELECT * FROM `test` WHERE `id` = 5 FOR UPDATE;
/* 延迟30秒执行，防止锁释放 */
SELECT SLEEP(30);

-- 注意：以下的语句不是放在一个事务中执行，而是分开多次执行，每次事务中只有一条添加语句

/* 事务2插入一条 name = '小张' 的数据 */
INSERT INTO `test` (`id`, `name`) VALUES (4, '小张'); # 正常执行

/* 事务3插入一条 name = '小张' 的数据 */
INSERT INTO `test` (`id`, `name`) VALUES (8, '小东'); # 正常执行

/* 提交事务1，释放事务1的锁 */
COMMIT;

Выше, поскольку первичный ключ является уникальным индексом, а для запроса используется только один индекс и блокируется только одна запись, к данным с id = 5 добавляется только блокировка записи, и блокировка пробела не создается.

сгенерировать блокировку гэпа

/* 开启事务1 */
BEGIN;
/* 查询 id 在 7 - 11 范围的数据并加记录锁 */
SELECT * FROM `test` WHERE `id` BETWEEN 5 AND 7 FOR UPDATE;
/* 延迟30秒执行，防止锁释放 */
SELECT SLEEP(30);

-- 注意：以下的语句不是放在一个事务中执行，而是分开多次执行，每次事务中只有一条添加语句

/* 事务2插入一条 id = 3，name = '小张1' 的数据 */
INSERT INTO `test` (`id`, `name`) VALUES (3, '小张1'); # 正常执行

/* 事务3插入一条 id = 4，name = '小白' 的数据 */
INSERT INTO `test` (`id`, `name`) VALUES (4, '小白'); # 正常执行

/* 事务4插入一条 id = 6，name = '小东' 的数据 */
INSERT INTO `test` (`id`, `name`) VALUES (6, '小东'); # 阻塞

/* 事务5插入一条 id = 8， name = '大罗' 的数据 */
INSERT INTO `test` (`id`, `name`) VALUES (8, '大罗'); # 阻塞

/* 事务6插入一条 id = 9， name = '大东' 的数据 */
INSERT INTO `test` (`id`, `name`) VALUES (9, '大东'); # 阻塞

/* 事务7插入一条 id = 11， name = '李西' 的数据 */
INSERT INTO `test` (`id`, `name`) VALUES (11, '李西'); # 阻塞

/* 事务8插入一条 id = 12， name = '张三' 的数据 */
INSERT INTO `test` (`id`, `name`) VALUES (12, '张三'); # 正常执行

/* 提交事务1，释放事务1的锁 */
COMMIT;

Из приведенного выше видно, что данные не могут быть вставлены в два диапазона (5, 7] и (7, 11), а в другие диапазоны данные могут быть вставлены обычным образом. Поэтому, когда мы блокируем диапазон (5, 7], когда , два интервала (5, 7] и (7, 11] будут заблокированы.

заблокировать несуществующие данные

/* 开启事务1 */
BEGIN;
/* 查询 id = 3 这一条不存在的数据并加记录锁 */
SELECT * FROM `test` WHERE `id` = 3 FOR UPDATE;
/* 延迟30秒执行，防止锁释放 */
SELECT SLEEP(30);

-- 注意：以下的语句不是放在一个事务中执行，而是分开多次执行，每次事务中只有一条添加语句

/* 事务2插入一条 id = 3，name = '小张1' 的数据 */
INSERT INTO `test` (`id`, `name`) VALUES (2, '小张1'); # 阻塞

/* 事务3插入一条 id = 4，name = '小白' 的数据 */
INSERT INTO `test` (`id`, `name`) VALUES (4, '小白'); # 阻塞

/* 事务4插入一条 id = 6，name = '小东' 的数据 */
INSERT INTO `test` (`id`, `name`) VALUES (6, '小东'); # 正常执行

/* 事务5插入一条 id = 8， name = '大罗' 的数据 */
INSERT INTO `test` (`id`, `name`) VALUES (8, '大罗'); # 正常执行

/* 提交事务1，释放事务1的锁 */
COMMIT;

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

в заключении

  • Для оператора блокировки, который указывает запрос для записи, если запись не существует, будут сгенерированы блокировка записи и блокировка пробела.Если запись существует, будет сгенерирована только блокировка записи, например: ГДЕid = 5 FOR UPDATE;
  • Для операторов запроса, которые находят определенный диапазон, будет сгенерирована блокировка пробела, например: ГДЕid BETWEEN 5 AND 7 FOR UPDATE;

Gap lock для обычных индексов

подготовка данных

Создайте таблицу test1:

  • Примечание: число не является уникальным значением
CREATE TABLE `test1` (
  `id` int(1) NOT NULL AUTO_INCREMENT,
  `number` int(1) NOT NULL COMMENT '数字',
  PRIMARY KEY (`id`),
  KEY `number` (`number`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

id является первичным ключом, а по номеру устанавливается общий индекс. Сначала добавьте некоторые данные:

INSERT INTO `test1` VALUES (1, 1);
INSERT INTO `test1` VALUES (5, 3);
INSERT INTO `test1` VALUES (7, 8);
INSERT INTO `test1` VALUES (11, 12);

Скрытые пропуски в числовом индексе в таблице test1:

(-infinity, 1] (1, 3] (3, 8] (8, 12] (12, +infinity]

Выполните следующие транзакции (транзакция 1 фиксируется последней)

/* 开启事务1 */
BEGIN;
/* 查询 number = 5 的数据并加记录锁 */
SELECT * FROM `test1` WHERE `number` = 3 FOR UPDATE;
/* 延迟30秒执行，防止锁释放 */
SELECT SLEEP(30);

-- 注意：以下的语句不是放在一个事务中执行，而是分开多次执行，每次事务中只有一条添加语句

/* 事务2插入一条 number = 0 的数据 */
INSERT INTO `test1` (`number`) VALUES (0); -- 正常执行

/* 事务3插入一条 number = 1 的数据 */
INSERT INTO `test1` (`number`) VALUES (1); -- 被阻塞

/* 事务4插入一条 number = 2 的数据 */
INSERT INTO `test1` (`number`) VALUES (2); -- 被阻塞

/* 事务5插入一条 number = 4 的数据 */
INSERT INTO `test1` (`number`) VALUES (4); -- 被阻塞

/* 事务6插入一条 number = 8 的数据 */
INSERT INTO `test1` (`number`) VALUES (8); -- 正常执行

/* 事务7插入一条 number = 9 的数据 */
INSERT INTO `test1` (`number`) VALUES (9); -- 正常执行

/* 事务8插入一条 number = 10 的数据 */
INSERT INTO `test1` (`number`) VALUES (10); -- 正常执行

/* 提交事务1 */
COMMIT;

Здесь видно, что в промежутке числа (1 - 8) операторы вставки заблокированы, а операторы, не входящие в этот диапазон, выполняются нормально из-за блокировки промежутка.

Углубить понимание гэп-лока

восстановить данные, как они были инициализированы

/* 开启事务1 */
BEGIN;
/* 查询 number = 5 的数据并加记录锁 */
SELECT * FROM `test1` WHERE `number` = 3 FOR UPDATE;
/* 延迟30秒执行，防止锁释放 */
SELECT SLEEP(30);

/* 事务1插入一条 id = 2， number = 1 的数据 */
INSERT INTO `test1` (`id`, `number`) VALUES (2, 1); -- 阻塞

/* 事务2插入一条 id = 3， number = 2 的数据 */
INSERT INTO `test1` (`id`, `number`) VALUES (3, 2); -- 阻塞

/* 事务3插入一条 id = 6， number = 8 的数据 */
INSERT INTO `test1` (`id`, `number`) VALUES (6, 8); -- 阻塞

/* 事务4插入一条 id = 8， number = 8 的数据 */
INSERT INTO `test1` (`id`, `number`) VALUES (8, 8); -- 正常执行

/* 事务5插入一条 id = 9， number = 9 的数据 */
INSERT INTO `test1` (`id`, `number`) VALUES (9, 9); -- 正常执行

/* 事务6插入一条 id = 10， number = 12 的数据 */
INSERT INTO `test1` (`id`, `number`) VALUES (10, 12); -- 正常执行

/* 事务7修改 id = 11， number = 12 的数据 */
UPDATE `test1` SET `number` = 5 WHERE `id` = 11 AND `number` = 12; -- 阻塞

/* 提交事务1 */
COMMIT;

Вот странное явление:

Транзакция 3 добавляет данные с id=6 и number=8, которые блокируются; Транзакция 4 добавляет данные с id = 8 и number = 8 и выполняется нормально. Транзакция 7 изменяет данные id = 11, number = 12 на операцию id = 11, number = 5, которая заблокирована;

Почему это? Давайте посмотрим на картинку ниже

gap locks

Как видно из рисунка, когда число одинаковое, оно будет отсортировано по идентификатору первичного ключа, поэтому:

id = 6, number = 8, добавленные транзакцией 3, эти данные находятся в интервале (3, 8), поэтому они будут заблокированы; id = 8, number = 8 добавлено транзакцией 4, эти данные находятся в интервале (8, 12), поэтому блокироваться не будут; Оператор модификации транзакции 7 эквивалентен вставке части данных в интервал (3, 8), поэтому он также блокируется.

в заключении

  • Для обычных столбцов индекса, независимо от типа запроса, при добавлении блокировки будет сгенерирована блокировка пробела, которая отличается от уникального индекса.
  • В общем индексе и уникальном индексе при анализе пробелов в данных строки данных сначала сортируются в соответствии с общим индексом, а затем сортируются в соответствии с уникальным индексом.

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

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

Примечание. Основная цель блокировки следующей клавиши — избежать фантомного чтения. Если уровень изоляции транзакции будет понижен до RC, блокировка следующего ключа также не удастся.

Суммировать

  • Блокировки записи, блокировки пробелов и блокировки почтовых индексов являются эксклюзивными блокировками;
  • Блокировка записи предназначена для блокировки ряда записей;
  • Гэп-блокировки генерируются только на уровне изоляции транзакций RR;
  • Уникальный индекс будет генерировать блокировку промежутка только в том случае, если заблокировано несколько записей или несуществующая запись.Когда блокировка назначается существующей записи, будет добавлена ​​только блокировка записи, и блокировка промежутка не будет создана;
  • Независимо от того, блокирует ли общий индекс одну запись или несколько записей, будет сгенерирована блокировка пробела;
  • Блокировка промежутка заблокирует пустую область между двумя соседними ключами записи, не позволяя другим транзакциям вставлять, изменять и удалять данные в этой области, чтобы предотвратить явление фантомного чтения;
  • Для промежутков между общими индексами сначала сортируются общие индексы, а затем сортируются индексы первичных ключей;
  • Если уровень транзакции — уровень RC (чтение подтверждено), блокировка промежутка будет недействительной.

материал

Категории