SQLinfo.ru - Все о MySQL

Форум пользователей MySQL

Задавайте вопросы, мы ответим

Вы не зашли.

#1 10.04.2020 15:13:56

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

вопрос по блокировкам

Приветствую.

Есть таблица и блокирующий запрос:

CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c1` int(11) DEFAULT NULL,
  `c2` int(11) DEFAULT NULL,
  `c3` int(11) DEFAULT NULL,
  `c4` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `c1` (`c1`,`c2`,`c3`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=latin1

insert into test values(1,1,1,1,1),(2,9,10,10,1),(3,7,2,10,1),(4,7,0,1,3),(6,2,0,7,3),(7,5,9,6,9),(8,5,9,9,0),(9,4,8,0,6);

select * from test;
+----+------+------+------+------+
| id | c1   | c2   | c3   | c4   |
+----+------+------+------+------+
|  1 |    1 |    1 |    1 |    1 |
|  2 |    9 |   10 |   10 |    1 |
|  3 |    7 |    2 |   10 |    1 |
|  4 |    7 |    0 |    1 |    3 |
|  6 |    2 |    0 |    7 |    3 |
|  7 |    5 |    9 |    6 |    9 |
|  8 |    5 |    9 |    9 |    0 |
|  9 |    4 |    8 |    0 |    6 |
+----+------+------+------+------+

select * from test where c1=5 and c2=9 and (c3>5) order by c4 limit 1 for update;
+----+------+------+------+------+
| id | c1   | c2   | c3   | c4   |
+----+------+------+------+------+
|  8 |    5 |    9 |    9 |    0 |
+----+------+------+------+------+


Условию where c1=5 and c2=9 and (c3>5) соответствуют 2 строки (id=7 и id=8). Они будут найдены по индексу, заблокированы и переданы на уровень сервера, где после сортировки и limit останется одна строка с id=8.

Вопрос: при каких условиях происходит освобождение блокировки на строку с id=7 по завершении выполнения запроса?

Согласно доке он должен освободить лишние строки от блокировки
https://dev.mysql.com/doc/refman/5.7/en … s-set.html

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.

Но следующий эксперимент показывает, что это не так.
В первой сессии:

shell 1> start transaction;
Query OK, 0 rows affected (0.00 sec)

shell 1> show variables like '%isol%';
+---------------+----------------+
| Variable_name | Value          |
+---------------+----------------+
| tx_isolation  | READ-COMMITTED |
+---------------+----------------+
1 row in set (0.00 sec)

shell 1> select * from test where c1=5 and c2=9 and (c3>5) order by c4 limit 1 for update;
+----+------+------+------+------+
| id | c1   | c2   | c3   | c4   |
+----+------+------+------+------+
|  8 |    5 |    9 |    9 |    0 |
+----+------+------+------+------+
1 row in set (0.00 sec)


Во второй сессии:
shell 2> select * from test where id=7 for update;

запрос повисает до ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction.

В третьей сессии видно, что первая транзакция №9259 держит блокировку на строку с id=7:
shell 3> select * from information_schema.innodb_locks\G
*************************** 1. row ***************************
    lock_id: 9260:343:3:7
lock_trx_id: 9260
  lock_mode: X
  lock_type: RECORD
 lock_table: `test`.`test`
 lock_index: PRIMARY
 lock_space: 343
  lock_page: 3
   lock_rec: 7
  lock_data: 7
*************************** 2. row ***************************
    lock_id: 9259:343:3:7
lock_trx_id: 9259
  lock_mode: X
  lock_type: RECORD
 lock_table: `test`.`test`
 lock_index: PRIMARY
 lock_space: 343
  lock_page: 3
   lock_rec: 7
  lock_data: 7
2 rows in set (0.00 sec)


У меня 10.0.29-MariaDB, возможно это её недоработка. Проверьте у кого есть 5.7

Неактивен

 

#2 10.04.2020 17:21:55

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3878

Re: вопрос по блокировкам

Проверил на MySQL 5.7.28. Точно такое же поведение в режимах изоляции READ-COMMITTED или REPEATABLE-READ.

vasya, а почему у тебя пропущена строка с id=5?

Неактивен

 

#3 10.04.2020 18:00:24

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

Re: вопрос по блокировкам

rgbeast написал:

vasya, а почему у тебя пропущена строка с id=5?

я таблицу заполнял
insert into test (c1, ..) select round(rand()*10), .. from test; -- повторить несколько раз.

потом уже написал в явном виде insert values, чтобы проще было воспроизвести
отсутствие 5-ки не заметил
исправил

Неактивен

 

#4 11.04.2020 00:32:23

deadka
Администратор
Зарегистрирован: 14.11.2007
Сообщений: 2419

Re: вопрос по блокировкам

Воспроизвелось.

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)


Зеленый свет для слабаков, долги отдают только трусы, тру гики работают только в консоли...

Неактивен

 

#5 11.04.2020 00:38:32

deadka
Администратор
Зарегистрирован: 14.11.2007
Сообщений: 2419

Re: вопрос по блокировкам

rgbeast написал:

Точно такое же поведение в режимах изоляции READ-COMMITTED или REPEATABLE-READ.

rgbeast, а разве конструкция FOR UPDATE не одинаково работает на разных уровнях изоляции транзакции?
В доке не нашёл.


Зеленый свет для слабаков, долги отдают только трусы, тру гики работают только в консоли...

Неактивен

 

#6 11.04.2020 10:35:08

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3878

Re: вопрос по блокировкам

deadka написал:

rgbeast написал:

Точно такое же поведение в режимах изоляции READ-COMMITTED или REPEATABLE-READ.

rgbeast, а конструкция FOR UPDATE не одинаково работает на разных уровнях изоляции транзакции?
В доке не нашёл.

deadka, должна работать одинаково, так как это явная блокировка

Неактивен

 

#7 11.04.2020 10:35:35

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

Re: вопрос по блокировкам

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)

Неактивен

 

#8 11.04.2020 10:57:43

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

Re: вопрос по блокировкам

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.

shell 1> start transaction;
Query OK, 0 rows affected (0.00 sec)


shell 1> select * from test where c3=10 for update;
+----+------+------+------+------+
| id | c1   | c2   | c3   | c4   |
+----+------+------+------+------+
|  2 |    9 |   10 |   10 |    1 |
|  3 |    7 |    2 |   10 |    1 |
+----+------+------+------+------+
2 rows in set (0.00 sec)


Во второй сессии
MariaDB [test]> select * from test where id=7 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction


Если в первой сессии использовать режим READ-COMMITTED во второй сессии результат будет иной
MariaDB [test]> select * from test where id=7 for update;
+----+------+------+------+------+
| id | c1   | c2   | c3   | c4   |
+----+------+------+------+------+
|  7 |    5 |    9 |    6 |    9 |
+----+------+------+------+------+


Итого:
READ-COMMITTED блокирует только те строки, которые соответствуют условию where.
REPEATABLE-READ блокирует все прочитанные в ходе выполнения запроса строки.

P.S. как раз об этом был мой первый вопрос в начале темы. В данном случае innodb блокирует все строки и передает их на уровень сервера, где проверяется условие where. Потом для не подошедших строк блокировка в режиме READ-COMMITTED снимается. А вот в примере из первого поста этого не происходит. Почему?

Неактивен

 

#9 11.04.2020 11:20:03

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3878

Re: вопрос по блокировкам

vasya, это похоже на багу

Неактивен

 

#10 11.04.2020 21:00:17

deadka
Администратор
Зарегистрирован: 14.11.2007
Сообщений: 2419

Re: вопрос по блокировкам

Нде, неутешительно.

Итого:

например, для таблицы с одной строкой select * .. for update показывает 2 lock struct(s), heap size 360, 2 row lock(s)
то есть show engine innodb status; тоже обманывает sad, равно как и innodb_locks.

как к 8-ке доступ получу, отпишусь что там.


Зеленый свет для слабаков, долги отдают только трусы, тру гики работают только в консоли...

Неактивен

 

#11 12.04.2020 11:58:18

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

Re: вопрос по блокировкам

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:

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test where c1=5 and c2=9 and (c3>5) order by c4 limit 1 for update;
+----+------+------+------+------+
| id | c1   | c2   | c3   | c4   |
+----+------+------+------+------+
|  8 |    5 |    9 |    9 |    0 |
+----+------+------+------+------+
1 row in set (0.00 sec)

mysql> select object_name, index_name, lock_type, lock_mode, lock_status, lock_data from performance_schema.data_locks;
+-------------+------------+-----------+---------------+-------------+------------+
| object_name | index_name | lock_type | lock_mode     | lock_status | lock_data  |
+-------------+------------+-----------+---------------+-------------+------------+
| test        | NULL       | TABLE     | IX            | GRANTED     | NULL       |
| test        | c1         | RECORD    | X,REC_NOT_GAP | GRANTED     | 7, 0, 1, 4 |
| test        | c1         | RECORD    | X,REC_NOT_GAP | GRANTED     | 5, 9, 6, 7 |
| test        | c1         | RECORD    | X,REC_NOT_GAP | GRANTED     | 5, 9, 9, 8 |
| test        | PRIMARY    | RECORD    | X,REC_NOT_GAP | GRANTED     | 7          |
| test        | PRIMARY    | RECORD    | X,REC_NOT_GAP | GRANTED     | 8          |
+-------------+------------+-----------+---------------+-------------+------------+
6 rows in set (0.00 sec)
 


Не ясно почему во вторичном индексе c1 блокируется запись (7, 0, 1, 4)?

Неактивен

 

#12 12.04.2020 23:34:58

deadka
Администратор
Зарегистрирован: 14.11.2007
Сообщений: 2419

Re: вопрос по блокировкам

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 ряда-то взял...


Зеленый свет для слабаков, долги отдают только трусы, тру гики работают только в консоли...

Неактивен

 

#13 12.04.2020 23:48:10

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

Re: вопрос по блокировкам

mysql> CREATE TABLE `test1` (
    ->   `id` int(11) NOT NULL AUTO_INCREMENT,
    ->   `c1` int(11) DEFAULT NULL,
    ->   `c2` int(11) DEFAULT NULL,
    ->   `c3` int(11) DEFAULT NULL,
    ->   `c4` int(11) DEFAULT NULL,
    ->   PRIMARY KEY (`id`),
    ->   KEY `c1` (`c1`,`c2`,`c3`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.14 sec)

mysql> insert into test1 values(1,1,1,1,1);
Query OK, 1 row affected (0.13 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test1 where c1 = 1 for update;
+----+------+------+------+------+
| id | c1   | c2   | c3   | c4   |
+----+------+------+------+------+
|  1 |    1 |    1 |    1 |    1 |
+----+------+------+------+------+
1 row in set (0.00 sec)

mysql>  select object_name, index_name, lock_type, lock_mode, lock_status, lock_data from performance_schema.data_locks;
+-------------+------------+-----------+---------------+-------------+------------------------+
| object_name | index_name | lock_type | lock_mode     | lock_status | lock_data              |
+-------------+------------+-----------+---------------+-------------+------------------------+
| test1       | NULL       | TABLE     | IX            | GRANTED     | NULL                   |
| test1       | c1         | RECORD    | X             | GRANTED     | supremum pseudo-record |
| test1       | c1         | RECORD    | X             | GRANTED     | 1, 1, 1, 1             |
| test1       | PRIMARY    | RECORD    | X,REC_NOT_GAP | GRANTED     | 1                      |
+-------------+------------+-----------+---------------+-------------+------------------------+
4 rows in set (0.00 sec)

Неактивен

 

#14 12.04.2020 23:53:01

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

Re: вопрос по блокировкам

предыдущий пример был для  REPEATABLE-READ

а в  READ-COMMITTED будет иначе:

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test1 where c1 = 1 for update;
+----+------+------+------+------+
| id | c1   | c2   | c3   | c4   |
+----+------+------+------+------+
|  1 |    1 |    1 |    1 |    1 |
+----+------+------+------+------+
1 row in set (0.00 sec)

mysql>  select object_name, index_name, lock_type, lock_mode, lock_status, lock_data from performance_schema.data_locks;
+-------------+------------+-----------+---------------+-------------+------------+
| object_name | index_name | lock_type | lock_mode     | lock_status | lock_data  |
+-------------+------------+-----------+---------------+-------------+------------+
| test1       | NULL       | TABLE     | IX            | GRANTED     | NULL       |
| test1       | c1         | RECORD    | X,REC_NOT_GAP | GRANTED     | 1, 1, 1, 1 |
| test1       | PRIMARY    | RECORD    | X,REC_NOT_GAP | GRANTED     | 1          |
+-------------+------------+-----------+---------------+-------------+------------+
3 rows in set (0.00 sec)
 

Неактивен

 

#15 13.04.2020 01:01:27

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

Re: вопрос по блокировкам

Итого:
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, поэтому

mysql> select * from test1 where c1 = 1 for update;
+----+------+------+------+------+
| id | c1   | c2   | c3   | c4   |
+----+------+------+------+------+
|  1 |    1 |    1 |    1 |    1 |
+----+------+------+------+------+
1 row in set (0.00 sec)

mysql>  select object_name, index_name, lock_type, lock_mode, lock_status, lock_data from performance_schema.data_locks;
+-------------+------------+-----------+---------------+-------------+------------------------+
| object_name | index_name | lock_type | lock_mode     | lock_status | lock_data              |
+-------------+------------+-----------+---------------+-------------+------------------------+
| test1       | NULL       | TABLE     | IX            | GRANTED     | NULL                   |
| test1       | c1         | RECORD    | X             | GRANTED     | supremum pseudo-record |  -- блокирует интервал (1, +∞)
| test1       | c1         | RECORD    | X             | GRANTED     | 1, 1, 1, 1             |  -- блокирует интервал (-∞, 1]
| test1       | PRIMARY    | RECORD    | X,REC_NOT_GAP | GRANTED     | 1                      |
+-------------+------------+-----------+---------------+-------------+------------------------+
4 rows in set (0.00 sec)


Ну, а в READ-COMMITTED блокируется только сама запись индекса (без промежутка).

Неактивен

 

#16 13.04.2020 01:16:26

deadka
Администратор
Зарегистрирован: 14.11.2007
Сообщений: 2419

Re: вопрос по блокировкам

Круто! Спасибо за ресерч. Жаль, что только в 8.0 есть такая инфа.
Потому что в 5.7 по сути можно ориентироваться на не вполне понятную (и возможно некорректную ) инфу из show engine innodb status;
я про
https://sqlinfo.ru/forum/viewtopic.php?pid=48215#p48215


Зеленый свет для слабаков, долги отдают только трусы, тру гики работают только в консоли...

Неактивен

 

Board footer

Работает на PunBB
© Copyright 2002–2008 Rickard Andersson