пятница, 19 июля 2013 г.

Конструкция select for update

Есть такие примечательные sql-конструкции:
  • oracle: select ... from table where ... for update
  • ms sql server: select ... from table with (updlock, rowlock) where ...
Смысл и результат выполнения обеих весьма схож: выбрать из таблицы записи для обработки, удовлетворяющие определённым условиям, и установить на выбранные записи блокировку, чтобы предотвратить их чтение/изменение другими пользователями до окончания обработки.
Вероятно, отличия между ними есть. Однако, после столкновения "здесь и сейчас" именно с oracle-вариантом, дальнейшее описание будет посвящено этой конструкции - select for update.


Прим.: в тексте слова "блокировка" и "лок" используются как синонимы, для красоты повествования.

Существует 4 разновидности этой конструкции.
  • select for update - первая и самая простая. Она присутствует в oracle с весьма древних незапамятных времён. Алгоритм таков: запрос читает таблицу, находит желаемые пользователем строки, а затем пытается наложить на них построчную блокировку. Если в процессе этого обнаруживается, что на какой-то строке уже имеется лок от другой сессии, то текущая сессия будет ожидать - бесконечно! - пока лок не будет снят, и только после этого продолжит выполнение.
  • select for update nowait - модификация с опцией nowait. В этом случае при встрече со строкой, заблокированной другой сессией, текущая сессия не будет ждать, а сразу сгенерирует ошибку "ORA-00054 resource busy and NOWAIT specified" и откатит всё, что успела сделать.
  • select for update wait n - функциональность предыдущего варианта сохранялась неизменной также весьма долгое время, пока в oracle 9i release 2 (версия 9.2.0) не появилась новая опция wait n. Она позволяет разработчику управлять поведением и задавать допустимое время ожидания в секундах. Если ни одна запрашиваемая строка не была заблокирована другой сессией - сразу возвращается найденное. При обнаружении лока сессия ждёт указанное время; если лок за это время будет снят - ок, выполнение продолжится. Если же по истечении n секунд хотя бы одна строка продолжит оставаться заблокированной, сгенерируется ошибка "ORA-30006 resource busy; acquire with WAIT timeout expired" с последующим откатом.
  • select for update skip locked - самый "молодой" вариант, появившйся в oracle 11g (версия 11.1.0 (хотя, говорят, он присутствовал в незадокументированном (и потому в неподдерживаемом) виде с версии 8.0)). Он позволяет пропускать строки, которые уже заблокированы. В результате, запрос не тратит время на ожидание, не генерирует при проблемах ORA-00054 / ORA-30006; он просто блокирует то, что может, и идёт дальше. Весьма удобное решение для многопоточной работы, когда важна скорость, а реакцию на пропущенные строки можно безопасно реализовать в коде обработчика данных.
Картинка в тему: отличия вариантов select for update
В целом, поведение select for update skip locked можно описать так:
- второй запрос возвращает только то, что он запрашивал, и что не было залочено первым запросом;
- если второй запрос запрашивает именно то, что уже залочил первый, то на второй запрос будет пустой ответ без ошибок;
- явное включение/отключение автокоммита не влияет на поведение;
- лок снимается только при явном коммите, явном откате и при закрытии соединения.
Впрочем, имеются некоторые особенности (или неожиданности?) при работе, описываемые, например, здесь [http://markjbobak.wordpress.com/2010/04/06/unintended-consequences/] и здесь [http://stackoverflow.com/questions/5847228/oracle-select-for-update-behaviour].

Небольшое пояснение по поводу блокировок (в целом с типами и уровнями локов можно ознакомиться тут [http://my-oracle.it-blogs.com.ua/post-37.aspx]). Сессия, которая выполняет запрос select for update, устанавливает:
  1. блокировку таблицы TM в режиме 2 row share (разделяемая блокировка строки, она же RS), чтобы предотвратить изменения структуры таблицы;
  2. блокировку транзакций (строк) TX в режиме 6 exclusive (монопольная, она же X).
P.S. Картинки взяты (а затем немного подредактированы) отсюда: https://blogs.oracle.com/rammenon/entry/select_for_update_nowait_skip.

3 комментария:

  1. Хорошая статья, если не против, то хотелось бы перепостить на свой блог.

    ОтветитьУдалить
    Ответы
    1. Доброго дня. Что называется - u r welcome, буду только рад!

      Удалить
  2. А если выборка из нескольких таблиц? будет блокировать одну основную или все? или заложет хитрый механизм?

    ОтветитьУдалить