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!