17.06.2010 Кто блокирует запись таблицы Oracle?
Такой вопрос возникает у любого разработчика многопользовательских приложений на основе сервера Oracle. К сожалению, точно ответить на вопрос, какой именно пользователь заблокировал конкретную запись конкретной таблицы не представляется возможным. По крайней мере, автору такой способ неизвестен. На этот вопрос можно ответить только с некоторой долей вероятности. К сожалению, сервер Oracle не предоставляет информации о том, какая сессия заблокировала запись в таблице. Такая информация может быть получена только для рассматриваемой таблицы, а не конкретной записи. Но все же посмотрим, что можно получить в практическом смысле для разработки наших приложений.
Сначала несколько слов о блокировках записей.
Если мы разрабатываем многопользовательское приложение, то нам необходимо перед любой модификацией записи таблицы или перед ее удалением заблокировать эту запись.
Делается стандартным образом это так:
select ID into n from TABLE_NAME where … for update nowait
Тем самым мы блокируем требуемую запись, после чего можем модифицировать ее или удалить уже не опасаясь, что другой пользователь может сделать то же самое. Мы блокируем выбранную запись до тех пор, пока не будет завершена текущая транзакция (commit, rollback).
Если приложение построено правильно, и в этот момент другой пользователь также попытается заблокировать эту же запись в этой же таблице, то он получит исключение, сигнализирующее о занятости ресурса и вынужден будет через какое-то время повторить попытку блокировки этой записи и делать это до тех пор, пока первый пользователь не разблокирует занятый ресурс, завершив транзакцию.
В общем, механизм известный, но при эксплуатации приложений может быть множество проблем из-за того, что получая сообщение о занятости ресурса пользователь не знает, кто именно его заблокировал. Если бы пользователь это знал, то разрешение такого рода проблем было бы гораздо более быстрым, так как появилась бы возможность обратиться к этому пользователю напрямую и лично урегулировать вопрос конкуренции за ресурсы. Проблема эта известна многим пользователям и администраторам приложений и баз данных.
Для частичного решения этой проблемы есть техническая возможность узнать, какие именно пользователи блокируют некоторые записи конкретной таблицы. Это уже хоть какая-то информация. В сообщении о занятости ресурса можно показать список пользователей, которые блокируют некоторые строки таблицы. С таким списком пользователь уже что-то может сделать самостоятельно. Естественно, если приложение построено на нескольких очень больших таблицах, с которыми работает очень много пользователей, этот способ может быть неэффективным, но часто этого может быть достаточным.
Для решения поставленной задачи мы будем использовать системное представление базы данных Oracle V$LOCKED_OBJECT, в котором мы можем найти список заблокированных таблиц, имен пользователей, заблокировавших какие-то строки этих таблиц и идентификаторы их сессий. Мы напишем функцию, которая будет вычислять на основе этого представления список пользователей, блокировавших записи заданной таблицы и выдавать его в приемлемом для приложения виде. Структуры данных и функции, реализующие эту технологию, удобно оформить в виде пакета. Пример рабочей реализации пакета LOCKS приведен ниже. Затем мы рассмотрим использование этого пакета в коде приложений.
create or replace package LOCKS
authid current_user
is
e_busy exception;
pragma exception_init(e_busy,-00054);
type ttTEXT_S is table of varchar2(32767) index by varchar2(48);
subtype tsRSLT is varchar2(32767);
gsTABLE varchar2(256);
procedure SET_TABLE(psTABLE varchar2);
function BUSY(psTABLE varchar2:=null) return varchar2;
end LOCKS;
/
create or replace package body LOCKS
is
procedure SET_TABLE(psTABLE varchar2)
is
begin
gsTABLE:=psTABLE;
end SET_TABLE;
function GET_USER_NAME(psNAME in varchar2) return varchar2
is
begin
return psNAME;
— Здесь реализуется получение имени пользователя по его Oracle имени.Например, ФИО пользователя должно храниться в специфической для вашего приложения таблице и должно быть связано с его Oracle именем.
end GET_USER_NAME;
function GET_TABLE_NAME(psTABLE in varchar2) return varchar2
is
begin
return psTABLE;
— Здесь реализуется получение описание таблицы. Возможно, ваше приложение содержит такую метаинформацию.
end GET_TABLE_NAME;
function USERS(psTABLE in varchar2) return ttTEXT_S
is
ltUSERS ttTEXT_S;
begin
if psTABLE is null
then
return ltUSERS;
end if;
for i in (select V.ORACLE_USERNAME
from V$LOCKED_OBJECT V,
ALL_OBJECTS O
where V.OBJECT_ID=O.OBJECT_ID
and O.OBJECT_TYPE=’TABLE’
and O.OBJECT_NAME=psTABLE
)
loop
ltUSERS(i.ORACLE_USERNAME):=null;
end loop;
return ltUSERS;
end USERS;
function BUSY(psTABLE varchar2:=null) return varchar2
is
ltUSERS ttTEXT_S;
lsUSER tsRSLT;
lsRSLT tsRSLT;
lsTABLE varchar2(256);
begin
if gsTABLE is not null
then
lsTABLE:=gsTABLE;
end if;
if lsTABLE is null
then
return ‘Ресурс заблокирован другим пользователем’;
end if;
ltUSERS:=USERS(lsTABLE);
if ltUSERS.count=0
then
return ’Таблица ‘||GET_TABLE_NAME(lsTABLE)||’ заблокирована другим пользователем’;
end if;
if ltUSERS.count=1
then
if ltUSERS.first=sys_context(’USERENV’,'SESSION_USER’)
then
return ‘Таблица ‘||GET_TABLE_NAME(lsTABLE)||’ заблокирована пользователем с этим же именем ‘||GET_USER_NAME(ltUSERS.first)||’ , но в другой сессии’;
end if;
return ‘Таблица ‘||GET_TABLE_NAME(lsTABLE)||’ заблокирована пользователем ‘||GET_USER_NAME(ltUSERS.first);
end if;
lsRSLT:=null;
lsUSER:=ltUSERS.first;
while lsUSER is not null
loop
lsRSLT:=lsRSLT||GET_USER_NAME(lsUSER);
if lsUSER!=ltUSERS.last
then
lsRSLT:=lsRSLT||’,';
end if;
lsUSER:=ltUSERS.next(lsUSER);
end loop;
return ‘Таблица ‘||GET_TABLE_NAME(lsTABLE)||’ заблокирована следующими пользователями: ‘||lsRSLT;
end BUSY;
end LOCKS;
/
Пакет LOCKS очень просто использовать в коде ваших приложений. Перед тем, как выполнить попытку блокировки записей таблицы, необходимо вызвать LOCKS.SET_TABLE(’имя таблицы’). Обработка исключений, связанных с блокировкой ресурсов, может быть сделана следующим образом:
…
exception
when LOCKS.e_busy
then
raise_application_error(-20100,LOCKS.BUSY);
end;
Еще о блокировках в Oracle вы можете почитать в статье Блокировки Oracle при неиндексированных внешних ключах.
Оставьте свой комментарий
Вы должны быть авторизированны, чтобы оставить комментарий.
