Пакет DBMS LOCK

Пакет DBMS LOCK дает программисту доступ к механизму блокирования, используемому сервером Oracle. Он позволяет создавать собственные именованные блокировки. Эти блокировки можно контролировать точно так же, как и любые другие блокировки Oracle. Они будут отображаться в представлении динамической производительности V$LOCK как блокировки типа UL (user lock - пользовательская блокировка). Кроме того, они будут отображаться любыми стандартными средствами, такими как Oracle Enterprise Manager и сценарий UTLOCKT.SQL (который находится в каталоге [ORACLE HOME]/rdbms/admin). Помимо обеспечения доступа к механизму блокирования пакет DBMS LOCK (благодаря наличию функции SLEEP) позволяет приостановить работу PL/SQL-программы на указанное количество секунд.

Пакет DBMS LOCK имеет много применений, например.

Предположим, имеется подпрограмма, использующая средства пакета UTL FILE для записи сообщений проверки в файл операционной системы. Записывать сообщения в этот файл процессы должны поочередно. В некоторых операционных системах, например в ОС Solaris, записывать данные в файл могут одновременно много пользователей (ОС этого не предотвращает). В результате сообщения с данными проверки перемешиваются, и читать их сложно или невозможно. Пакет DBMS LOCK можно использовать для обеспечения очередности доступа к этому файлу.

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

1630

Приложение А

обращаться к данным в процессе их подготовки. Сеанс подготовки должен устанавливать именованную блокировку в режиме X (как исключительную). Другие сеансы должны пытаться установить эту же именованную блокировку в режиме S (как разделяемую). Запрос блокировки X будет ожидать, если имеются блокировки S, а запрос блокировки S будет ожидать, если удерживается блокировка X. В результате сеанс подготовки данных будет в состоянии ожидания, пока работают обычные сеансы, но если сеанс подготовки уже начался, все остальные сеансы будут заблокированы до его завершения.

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

Этой проблемы можно избежать при использовании оператора 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 = 123 4;

За счет использования конструкции FOR UPDATE NOWAIT в операторе SELECT можно заблокировать строку для использования сеансом (так что выполнение UPDATE не будет заблокировано) или будет получено сообщение об ошибке ORA-54 Resource Busy. Если при выполнении оператора SELECT сообщений об ошибках не получено, строка уже заблокирована.

Однако при выполнении операторов INSERT этот метод неприменим. Нет строки, которую можно было бы выбрать с помощью SELECT и заблокировать, а потому нет и способа предотвратить вставку строки с таким же значением в других сеансах, что приведет к блокированию и потенциально бесконечному ожиданию в текущем сеансе. Вот тут и поможет пакет DBMS LOCK. Чтобы продемонстрировать, как, я создам таблицу с первичным ключом, предотвращающим одновременную вставку одних и тех же значений двумя (или более) сеансами. Для этой таблицы я задам триггер. Триггер будет использовать функцию DBMS UTILITY.GET HASH VALUE (подробнее о ней см. в разделе, посвященном пакету DBMS UTILITY, далее в этом приложении) для получе-

Пакет DBMS LOCK

1631

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

tkyte@TKYTE816> create table demo Table created.

(x int primary key) ;

tkyte@TKYTE816> create or replace trigger demo bifer

4 5 6

9 10

11 12 13 14 15 16 17 18 19

before insert on demo for each row declare

l lock id number; resource busy exception; pragmaexception init(resource busy, -54) ; begin

l lock id :=

dbms utility.get hash value(to char(:new.x),

0, 1024);

if (dbms lock.reest

lockmode timeout release on commit then

raise resource busy;

end if;

=> l lock id,

=> dbms lock.x mode,

=> 0,

=> TRUE) = 1)

end;

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

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