MySQL - обновить строки таблицы без блокировки строк

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

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

Однако я не думаю, что поведение базы данных является тем, чего вы ожидаете, поскольку в этом случае разрешено грязное чтение. READ UNCOMMITTED редко бывает полезным на практике.

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

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

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

P.S. Блокировка IX находится не в самой записи, а прикреплена к объекту таблицы с более высокой степенью детализации. И даже с уровнем изоляции транзакции REPEATABLE READ блокировка пропусков отсутствует, когда запрос использует уникальный индекс.

Лучшая практика - всегда получать определенную блокировку, когда есть вероятность, что обновление может происходить одновременно с другими транзакциями. Если вашим механизмом хранения является 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.