Row Locking во время выборки в MySQL

    Да-да, все «реальные пацаны» умеют строить веб-системы, способные выдержать монументальные нагрузки. Ну а для «непацанофф» всегда есть гугл и масса сайтов посвящёных данной тематике. Однако «проблема роста» включает в себя не только вопрос верной сервировки данных клиенту и их грамотной репликации/распределении на кластере. Зачастую проблемы возникают от того, что всё как раз-таки наоборот — слишком шустро работает. Рассмотрим пример из недавней практики:



    Дано:
    1. Очередь событий (events)
    2. Факт того, что события могут быть связаны в цепочки
    3. Факт того, что события могут генерировать «новые» события
    4. Процессор (daemon), который эту очередь обрабатывает (кастит необходимые классы, подгружает библиотеки, делает то всё что требуется сделать в событии) и отмечает каждую запись в очереди как «обработано», пишет лог и работает дальше.
    5. СУБД MySQL (уж так исторически сложилось)


    Пока что всё довольно чётко — есть таблица для очереди с полями из серии: id (int), event_id (int fk), event_data (blob), execute_at (datetime), executed_at (datetime). Демон берёт по-одному событию, и делает своё злое дело :)

    Но вот наш проект вырос и пользователей в системе прибавилось, машин в кластере тоже прибавилось, ну и заданий в очереди прибавилось соот-но. Пользователям ждать «3 секунды» пока демон соизволит обработать очередной шаг цепочки стало влом и они стали клянчить у саппорта БОЛЬШЕ производительности. Саппорт «рвал и метал» и в итоге решили запустить еще несколько процессоров.

    Соот-но и архитектура очереди изменилась — у нас появились «замки». Проще говоря, прежде чем начать какое-то событие обрабатывать, демон «А» маркирует это событие как «занятое», и все остальные демоны его «не видят». Соот-но процесс выборки и обработки стал выглядеть так (псевдокод):

    if(event = db.select("select id from queue where locked = 'false' and execute_at < NOW() LIMIT 1")){
    db.execute("update queue set locked = 'true' where id = " + event.id);
    [...]
    }
    


    И всё бы было хорошо, если бы система не была построена на кластере из ультра-накаченых машин, и если бы в «пространстве» не было активными около 100 демонов. Ибо, как показала практика, между первым и вторым SQL запросом запросто успевало «вклиниться» ещё несколько демонов и начать отработку того же самого задания. Если исходить из соображений, что одно задание может «кастить» новые, то через несколько суток в очереди может оказаться 1000000+ заданий на исполнение, а логи на сервере перевалят за 10 гектар полезного пространства. Как старшно жить!

    Кто виноват? Что делать? Трансакции не дают должного результата. Как жить? Кого бить? Кому отрывать руки?

    А что нам, собственно, надо? А надо чтобы СУБД автоматом блокировала для других процессов РЯД из которого делается выборка (блокировать таблицу — не предлагать). То бишь чтобы другие демоны просто не видели ряд который попал в мою выборку.

    И чё? И как? А всё — элементарно, в MySQL есть конструкт SELECT * FROM table FOR UPDATE — который именно это и делает. Соот-но, переписываем кусок кода обработки примерно в следующее:

    db.execute("TRANSACTION START");
    if(event = db.select("select id from queue where locked = 'false' and execute_at < NOW() LIMIT 1 FOR UPDATE")){
    [...]
    }
    db.execute("COMMIT");
    


    Вот и вся любовь!

    p.s. Внимание! Всё это работает только на таблицах типа InnoDB!
    Поделиться публикацией

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

      +1
      Приветствую. Хорошая задача подобрана под ответ. Имхо, лучше оформить "##SELECT * FROM table FOR UPDATE##" без решеток - потребовалось несколько секунд чтобы разобраться, что два начальных не часть SELECT.
        0
        Сейчас поправлю!
        0
        А не могли бы Вы рассказать, хотя бы в общих чертах, что за проект?
          0
          Часть ERP системы: конструктор «событий», то бишь администратор портала может сам собирать сложные логические «блоки», которые прорадабываются в случае разные событий. Банальный пример — пользователь системы создаёт в CRM модуле новый контакт с набором характеристик. Админ в «конструкторе событий» описывает логику, где проверяются харакретистики и выполняются разные действия, например: спросить подтверждения у менеджера группы, отправить мейл тому-то, присобачить контакт к такой-то компании, создать где-то какую-то линию действий для этого контакта и тд и тп. В итоге получаются довольно сложные схемы. Если учесть что «событий» в даже очень простой ERP системе может генерироваться сотни в минуту, задача по отработке логики оных нетривиальна. Но как показывает практика — нет невозможных задачь ;)
          0
          Последний запрос, в котором и должен быть волшебный FOR UPDATE, его собственно не содержит :)
            0
            как это не содержит, а как же:
            select id from queue where locked = 'false' and execute_at FOR UPDATE
              0
              баг в Хабре :) теряется всё после знака <
              0
              как это не содержит, а как же:
              select id from queue where locked = 'false' and execute_at < NOW() LIMIT 1 FOR UPDATE
              0
              Салют clops =) а innodDB тюнили как Петя Зайцев учит ???
              ну там нереальный InnoDB_pool_size и т.п.???
                0
                с самой инной проблем не ощущаем, кстати... подкрутили конечно несколько болтиков как в мане рекомендуют, но особых танцев с бубном пока не было )
                  0
                  блин... у меня primary key - char(36) - InnoDB в таких раскладах ВООБЩЕ не проканает =) ибо там внутре записи так распологаются, а вводить id автоинкрементный везде, пока не получается
                0
                execute_at (datetime), executed_at (datetime)

                эх, сдается мне, уже было или еще будет проведен не один час в поисках бага. Тем более, что буквы E и D находятся рядом ;)

                По теме:
                а у вас INSERT в таблицу "queue" не лочится, пока вы пережевываете записи, полученные SELECT ... FOR UPDATE?
                У нас вот стояла похожая задача, мы сделали так (местами синтаксис PHP), грубый пример:
                0) генерируем какой-нибудь уникальный по времени и по демону $pid;
                $pid = $server_uid . ":" . microtime(true);
                1) и подминаем под него интересующие нас записи:
                db_query("UPDATE tbl SET process_id='$pid', in_process=1 WHERE event_id='$eid'");
                2) потом не спеша эти записи обрабатываем:
                db_query("BEGIN");
                $result = db_query("SELECT * FROM tbl WHERE process_id='$pid' AND in_process=1");
                while($row=db_fetch($result){db_query("UPDATE tbl SET value='blablabla' WHERE id='$row->id';");}
                db_query("COMMIT;");
                  0
                  полность согласен:
                  1 - занимаем записи
                  1.а - проверяем, сколько строк затронул апдейт.
                  2. - если затронутые ряды есть - не спеша эти записи обрабатываем:

                  неплохобы еще и организовать демон, которые периодически проверяет - а есть ли записи, залоченные за процесс, но выполняющиеся долго - процесс то мог и вывалиться во время работы.
                  0
                  А всё — элементарно

                  Столько буков ради того, чтобы сказать миру, что Вы наконец-то удосужились прочесть мануал?
                    0
                    Мать моя женщина! Мускулистые парни - это все делал фокспро на автомате - достаточно было выставить один флаг
                      0
                      Полезно в любом случае.
                        +3
                        Спасибо! Как раз на этой неделе появилась задача перелопачивать большую очередь заданий десятками демонов - тоже никак не мог придумать как метить взятые на обработку...

                        Чума нашего времени. Айтишные знания очень глубокие. Есть нетривиальная задача и кто-то придумал ей красивое решение, назовем его "for update", об этом решении знает 1 из 100. 49 спецов потратят несколько часов на чтение мануалов и гугление с негарантированным результатом, еще 50 будут три дня писать incredible machine делающую аналог "for update". И только один готов за полсекунды ответить - "юзайте "for update". Вопрос - как за кратчайшее время найти этого одного?
                          0
                          Найдите один раз одного знакомого, который прошёл нормальный курс подготовки по базам данных, только и всего - тот, где среди прочего рассматриваются транзакции, блокировки и способы их реализации в основных СУБД - и использует эти знания на деле. Если совсем лень - запостите в разделе "Проектирование" на форуме SQL.ru.

                          Если вы сами разработчик - то лучше пройти такой курс самостоятельно.
                            0
                            Не мысли плоско... Таких задач сотни и направлений в айти - не меньше. Искать сотни супергуру себе в знакомые - жизни не хватит.
                              0
                              Задач может и десятки тысяч, а вот направлений - не соглашусь. Базы данных - это один из десятка-двух основных курсов по IT.

                              Какие супергуру? Это элементарная задача для рядового промышленного разработчика БД.
                              0
                              Реализация транзакций и блокировок в каждой СУБД своя поэтому в любом случае потребуется или время на поиск рещения в документации или на поиск человек который уже работал с данной СУБД.
                                0
                                цитриую себя:
                                ...способы их реализации в основных СУБД...

                                на SQL.ru все эти СУБД представлены, к тому же отличий там довольно мало
                                  0
                                  Туплю %) Надо больше спать.

                                  PS У нас читали только общие положения. Разбора конкретных СУБД не производилось.
                              0
                              Кроме того, это решение не является красивым, потому как:
                              1) Да, изолируется обновление строк между процессами.
                              2) Но тот процесс I, который попал на чтение ещё не обновлённой, но заблокированной на запись выборки процессом II, ждёт окончания обновления I-м.

                              Просто поскольку обновление похоже занимает меньше времени, чем обработка, то это даёт выигрыш по сравнению с блокировкой записей на время обработки (полная транзакция).
                                0
                                увы, решение, конечно, не блещет, тут вы — правы! да, вы правы — выигрышь в производительности тут исключительно в том, что обновение занимает меньше времени чем обработка :)

                                однако а) с своей задачей справляется великолепно б) было введено в действие менее чем за сутки (это включая тесты на производительность!)
                              0
                              В Вашем примере где в конце LIMIT 1 FOR UPDATE не будет использоваться построчная блокировка.
                              Если пустить в другом коннекте такой же запрос до того как в первом сказан COMMIT будет стандартное ожидание освобождения лока.

                              Если уж дошли в мануале до селекта, посмотрите в каких случаях происходит построчная блокировка.
                                0
                                Как это "Трансакции не дают должного результата"?! Как раз классическое решение этой задачи - в обертывании select и update в одну транзакцию. Другой вопрос, что mysql... не очень транзакционная СУБД ;) Поэтому ей такие подсказки необходимы, чтобы нормально работать.
                                  0
                                  Не поможет обертывание в транзакцию, если SELECT в ней не for update.
                                  Две транзакции могут сделать SELECT до того, как в первой из них пройдет commit.
                                    0
                                    Согласен, написал не вникнув в задачу. На самом деле достаточно к условию UPDATE дописать WHERE locked = 'false' - и смотреть на количество затронутых рядов. Тогда и транзакции ни к чему - атомарность выполнения update обеспечит отсутствие коллизий.
                                      0
                                      Кстати вполне себе идейное решение :)
                                  • НЛО прилетело и опубликовало эту надпись здесь
                                    0
                                    А я не заморачивался по поводу FOR UPDATE, так как на моем мыскале нет InnoDB...
                                    Я взял следующую выборку:
                                    SELECT * FROM tbl WHERE id%N=n LIMIT скока надо;
                                    где N - количество демонов, обслуживающих очередь,
                                    n - номер демона (присваивается при старте демона и не повторяется у остальных демонов)
                                    В результате не нужно рукоблудие с локами. Правда появилось рукоблудие с демонами, но оно решается гораздо легче.
                                    И рациональное предложение - а не завести ли Вам на каждой физической машине по очереди (MySQL таблица) и паре..тройке демонов, работающих с этой локальной очередью ?
                                      +1
                                      Тоже иногда использую такое распараллеливание, только как правило не по id. Минусы для автора очевидны - возможно такое построение задач, при котором большая часть нагузки уйдёт в один из потоков.
                                        0
                                        демонов сложно менеджить когда их много. более того, они «поднимаются» автоматически при необходимости!
                                          0
                                          Меня спасают pid файлики и расфоркивающиеся демоны.
                                          Удалил файлики - демоны померли.
                                          Запустил отца - детки поднялись, посмотрели pid-файлики и померли, если предыдущий ребенок еще работает...
                                          0
                                          а в SELECT вашем индексы-то не используются. и производительность сильно падает.
                                          0
                                          Мега-прокачанный проект открывает для себя транзакции. Смешно
                                            0
                                            простое применение транзакций в данном случае проблемы не решает, смешно, что не прочитали внимательно
                                              0
                                              FOR UPDATE - это частный случай блокировки, а блокировка - это и есть одна из изюминок транзакций :)
                                                0
                                                А по-моему смешно, что Вы утверждаете, что FOR UPDATE помогает демонам "просто не видеть ряд". Ибо это бред. И, как я уже до этого написал, всё равно вышеприведённые запросы НЕ ПОЗВОЛЯЮТ распараллеливать задачи!
                                                  0
                                                  Не бред, а заблуждение или описка )

                                                  Они позволяют распараллелить обработку, при этом подтормаживая иногда на коллизиях попыток чтения заблокированных строк.
                                              0
                                              К этому надо очень аккуратно подходить. Просчитывать все до мельчайших деталей. Делать нагрузочные тестирования. Мы уже с этим напоролись, такая блокировка создалась на Oracle от SELECT`a FOR UPDATE. Бизнес стоял.

                                              Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.

                                              Самое читаемое