Пакет DBMS_LOCK. Пользовательские блокировки

dbstalker, 05 июня

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

Я попробую осветить поднятую тему таким образом:

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

Для этого создана служба управления блокировками ORACLE. Эта предоставляет возможность разработчику включать в блоки PL/SQL предложения, запрашивающие блокировку указанного типа, давать ей уникальное имя, изменять тип блокировки и освобождать блокировку. Эти действия включаются в понятие пользовательская блокировка. Пользовательская блокировка обладает всеми возможностями блокировки ORACLE. Пользовательские блокировки идентифицируются префиксом "UL" в динамическом представлении производительности v$lock, никогда не конфликтуют с блокировками ORACLE. Доступ к службе управления блокировками ORACLE осуществляется через процедуры в пакете DBMS_LOCK.

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

Блокировка - это некая булевская переменная, которая показывает, что ресурс свободен или занят. Если значение переменной 0 (false), то это означает, что блокировка свободна и любой процесс может изменить ее значение на 1 (true), а затем обращаться к защищаемому ресурсу. Если значение блокировки true, то процессу следует подождать, поскольку кто-то еще пользуется этим ресурсом.

Если мы используем DBMS_LOCK, то мы не блокируем объект, с которым работаем, а специально создаем именованный семафор (булеву переменную, виртуальную блокировку). А условием доступа к нашему объекту есть проверка этой виртуальной блокировки. Обычно если речь идет о пользовательском блокировании таблицы, то создается триггер на before update, insert, delete , в котором идет работа по блокированию с помощью пакета dbms_lock. К сведению, по завершению сессии пользовательские блокировки освобождаются.

Из вышеизложенного можно сделать выводы:

  • пакет DBMS_LOCK служит для управления пользовательскими блокировками, которые к объектным блокировкам отношения не имеют.
  • при обращении к нашему объекту все пользователи добровольно должны выполнять проверку «семафора», иначе задуманная блокировка не получится. Это явление можно назвать согласованностью на уровне соглашения.

Теперь перейдем непосредственно к самому пакету.

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

  • Выделить блокировку
  • Запросить блокировку
  • Ожидать, если запрос не удался
  • Снять блокировку

Процедуры и функции пакета:

procedure allocate_unique(lockname in varchar2,
                       lockhandle out varchar2,
                       expiration_secs in integer default 864000);
выделяет блокировку с заданным именем. При выделении блокировки необходимо задать имя блокировки (lockname). Результатом вызова этой процедуры будет дескриптор (lockhandle), присваиваемый новой выделенной блокировке.

Для идентификации блокировки (id) используются номера из диапазона 0..1999999999. Однако номера в диапазоне от 1073741824 до 1999999999 распределяются самой процедурой dbms_lock.allocate_unique, номера из диапазона от 0 до 1073741823 может указывать сам пользователь.

Параметр expiration_secs – время жизни блокировки.

Мне кажется, что при работе с этим пакетом всё таки разработчикам лучше использовать не числовой идентификатор ( анонимная блокировка), а имя блокировки. Хотя бы потому, что совсем другие приложения могут использовать этот же номер совершенно в других целях. Накладочка выйдет. Поэтому проще придумать уникальное имя блокировки, а ORACLE назначит ей свободный номер. Выходной параметр lockhandle будет использоваться в вызовах других процедур и функций пакета. После того как блокировка выделена, ёё можно запросить.

Важно:

  • процедура всегда выполняет COMMIT;
  • используется только для именованных блокировок;
  • установите на своей фирме общие правила именования пользовательских блокировок.

function request(id in integer,
                             lockmode in integer default x_mode,
                             timeout in integer default maxwait,
                             release_on_commit in boolean default FALSE)
                             return integer;
 function  request(lockhandle in varchar2,
                              lockmode in integer default x_mode,
                              timeout in integer default maxwait,
                              release_on_commit in boolean default FALSE)
                              return integer;

Эта функция запрашивает блокировку (происходит фактическое блокирование именованного ресурса) в одном из следующих режимов :

  nl_mode  constant integer := 1 блокировка отсутствует
  ss_mode  constant integer := 2 совместная блокировка строки	
  sx_mode  constant integer := 3 исключительная блокировка строки
  s_mode   constant integer := 4 совместная блокировка строки
  ssx_mode constant integer := 5 исключительная совместная блокировка строки
  x_mode   constant integer := 6 исключительная блокировка

Функция возвращает одно из следующих значений:

0 –успешно
1-тайм-аут
2 - взаимная блокировка
3 - ошибка параметра
4 - блокировка уже существует
5  - недопустимый дескриптор блокировки

Входным параметром служит или полученный дескриптор после вызова dbms_lock.allocate_unique, или числовой идентификатор id из диапазона от 0 до 1073741823. timeout – время ожидания блокировки, по умолчанию принимает значение maxwait constant integer := 32767. Для release_on_commit если вы укажете значение true, то снятие блокировки произойдет по commit.

function convert(id in integer,
                   lockmode in integer,
                   timeout in number default maxwait)
                   return integer;
 function convert(lockhandle in varchar2,
                   lockmode in integer,
                   timeout in number default maxwait)
                   return integer;

Эта функция предназначена для того, чтобы поменять (преобразовать) режим блокирования. Функция возвращает одно из следующих значений:

0 –успешно
1-тайм-аут
2 – взаимная блокировка
3 - ошибка параметра
4 – блокировка не существует
5  - недопустимый дескриптор блокировки

function release(id in integer) return integer;
function release(lockhandle in varchar2) return integer;

Используется для снятия блокировки. Результат вызова:

0 – успешно
3 - ошибка параметра
4 – блокировка не существует
5  - недопустимый дескриптор блокировки

procedure sleep(seconds in number);

Процедура приостанавливает процесс на указанное время. Период сна можно указывать с учетом сотых долей секунды. Например, 1,45 или 2,89 и т.д.

Если вы сделаете следующий вызов:

BEGIN
   DBMS_LOCK.SLEEP(null);
END;

То получите ORA-00600: internal error code, arguments: [15454], [0], [], [], [], [], [], []

Вот так то!!!!

Ну а теперь примеры использования.

Часто возникает ситуация, когда программа выводит какую – то информацию в файл операционной системы, используя пакет UTL_FIILE. И если программу одновременно могут использовать несколько пользователей, то содержимое внешнего файла будет не таким, как ожидалось: записи от разных сеансов будут перемешаны. Здесь вам может помочь dbms_lock:

Procedure writetofile(fname in varchar2,Text in varchar2)
Is 
Lockid varchar2(30);
Code number;
Begin

Dbms_lock. allocate_unique (fname,lockid); --выделена блокировка
Code:= dbms_lock.request(lockid,dbms_lock.x_mode); --  запрошена блокировка в исключительном режиме
If code=0 then     работаем с файлом
Else     описываем что делать если файл занят
End if;
Code:=dbms_code. Release(lockid);
End;

Эту процедуру могут вызывать несколько пользователей для записи в один и тот же файл. Но так как имя блокировки одно и тоже – имя файла, то запись в него будет строго последовательной. Так можно навести порядок.

Еще один случай, когда использование dbms_lock оправдано. Этот вариант описан у Тома Кайта. Цитирую.

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

  • Этой проблемы можно избежать при использовании оператора UPDATE, поскольку можно заранее заблокировать строку, которую предполагается менять, так, чтобы работа других сеансов не блокировалась. Другими словами, вместо выполнения:
    update emp set ename = 'King' where empno = 1234;
    можно написать:
    select ename from emp where empno = 1234 FOR UPDATE NOWAIT;
    update emp set ename = 'King' where empno = 1234;
    За счет использования конструкции FOR UPDATE NOWAIT в операторе SELECTможно заблокировать строку для использования сеансом (так что выполнение UPDATE не будет заблокировано) или будет получено сообщение об ошибке ORA-00054 'Resource Busy'. Если при выполнении оператора SELECT сообщений об ошибках не получено, строка уже заблокирована.
  • Однако при выполнении операторов INSERT этот метод неприменим. Нет строки,которую можно было бы выбрать с помощью SELECT и заблокировать, а потому нет и способа предотвратить вставку строки с таким же значением в других сеансах, что приведет к блокированию и потенциально бесконечному ожиданию в текущем сеансе. Вот тут и поможет пакет DBMS_LOCK.

Чтобы продемонстрировать, как, я создам таблицу с первичным ключом, предотвращающим одновременную вставку одних и тех же значений двумя (или более) сеансами. Для этой таблицы я задам триггер. Триггер будет использовать функцию DBMS_UTILITY.GET_HASH_VALUE, для получения по первичному ключу числового хеш-значения в диапазоне от 0 до 1073741823 (диапазон значений идентификаторов блокировок, допускаемых сервером Oracle см выше). B этом примере я задал размер хеш-таблицы равным 1024, т.е. по первичным ключам будет получено одно из 1024 значений идентификаторов блокировок. Затем я использую вызов DBMS_LOCK.REQUEST для выделения (запрос блокировки с указанным числовым идентификатором) исключительной блокировки с этим идентификатором. В каждый момент времени это сможет сделать только один сеанс, поэтому, если другой сеанс попытается вставить запись в таблицу с таким же первичным ключом, его запрос на блокировку завершится неудачно (и будет получено сообщение об ошибке RESOURCE BUSY):

1631tkyte@TKYTE816> create table demo (x int primary key);
Table created.
tkyte@TKYTE816> create or replace trigger demo_bifer
2 before insert on demo
3 for each row
4 declare
5 l_lock_id number;
6 resource_busy exception;
7 pragmaexception_init(resource_busy, -54);
8 begin
9 l_lock_id :=
10 dbms_utility.get_hash_value(to_char(:new.x), 0, 1024);
11
12 if (dbms_lock.request
13 (id => l_lock_id,
14 lockmode => dbms_lock.x_mode,
15 timeout => 0,
16 release_on_commit => TRUE) = 1)
17 then
18 raise resource_busy;
19 end if;
20 end;
21 /
Trigger created.

Если в двух отдельных сеансах теперь выполнить:

tkyte@TKYTE816> insert into demo values (1);
1 row created.
то в первом сеансе оператор выполнится, но во втором будет выдано:
tkyte@TKYTE816> insert into demo values (1);
insert into demo values (1)
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified
ORA-06512: at "TKYTE.DEMO_BIFER", line 15
ORA-04088: error during execution of trigger 'TKYTE.DEMO_BIFER'

если в первом сеансе транзакция будет зафиксирована, то будет выдано сообщение о нарушении требования уникальности.

Идея здесь в том, чтобы в триггере брать первичный ключ (вставляемой записи) таблицы и помещать его значение в строку символов. После этого можно использовать функцию DBMS_UTILITY.GET_HASH_VALUE для получения "почти уникального" хеш-значения для строки. Если использовать хеш-таблицу размером не более 1073741823 значений, можно будет заблокировать это значение в исключительном режиме с помощью пакета DBMS_LOCK( то есть хеш-значение использовать как уникальный идентификатор блокировки для каждого первичного ключа) .

Можно также использовать подпрограмму ALLOCATE_UNIQUE пакета DBMS_LOCK ( то есть когда ORACLE сам формирует уникальный дескриптор по указанному имени), но на это потребуются дополнительные ресурсы. Подпрограмма ALLOCATE_UNIQUE создает уникальный идентификатор блокировки в диапазоне от 1073741824 до 1999999999. Для этого она использует другую таблицу в базе данных и рекурсивную (автономную) транзакцию. Благодаря хешированию используется меньше ресурсов и, кроме того, можно избежать вызова рекурсивных SQL-операторов.

После хеширования мы берем полученное значение (уникальный идентификатор блокировки) и с помощью пакета DBMS_LOCK запрашиваем блокировку с соответствующим идентификатором в исключительном режиме с нулевым временем ожидания (если значение кем-то уже заблокировано, происходит немедленный возврат). Если получить блокировку за это время не удалось, возбуждается исключительная ситуация ORA-00054 RESOURCE BUSY. B противном случае можно выполнить оператор INSERT, и он не будет заблокирован.

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

  • Нужно "поиграть" с размером хеш-таблицы (в моем примере — 1024), чтобы избежать сообщений RESOURCE BUSY, связанных с получением одного и того же хеш-значения по разным строкам. Размер хеш-таблицы зависит от приложения (точнее, от используемых данных); на него также влияет количество одновременно выполняемых вставок.
  • Кроме того, владельцу триггера понадобится непосредственно (не через роль) предоставленная привилегия EXECUTE на пакет DBMS_LOCK.
  • Наконец, при вставке большого количества строк таким способом, без фиксации может не хватить ресурсов ENQUEUE_RESOURCES. В случае возникновения такой проблемы (при этом генерируется соответствующее сообщение) необходимо увеличить значение параметра инициализации ENQUEUE_RESOURCES. Можно также добавить в триггер флаг, позволяющий включать и отключать эту проверку. Например, если бы я планировал вставлять сотни/тысячи записей, то не хотел бы выполнять подобную проверку при каждой вставке.

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

tkyte@TKYTE816> update demo set x = 2 where x = 1;
1 row updated.

а другой сеанс попытается вставить строку с измененным значением первичного ключа:

tkyte@TKYTE816> INSERT INTO DEMO VALUES (2);

Второй сеанс опять окажется заблокированным. Проблема в том, что не каждый процесс, который может изменить первичный ключ, учитывает измененную схему блокирования. Для решения этой проблемы, связанной с изменением первичного ключа, необходимо изменить событие, вызывающее срабатывание триггера: before insert OR UPDATE OF X on demo Если созданный триггер срабатывает до вставки данных в столбец X или каких-либоизменений его значения, будет происходить именно то, что требуется (и изменение тоже станет неблокирующим).

ENQUEUE_RESOURCES – количество ресурсов, которые могут быть параллельно заблокированы менеджером блокировок

DBMS_LOCK_ALLOCATED Информация об определенных пользователем блокировках, созданных пакетом утилит DBMS_LOCK

1 комментарий

Прокоментировать

Toleg
10 октября 2012 г. в 19:51

дельная статья. спасибо за пример!

 

Новый комментарий

Я не спамер: введите суму 4+8



 

От авторов блога

О Блоге - прочитай перед началом.

Задать вопрос и получить ответ - уже решено 94 вопросов

Глоссарий - список терминов и сокращений


 
 

Бизнес форум

Последние темы:

Спутниковое тв
21 января, 3 ответа