Блокування Oracle при неіндексованих зовнішніх ключах - Oracle - Програмні продукти
У цій статті йдеться про блокування при використанні неіндексованих зовнішніх ключів. Тема відома для розробників, проте, на мій погляд, існує кілька підводних каменів, на які часто не звертають увагу багато розробників, внаслідок чого можуть виникати дуже серйозні проблеми в розрахованому на багато користувачів середовищі.
p align="justify"> При проектуванні схеми бази даних і розробці таблиць існує дуже важливе правило: зовнішні ключі в таблицях без індексу не мають права на існування. Інакше ми отримуємо повне блокування підпорядкованої таблиці при виконанні зміни первинного ключа або видалення запису з головної таблиці. Однак що означає повне блокування підпорядкованої таблиці? Як вона проявляється? При детальному дослідженні можна дізнатися багато цікавого.
Спробуємо продемонструвати паразитні ефекти неіндексованих зовнішніх ключів на прикладі. Наведений нижче приклад був створений і протестований для версії Oracle 10g R2. Створимо три прості таблиці. Перша буде містити список класів і складається з двох полів - ідентифікатор та найменування класу, друга буде містити список типів, складається також із двох полів - ідентифікатор та найменування типу, третя міститиме списки значень довідників, і містить чотири поля - ідентифікатор, посилання на клас , посилання на тип та найменування. Ідентифікатори в таблицях являють собою сурогатні первинні ключі. Скрипти для створення таблиць:
create table T_CLASSES
add constraint T_CLASSES_PK
add constraint T_TYPES_PK
add constraint T_VALUES_PK
add constraint T_VALUES_CLASS_FK
add constraint T_VALUES_TYPE_FK
Заповнимо створенітаблиці тестовими даними:
insert в T_CLASSES values (10,"State");
insert в T_CLASSES values (20,"Repair");
insert in T_TYPES values (1,"Type 1′);
insert in T_TYPES values (2,"Type 2′);
insert в T_VALUES values (101,10,1,"Wait");
insert in T_VALUES values (102,10,2,"Execute");
insert in T_VALUES values (201,20,1,"Capital");
insert in T_VALUES values (202,20,2,"Current");
Припустимо, що ми хочемо видаляти записи з таблиці T_CLASSES за допомогою зовнішньої процедури кількома командами видалення, не даючи можливості видаляти записи в клієнтському додатку безпосередньо і дозволяючи виконувати тільки нашу процедуру. Такий підхід є дуже гарною практикою розробки додатків. Але тут у цьому прикладі ми процедури писати не будемо, а виконаємо послідовність команд, яка повинна знаходитися в тілі процедури і має правильно видаляти запис із таблиці класів. Відкриємо першу сесію SQL*Plus та виконаємо команди:
select * з T_VALUES where CLASS_ID=10 для update nowait;
select * from T_CLASSES where for update nowait;
delete T_VALUES where CLASS_ID=10;
Ми спочатку блокуємо записи у підпорядкованій таблиці, потім блокуємо запис у головній таблиці, потім видаляємо записи у підпорядкованій та головній таблицях. Але поки що не завершуємо транзакцію.
Тепер відкриємо другу сесію SQL*Plus для імітації розрахованого на багато користувачів доступу. Припустимо, що ми хочемо видалити запис з таблиці T_CLASSES з ID 20. За логікою роботи програми ми маємо право очікувати успішного видалення цього запису, адже перший сеанс явно заблокував тільки запис у T_CLASSES з ID 10 і записи в T_VALUES з ID 101 і 102.записи у другому сеансі. Ми пам'ятаємо, що перший сеанс видалив свої записи, але не завершив транзакцію. Виконаємо команди:
select * з T_VALUES where CLASS_ID=20 для update nowait;
select * from T_CLASSES where for update nowait;
delete T_VALUES where CLASS_ID=20;
Ми бачимо, що вони пройшли успішно. Нам вдалося нормально заблокувати необхідні записи як у підлеглій таблиці, так і в головній. Результат не дуже узгоджується з поширеним твердженням (втім, вірним для версій сервер до дев'ятої), що повністю блокується підпорядкована таблиця за відсутності індексу зовнішній ключ. Ми навіть успішно видалили записи у підпорядкованій таблиці. Це пояснюється тим, що сервер блокує підлеглу таблицю повністю тільки в момент виконання видалення, після чого блокування з усієї таблиці знімається. Тому ми й змогли заблокувати та видалити потрібні нам записи у другій сесії. У версіях сервера Oracle до дев'ятого блокування підпорядкованої таблиці залишалося до закінчення транзакції. У будь-якому випадку знадобилися ресурси сервера для повного блокування підпорядкованої таблиці. Тепер виконаємо:
В результаті ми отримали зависання. Другий сеанс, незважаючи на успішне блокування запису T_CLASSES з ID=20, чекає закінчення транзакції першого сеансу. Виконаємо у першому сеансі rollback та побачимо, що у другому сеансі успішно завершилося видалення запису. Як бачимо, у сучасних версіях сервера Oracle проблема блокування при неіндексованих зовнішніх ключах остаточно не знято, лише кілька зменшено можливі наслідки. При цьому незважаючи на начебто успішне блокування запису в T_CLASSES видалити запис не вдається!
Якщо ж ми створимо необхідний індекс:
create index T_VALUES_CLASS_FK
і потімповторимо наш тест, то побачимо, що все працює так, як очікується, без жодних зависань.
Ми отримали цікаві результати. Звичайно, відсутність індексів на зовнішні ключі це принципово погано, проте цю помилку розробники повторюють щоразу. У складних системах з сотнями таблиць і безліччю зовнішніх ключів відсутність таких індексів може призводити до різкого зниження продуктивності системи, а також до зависань додатків, які очікують завершення транзакцій інших користувачів. Найсумніше, що при використанні операторів блокування записів 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 * з T_VALUES where TYPE_ID=1 для update nowait;
select *від T_TYPES, де для update nowait;
delete T_VALUES where TYPE_ID=1;
Ми заблокували та видалили записи з T_VALUES та T_TYPES для ID=1. У другому сеансі виконуємо команди:
select * з T_VALUES where TYPE_ID=2 для update nowait;
select * from T_TYPES where for update nowait;
delete T_VALUES where TYPE_ID=2;
constraint T_VALUES_PK primary key (CLASS_ID, TYPE_ID)
add constraint T_VALUES_CLASS_FK
add constraint T_VALUES_TYPE_FK
insert в T_VALUES values (10,1,"Wait");
insert в T_VALUES values (10,2,"Execute");
insert in T_VALUES values (20,1,"Capital");
insert in T_VALUES values (20,2,"Current");
Потім виконаємо перший тест, все пройде нормально зовнішнього ключа CLASS_ID, який лідирує у визначенні первинного ключа таблиці, організованої за індексом. Другий тест призведе до зависань, пов'язаних з очікуванням на завершення транзакції в першому сеансі. У разі, якщо в таблиці такого типу в первинному ключі є більше одного зовнішнього ключа, то за цим зовнішнім ключем доведеться створювати індекс для запобігання проблемам блокування. У зв'язку з цим використання таблиць, організованих за індексом, у цьому випадку навряд чи себе може виправдати. Крім того, якщо для таких таблиць зовнішній ключ у складі первинного ключа таблиці лише один, але він з якихось причин не лідирує у визначенні первинного ключа, також виникне проблема блокування. Приклад:
constraint T_VALUES_PK primary key (NAME, CLASS_ID)
add constraint T_VALUES_CLASS_FK
add constraint T_VALUES_TYPE_FK
insert в T_VALUES values (10,1,"Wait");
insert intoT_VALUES values (10,2, "Execute");
insert in T_VALUES values (20,1,"Capital");
insert in T_VALUES values (20,2,"Current");
З розглянутого вище матеріалу можна зробити такі выводы:
Використання неіндексованих зовнішніх ключів є у випадку грубої помилкою під час проектування схеми бази даних. В результаті дуже ймовірно отримання блокувань підпорядкованих таблиць повністю при роботі в розрахованому на багато користувачів середовищі, що виражаються в зависанні клієнтських додатків, які можуть очікувати завершення транзакцій інших користувачів. Також дуже ймовірно різке зниження продуктивності в розрахованому на багато користувачів середовищі через блокування цілих таблиць.
Різко зростає ймовірність виникнення deadlock внаслідок великого обсягу рядків, що блокуються в таблицях.
Необхідно чітко уявляти, що таке неіндексований зовнішній ключ. У складових індексах зовнішні ключі, що не лідирують, поводяться при блокуваннях так само, як якщо індексу зовсім немає. Те саме спостереження відноситься до первинних ключів у таблицях, організованих за індексом. Тут порада може бути така: завжди створюйте одиночний, не складовий індекс для зовнішнього ключа.
Використання таблиць, організованих за індексом, має бути чітко обґрунтовано. Завжди необхідно мати на увазі можливість виникнення проблем із блокуваннями таблиць цілком.
Насамкінець наводжу відомий скрипт Тома Кайта для пошуку таблиць з неіндексованими зовнішніми ключами:
select table_name, constraint_name,
from (select b.table_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,
from (виберіть substr(table_name,1,30) table_name,
from user_cons_columns ) a,
де a.constraint_name = b.constraint_name
і b.constraint_type = "R" -R посилальна цілісність
групувати за b.table_name, b.constraint_name
з user_ind_columns i
де i.table_name = cons.table_name
та i.column_name у (cname1, cname2, cname3, cname4,
cname5, cname6, cname7, cname8 )