Форум пользователей MySQL

Здравствуйте!
После прочтения кучи документации так и остался нерешенным нубский вопрос

В теоретическом виде он выглядит так:

Есть MySQL таблица foobar с тремя полями id (PK), foo и bar и тысячами (миллионами) строк.

К ней постоянно php-скрипты делают запросы вида SELECT * FROM foobar WHERE id=[число], 500+ в секунду.

И 50+ запросов/сек с логикой вида
SELECT foo FROM foobar WHERE id=[число]
Если foo == 0, то выходим из скрипта
Если foo > 0, пишем foo=0, долго работаем c сохраненным значением foo, с другими таблицами, и потом
UPDATE foobar SET foo = [новое_foo], bar = bar + [производное_от_foo] WHERE id=[число]

Т.е. 1 скрипт начинает работу, делает все операции, обновляет таблицу, в то время как остальные тыкаются в только что проставленное foo=0 (по сути флаг занятости строки) и выходят.

Вопрос: как обеспечить невозможность ситуации
1) Скрипт 1 читает foo>0
2) Пока он не сделал апдейт foo=0, другой скрипт успевает прочесть foo>0
3) Оба (или много если не повезет) скрипта отправляются работать паралелльно
4) На выходе месиво

при этом
1) не прибив все остальные SELECT * FROM foobar WHERE id=[число] на время работы скрипта
2) обеспечив максимальное быстродействие

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

Попытки решения:
1) MyISAM таблица:

LOCK TABLES foobar WRITE
SELECT foo FROM foobar WHERE id=[число]
Если foo == 0, то UNLOCK TABLES и выходим из скрипта
Если foo > 0, пишем foo=0, UNLOCK TABLES, долго работаем c сохраненным значением foo, с другими таблицами, и потом
UPDATE foobar SET foo = [новое_foo], bar = bar + [производное_от_foo] WHERE id=[число]

Недостатки:
Скриптов тысячи. Пусть даже блокировка на время проверки 1 параметра очень недолга, при множестве скриптов непрерывные блокировки чтения и записи всех строк всей таблицы приводят к тому, что больше и больше отправляется в table_locks_waited т.е. ждут своей очереди, накапливается ворох запросов (как на "блокированное обновление", так и на просто почитать), вплоть до краша.

2) InnoDB таблица:
START TRANSACTION
SELECT foo FROM foobar WHERE id=[число]
Если foo == 0, то выходим из скрипта
Если foo > 0, пишем foo=0, COMMIT, START TRANSACTION, долго работаем c сохраненным значением foo, с другими таблицами, и потом
UPDATE foobar SET foo = [новое_foo], bar = bar + [производное_от_foo] WHERE id=[число]
COMMIT

Проблемы:
теперь на выходе нет месива, но все равно много (произвольно много) скриптов могут одновременно работать с одной строкой (вклинившись между первыми START TRANSACTION и COMMIT, прочитав foo>0). В результате быстродействие сильно падает. При этом оно падает и от того, что вместо MyISAM стал InnoDB.

3) пока самое интересное:
UPDATE foobar SET foo = 0 WHERE foo>0 and id=[число]
Если вернулось rows affected = 0, выйти из скрипта
Если > 0, долго работаем c сохраненным значением foo, с другими таблицами, и потом
UPDATE foobar SET foo = [новое_foo], bar = bar + [производное_от_foo] WHERE id=[число]

будет ли это работать в MyISAM без коллизий?

Как же решить этот вопрос? Т.е сделать так, что
1) Скрипт 1 читает foo>0 в строке с каким-то id и хочет проставить foo=0
2) Другие скрипты (никто кроме 1) после 1) не могут тронуть эту строку и видят foo=0
3) Другие скрипты могут свободно работать с другими строками и читать из любых строк, пока скрипт 1 обновляет строку
4) и при этом все не тормозит?

Отредактированно DmitriyIT (11.09.2009 17:14:03)