Сколько строк будет заблокировано командой SELECT ... ORDER BY xxx LIMIT 1 FOR UPDATE?

У меня есть запрос со следующей структурой:

SELECT ..... WHERE status = 'QUEUED' ORDER BY position ASC LIMIT 1 FOR UPDATE;

Это оператор SELECT для одной таблицы в таблице InnoDB. поле position (INT NOT NULL) имеет индекс. статус ENUM и также индексируется.

SELECT ... FOR UPDATE Страница руководства говорит, что она блокирует все строки, которые читает. Правильно ли я понимаю, что в этом случае будет заблокирован только один ряд? Или, скорее, он заблокирует всю таблицу?

Можно ли определить, какие строки будут заблокированы с EXPLAIN запрос? Если да - как? Объяснение для запроса по пустой таблице показывает следующее:

1;'SIMPLE';'job';'index';<null>;'index_position';[34,...];<null>;1;'Using where'
Источник

4 ответа

Решение

Это большой вопрос. 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;

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

Итак, что делает 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
Источник

Я сделал тесты. Создана следующая таблица:

id  data1   data2
1   1   2
2   2   1
5   2   2
6   3   3
3   3   4
4   4   3

Затем я создал первое соединение с транзакцией:

SELECT id FROM test ORDER BY data1 LIMIT 1 FOR UPDATE;

результатом была строка с id=1;

Затем я создал вторую транзакцию из другого соединения, не совершая первое:

SELECT id FROM test WHERE data1=2 FOR UPDATE;

Это не заблокировало. И он блокировался только тогда, когда я пытался выбрать саму строку, выбранную первой транзакцией. Я попробовал следующее с изменением ORDER BY на DESC, оно также работает.

Вывод: MySQL блокирует только те строки, которые он фактически выбрал при использовании предложений ORDER BY и LIMIT. См. @Morgan ответ для объяснения блокировки пробелов.

Моя версия MySQL 5.0.45

Источник

В некоторых версиях MySQL есть ошибка: # 67745 Слишком много блокировок строк при использовании SELECT для UPDATE, LIMIT и ORDER BY.

Версия: 5.5.28, 5.5.30, 5.7.1

Та же ошибка на моем локальном mysql 5.5.25 win64.

Источник

В отличие от других баз данных, в MySQL запрос заблокирует позиции индекса. Это фактически означает, что все строки, которые в настоящее время имеют status равно 'QUEUED' или хотел бы, чтобы он изменился на 'QUEUED' от другой транзакции заблокированы. Единственное решение, которое я нашел, - это выбрать строки без FOR UPDATEзатем выберите их с помощью фильтра на основе идентификатора и повторно проверьте условие, как только они будут заблокированы. Не приятно, но это делает работу.

Источник
Другие вопросы по тегам