Атомарный ВЫБОР и ОБНОВЛЕНИЕ
Если мои данные выглядят так:
ID STATUS DATE_ADDED
== ========== ==========
1 Processing 2011-04-01
2 New 2011-04-02
3 New 2011-04-03
4 Processing 2011-04-03
5 Done 2011-04-06
6 New 2011-04-06
7 New 2011-04-14
8 Done 2011-04-14
...
... каков рекомендуемый способ выбрать 10 самых старых записей со статусом "Новые" и установить для них статус "Обработка", гарантируя, что любой другой параллельный процесс не сможет сделать то же самое с теми же записями?
Это веб-приложение, работающее на PHP/5.2.6 под управлением Windows Server 2003, которое подключается к удаленному серверу Oracle 10g через ODBC (драйвер Oracle, а не Microsoft).
5 answers
Это трудно сделать в Oracle 10g. В 11g синтаксис SELECT FOR UPDATE ... SKIP LOCKED упрощает это.
Простая инструкция UPDATE будет сериализована. Как и SELECT FOR UPDATE. Конечно, два конкурирующих процесса никогда не получат одинаковые строки; проблема в том, что в лучшем случае они будут сериализованы, а в худшем - могут зайти в тупик.
Рекомендуемым способом было бы использовать Oracle Advanced Queuing (или реализацию очереди по вашему выбору) для постановки в очередь идентификаторов, подлежащих обработке, и разрешить реализацию очереди управляйте борьбой за ценности.
--
SQL будет работать, но выйдет из строя с ORA-00054, если второй пользователь запустит его для тех же смещений, в то время как у кого-то этот диапазон заблокирован. Это можно смягчить, заключив select в цикл, поймав ошибку ORA-00054 и используя ее для увеличения смещений.
select * from my_table
where rowid in
(select row_id
from (select rowid as row_id, rownum as rn
from mytable where some_condition
order by deterministic_sort_order)
where rn between :low_rn and :hi_rn
)
for update nowait;
Выражение сортировки должно быть детерминированным (просто включите первичный ключ в качестве конца выражения сортировки), чтобы предотвратить конфликты.
Для этого используйте транзакцию. Использование уровня изоляции "сериализуемый" для транзакции предотвратит любой другой процесс для доступа/изменения строк, пока ваша транзакция работает над ними.
Если сериализуемая транзакция пытается выполнить инструкцию по обработке данных SQL, которая изменяет любую таблицу, уже измененную незафиксированной транзакцией, инструкция завершается неудачно.
Возможно, вы захотите использовать:
set transaction isolation level serializable;
Самый простой способ решить эту проблему - заблокировать таблицу, чтобы никакой другой сеанс не мог ее обновить:
lock your_table in exclusive mode
К сожалению, другие сеансы не смогут вставлять новые строки до тех пор, пока блокировки не будут сняты, так что это действительно может снизить параллельность приложения.
Вы можете создать новую таблицу и поместить в нее одну строку. Затем ваша программа может заблокировать строку с обновлением или выбрать для обновления, прежде чем перейти к исходной таблице. Это будет работать, если все программы используют одну и ту же процедуру для пометки таблицы "Обработка".
create table Lock_Table (
app_catagory varchar2(20) primary key,
usage_ts timestamp(6)
);Вставьте одну строку: insert into Lock_Table (app_category) values 'APP1' и зафиксируйте. Это одноразовая вставка.
Затем, чтобы заблокировать другие сеансы: update Lock_Table set usage_ts = current_timestamp where app_category = 'CAT1'
Вам не нужен столбец usage_ts, вы можете использовать select for update.
До тех пор, пока вы делаете приведенное выше обновление перед запросом "выберите 10 самых старых", вы гарантируете свой результат. Я рекомендую поместить все в одну процедуру (или одну процедуру в пакете), чтобы программистам приложений было легко "делать правильные вещи"
.Просто для примечания
Кроме того, Оптимистичная стратегия блокировки может быть применена для решения этой проблемы