Ответ 1
Это отличный вопрос. InnoDB - это механизм блокировки на уровне строк, но он должен установить дополнительные блокировки для обеспечения безопасности с помощью двоичного журнала (используется для репликации, время восстановления по времени). Чтобы начать объяснять это, рассмотрим следующий (наивный) пример:
session1> START TRANSACTION;
session1> DELETE FROM users WHERE is_deleted = 1; # 1 row matches (user_id 10), deleted.
session2> START TRANSACTION;
session2> UPDATE users SET is_deleted = 1 WHERE user_id = 5; # 1 row matches.
session2> COMMIT;
session1> COMMIT;
Поскольку операторы записываются только в бинарный журнал после его фиксации, на подчиненном сеансе второй будет применяться первый и будет производить другой результат, , приводящий к повреждению данных.
Итак, что делает InnoDB, устанавливает дополнительные блокировки. Если is_deleted индексируется, то до того, как session1 не решит, никто не сможет изменять или вставлять в диапазон записей, где is_deleted=1. Если на is_deleted нет индексов, тогда InnoDB необходимо заблокировать каждую строку во всей таблице, чтобы убедиться, что воспроизведение выполнено в том же порядке. Вы можете думать об этом как о блокировании пробела, , который представляет собой другое понятие, чтобы схватить его непосредственно за пределы уровня..
В вашем случае с этим ORDER BY position ASC, InnoDB должен убедиться, что новые строки не могут быть изменены между наименьшим значением ключа и "специальным" наименьшим возможным значением. Если вы сделали что-то вроде ORDER BY position DESC.. ну, тогда никто не смог бы вставить в этот диапазон.
Итак, вот решение:
-
Записывающий двоичный журнал отстой. Я действительно с нетерпением жду будущего, когда все мы переключимся на двоичное ведение журнала на основе строк (доступно из MySQL 5.1, но не по умолчанию).
-
При использовании репликации на основе строк, если вы измените уровень изоляции на чтение, то должна быть заблокирована только одна строка, которая должна быть заблокирована.
-
Если вы хотите быть мазохистом, вы также можете включить innodb_locks_unsafe_for_binlog с репликацией на основе инструкций.
Обновление 22 апреля. Чтобы скопировать + вставить мою улучшенную версию вашего тестового теста (он не искал "в промежутке" ):
session1> CREATE TABLE test (id int not null primary key auto_increment, data1 int, data2 int, INDEX(data1)) engine=innodb;
Query OK, 0 rows affected (0.00 sec)
session1> INSERT INTO test VALUES (NULL, 1, 2), (NULL, 2, 1), (5, 2, 2), (6, 3, 3), (3, 3, 4), (4, 4, 3);
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0
session1> start transaction;
Query OK, 0 rows affected (0.00 sec)
session1> SELECT id FROM test ORDER BY data1 LIMIT 1 FOR UPDATE;
+----+
| id |
+----+
| 1 |
+----+
1 row in set (0.00 sec)
session2> INSERT INTO test values (NULL, 0, 99); # blocks - 0 is in the gap between the lowest value found (1) and the "special" lowest value.
# At the same time, from information_schema:
localhost information_schema> select * from innodb_locks\G
*************************** 1. row ***************************
lock_id: 151A1C:1735:4:2
lock_trx_id: 151A1C
lock_mode: X,GAP
lock_type: RECORD
lock_table: `so5694658`.`test`
lock_index: `data1`
lock_space: 1735
lock_page: 4
lock_rec: 2
lock_data: 1, 1
*************************** 2. row ***************************
lock_id: 151A1A:1735:4:2
lock_trx_id: 151A1A
lock_mode: X
lock_type: RECORD
lock_table: `so5694658`.`test`
lock_index: `data1`
lock_space: 1735
lock_page: 4
lock_rec: 2
lock_data: 1, 1
2 rows in set (0.00 sec)
# Another example:
select * from test where id < 1 for update; # blocks