При использовании блокировки MySQL FOR UPDATE, что точно заблокировано?

Ответ 1

Почему бы нам просто не попробовать?

Настройка базы данных

CREATE DATABASE so1;
USE so1;
CREATE TABLE notification (`id` BIGINT(20), `date` DATE, `text` TEXT) ENGINE=InnoDB;
INSERT INTO notification(id, `date`, `text`) values (1, '2011-05-01', 'Notification 1');
INSERT INTO notification(id, `date`, `text`) values (2, '2011-05-02', 'Notification 2');
INSERT INTO notification(id, `date`, `text`) values (3, '2011-05-03', 'Notification 3');
INSERT INTO notification(id, `date`, `text`) values (4, '2011-05-04', 'Notification 4');
INSERT INTO notification(id, `date`, `text`) values (5, '2011-05-05', 'Notification 5');

Теперь запустите два подключения к базе данных

Соединение 1

BEGIN;
SELECT * FROM notification WHERE `date` >= '2011-05-03' FOR UPDATE;

Соединение 2

BEGIN;

Если MySQL блокирует все строки, следующий оператор блокирует. Если он только блокирует строки, которые он возвращает, он не должен блокироваться.

SELECT * FROM notification WHERE `date` = '2011-05-02' FOR UPDATE;

И действительно, блок блокируется.

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

INSERT INTO notification(id, `date`, `text`) values (6, '2011-05-06', 'Notification 6');

блокировки!

Я не могу быть уверен в этом, будет ли MySQL просто идти вперед и блокирует всю таблицу, когда определенный процент строк заблокирован, или где он действительно действительно интеллектуальный, убедившись, что результат запроса SELECT ... FOR UPDATE никогда не сможет (с INSERT, UPDATE или DELETE) при блокировке.

Ответ 2

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

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

CREATE TABLE `t1` (                       
  `id` int(11) NOT NULL AUTO_INCREMENT,                 
  `notid` int(11) DEFAULT NULL,                         
  PRIMARY KEY (`id`)                                    
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

12 строк, вставленных с помощью INSERT INTO t1 (notid) VALUES (1), (2),..., (12). В соединение 1:

BEGIN;    
SELECT * FROM t1 WHERE id=5 FOR UPDATE;

В соединении 2 заблокированы следующие утверждения:

SELECT * FROM t1 WHERE id!=5 FOR UPDATE;
SELECT * FROM t1 WHERE id<5 FOR UPDATE;
SELECT * FROM t1 WHERE notid!=5 FOR UPDATE;
SELECT * FROM t1 WHERE notid<5 FOR UPDATE;
SELECT * FROM t1 WHERE id<=4 FOR UPDATE;

Самая странная часть заключается в том, что SELECT * FROM t1 WHERE id>5 FOR UPDATE; не заблокирован и не имеет значения

...
SELECT * FROM t1 WHERE id=3 FOR UPDATE;
SELECT * FROM t1 WHERE id=4 FOR UPDATE;
SELECT * FROM t1 WHERE id=6 FOR UPDATE;
SELECT * FROM t1 WHERE id=7 FOR UPDATE;
...

Я также хотел бы отметить, что вся таблица заблокирована, когда условие WHERE в запросе из соединения 1 совпадает с неиндексированной строкой. Например, когда соединение 1 выполняется SELECT * FROM t1 WHERE notid=5 FOR UPDATE, блокируются все запросы с запросами FOR UPDATE и UPDATE из соединения 2.

- ИЗМЕНИТЬ -

Это довольно конкретная ситуация, но я смог найти только такое поведение:

Соединение 1:

BEGIN;
SELECT *, @x:[email protected]+id AS counter FROM t1 CROSS JOIN (SELECT @x:=0) b HAVING counter>5 LIMIT 1 FOR UPDATE;
+----+-------+-------+---------+
| id | notid | @x:=0 | counter |
+----+-------+-------+---------+
|  3 |     3 |     0 |       9 |
+----+-------+-------+---------+
1 row in set (0.00 sec)

От соединения 2:

SELECT * FROM t1 WHERE id=2 FOR UPDATE; заблокирован;

SELECT * FROM t1 WHERE id=4 FOR UPDATE; заблокирован не.

Ответ 3

Следующие ссылки на странице документации, которую вы опубликовали, дают дополнительную информацию о locking. На этой странице

A SELECT... FOR UPDATE читает последние доступные данные, устанавливая эксклюзивные блокировки для каждой прочитанной строки. Таким образом, он устанавливает те же блокировки, что и искомый SQL UPDATE, установленный в строках.

Это кажется довольно очевидным, что все строки, которые он должен сканировать.

Ответ 4

Поток довольно старый, просто чтобы поделиться моими двумя центами относительно тестов, выполненных @Frans выше.

Соединение 1

BEGIN;
SELECT * FROM notification WHERE 'date' >= '2011-05-03' FOR UPDATE;

Соединение 2

BEGIN;

SELECT * FROM notification WHERE 'date' = '2011-05-02' FOR UPDATE;

Параллельная транзакция 2 будет точно заблокирована, но причина НЕ в том, что транзакция 1 удерживает блокировку всей таблицы. Ниже объясняется, что произошло за сценой:

Прежде всего, уровень изоляции по умолчанию для механизма хранения InnoDB - RR. В этом случае,

1- Когда столбец используется в тех случаях, когда условие не индексируется (как в случае выше):

Движок обязан выполнить полное сканирование таблицы, чтобы отфильтровать записи, не соответствующие критериям. КАЖДЫЙ СТРОК, который был отсканирован, блокируется в первую очередь. MySQL может снять блокировки на этих записях, не соответствующих предложению where, позже. Это оптимизация производительности, однако такое поведение нарушает ограничение 2PL.

Когда транзакция 2 начинается, как объяснено, она должна получить блокировку X для каждой извлеченной строки, хотя существует только одна запись (id = 2), соответствующая предложению where. В конечном итоге транзакция 2 будет ожидать блокировки X первой строки (id = 1), пока транзакция 1 не выполнит фиксацию или откат.

2- Когда используется столбец, в котором условие является первичным индексом

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

3 - когда используется столбец, где условие является индексом, но не уникальным

Этот случай сложнее. 1) Индексная запись заблокирована. 2) Одна X-блокировка прикреплена к соответствующему первичному индексу. 3) Два пробельных замка прикрепляются к несуществующим записям непосредственно перед и после записи, соответствующей критериям поиска.

Ответ 5

Из MySQL официальный документ:

Блокировка чтения, UPDATE или DELETE обычно устанавливают блокировки записи для каждой индексной записи, которая сканируется при обработке оператора SQL. Неважно, есть ли в операторе условия WHERE, исключающие строку.

В случае, рассмотренном в ответе Франса, все строки заблокированы, потому что во время обработки sql происходит сканирование таблицы:

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

Проверьте последнюю версию документа здесь: https://dev.mysql.com/doc/refman/8.0/ru/innodb-locks-set.html

Ответ 6

Он блокирует все строки, выбранные по запросу.