Oracle как заблокировать запись

Обновлено: 06.07.2024

Блокировки транзакций. Захват и ожидание в исключительном режиме

Механизм взаимоблокировки

Вначале создадим тестового пользователя zh и выдадим ему все необходимые привилегии:

Образуем два сеанса и для наглядности разбора ситуации включим в первом из них трассировку на уровне событий ожидания. В этом же сеансе будем последовательно изменять первую и вторую строки, а во втором проделаем те же действия, но в обратном порядке.

В результате мы видим, что в каждом из сеансов имеется по одной TX-блокировке в установившемся исключительном режиме. Определить, к какой транзакции относится каждая из этих TX-блокировок, можно с помощью содержимого дополнительных столбцов ID1 и ID2. В них находятся составные части идентификаторов транзакций, декодировать которые можно с помощью следующего запроса:

Данный идентификатор нам ещё встретится в дальнейшем, когда мы будем разбирать содержимое трассировочного файла взаимной блокировки, а пока продолжим последовательность начатых нами действий и изменим в первом сеансе содержимое второй строки:

Сеанс находиться в ожидании. Оно возникло от того, что первый сеанс пытается установить TX-блокировку в исключительном режиме второй строки, которая уже захвачена TX-блокировкой второго сеанса. Если в это время заглянуть в файл трассировки первого сеанса, то мы увидим там следующие строки:

Итак, на данный момент мы имеем классическую картину ожидания. Но что произойдет, если мы изменим во втором сеансе первую строку? Ведь она уже захвачена первым сеансом:

Убедимся в том, что транзакции отменены не были. Для этого выполним следующий запрос:

Как видно из результатов запроса, транзакции по-прежнему активны. Отменять их полностью у Oracle нет необходимости, достаточно лишь просто вернуться в одном из сеансов к неявной точке сохранения, которая делается перед каждым DML-оператором.

Что же касается самой Oracle, то в результате всех перечисленных выше действий, приведших к возникновению взаимной блокировки, в файл журнала alert_xe.log будет занесена запись следующего вида:

Трассировочный файл взаимной блокировки

Вспомним, как Oracle обнаруживает блокировки. Для этого он постоянно строит граф ожидания транзакций. Если в этом графе обнаружен цикл, то это означает, что возникла взаимная блокировка. Так вот именно этот цикл и отображается в секции Deadlock graph, правда в очень специфическом виде.

Итак, граф расшифрован. Он дал нам описание цепочки захватов и ожиданий TX блокировок в сеансах. Но по этой цепочке мы можем судить только об общей картине возникновения взаимной блокировки. Если же нам потребуется найти конкретные ресурсы, из-за которых возникают ожидания, сделать нам это будет затруднительно. К счастью Oracle сам позаботился об этом, записав в файл трассировки информацию о строках, освобождения которых от TX-блокировок ожидают сеансы. Рассмотрим более подробно эту секцию. Найти её можно сразу после графа, по ключевой строке Rows waited on:

В этой секции для каждого ожидающего сеанса, который перечислен в графе, указана строка, TX-блокировку которой пытается получить этот сеанс. Строка идентифицируется номером объекта, которому она принадлежит, и идентификатором ROWID. Чуть ниже дана их полная расшифровка в десятичном виде. Это позволяет с лёгкостью, обратившись, например, к системному представлению dba_objects, идентифицировать объект, которому принадлежит данная строка:

Следующая секция трассировочного файла, которую мы рассмотрим, хотя и не столь важна, позволяет дополнить картину взаимной блокировки. Она располагается сразу за секцией Rows waited on и находится по следующим ключевым словам:

Из этой секции можно получить информацию о пользователе, приложении и текущем SQL-курсоре ожидающих сеансов, вовлеченных в процесс взаимной блокировки. Иногда это может быть полезно при поиске причин образования ситуации взаимного блокирования.

Как определить по содержимому трассировочного файла, что возник первый сценарий взаимного блокирования? Для ответа на этот вопрос обратимся в первую очередь к графу взаимной блокировки. Для начала мы должны определить, с какого идентификатора начинаются имена ресурсов графа в столбце «Resource Name». В нашем случае это всегда будет идентификатор TX, то есть блокировка транзакции. Далее нам следует проверить значения режимов блокировок, отображаемые в столбцах holds и waits. Они должны иметь одинаковое значение, равное символу X. Не следует также забывать, что данный сценарий взаимного блокирования возникает на уровне строк, и, следовательно, в секции «Rows waited» всегда будут присутствовать данные об ожидающих строках. Отсюда следует непреложное правило о том, что в первой секции «Current SQL statement for this session» при данном сценарии вы никогда не встретите оператора INSERT, так как строки, вставленные в одном из сеансов, никогда не будут доступны для другого сеанса до фиксации транзакции.

Выводы

Пришло время обобщить информацию, которую мы получили, изучая взаимную блокировку, образующуюся по первому сценарию. Обычно такая взаимная блокировка возникает из-за неодинаковой последовательности обработки строк в пределах транзакций разных сеансов. Если бы мы, к примеру, в нашем случае проводили бы обновление строк таблицы в каждом из сеансов последовательно в соответствии со значением ключевого столбца «c1», то мы могли бы избежать взаимных блокировок. Образовалась бы простая очередь ожидания ресурса. Правда, бывают ситуации, когда одинаковую последовательность обработки строк в разных сеансах сделать трудно или даже невозможно. Как тогда выходить из ситуации?

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

Блокировка - это механизм, используемый для управления одновременным доступом к общему ресурсу. Блокирование происходит, когда один сеанс удерживает ресурс , запрашиваемый другим сеансом . В результате запрашивающий сеанс будет заблокирован - он "повиснет" до тех пор, пока удерживающий сеанс не завершит работу с ресурсом.

Оптимистическое блокирование (optimistic locking ) - стратегия блокирования набора данных, при которой раздел, содержащий изменяемую запись , блокируется только на время внесения изменений в запись программой, но не пользователем.

Пессимистическое блокирование (pessimistic locking ) - стратегия блокирования набора данных, при которой раздел, содержащий изменяемую запись , блокируется на все время внесения изменений в запись пользователем и не доступна для редактирования другим пользователям.

Особенность механизма блокировок Oracle - блокировки данных не хранятся как отдельный ресурс , а содержатся непосредственно в блоках данных . Это позволяет избежать таких проблем, как эскалация блокировок. Ниже перечислены пять основных классов блокировок в Oracle . Первые три - общие (используются во всех базах данных Oracle ), а две остальные — только в OPS ( Oracle Parallel Server - параллельный сервер ).

Блокировки ЯМД ( DML locks). ЯМД означает язык манипулирования данными ( Data Manipulation Language ), то есть операторы SELECT , INSERT , UPDATE и DELETE . К блокировкам ЯМД относятся, например, блокировки строки данных или блокировка на уровне таблицы, затрагивающая все строки таблицы.
Блокировки ЯОД ( DDL locks). ЯОД означает язык определения данных ( Data Definition Language ), то есть операторы CREATE , ALTER и так далее. Блокировки ЯОД защищают определения структур объектов.
Внутренние блокировки ( internal locks) и защелки ( latches ). Защелки - это простые низкоуровневые средства обеспечения последовательности обращений. Защелки обычно запрашиваются системой в режиме ожидания. Это означает, что, если защелку нельзя установить, запрашивающий сеанс приостанавливает работу на короткое время, а затем пытается повторить операцию. Другие защелки могут запрашиваться в оперативном режиме, то есть процесс будет делать что-то другое, не ожидая возможности установить защелку. Защелки выделяются случайным образом. Внутренние блокировки - более сложное средство обеспечения очередности доступа, они позволяют запрашивающему "встать в очередь" в ожидании освобождения ресурса . Запрашивающий защелку сразу уведомляется об освобождении ресурса . В случае внутренней блокировки запрашивающий полностью блокируется.
Распределенные блокировки ( distributed locks). Эти блокировки используются сервером OPS для согласования ресурсов машин, входящих в кластер . Распределенные блокировки устанавливаются экземплярами баз данных, а не отдельными транзакциями .
Блокировки параллельного управления кэшем ( PCM - Parallel Cache Management Locks). Такие блокировки защищают блоки данных в кэше при использовании их несколькими экземплярами баз данных.

SQL Server поддерживает три основных типа блокировок:

Shared Lock - разделяемая блокировка , которая используется при выполнении операции чтения данных . Позволяется чтение данных другой транзакцией, но запрещено изменение данных.
Exclusive Lock - монопольная блокировка , которая применяется при изменении данных. Эта блокировка полностью запрещает доступ к данных другим транзакциям.
Update Lock - блокировка обновления , которая является промежуточной между разделяемой и монопольной блокировкой . Используется, когда транзакция хочет обновить данные в какой-то ближайший момент времени, но не сейчас, и, когда этот момент придет, не хочет ожидать другой транзакции . В этом случае другим транзакциям разрешается устанавливать разделяемые блокировки , но не позволяет устанавливать монопольные.

Блокировки могут устанавливаться на трех уровнях -

Уровень строк - уровень по умолчанию.
Уровень страницы - используется для более эффективного использования ресурсов и в случае если в данной странице много строк с этой блокировкой.
Уровень таблицы - используется при операциях над таблицей и в случае эскалации блокировок.

Взаимоблокировки

Возможна ситуация, когда две транзакции блокируют друг друга так, что продолжение работы не возможно без принудительного завершения одной из транзакций .

Главная /
Статьи /
Oracle /
Практическое администрирование Oracle - Файл паролей. Часть 1.

Практическое администрирование Oracle - Взаимные блокировки. Часть 1.

Введение

В заимная блокировка (deadlock) —это ситуация в СУБД, при которой двое или более сеансов находятся в состоянии бесконечного ожидания ресурсов, захваченных самими этими же сеансами. При обычном состоянии, когда один из сеансов захватывает, какой либо ресурс, другие сеансы будут ожидать его освобождения, выстраиваясь в очередь, друг за другом. Но если удерживающий сеанс не может освободить ресурс, вследствие того что он ожидает освобождения ресурса, захваченного одним из ожидающих сеансов, возникает парадоксальная ситуация, при которой ни один из захваченных сеансами ресурсов не может быть освобождён. В этом случае СУБД сама должна вмешаться в процесс бесконечного ожидания и принудительно освободить запрос в сеансе к одному из ресурсов. При этом никак не должна пострадать уже начатая транзакция сеанса. СУБД должна только отменить последнее действие в сеансе, которое привело к сложившейся ситуации взаимного блокирования, и предоставить сеансу решить, зафиксировать ли изменения в данных или отменить их.

Надо сказать, что возникновение взаимного блокирования это исключительный случай для СУБД. У Oracle даже для этого случая есть специальная ошибка ORA-00060. Если взаимные блокировки возникают очень редко, то можно просто игнорировать эту ошибку, обрабатывая в приложении данное исключение. Но когда подобная ошибка начинает возникать очень часто, требуется детальный анализ возникающей ситуации. Большую помощь в этом нам может оказать файл трассировки, который создаёт Oracle в каждом случае возникновения взаимных блокировок. Образуется этот файл в каталоге, который определяется параметром инициализации user_dump_dest. Кроме создания файла, Oracle делает так же запись о возникшей ошибке в системный журнал alert.log. В этом журнале наряду с самим фактом происшедшей ошибки записывается ссылка к образованному файлу трассировки. Так как данный журнал и файл доступны только администратору базы данных, одним из пунктов его обязанностей, должно являться обнаружение подобных записей, и предоставление разработчику приложения всей доступной информации для исправления повторяющихся взаимных блокировок.

Граф ожидания транзакций

Прежде чем начинать подробно изучать устройство взаимоблокировки, попробуем разобраться в том, как Oracle обнаруживает ситуации взаимного блокирования. В будущем это позволит нам легче понять этот механизм при некоторых сложных сценариях развития взаимной блокировки, а так же позволит лучше ориентироваться в содержимом трассировочного файла взаимного блокирования.

В Oracle, впрочем, как и в других современных СУБД, поиск ситуаций взаимного блокирования происходит постоянно. Вначале строится так называемый граф ожидания транзакций. Граф состоит из вершин и соединяющих их рёбер. Существуют два типа вершин – это вершины соответствующие транзакциям или сеансам, и вершины, представляющие из себя ресурсы или объекты. Ребра в данном случае представляют собой блокировки. Если блокировка захвачена, то ребро направлено от вершины соответствующей сеансу к вершине определяющей ресурс. Если же блокировка ожидает установки, то, наоборот, ребро направлено от вершины ресурса к вершине соответствующей сеансу. Если в этом сплетении рёбер и вершин обнаруживается цикл, то это означает, что возникла ситуация взаимного блокирования. При этом Oracle должен выбрать и отменить одно из ожидающих рёбер, что приведёт к разрыву цикла и нормализации ситуации.

В дальнейшем для некоторых сложных сценариев взаимной блокировки мы будем приводить схематическое изображение графа ожидания транзакций, что позволит нам увидеть взаимную блокировку в графическом виде.

Сценарии возникновения

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

Блокировки транзакций. Захват и ожидание в исключительном режиме.

Рассмотрим первый сценарий взаимного блокирования. Необходимым условием для его возникновения является наличие в двух разных сеансах установленной и ожидающей блокировок транзакций (TX) в исключительном режиме. Моделировать ситуацию будем с использованием редакции Oracle Express Edition и инструмента администратора ZhiSQL for Oracle.

Механизм взаимоблокировки

Вначале создадим тестового пользователя zh и выдадим ему все необходимые привилегии:

Далее, создадим простую таблицу и вставим в неё две строки:

Образуем два сеанса и для наглядности разбора ситуации включим в первом из них трассировку на уровне событий ожидания. Далее, в этом же сеансе будем последовательно изменять первую и вторую строки, а во втором проделаем те же действия, но в обратном порядке.

В результате выполненных нами действий в существующих сеансах были открыты две транзакции. В первом сеансе была выставлена блокировка транзакции (TX) на первую строку в исключительном режиме. Такая же блокировка выставлена и во втором сеансе, но на вторую строку. Убедиться в этом, мы можем, сделав небольшой запрос к системному представлению v$lock:

Рассмотрим более подробно содержимое этого запроса. Столбец SID здесь содержит идентификаторы первого и второго сеансов (28 и 24). Содержимое столбца TYPE указывает на тип блокировки, в нашем случае блокировки транзакции (TX). Столбец LMODE хранит значение 6, что соответствует установившемуся исключительному режиму блокировки. В результате мы видим, что в каждом из сеансов имеется по одной TX блокировке в установившемся исключительном режиме. Определить к какой транзакции относиться каждая из этих TX блокировок можно с помощью содержимого дополнительных столбцов ID1 и ID2. В них находятся составные части идентификатора транзакций, декодировать которые можно с помощью следующего запроса:

Расшифровав значения столбцов ID1 и ID2, мы получили номер сегмента отката, слот и номер изменения транзакции. Эти значения полностью совпадают со значениями из представления v$transaction и все вместе, в шестнадцатеричном виде, представляют собой идентификатор транзакции:

Данный идентификатор нам встретиться ещё в дальнейшем, когда мы будем разбирать содержимое трассировочного файла взаимной блокировки, а пока продолжим последовательность начатых нами действий и изменим в первом сеансе содержимое второй строки:

Сеанс находиться в ожидании. Оно возникло от того, что первый сеанс пытается установить TX блокировку в исключительном режиме на вторую строку, которая уже захвачена TX блокировкой второго сеанса. Если в это время заглянуть в файл трассировки первого сеанса, то мы увидим там следующие строки:

В сеансе постоянно возникает ожидание “конкуренция блокировки строки”. В параметрах этого ожидания мы видим уже знакомые нам значения идентификатора транзакции второго сеанса (slot, sequence). Именно эта транзакция, установила ранее TX блокировку в исключительном режиме на вторую строку и привела к ожиданию. Более детально это можно просмотреть в содержимом представления v$lock:

Как мы видим, в представлении появилась новая строка о TX блокировке со значением 6 в поле REQUEST. Данное значение означает, что сеанс 28 запросил установку TX блокировки на строку в исключительном режиме. При этом значения столбцов ID1 и ID2 этого запроса содержат идентификатор транзакции сеанса 24. Это свидетельствует о том, что первый сеанс ожидает освобождения строки захваченной транзакцией именно второго сеанса.

И так, на данный момент, мы имеем классическую картину ожидания. Но что произойдет, если мы изменим во втором сеансе первую строку? Ведь она уже захвачена первым сеансом:

Возникает ожидание, и мы становимся свидетелями типичного случая взаимного блокирования. Второй сеанс ожидает освобождения первой строки, заблокированной в результате начатой транзакции первого сеанса. В то же время первый сеанс ожидает освобождение второй строки, заблокированной транзакцией второго сеанса. Такое ожидание могло бы продолжаться вечно, но как мы рассматривали ранее, Oracle сам определяет подобные возникающие тупиковые ситуации в течение примерно трёх секунд. В нашем случае он просто отменит выполнение последнего оператора UPDATE в первом сеансе и сгенерирует следующую ошибку:

Убедимся в том, что транзакции отменены не были. Для этого выполним следующий запрос:

Теперь мы должны решить, что нам делать с этой незавершённой транзакцией в первом сеансе. Повторять отменённый оператор не имеет смысла. Ситуация взаимного блокирования повториться, и ошибка возникнет уже в другом сеансе. Поэтому нам остаётся либо произвести отмену, либо зафиксировать транзакцию. После этого второй сеанс продолжит выполнение оператора UPDATE, так как необходимая строка для этого будет освобождена.

Что же касается самой Oracle, то в результате всех перечисленных выше действий приведших к возникновению взаимной блокировки, в файл журнала alert_xe.log будет занесена запись следующего вида:

По этой записи мы всегда можем определить, когда произошел случай взаимного блокирования, а также получить ссылку к образовавшемуся трассировочному файлу. Чуть ниже мы попытаемся детально разобрать содержимое этого файла, так как в большинстве случаев он является единственным источником дополнительной информации о произошедшей взаимной блокировке.

Трассировочный файл взаимной блокировки

Сам файл трассировки содержит множество данных, в том числе здесь находится и полный дамп состояния процессов Oracle на момент возникновения взаимоблокировки. Но нам важны только несколько секций файла. Первая из них – это текущий SQL оператор сеанса, который столкнулся с ошибкой взаимной блокировки и был отменён. Для этого находим в файле строку DEADLOCK DETECTED. Чуть ниже её, после ключевых слов «Current SQL statement for this session» будет находиться необходимая нам секция:

Вторая секция, которая нас заинтересует – это граф взаимной блокировки. Он находиться после ключевой строки Deadlock graph, и отображает цепочку захватов и ожиданий блокировок между сеансами:

Вспомним, как Oracle обнаруживает блокировки. Для этого он постоянно строит граф ожидания транзакций. Если обнаружен цикл в этом графе, то это означает, что возникла взаимная блокировка. Так вот именно этот цикл и отображается в секции Deadlock graph, правда в очень специфическом виде.

Визуально граф представлен в виде таблицы, которая разделена на три логических блока. Первый блок обозначает ресурсы, участвующие во взаимной блокировке. В нашем случае таким ресурсом является блокировка транзакции. Ресурс обозначается символьным кодом TX, после которого идет идентификатор транзакции в шестнадцатеричном виде. Именно этот идентификатор, но только в десятичном виде мы получили, когда расшифровывали значения столбцов ID1 и ID2 представления v$lock. Второй блок состоит из столбцов, содержащих информацию о сеансах удерживающих данную блокировку, а также режим, в котором эта блокировка установлена. И наконец, третий блок аналогичен второму, но противоположен по содержанию. Он хранит информацию о сеансах, которые сделали запрос на установление блокировки, но вынуждены ожидать освобождения блокировки из второго блока.

Расшифровка графа не представляет сложности. Для этого нам надо проанализировать содержимое таблицы построчно, слева направо. К примеру, для нашего случая это будет выглядеть следующим образом. Транзакционная блокировка TX-00040016-000000a9 на строку удерживается сеансом 28 (поле session) в исключительном режиме (символ X в поле holds). Сеанс 24 одновременно ждёт освобождение этого ресурса, чтобы установить свою TX блокировку в исключительном режиме (символ X в поле waits).

Пока это нормальное ожидание необходимого ресурса. Поэтому далее мы обратимся ко второй строке графа. Здесь транзакционная блокировка TX-00050026-000000a3 на строку удерживается сеансом 24 в исключительном режиме, а сеанс 28 ждёт освобождение строки, чтобы установить свою TX блокировку в исключительном режиме. В то же время в первой строке графа сеанс 24 уже ожидает освобождения ресурса, в результате чего получается, что сеансы находятся в состоянии бесконечного ожидания. Единственным логичным действием в этом случае, явилось бы отмена ожидания установки блокировки в сеансе 28, что собственно и было сделано Oracle. В графе такое отменённое ожидание всегда отображается последним в блоке Waiter(s).

Итак, граф расшифрован. Он дал нам описание цепочки захватов и ожиданий TX блокировок в сеансах. Но по этой цепочке мы можем судить только об общей картине возникновения взаимной блокировки. Если же нам потребуется найти конкретные ресурсы, из-за которых возникают ожидания, сделать нам это будет затруднительно. К счастью Oracle сам позаботился об этом, записав в файл трассировки информацию о строках, освобождения которых от TX блокировок ожидают сеансы. Рассмотрим более подробно эту секцию. Найти её можно сразу после графа, по ключевой строке Rows waited on:

В этой секции для каждого ожидающего сеанса, который перечислен в графе, указана строка, на которую этот сеанс пытается получить TX блокировку. Строка идентифицируется номером объекта, которому она принадлежит, и идентификатором ROWID. Чуть ниже дана их полная расшифровка в десятичном виде. Это позволяет, с лёгкостью, обратившись, например, к системному представлению dba_objects, идентифицировать объект, к которому принадлежит данная строка:

Следующая секция трассировочного файла, которую мы рассмотрим, хотя и не столь важна, но позволяет дополнить картину взаимной блокировки. Она располагается сразу за секцией Rows waited on и находится по следующим ключевым словам:

Из этой секции можно получить информацию о пользователе, приложении и текущем SQL курсоре ожидающих сеансов, вовлеченных в процесс взаимной блокировки. Иногда это может быть полезно при поиске причин образования ситуации взаимного блокирования.

Как определить по содержимому трассировочного файла, что произошел первый сценарий взаимного блокирования? Для ответа на этот вопрос обратимся в первую очередь к графу взаимной блокировки. Для начала мы должны определить, с какого идентификатора начинаются имена ресурсов графа в столбце «Resource Name». В нашем случае это всегда будет идентификатор TX, то есть блокировка транзакции. Далее нам следует проверить значения режимов блокировок, отображаемые в столбцах holds и waits. Они должны иметь одинаковое значение равное символу X. Не следует так же забывать, что данный сценарий взаимного блокирования возникает на уровне строк, и, следовательно, в секции «Rows waited» всегда будут присутствовать данные об ожидающих строках. Отсюда следует непреложное правило о том, что в первой секции «Current SQL statement for this session» при данном сценарии вы никогда не встретите оператора INSERT, так как строки, вставленные в одном из сеансов, никогда не будут доступны для другого сеанса до фиксации транзакции.

Выводы

Пришло время обобщить полученную информацию, которую мы узнали, изучая взаимную блокировку, образующуюся по первому сценарию. Обычно, такая взаимная блокировка возникает из-за неодинаковой последовательности обработки строк, в пределах транзакций разных сеансов. Если бы мы, к примеру, в нашем случае проводили бы обновление строк таблицы в каждом из сеансов последовательно в соответствии со значением ключевого столбца «c1», то мы могли бы избежать взаимных блокировок. Образовалась бы простая очередь ожидания ресурса. Правда, бывают ситуации, когда одинаковую последовательность обработки строк в разных сеансах сделать трудно или даже невозможно. Как тогда выходить из ситуации? Самое простое, что приходит на ум, и о чём я уже упоминал выше, это можно обрабатывать ошибку в приложении, и в случае её возникновения в одном из сеансов откатывать транзакцию, повторяя все необходимые действия позже. Велика вероятность, что транзакция в другом сеансе за это время завершиться и не будет мешать производить изменения в данных. Вообще по возможности надо всегда уменьшать время длительности транзакций в приложениях. Если транзакция будет короткой, то это уменьшает время в течение, которого сеансы могут попасть в неприятную ситуацию с взаимным блокированием. Это справедливо и для длинной цепочки каскадного срабатывания триггеров, которым так грешат некоторые сложные системы. По возможности эту цепочку надо укорачивать.

Сеансы работы с СУБД Oracle есть параллельно выполняющиеся процессы, работающие как с собственными, так и с общими ресурсами. Объекты БД, такие как таблицы, индексы, являются общими ресурсами. CУБД Oracle делает все возможное для того, чтобы конкурентный доступ к табличным данным был эффективным и максимально незаметным - прозрачным - для сеансов. Однако, в ряде случаев от программиста требуется явная блокировка ресурса на время работы с ним, и освобождение ресурса по окончании работы.

Таким ресурсом может быть, например, экземпляр некоторой сущности предметной области (сотрудник, элемент орг. структуры, и т.д.) Строки одной таблицы сеанс может зарезервировать для исключительного использования при помощи SELECT . FOR UPDATE . Но в случае, когда необходимо заблокировать данные, распределенные по нескольким таблицам, лучшим решением будет специальное соглашение между конкурирующими процессами о доступе к ресурсу. А реализовать такое соглашение поможет пакет DBMS_LOCK .

Пакет DBMS_LOCK предоставляет программисту API для работы с пользовательскими блокировками, которые обладают всеми возможностями системных блокировок СУБД Oracle. Так же, как и системные блокировки, пользовательские блокировки отображаются в динамическом вью v$lock .

Следующий блок PL/SQL создает исключительную блокировку при помощи пакета DBMS_LOCK и выводит ее идентификатор:

Процедура DBMS_LOCK.ALLOCATE_UNIQUE в строке 5 связывает придуманное программистом имя блокировки (первый агрумент) с числовым идентификатором (второй аргумент), назначенным системой. Далее для работы с блокировкой используется полученный числовой идентификатор.

Функция DBMS_LOCK.REQUEST в строке 6 запрашивает у СУБД блокировку с данным идентификатором в исключительном режиме и получает ее, о чем говорит возвращаемый код 0. Как видим, повторная попытка получить ту же самую блокировку, выполнив PL/SQL блок еще раз, не увенчалась успехом: код возврата 4 означает, что у блокировки уже есть владелец. (Мьютексы в DBMS_LOCK нереентерабельны.)

См. описание функций и процедур пакета DBMS_LOCK в официальной документации по СУБД Oracle.

Полученную нами блокировку можно увидеть во вью v$lock , где пользовательские блокировки имеют тип 'UL' :

Снимем блокировку и убедимся, что она больше не отражается в v$lock :

Процедура DBMS_LOCK.ALLOCATE_UNIQUE в строке 5 возвращает числовой идентификатор уже существующей блокировки по ее имени. Далее, функция DBMS_LOCK.RELEASE в строке 6 снимает данную блокировку.

Теперь вернемся к сценарию, в котором некоторый код может одновременно выполняться только в одном сеансе. Этот сценарий реализует процедура critical :

Чтобы создать процедуру critical , текущему пользователю должна быть явно (не через роль) предоставлена привилегия EXECUTE на пакет SYS.DBMS_LOCK .

Для запуска процедуры critical в нескольких сеансах параллельно воспользуюсь возможностями пакета DBMS_SCHEDULER :

Дав процедуре время выполниться, проверю результат выполнения:

Как видим, три задачи отработали, причем каждой следующей понадобилось на 5 секунд больше, чтобы выполниться. Это - время, в течение которого функция DBMS_LOCK.REQUEST ожидала получения блокировки во втором и третьем сеансах.

Мы рассмотрели работу с исключительной (exclusive) блокировкой, но не менее важны разделяемые (shared) блокировк. Они позволяют параллельным процессам более эффективно работать с общим ресурсом. Исключительной блокировкой может владеть только один процесс, и, пока он ею владеет, ни один другой процесс не может получить доступ к заблокированному ресурсу. Разделяемой блокировкой могут владеть многие процессы, и, пока хотя бы один процесс ею владеет, ни один другой процесс не может получить исключительную блокировку данного ресурса.

Как это используется? Например, процессы, которым нужно только читать (но не изменять) некоторый ресрус, блокируют его в разделяемом режиме, что позволяет другим процессам также получать доступ к нему в разделяемом режиме, не дожидаясь его полного освобождения. Однако, процесс, которому необходимо изменить ресурс, для получения исключительной блокировки должен дождаться, когда с ресурса будут сняты все блокировки. И пока этот процесс будет изменять ресурс, ни один другой процесс не сможет получить доступ к ресурсу ни для чтения, ни для изменения.

Проиллюстрирую совместную работу разделяемой и исключительной блокировок. Для этого создам процедуру sharedmode , подобную critical , но, в отличие от нее, sharedmode запрашивает блокировку в разделяемом режиме:

Теперь в трех различных сеансах параллельно запущу процедуры sharedmode , а в четвертом сеансе - процедуру critical . Три процесса, выполняющие sharedmode , получат разделяемые блокировки сразу, как только за ними обратятся, и выполнятся приблизительно за 5 секунд, перед завершением сняв блокировки. Процесс, выполняющий процедуру critical , получит исключительную блокировку только после того, как будут сняты разделяемые блокировки, и будет выполняться еще в течение 5 секунд, итого, около 10 секунд.

Последний запрос полностью подтвердил ожидания.

На этом закончу эксперименты с DBMS_LOCK :

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

Читайте также: