MySQL: Single row lock implementation

Рубрика: Development, MySQL | 24 June 2009, 16:01 | Vadim Voituk

При разработке приложений с использованием баз данных, часто возникает сценарий “insert if not exists”:

if ( <row exists in table> )
   then <insert new record into table>

Из-за того, что данный блок псевдо-кода не выполяняется атомано, может возникнуть ситуация, когда между проверкой <row exists in table> и выполнением действия <insert new record> паралельный поток (клиент)  выполнит добавление новой записи и в итоге в БД окажется 2 одинаковых записи (или произойдет ошибка “duplicate key”).

Такой, не самый приятный case, в книгах по паралельному программированию называют race condition и обходят путем создания синхронизирующих блокировок , использованием “выпрямителей” и тд.

С точки зрения MySQL надо бы переписать приведенный псевдокод в таком виде:

mysql_query('LOCK TABLE `table` WRITE')

if ( <row exists in table> )
   then <insert new record into table>

mysql_query('UNLOCK TABLES') 

В таком случае таблица будет заблокирована для записи и мы будем уверены что во время выполнения нашего кода, никто, кроме текущего потока, ничего в таблицу не “дозапишет” и race condition не возникнет.

Способ вроде простой, и если мне не изменяет память, даже рекомендовался в какой-то книге по разработке web-приложений.
Самый же существенный его минус – он блокирует ВСЮ таблицу и все запросы на запись в эту таблицу будут ждать окончания выполнения блока кода и вызова “UNLOCK TABLES”.
Это, в условиях интенсивной записи в таблицу, может создать весьма критичный bottleneck в производительности всей системы.

Очень неплохо бы в таком случае выполнять блокировку на уровне не всей таблицы, а только определенной строки. Немного покопавшись в мануале MySQL, отыскал несколько встроенных функций, которые помогают это реализовать:

GET_LOCK(str, timeout)
Создает глобальный мютекс с именем str и временем жизни timeout. При попытке другого потока “занять” уже созданный мютекс, он будет ожидать пока он не освободится вызовом RELEASE_LOCK() или не пройдет timeout секунд
RELEASE_LOCK(str)
Освобождает глобальный мютекс с именем str

Кроме того есть еще 2 вспомогательные фунцкции для проверки создан ли уже мютекс, и если создан, то каким ProcessID: IS_FREE_LOCK() и IS_USED_LOCK()

С учетом вышесказанного описанный выше псевдокод, с использованием row-based блокировки будет выглядеть таким образом:

mysql_query('DO GET_LOCK("my-prefix.my-row-id", 60)')

if ( <row exists in table> )
   then <insert new record into table>

mysql_query('DO RELEASE_LOCK("my-prefix.my-row-id")') 

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

25 Responses to “MySQL: Single row lock implementation”

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

  1. crypto5

    А не оптимальнее ли делать синхронизацию в приложении а не на уровне БД?

  2. Vadim Voituk

    Может и оптимальнее – тут уже на вкус и цвет…
    С точки зрения MySQL наверное и потимальнее, потому как слишком много ожидающих потоков может вызвать “Too many connections”.
    Хотя реализовывать свои system-wide мютексы с поддержкой timeout на уровне приложения – is a mission in itself.

  3. corsair

    Питання – а констрейнт на унікальність запису хіба не допоможе?

  4. Vadim Voituk

    corsair,
    Каким образом?

  5. corsair

    ну типу якось так – в тебе в таблиці Users є констрейнт який каже що поле name повинно мани унікальні записи. При вставці нового рядка можеш отримати ексепшен, якщо юзер з таким ім”ям уже є в таблиці.

  6. Vadim Voituk

    Ну такой метод не защитит от изменений “неиндексных” данных.
    Да и “отлов” exception-а мне не очень нравится.

  7. cheplv

    2 Варианта
    1) Insert .. ON DUPLICATE KEY UPDATE …
    2) SELECT FOR UPDATE

  8. Vadim Voituk

    cheplv
    1. Не всегда то, что нужно, т.к. иногда после SELECT нужно провести манипуляции с данными, а уже потом выполнить INSERT/UPDATE

    2. Вот про это не знал, спасибо – обязательно почитаю внимательнее.
    Насколько я понял оно работает только для InnoDB?

  9. cheplv

    1) Вобще через insert on dup key update + triggers если нужно проводить манипуляции с данными (ins/update/del сам по себе блокирует таблицу). А в целом если надо запретоть запись в таблицу – то LOCK WRITE – как то так было.
    2) Да – Инно поддерживает row lock.

  10. FractalizeR

    А откуда известно, что GET_LOCK работает через мьютексы?

  11. Vadim Voituk

    Вообще-то “мютекс” – это общее понятие, и тут я использовал его именно так.
    А вопрос правильнее перефразировать “используются в реализации GET_LOCK cистемные глобальные мютексы ОС”.
    Ответ – не знаю.

  12. cheplv

    В целом неаправильно ставить LOCK на таблицу – оптимальный вариант в вашем случае: insert on dup key update + triggers

  13. Alexius

    А почему бы просто транзакции не использовать, зачем такие извращения? В innodb вроде поддержка транзакций есть..

  14. Vadim Voituk

    cheplv
    потому я и использую реализацию вроде single-row-lock, хотя упомянутый тобой SELECT FOR UPDATE существенно упрощает жизнь на InnoDB-таблицах

    Alexius
    Потому что транзакции решают иную задача.
    В случае с InnoDB-транзакциями получается “кто последний, того и тапки”.
    Пример:
    TransactionA: Start
    TransactionB:Start
    TransactionA: if =>

  15. Vadim Voituk

    cheplv
    потому я и использую реализацию вроде single-row-lock, хотя упомянутый тобой SELECT FOR UPDATE существенно упрощает жизнь на InnoDB-таблицах.
    К тому же INSERT … ON DUPLICATE KEY еще и ОЧЕНЬ медленный.

    Alexius
    Потому что транзакции решают совершенно иную задачу – ACID – т.е. атомарность операций, а никак не синхронизацию доступа.
    В случае с InnoDB-транзакциями получается что-то вроде:
    TransactionA: Begin
    TransactionB: Begin
    TransactionA: if => false
    TransactionB: if
    => false
    TransactionA: => OK
    TransactionB:
    => OK
    TransactionA: commit => OK, row added
    TransactionB: commit => Exception: row already exists
    или как вариант
    TransactionB: commit => OK, added another row instance, got - RACE CONDITION

  16. Dima

    Зачем мучать себе мозе ? Не проще ли хранимую процедурку написать, вызвав которую одним запросом – и она все сделает. Так красивее!! ИМХО. И плюс быстрее..

  17. Vadim Voituk

    Dima
    Сделает что? Исключит паралельный доступ к строке?

  18. Dima

    Да советую почитать книжечку по MySQL Strored Procedure. В текущей логике – у тебя получается ты блокируешь – посылаешь запрос – получаешь ответ – смотришь на него – посылаешь в зависимости от ответа еще запрос – разблокируешь. С хранимой процедурой ты посылаешь инфу на сервер и получаешь ответ в котором написано как все прошло . С моей точки зрения так проще.!!! Плюс хранимые процедуры позволяют не менять код в случае чего а просто поменять процедуру.

  19. Alexius

    >TransactionB: commit => OK, added another row instance, got – RACE CONDITION

    такой ситуации никак не может быть, если использовать должный уровень изоляции в транзакции. В режиме serialize если одна транзакция попытается изменить данные, которые используются другой, то возникнет исключение, которое можно обработать и откатить 2ю транзацию и попытаться её повторить позже.
    ИМХО, это как раз та ситуация, когда транзакции и нужны. Не нужно городить огород с мютексами и блокировкой таблицы (что за бред!).

  20. crypto5

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

  21. Vadim Voituk

    Alexius,
    Как раз бред – это пихать транзакции туда, где ими и не пахнета, а банально нужна блокировка записи или “выпрямилель”,
    Ну и ловить в этом случае исключения о провале транзакции – полная ахинея – получается, что попытки COMMIT-ов выполняют роль функции IS_FREE_LOCK (Ok=Not Locked, Exception=Locked) – не находите это “перегибом”?

    Согласен что SELECT FOR UPDATE более красивое решение, но на не-InnoDB-таблицах лучшего я не придумал.
    Также согласен с crypto5, что лучше это делать на уровне приложения, но тогда мне самому прийдется заботиться он “замерших” блокировках и таймаутах

    Вырезка из документации MySQL o GET_LOCK:
    This function can be used to implement application locks or to simulate record locks.

    Мне кажется вы неправильно поняли исходную задачу – нам надо выполнить действия ОБЕИХ паралельных клиентов, но “по очереди” дабы избежать описанного race condition.

    Проясните как “использовать должный уровень изоляции в транзакции” для описанной задачи в MySQL?

  22. bioplex

    придумалось такое:
    Представьте сидящих в кафешке посетителей, вызывающих по почти-почти одновременному сигналу официанта. Предлагается реализовать подачу такого сигнала через переменную, первоначально равную нулю. При подаче сигнала, выражаущего намерение обслужиться, значение переменной возрастает на единицу. Итак, при условии нуля “посетитель” увеличивает значение на единицу и сразу же проверяет, не успел ли кто одновременно подать такой же сигнал (т.е. переменная могла стать больше 1). Если так, то он сразу же уменьшает на 1 переменную и снова “ждёт” нуля. Как только “клиент” обслужился, переменная сбрасывается в ноль. Ещё может быть случай с образованием очереди (думаю это вы знаете как реализовать).

  23. bioplex

    ^^^ это если решать на уровне приложения

    з.ы. это всё догадки, у меня только махонький экспиренс на PHP, поэтому мог не понять всей тонкости ситуации

  24. v0id

    Зашел сюда случайно, но не сдержался написать :)
    Как раз такие задачи и решаются транзакциями (они как раз и были придуманы для решения проблемы одновременного изменения записи в таблице двумя клиентами). Для работы клиентской части используется две транзакции – пишущая и читающая. Причем время жизни пишущей очень мало, она живет только на время выполнение запроса insert\update. Какой метод блокировки(wait\nowait) ты выберешь, уже зависит от твоей задачи. Такая схема используется в многопользовательских приложениях с большим количеством insert\update запросов.

  25. Vadim Voituk

    @v0id,
    А какими механизмами в MySQL можно указать типа блокировки wait/nowait?

Leave a Reply