Ни для кого не секрет, что в различных СУБД блокирование реализовано по разному. Есть несколько основных принципов блокирования в СУБД Oracle, незнание которых может привести к возникновению ошибок в приложениях.

• Oracle блокирует данные на уровне строк и только при изменении. Эскалация блокировок до уровня блока или таблицы никогда не выполняется.
• Oracle никогда не блокирует данные с целью считывания. При обычном считывание блокировки на строки не устанавливаются.
• Сеанс, записывающий данные не блокирует сеанс, читающий данные.
• Сеанс записи данных блокируется, только если сеанс записи уже заблокировал строку, которую предполагается изменять. Сеанс считывания данных никогда не блокирует сеанс записи.

Однако не во всех случаях эти принципы блокировки записей в СУБД Oracle помогают работе. Иногда, особенно при многопользовательской работе, необходимо чтобы те записи, которые выбрали, были заблокированы время, пока с ними идет работа. С этой целью в Oracle есть конструкция select … for update, которая помогает установить такую блокировку. Задача этой конструкции — выбрать из таблицы записи для обработки, удовлетворяющие определённым условиям, и установить на выбранные записи блокировку, чтобы предотвратить их чтение/изменение другими пользователями до окончания обработки.

Существует 4 разновидности этой конструкции:

1. select for update — первая и самая простая. Она присутствует в Oracle с весьма древних, незапамятных времён. Алгоритм таков: запрос читает таблицу, находит желаемые пользователем строки, а затем пытается наложить на них построчную блокировку. Если в процессе этого обнаруживается, что на какой-то строке уже имеется lock от другой сессии, то текущая сессия будет ожидать (бесконечно!) пока lock не будет снят, и только после этого продолжит выполнение.
2. select for update nowait — модификация предыдущей конструкции с добавлением опции nowait. В этом случае при встрече со строкой, заблокированной другой сессией, текущая сессия не будет ждать, а сразу сгенерирует ошибку «ORA-00054 resource busy and NOWAIT specified», и откатит всё, что успела сделать.
3. select for update wait n — функциональность предыдущего варианта сохранялась неизменной также весьма долгое время, пока в oracle 9i release 2 (версия 9.2.0) не появилась новая опция wait n. Она позволяет разработчику управлять поведением и задавать допустимое время ожидания в секундах. Если ни одна запрашиваемая строка не была заблокирована другой сессией — сразу возвращается найденное. При обнаружении lock’a сессия ждёт указанное время; если lock за это время будет снят — Ok, выполнение продолжится. Если же по истечении n секунд хотя бы одна строка продолжит оставаться заблокированной, сгенерируется ошибка «ORA-30006 resource busy; acquire with WAIT timeout expired» с последующим откатом.
4. select for update skip locked — самый «молодой» вариант, появившийся в oracle 11g (версия 11.1.0, хотя, говорят, он присутствовал в незадокументированном, и потому в неподдерживаемом, виде с версии 8.0). Он позволяет пропускать строки, которые уже заблокированы. В результате запрос не тратит время на ожидание, не генерирует при проблемах ORA-00054 / ORA-30006; он просто блокирует то, что может, и идёт дальше. Весьма удобное решение для многопоточной работы, когда важна скорость, а реакцию на пропущенные строки можно безопасно реализовать в коде обработчика данных.

Неплохо показана разница между работой описанных конструкций на картинке ниже:

По материалам ITech notes.