Блокування Oracle при неіндексованих зовнішніх ключах

У даній статті мова піде про блокування при використанні неіндексованих зовнішніх ключів. Тема відома для розробників, однак, на мій погляд, існує декілька підводних каменів, на які часто не звертають увагу багато розробників, внаслідок чого можуть виникати дуже серйозні проблеми в багатокористувацької середовищі.


При проектуванні схеми бази даних і розробці таблиць існує дуже важливе правило: зовнішні ключі в таблицях без індексу не мають права на існування. Інакше ми отримуємо повне блокування підпорядкованої таблиці при виконанні зміни первинного ключа або при видаленні запису з головної таблиці. Однак, що означає повне блокування підпорядкованої таблиці? Як вона проявляється? При детальному дослідженні можна з'ясувати багато цікавого.


Спробуємо продемонструвати паразитні ефекти неіндексованих зовнішніх ключів на прикладі. Наведений нижче приклад був створений і протестовано для версії 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

Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*