Блог

У меня есть требование, когда нам нужно обновить строку, не удерживая блокировку во время обновления.

Вот подробная информация о требованиях, мы будем запускать пакетную обработку таблицы каждые 5 минут update blogs set is_visible=1 where some conditions , чтобы этот запрос выполнялся на миллионах записей, поэтому мы не хотим блокировать все строки для записи во время обновлений.

Я полностью понимаю последствия отсутствия блокировок записи, что для нас хорошо, потому что столбец is_visible будет обновляться только этим пакетным процессом, никакой другой поток не обновит этот столбец. С другой стороны, будет много обновлений для других столбцов той же таблицы, которые мы не хотим блокировать

Прежде всего, если вы по умолчанию используете механизм хранения InnoDB в MySQL, вы не сможете обновлять данные без блокировок строк, кроме как понизив уровень изоляции транзакции до READ UNCOMMITTED, выполнив

 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
 

Однако я не думаю, что поведение базы данных соответствует вашим ожиданиям, поскольку в этом случае разрешено грязное чтение. ЧТЕНИЕ без ФИКСАЦИИ редко бывает полезным на практике.

В дополнение к ответу от @Tim, действительно неплохо иметь уникальный индекс в столбце, используемом в предложении where . Однако, пожалуйста, также обратите внимание, что нет абсолютной гарантии, что оптимизатор в конечном итоге выберет такой план выполнения, используя созданный индекс. Это может работать или не работать, в зависимости от случая.

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

Кстати, я предполагаю, что ваш пакет имеет более низкий приоритет, чем другие онлайн-процессы, поэтому его можно запланировать вне часов пик, чтобы еще больше минимизировать воздействие.

PS Блокировка IX не относится к самой записи, а привязана к объекту таблицы с более высокой степенью детализации. И даже при ПОВТОРЯЕМОМ уровне изоляции транзакции чтения блокировка пробелов отсутствует, когда запрос использует уникальный индекс.

Рекомендуется всегда устанавливать определенную блокировку, когда есть вероятность, что обновление может произойти одновременно с другими транзакциями. Если вашим механизмом хранения данных является MyISAM, то MySQL заблокирует всю таблицу во время обновления, и вы мало что можете с этим поделать. Если механизм хранения будет InnoDB, то возможно, что MySQL установит эксклюзивную блокировку IX только для записей, на которые нацелено обновление, но в этом случае есть предостережения. Первое, что вы должны сделать, чтобы попытаться достичь этого, было бы SELECT ... FOR UPDATE :

 SELECT * FROM blogs WHERE <some conditions> FOR UPDATE;
 

Чтобы гарантировать, что InnoDB блокирует только обновляемые записи, в столбце, который появляется в предложении, должен быть уникальный индекс WHERE . В случае вашего запроса, предполагая id , что задействован столбец, это должен быть первичный ключ, иначе вам нужно будет создать уникальный индекс:

 CREATE UNIQUE INDEX idx ON blogs (id);
 

Даже с таким индексом InnoDB может по-прежнему применять блокировки пробелов к записям между значениями индекса, чтобы гарантировать соблюдение REPEATABLE READ контракта.

Итак, вы можете добавить индекс к столбцу (столбцам), участвующим в вашем WHERE предложении, для оптимизации обновления в InnoDB.