В данной статье речь пойдет о блокировках при использовании неиндексированных внешних ключей. Тема известная для разработчиков, однако, на мой взгляд, существует несколько подводных камней, на которые часто не обращают внимание многие разработчики, вследствие чего могут возникать очень серьезные проблемы в многопользовательской среде.
При проектировании схемы базы данных и разработке таблиц существует очень важное правило: внешние ключи в таблицах без индекса не имеют права на существование. Иначе мы получаем полную блокировку подчиненной таблицы при выполнении изменения первичного ключа или при удалении записи из главной таблицы. Однако, что означает полная блокировка подчиненной таблицы? Как она проявляется? При детальном исследовании можно выяснить много интересного.
Попробуем продемонстрировать паразитные эффекты неиндексированных внешних ключей на примере. Приведенный ниже пример был создан и протестирован для версии Oracle 10g R2. Создадим три простые таблицы. Первая будет содержать список классов и состоит из двух полей - идентификатор и наименование класса, вторая будет содержать список типов, состоит также из двух полей - идентификатор и наименование типа, третья будет содержать списки значений справочников, и содержит четыре поля - идентификатор, ссылку на класс, ссылку на тип и наименование. Идентификаторы в таблицах представляют из себя суррогатные первичные ключи. Скрипты для создания таблиц:
create table T_CLASSES
(ID number(32),
NAME varchar2(256)
);
alter table T_CLASSES
add constraint T_CLASSES_PK
primary key (ID)
using index pctfree 0;
create table T_TYPES
(ID number(32),
NAME varchar2(256)
);
alter table T_TYPES
add constraint T_TYPES_PK
primary key (ID)
using index pctfree 0;
create table T_VALUES
(ID number(32),
CLASS_ID number(32),
TYPE_ID number(32),
NAME varchar2(256)
);
alter table T_VALUES
add constraint T_VALUES_PK
primary key (ID)
using index pctfree 0;
alter table T_VALUES
add constraint T_VALUES_CLASS_FK
foreign key (CLASS_ID)
references T_CLASSES;
alter table T_VALUES
add constraint T_VALUES_TYPE_FK
foreign key (TYPE_ID)
references T_TYPES;
Заполним созданные таблицы тестовыми данными:
insert into T_CLASSES values (10,"State");
insert into T_CLASSES values (20,"Repair");
insert into T_TYPES values (1,"Type 1′);
insert into T_TYPES values (2,"Type 2′);
insert into T_VALUES values (101,10,1,"Wait");
insert into T_VALUES values (102,10,2,"Execute");
insert into T_VALUES values (201,20,1,"Capital");
insert into T_VALUES values (202,20,2,"Current");
commit;
Мы заметили, что ссылка в таблице T_VALUES на T_CLASSES оформлена в виде FK, но индекса по CLASS_ID нет. Теперь попробуем увидеть проблемы, возникающие при этой грубой ошибке в проектировании схемы данных. Используем для этого SQL*Plus.
Предположим, что мы хотим удалять записи из таблицы T_CLASSES при помощи внешней процедуры несколькими командами удаления, не давая возможности напрямую удалять записи в клиентском приложении и позволяя выполнять только нашу процедуру. Такой подход является очень хорошей практикой при разработке приложений. Но здесь в этом примере мы процедуру писать не будем, а выполним последовательность команд, которая должна находиться в теле процедуры и должна правильно удалять запись из таблицы классов. Откроем первую сессию SQL*Plus и выполним команды:
select * from T_VALUES where CLASS_ID=10 for update nowait;
select * from T_CLASSES where for update nowait;
delete T_VALUES where CLASS_ID=10;
delete T_CLASSES where;
Мы сначала блокируем записи в подчиненной таблице, затем блокируем запись в главной таблице, затем удаляем записи в подчиненной и главной таблицах. Но пока не завершаем транзакцию.
Теперь откроем вторую сессию SQL*Plus для имитации многопользовательского доступа. Предположим, что мы хотим удалить запись из таблицы T_CLASSES с ID 20. По логике работы приложения мы вправе ожидать успешного удаления этой записи, ведь первый сеанс явно заблокировал только запись в T_CLASSES с ID 10 и записи в T_VALUES с ID 101 и 102. Попробуем удалить записи во втором сеансе. При этом мы помним, что первый сеанс удалил свои записи, но не завершил транзакцию. Выполним команды:
select * from T_VALUES where CLASS_ID=20 for update nowait;
select * from T_CLASSES where for update nowait;
delete T_VALUES where CLASS_ID=20;
Мы видим, что они прошли успешно. Нам удалось нормально заблокировать требуемые записи как в подчиненной таблице, так и в главной. Результат не очень согласуется с распространенным утверждением (впрочем, верным для версий сервер до девятой), что полностью блокируется подчиненная таблица при отсутствии индекса на внешний ключ. Мы даже успешно удалили записи в подчиненной таблице. Это объясняется тем, что сервер блокирует подчиненную таблицу целиком только в момент выполнения удаления, после этого блокировка со всей таблицы снимается. Поэтому мы и смогли заблокировать и удалить нужные нам записи во второй сессии. В версиях сервера Oracle до девятой блокировка подчиненной таблицы оставалась до окончания транзакции. В любом случае потребовались ресурсы сервера для выполнения полной блокировки подчиненной таблицы. Теперь выполним:
delete T_CLASSES where;
В результате мы получили зависание. Второй сеанс, несмотря на успешную блокировку записи в T_CLASSES с ID=20, ждет окончание транзакции первого сеанса. Выполним в первом сеансе rollback и увидим, что во втором сеансе успешно завершилось удаление записи. Как мы видим, в современных версиях сервера Oracle проблема блокировки при неиндексированных внешних ключах до конца не снята, а лишь несколько уменьшены возможные последствия. При этом несмотря на вроде бы успешную блокировку записи в T_CLASSES удалить запись не удается!
Если же мы создадим требуемый индекс:
create index T_VALUES_CLASS_FK
on T_VALUES (CLASS_ID);
и затем повторим наш тест, то увидим, что все работает так, как ожидается, без каких либо зависаний.
Мы получили интересные результаты. Конечно, отсутствие индексов на внешние ключи это принципиально плохо, однако эту ошибку разработчики повторяют раз за разом. В сложных системах с сотнями таблиц и множеством внешних ключей отсутствие таких индексов может приводить к резкому снижению производительности системы, а также к зависаниям пользовательских приложений, которые ожидают завершение транзакций других пользователей. Самое печальное, что при использовании операторов блокировки записей select .. for update nowait в приложениях может не возникнуть ожидаемое исключение resource busy, а может возникнуть неконтролируемое зависание приложения, ожидающего завершения транзакций. Кроме того, вероятность возникновения deadlock многократно повышается.
Рекомендация известна и очень проста - нельзя допускать применение внешних ключей при отсутствии индекса по этим внешним ключам. Однако, так ли тривиален этот совет в реальной жизни? Рассмотрим несколько распространенных конструкций, в которых может показаться, что индекс по внешнему ключу есть, но он не работает при блокировках.
Продолжим наш пример. Выполним rollback в обеих наших сессиях и удалим недавно созданный индекс:
drop index T_VALUES_CLASS_FK;
Затем создадим составной индекс, который будет включать в себя два внешних ключа:
create index T_VALUES_IE1
on T_VALUES (CLASS_ID,TYPE_ID);
Снова повторим описанный выше пример. Все работает? Замечательно, так и ожидалось, ведь внешний ключ CLASS_ID индексирован. А теперь в первой сессии выполним следующие команды:
select * from T_VALUES where TYPE_ID=1 for update nowait;
select * from T_TYPES where for update nowait;
delete T_VALUES where TYPE_ID=1;
delete T_TYPES where;
Мы заблокировали и удалили записи из T_VALUES и T_TYPES для ID=1. Во втором сеансе выполняем команды:
select * from T_VALUES where TYPE_ID=2 for update nowait;
select * from T_TYPES where for update nowait;
delete T_VALUES where TYPE_ID=2;
delete T_TYPES where;
На четвертой команде получаем зависание, ожидающее завершение транзакции в первом сеансе. Уже становится понятно, что виновата во всем ссылка на T_TYPES, которая ведет себя как неиндексированная. Но ведь индекс по TYPE_ID присутствует в составном индексе! Но для блокировок такого индекса нет. Мы можем сделать вывод, что использование составного индекса для внешних ключей недопустимо, если этот ключ не является лидирующим в составном индексе. Хотя при выполнении запросов к таблице оптимизатор сервера сможет нормально использовать составной индекс и для CLASS_ID, и для TYPE_ID. В итоге мы получили еще один довольно интересный результат.
Некоторые разработчики используют в своих базах данных таблицы, организованные по индексу. Часто критерием для использования такого типа таблиц является уменьшение размера занимаемого дискового пространства и увеличение скорости поиска. Однако для такой таблицы обязательно должен быть указан при создании первичный ключ. И если первичный ключ состоит из нескольких полей, которые являются также ссылками на другие таблицы, то мы получим такие же проблемы при блокировках, как и в составных индексах обычных таблиц. Покажем это на примере. Создадим заново таблицу T_VALUES организованную по индексу.
drop table T_VALUES;
create table T_VALUES
(CLASS_ID number(32),
TYPE_ID number(32),
NAME varchar2(256),
constraint T_VALUES_PK primary key (CLASS_ID, TYPE_ID)
) organization index;
alter table T_VALUES
add constraint T_VALUES_CLASS_FK
foreign key (CLASS_ID)
references T_CLASSES;
alter table T_VALUES
add constraint T_VALUES_TYPE_FK
foreign key (TYPE_ID)
references T_TYPES;
insert into T_VALUES values (10,1,"Wait");
insert into T_VALUES values (10,2,"Execute");
insert into T_VALUES values (20,1,"Capital");
insert into T_VALUES values (20,2,"Current");
commit;
Затем выполним первый тест, все пройдет нормально для внешнего ключа CLASS_ID, который лидирует в определении первичного ключа таблицы, организованной по индексу. Второй тест приведет к зависаниям, связанным с ожиданием завершения транзакции в первом сеансе. В случае, если в таблице такого типа в первичном ключе есть более одного внешнего ключа, то по этому внешнему ключу придется создавать индекс для предотвращения проблем с блокированием. В связи с этим, использование таблиц, организованных по индексу в этом случае вряд ли себя может оправдать. Кроме того, если для таких таблиц внешний ключ в составе первичного ключа таблицы только один, но он по каким-либо причинам не лидирует в определении первичного ключа, также возникнет проблема с блокировкой. Пример:
drop table T_VALUES;
create table T_VALUES
(CLASS_ID number(32),
TYPE_ID number(32),
NAME varchar2(256),
constraint T_VALUES_PK primary key (NAME, CLASS_ID)
) organization index;
alter table T_VALUES
add constraint T_VALUES_CLASS_FK
foreign key (CLASS_ID)
references T_CLASSES;
alter table T_VALUES
add constraint T_VALUES_TYPE_FK
foreign key (TYPE_ID)
references T_TYPES;
insert into T_VALUES values (10,1,"Wait");
insert into T_VALUES values (10,2,"Execute");
insert into T_VALUES values (20,1,"Capital");
insert into T_VALUES values (20,2,"Current");
commit;
В этом примере в определении первичного ключа всего одна внешняя ссылка, но она не является лидирующей в определении, следовательно, первый тест приведет к зависанию.
На основании рассмотренного выше материала можно сделать следующие выводы:
Использование неиндексированных внешних ключей является в общем случае грубейшей ошибкой при проектировании схемы базы данных. В результате очень вероятно получение блокировок подчиненных таблиц целиком при работе в многопользовательской среде, выражаемых в как бы зависании клиентских приложений, которые могут ожидать завершение транзакций других пользователей. Также очень вероятно резкое снижение производительности в многопользовательской среде из-за блокирования целых таблиц.
Резко возрастает вероятность возникновения deadlock вследствие большого объема блокируемых строк в таблицах.
Необходимо четко себе представлять, что такое неиндексированный внешний ключ. В составных индексах не лидирующие внешние ключи ведут себя при блокировках так же, как если индекса совсем нет. То же самое наблюдение относится к первичным ключам в таблицах, организованных по индексу. Здесь совет может быть следующий: всегда создавайте одиночный, не составной индекс для внешнего ключа.
Использование таблиц, организованных по индексу, должно быть четко обосновано. Всегда необходимо иметь в виду возможность возникновения проблем с блокировками таблиц целиком.
В заключение привожу известный скрипт Тома Кайта для поиска таблиц с неиндексированными внешними ключами:
select table_name, constraint_name,
cname1 // nvl2(cname2,",'//cname2,null) //
nvl2(cname3,",'//cname3,null) // nvl2(cname4,",'//cname4,null) //
nvl2(cname5,",'//cname5,null) // nvl2(cname6,",'//cname6,null) //
nvl2(cname7,",'//cname7,null) // nvl2(cname8,",'//cname8,null)
columns
from ( select b.table_name,
b.constraint_name,
max(decode( position, 1, column_name, null )) cname1,
max(decode( position, 2, column_name, null )) cname2,
max(decode( position, 3, column_name, null )) cname3,
max(decode( position, 4, column_name, null )) cname4,
max(decode( position, 5, column_name, null )) cname5,
max(decode( position, 6, column_name, null )) cname6,
max(decode( position, 7, column_name, null )) cname7,
max(decode( position, 8, column_name, null )) cname8,
count(*) col_cnt
from (select substr(table_name,1,30) table_name,
substr(constraint_name,1,30) constraint_name,
substr(column_name,1,30) column_name,
position
from user_cons_columns ) a,
user_constraints b
where a.constraint_name = b.constraint_name
and b.constraint_type = "R" -R referential integrity
group by b.table_name, b.constraint_name
) cons
where col_cnt > ALL
( select count(*)
from user_ind_columns i
where i.table_name = cons.table_name
and i.column_name in (cname1, cname2, cname3, cname4,
cname5, cname6, cname7, cname8 )
and i.column_position <= cons.col_cnt
group by i.index_name
)
order by TABLE_NAME, CONSTRAINT_NAME, COLUMNS