Блог

Главная - Вопросы по программированию - Блокировка строк MySQL и атомарные обновления

Блокировка строк MySQL и атомарные обновления

#mysql #rowlocking

#mysql #блокировка строк

Вопрос:

Я создаю «систему очередей для бедных», используя MySQL. Это одна таблица, содержащая задания, которые необходимо выполнить (имя таблицы queue ). У меня есть несколько процессов на нескольких машинах, задача которых состоит в том, чтобы вызвать fetch_next2 sproc, чтобы получить элемент из очереди.

Весь смысл этой процедуры заключается в том, чтобы убедиться, что мы никогда не позволяем 2 клиентам выполнять одну и ту же работу. Я думал, что использование SELECT .. LIMIT 1 FOR UPDATE позволит мне заблокировать одну строку, чтобы я мог быть уверен, что она была обновлена только 1 вызывающим абонентом (обновлена таким образом, что она больше не соответствует критериям SELECT , используемым для фильтрации заданий, которые «ГОТОВЫ» к обработке).

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

 CREATE DEFINER=`masteruser`@`%` PROCEDURE `fetch_next2`()
BEGIN
    SET @id = (SELECT q.Id FROM queue q WHERE q.State = 'READY' LIMIT 1 FOR UPDATE);

    UPDATE queue
    SET State = 'PROCESSING', Attempts = Attempts   1
    WHERE Id = @id;

    SELECT Id, Payload
    FROM queue
    WHERE Id = @id;
END

Комментарии:

1. Почему так много проблем с созданием очереди для бедных? Почему бы не использовать настоящую очередь. Намного меньше пота

2. @middlestump: но это менее 10 строк кода. 🙂 Кроме того, мне бы очень хотелось понять, как эта блокировка строк работает в MySQL и для других проектов.

Ответ №1:

Код для ответа:

 CREATE DEFINER=`masteruser`@`%` PROCEDURE `fetch_next2`()
BEGIN
    SET @id := 0; 
    UPDATE queue SET State='PROCESSING', Id=(SELECT @id := Id) WHERE State='READY' LIMIT 1;

    #You can do an if @id!=0 here
    SELECT Id, Payload
    FROM queue
    WHERE Id = @id;
END

Проблема с тем, что вы делаете, заключается в том, что для операций нет атомной группировки. Вы используете кнопку ВЫБОРА … ДЛЯ синтаксиса ОБНОВЛЕНИЯ. В документах говорится, что он блокирует «чтение данных на определенных уровнях изоляции транзакций». Но не на всех уровнях (я думаю). Между вашим первым ВЫБОРОМ и ОБНОВЛЕНИЕМ может произойти другой ВЫБОР из другого потока. Вы используете MyISAM или InnoDB? MyISAM может не поддерживать это.

Самый простой способ убедиться, что это работает правильно, — заблокировать таблицу.

[Редактировать] Метод, который я описываю прямо здесь, требует больше времени, чем использование Id=(SELECT @id := Id) метода в приведенном выше коде.

Другим методом было бы выполнить следующее:

  1. Имейте столбец, для которого обычно установлено значение 0.
  2. Выполните «ОБНОВЛЕНИЕ… УСТАНОВИТЕ ColName=UNIQ_ID ГДЕ ColName=0 ОГРАНИЧЕНИЕ 1. Это гарантирует, что только 1 процесс сможет обновить эту строку, а затем получить ее через SELECT впоследствии. (UNIQ_ID — это не функция MySQL, а просто переменная)

Если вам нужен уникальный идентификатор, вы можете использовать таблицу с auto_increment только для этого.

Вы также можете сделать это с помощью транзакций. Если вы запускаете транзакцию в таблице, запускаете UPDATE foobar SET LockVar=19 WHERE LockVar=0 LIMIT 1; из одного потока и делаете то же самое в другом потоке, второй поток будет ждать фиксации первого потока, прежде чем он получит свою строку. Однако это может привести к полной блокировке таблицы.

Комментарии:

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

2. Есть ли конкретный метод из тех, которые я перечислил, который вы хотели бы, чтобы я сделал? Таблица полной блокировки была бы самой простой.

3. Поскольку я использую InnoDB, я предполагаю, что он заблокирует только одну строку, верно? «Если вы используете InnoDB, он заблокирует только строку, с которой вы работаете». Для меня эти параметры звучат нормально.

4. Он выполняет блокировку строк для транзакций, когда строка была обновлена. Я оговорился в том, что я сказал, исправляя это. полная «блокировка записи в таблицу» полностью заблокирует таблицу от всех других потоков.

5. @Dakusen: Хм, мне не нравится идея блокировки всей таблицы. Вы можете предположить, что есть UniqueValue столбец для выполнения того, что вы говорите, для обновления только одной строки. Вместо WHERE ColName=0 того, чтобы, я думаю, вы должны просто иметь возможность использовать WHERE State='READY'