Советы, рекомендации, опыт

Полтавский П.В.,
разработчик ПО
Компания: GCProjects (www.cgprojects.ru)
E-mail: peter63@interfintrade.ru
Home Page: www.ppv.newmail.ru

Механизм диагностики блокировок записей таблиц, возникающих при работе в многопользовательской среде.

1. Постановка задачи.

ORA-00054: resource busy and acquire with NOWAIT specified

Данное сообщение информирует нас о том, что редактируемый документ (запись таблицы) в текущий момент времени уже заблокирован другим процессом и недоступен для редактирования. Что может сделать пользователь в данной ситуации? Как правило, он обращается к администратору базы данных Oracle Server для разрешения возникшей проблемы.

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

2. Описание реализации.

Для получения информации о процессе, заблокировавшем объект БД, используем системные представления данных (VIEW) БД Oracle Server: ALL_OBJECTS, V$LOCKED_OBJECT (владелец SYS). Представление ALL_OBJECTS содержит информацию о всех объектах базы данных в разрезе владельцев этих объектов. В V$LOCKED_OBJECT хранятся сведения о пользовательских процессах и заблокированных ими объектах. Так как эти представления являются PUBLIC-синонимами, содержащиеся в них данные доступны любому пользователю БД без предоставления дополнительных привилегий. Ниже приводится PL/SQL код функции, реализующий получение информации о блокирующем процессе

--
-- Функция формирует сообщение о пользователе, блокировавшем запись
--
--       Параметры:
--
--          p_owner       – имя пользователя ORACLE,владельца заблокированного объекта
--          p_object_type – тип объекта схемы БД(TABLE, VIEW И т. д.)       
--          p_object_name – имя объекта       
--
FUNCTION Get_Locker_Attr (p_owner       in varchar2,
                          p_object_type in varchar2,
                          p_object_name in varchar2
                          )
         RETURN varchar2 IS
 --
 v_ret        varchar2(500):='0';
 v_acc_exec   FM_STNDS.Fm_Acct_Exec_TP;
 --
BEGIN
  for rec in (select v.oracle_username,
                     v.os_user_name,
                     v.session_id
              from v$locked_object v,
                   all_objects a
              where v.object_id=a.object_id
                    and a.object_type=p_object_type
                    and a.object_name=p_object_name
                    and a.owner=p_owner)
  loop
     -- Получение значений атрибутов записи о пользователе по имени пользователя ORACLE
     -- из таблицы ответственных исполнителей, определенной в схеме владельца 
     -- объектов БД прикладной системы

     v_acc_exec:=FM_STNDS.Get_Fm_Acct_Exec_Attr(rec.oracle_username);

     -- Формирование результирующего сообщения
     v_ret:='Запись заблокировал пользователь '||v_acc_exec.acct_exec_name||
            ' (ORACLE user - '||rec.oracle_username||
            ', OS user - '||rec.os_user_name||
            ', SESSION_ID - '||rec.session_id||').';
     --
     exit;
  end loop;
  --
  return v_ret;
END Get_Locker_Attr;

Рассмотрим подробнее механизм использования этой функции. Предположим, пользователь осуществляет редактирование документа через клиентскую часть приложения (например, Oracle Forms). При возникновении ошибки, описанной в пункте 1, предлагается следующий вариант её обработки (данный фрагмент PL/SQL кода необходимо включить в триггер ON-ERROR на уровне формы).

…
…
-- Обработка ошибки при блокировке записи

   if Error_Code = 40501 then –- код ошибки Oracle Forms, возникающей при блокировке      

 -- Определим имя объекта БД (таблицы, содержащей заблокированную запись)
 v_name:=GET_BLOCK_PROPERTY(NAME_IN(':SYSTEM.TRIGGER_BLOCK'),QUERY_DATA_SOURCE_NAME);

 -- Получение сообщения о блокирующем процессе
 v_ret:=Get_Locker_Attr(<имя_пользователя_ORACLE_владельца_схемы_БД>,
                        'TABLE',                                                 
                        v_name);
 --
 if v_ret <> '0' then -- блокировка обнаружена
    -- Выдача сообщения на экран
    v_al:=STD_SYSTOR_DISPLAY_MESSAGE('ERROR_ALERT',v_ret);   
    v_display_message := FALSE;
 end if;    

end if;     
…
…

В результате выполнения этого фрагмента PL/SQL кода формируется текст сообщения, которое выдается на экран монитора пользователю, редактирующему документ. Текст сообщения содержит следующую информацию: имя пользователя ORACLE, блокировавшего запись, его инициалы, имя пользователя (учетную запись) в операционной системе, номер сессии этого пользователя. По этой информации сотрудник, редактирующий документ, может связаться с пользователем, заблокировавшим данный документ и решить проблему самостоятельно (например, попросить блокирующего пользователя сохранить/отменить сделанные изменения). Если же самостоятельно проблему устранить не удаётся, то нужно обращаться к администратору БД, но уже с указанием номера сессии блокирующего пользователя. Эта ситуация иллюстрируется ниже (см. Рис. 1).

Рис. 1 Диагностика о блокировке записи редактируемого документа.