|
Задавайте вопросы, мы ответим
Вы не зашли.
Приветствую.
Есть таблица и блокирующий запрос:
For SELECT ... FOR UPDATE or SELECT ... LOCK IN SHARE MODE, locks are acquired for scanned rows, and expected to be released for rows that do not qualify for inclusion in the result set (for example, if they do not meet the criteria given in the WHERE clause). However, in some cases, rows might not be unlocked immediately because the relationship between a result row and its original source is lost during query execution. For example, in a UNION, scanned (and locked) rows from a table might be inserted into a temporary table before evaluation whether they qualify for the result set. In this circumstance, the relationship of the rows in the temporary table to the rows in the original table is lost and the latter rows are not unlocked until the end of query execution.
Но следующий эксперимент показывает, что это не так.
В первой сессии:
Неактивен
Проверил на MySQL 5.7.28. Точно такое же поведение в режимах изоляции READ-COMMITTED или REPEATABLE-READ.
vasya, а почему у тебя пропущена строка с id=5?
Неактивен
rgbeast написал:
vasya, а почему у тебя пропущена строка с id=5?
я таблицу заполнял
insert into test (c1, ..) select round(rand()*10), .. from test; -- повторить несколько раз.
потом уже написал в явном виде insert values, чтобы проще было воспроизвести
отсутствие 5-ки не заметил
исправил
Неактивен
Воспроизвелось.
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.22-22 |
+-----------+
1 row in set (0.00 sec)
В первой транзакции
после
select * from test where c1=5 and c2=9 and (c3>5) order by c4 limit 1 for update;
делаю
show engine innodb status;
видим там
---TRANSACTION 2837939, ACTIVE 15 sec
3 lock struct(s), heap size 1136, 5 row lock(s)
то есть 5 строк залочено.
mysql> show variables like '%isol%';
+-----------------------+-----------------+
| Variable_name | Value |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
| tx_isolation | REPEATABLE-READ |
+-----------------------+-----------------+
2 rows in set (0.00 sec)
Активен
rgbeast написал:
Точно такое же поведение в режимах изоляции READ-COMMITTED или REPEATABLE-READ.
rgbeast, а разве конструкция FOR UPDATE не одинаково работает на разных уровнях изоляции транзакции?
В доке не нашёл.
Активен
deadka написал:
rgbeast написал:
Точно такое же поведение в режимах изоляции READ-COMMITTED или REPEATABLE-READ.
rgbeast, а конструкция FOR UPDATE не одинаково работает на разных уровнях изоляции транзакции?
В доке не нашёл.
deadka, должна работать одинаково, так как это явная блокировка
Неактивен
deadka написал:
В первой транзакции
после
select * from test where c1=5 and c2=9 and (c3>5) order by c4 limit 1 for update;
делаю
show engine innodb status;
видим там
---TRANSACTION 2837939, ACTIVE 15 sec
3 lock struct(s), heap size 1136, 5 row lock(s)
то есть 5 строк залочено.
та же история, однако проверка через вторую сессию показывает, что заблокировано только 2 строки.
интуитивно параметр понятен, но при ближайшем рассмотрении возникают вопросы
например, для таблицы с одной строкой
select * .. for update
показывает
2 lock struct(s), heap size 360, 2 row lock(s)
Неактивен
deadka написал:
rgbeast написал:
Точно такое же поведение в режимах изоляции READ-COMMITTED или REPEATABLE-READ.
rgbeast, а конструкция FOR UPDATE не одинаково работает на разных уровнях изоляции транзакции?
В доке не нашёл.
По разному.
Рассмотрим запрос, который делает full scan.
MariaDB [test]> explain select * from test where c3=10;
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | test | ALL | NULL | NULL | NULL | NULL | 8 | Using where |
+------+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
В режиме REPEATABLE-READ будут заблокированы все строки, а не только соответствующие условию c3=10.
Неактивен
vasya, это похоже на багу
Неактивен
Нде, неутешительно.
Итого:
например, для таблицы с одной строкой select * .. for update показывает 2 lock struct(s), heap size 360, 2 row lock(s)
то есть show engine innodb status; тоже обманывает 
, равно как и innodb_locks.
как к 8-ке доступ получу, отпишусь что там.
Активен
deadka написал:
В первой транзакции
после
select * from test where c1=5 and c2=9 and (c3>5) order by c4 limit 1 for update;
делаю
show engine innodb status;
видим там
---TRANSACTION 2837939, ACTIVE 15 sec
3 lock struct(s), heap size 1136, 5 row lock(s)
то есть 5 строк залочено.
Частично разобрался. Эта цифра не кол-во заблокированных строк с данными, а кол-во блокировок типа record. Блокировки на строку ставятся на индексные записи. При поиске по вторичному ключу будет заблокирована индексная запись во вторичном ключе + в первичном ключе.
Если воспроизвести пример в 8-ке, где есть удобный механизм просмотра наложенных блокировок через performance_schema.data_locks:
Неактивен
vasya написал:
deadka написал:
В первой транзакции
после
select * from test where c1=5 and c2=9 and (c3>5) order by c4 limit 1 for update;
делаю
show engine innodb status;
видим там
---TRANSACTION 2837939, ACTIVE 15 sec
3 lock struct(s), heap size 1136, 5 row lock(s)
то есть 5 строк залочено.та же история, однако проверка через вторую сессию показывает, что заблокировано только 2 строки.
интуитивно параметр понятен, но при ближайшем рассмотрении возникают вопросы
например, для таблицы с одной строкой
select * .. for update
показывает
2 lock struct(s), heap size 360, 2 row lock(s)
Там же на 5.7
Взял такую же таблицу с одной строкой
select * from test1 where id = 1 for update; //выборка по первичному ключу
---TRANSACTION 2838227, ACTIVE 12 sec
2 lock struct(s), heap size 1136, 1 row lock(s)
select * from test1 where c1 = 1 for update; //выборка по индексу, но не по PK ( есть ключ на (`c1`,`c2`,`c3`))
---TRANSACTION 2838234, ACTIVE 5 sec
3 lock struct(s), heap size 1136, 3 row lock(s)
Эта цифра не кол-во заблокированных строк с данными, а кол-во блокировок типа record. Блокировки на строку ставятся на индексные записи. При поиске по вторичному ключу будет заблокирована индексная запись во вторичном ключе + в первичном ключе.
Откуда ж он 3 ряда-то взял...
Активен
Неактивен
предыдущий пример был для REPEATABLE-READ
а в READ-COMMITTED будет иначе:
Неактивен
Итого:
lock_mode X означает, что накладывается next-key lock, т.е. запись + промежуток перед ней.
Когда блокируется только запись используется обозначение X,REC_NOT_GAP
"supremum pseudo-record" виртуальная запись в индексе, которая больше чем любое значение в нем. Блокировка типа next-key lock на "supremum pseudo-record" означает, что мы блокируем интервал после максимальной записи в индексе.
Если индекс содержит одно значение равное 1, то блокировку next-key locks можно наложить на следующие интервалы:
(-∞, 1]
(1, +∞)
В REPEATABLE-READ для предотвращения фантомных строк накладывается next-key lock, поэтому
Неактивен
Круто! Спасибо за ресерч. Жаль, что только в 8.0 есть такая инфа.
Потому что в 5.7 по сути можно ориентироваться на не вполне понятную (и возможно некорректную ) инфу из show engine innodb status;
я про
https://sqlinfo.ru/forum/viewtopic.php?pid=48215#p48215
Активен
