#mysql #sql-update #mariadb #innodb #table-locking
#mysql #sql-update #mariadb #innodb #блокировка таблицы
Вопрос:
Предположим, я выполнил это утверждение:
UPDATE Employees set country='AU'
В таблице InnoDB, Employees, около 10 миллионов строк.
Эта таблица также активно обновляется другими пользователями с помощью SQL-запросов, подобных приведенному ниже:
Например, пользователь с ID = 20 меняет свою страну на NZ:
UPDATE Employees set country='NZ' where id = 20
- В этом случае будут ли какие-либо дальнейшие обновления этой таблицы блокироваться до завершения
общего обновления? - Если да, есть ли способ разрешить одновременный запуск определенных обновлений и общего обновления, если они не обновляют одну и ту же строку? (Чтобы уточнить, что я здесь имею в виду: предположим, что общее обновление завершает обновление сотрудников с идентификатором 1-50 и теперь обновляет Emplyoees 51 — ~ 10 миллионов, должно быть выполнено единственное обновление для сотрудника с идентификатором 20, не дожидаясь завершения общего обновления)
Комментарии:
1. Совершенно ненормально изменять все строки таблицы с помощью одного
UPDATE. Зачем нуженcountryстолбец, если таблица используется только для Австралии?2. Извините, country не может быть хорошим примером. В производстве мы всегда сбрасываем определенные счетчики на ноль для всех пользователей на ежедневной основе.
3. 50 миллионов строк? Обновляется ли счетчик более одного раза в день? (Если нет, есть тривиальный обходной путь.)
Ответ №1:
-
Да, первое обновление установит эксклюзивные блокировки на все записи в таблице, блокируя другие запросы от ее обновления. Блокировки удерживаются до тех пор, пока транзакция не будет совершена.
-
Нет. Блокировки удерживаются во время выполнения транзакции и освобождаются при совершении транзакции. Возможно, вы захотите обновлять таблицу по частям, а не за один большой взрыв, избегая первого обновления, блокирующего всю таблицу. Или выполните обновление в нерабочее время, если это возможно.
Комментарии:
1. Предположим, что рабочее время составляет 24/7, тогда подход к блоку является наиболее оптимальным? Других способов обойти эту проблему блокировки нет?
2. Как я уже писал, блокировки являются эксклюзивными. Одновременно только одна транзакция может удерживать ее в записи.
3. Я одобрю ваш ответ, но могу ли я узнать из ваших источников, что блокировка является блокировкой таблицы?
4. @lbj99 — DMLS выполняют только блокировки строк ; DDLS могут выполнять блокировки таблиц .
5. @lbj99 все строки в таблице будут заблокированы, поскольку в вашем обновлении нет предложения where для фильтрации любых записей.
Ответ №2:
Давайте «подумаем из коробки»…
Есть 2 столбца. Один с counter ; один ( dy ) с DATE последним приращением счетчика. Затем сделайте настройку счетчика немного более сложной, а именно сбросить его до 1, если дата до сегодняшнего дня. Также (всегда) обновляйте дату до CURDATE() .
Что-то вроде
UPDATE t
SET counter = IF (dy = CURDATE(), counter 1, 1),
dy = CURDATE()
WHERE id = 123
Это устраняет большое, еженощное обновление.
Для получения счетчика за текущий день,
SELECT IF (dy = CURDATE(), counter, 0) AS counter
WHERE id = 123;
Этот метод также позволяет избежать необходимости запускать большое обновление ровно в полночь. И «ошибка» второго порядка, если компьютер оказался неработающим в полночь (и обновление не удалось запустить в течение дня).