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

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

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

Чтобы продемонстрировать первый случай, создадим пару таблиц следующим образом:

tkyte@TKYTE816> create table p (x int primary key) ; Table created.

tkyte@TKYTE816> create table с (у references p) ; Table created.

tkyte@TKYTE816> insert into p values (1) ; tkyte@TKYTE816> insert into p values (2) ;

tkyte@TKYTE816> commit; А затем выполним:

tkyte@TKYTE816> update p set x = 3 where x = 1;

1 row updated.

В результате сеанс заблокировал таблицу С, и никакой другой сеанс не может удалять, вставлять или изменять в ней строки. Повторю еще раз: изменение первичного ключа не приветствуется при работе с реляционными базами данных, так что обычно подобная проблема не возникает. Зато проблема изменения первичного ключа становится актуальной в случае использования средств автоматической генерации SQL-опе-раторов, которые обновляют значения всех столбцов, независимо от того, изменил ли значение пользователь. Например, при создании в Oracle Forms стандартной формы для просмотра и редактирования таблицы по умолчанию генерируется оператор UPDATE, изменяющий все просматриваемые столбцы таблицы. Если создать стандартную форму для таблицы DEPT и включить в нее все три поля, Oracle Forms будет выполнять следующую команду при изменении любого из столбцов таблицы DEPT:

update dept set deptno=:1,dname=:2,loc=:3 where rowid=:4

В этом случае, если таблица EMP имеет внешний ключ, ссылающийся на DEPT, и по столбцу DEPTNO в таблице EMP нет индекса, вся таблица EMP будет заблокирована после изменения таблицы DEPT. За этим надо внимательно следить при использовании любого средства, автоматически генерирующего SQL-операторы. Хотя значение первичного ключа не изменилось, подчиненная таблица EMP после выполнения приведенного выше SQL-оператора будет заблокирована. В случае Oracle Forms необ-

ходимо установить значение Yes для свойства таблицы update changed columns only (обновлять только измененные столбцы). В результате Oracle Forms будет генерировать оператор UPDATE, включающий только изменившиеся столбцы (но не первичный ключ).

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

tkyte@TKYTE816> column columns format a30 word wrapped tkyte@TKYTE816> column tablename format a15 word wrapped tkyte@TKYTE816> column constraint name format a15 word wrapped

tkyte@TKYTE816> select table name, constraint name.

2 3 4 5 6

10 11 12 13 14 15 16 17 18 19

enamel nvl2(cname2 cname2,null)

nvl2(cname4 cname4,null) nvl2(cname6 cname6,null) nvl2(cname8 cname8,null)

nvl2(cname3 cname,null)

nvl2(cname5 cname5,null) nvl2(cname7 cname7,null) columns from (select b.table name,

b.constraint name, max(decode(position, max(decode(position, max(decode(position, max(decode(position, max(decode(position, max(decode(position, max(decode(position, max(decode(position, count(*) col cnt from (select substr(table name,1,30) table name,

substr(constraint name,1,30) constraint name.

1, 2, 3, 4, 5, 6, 7, 8,

colunm name, column name, column name, column name, colunm name, column name, column name, column name.

null)) enamel, null)) cname2, null)) cname3, null)) cname4.

null)) null)) null)) null))

cname5, cname6, cname7, cname8.

20 substr(column naine,1,30) column name,

21 position

22 from user cons columns) a,

23 user constraints b

24 where a.constraint name = b.constraint name

25 and b.constraint type = R

26 group by b.table name, b.constraint name

27 ) cons

2 8 where col cnt > ALL

29 (select count(*)

30 from user ind columns i

31 where i.table name = cons.table name

32 and i.column name in (cname1, cname2, cname3, cname4,

33 cname5, cname6, cname7, cname8)

34 and i.column position <= cons.col cnt

3 5 group by i.index name

36 )

37 /

TABLE NAME CONSTRAINT NAME COLUMNS

С SYS C004710 Y

Этот сценарий работает с ограничениями внешнего ключа, включающими до 8 столбцов (если у вас используется больше столбцов, имеет смысл пересмотреть структуру). Вначале создается подставляемое представление (inline view), названное в данном запросе CONS. Это представление транспонирует имена столбцов, входящих в ограничение, представляя их в виде столбцов. В результате для каждого ограничения получается одна строка, включающая до 8 имен столбцов в ограничении. Кроме того, в строке имеется столбец COL CNT, содержащий количество столбцов в самом ограничении внешнего ключа. Для каждой строки, выбранной из представления CONS, мы выполняем коррелированный подзапрос, проверяющий все индексы по обрабатываемой таблице. Он считает столбцы в этом индексе, соответствующие столбцам в ограничении внешнего ключа, а затем группирует результаты по имени индекса. Таким образом, он генерирует набор чисел, каждое из которых представляет собой количество совпавших столбцов в одном из индексов таблицы. Если исходное значение в столбце COL CNT больше, чем все эти числа, значит, нет ни одного индекса таблицы, обеспечивающего выполнение ограничения внешнего ключа. Если значение в столбце COL CNT меньше некоторых чисел, значит, имеется хотя бы один индекс, обеспечивающий выполнение ограничения. Обратите внимание на использование функции NVL2 (впервые появилась в Oracle 8.15), которая формирует из имен столбцов список (через запятую). Эта функция принимает три аргумента: А, В и С. Если аргумент А - не пустой, она возвращает аргумент В, в противном случае - аргумент С. В запросе предполагается, что владелец ограничения является также владельцем таблицы и индекса. Если таблица проиндексирована другим пользователем или принадлежит другому пользователю, сценарий будет работать некорректно (но подобные случаи встречаются редко).

Итак, представленный выше сценарий показывает, что таблица С имеет внешний ключ по столбцу Y, но по этому столбцу нет индекса. Проиндексировав столбец Y, мы