Лекція № 11
Управління транзакціями – 2 год
Транзакції
Транзакції та її властивості
Порушення цілісності при виконанні транзакцій
Обмеження цілісності та їх класифікація (за реалізацією, за часом, за областю)
Реалізація декларативних обмежень БД засобами SQL
Можливості SQL з задання декларативних обмежень
Синтаксис обмежень операторів SQL
Транзакції та паралелизм. Проблеми багатокористувацького доступу
Основні проблеми паралелизму
Робота транзакцій у суміші
Конфлікти між транзакціями
Способи розв'язання конфліктів між транзакціями. Серіалізація транзакцій.
1. Транзакції
1.1. Поняття транзакції. Транзакції та їх властивості
Транзакція - це неподільна, з погляду впливу на СУБД, послідовність операцій маніпулювання даними. Для користувача транзакція виконується за принципом "усі чи нічого", тобто або транзакція виконується цілком і переводить базу даних з одного цілісного стану в інший цілісний стан, або, якщо за якимись причинами, одна з дій транзакції нездійсненна, чи відбулося яке-небудь порушення роботи системи, база даних повертається у вихідний стан, що був до початку транзакції (відбувається відкат транзакції).
В однокористувальницьких системах транзакції - це логічні одиниці роботи, після виконання яких база даних залишається в цілісному стані. Транзакції також є одиницями відновлення даних після збоїв - відновлюючись, система ліквідує сліди транзакцій, що не встигли успішно завершитися в результаті програмного чи апаратного збою. Ці дві властивості транзакцій визначають атомарность (неподільність) транзакції. У багатокористувацьких системах, крім того, транзакції служать для забезпечення ізольованої роботи окремих користувачів - користувачам, що одночасно працюють з однією базою даних, здається, що вони працюють як би в однокористувальницькій системі і не заважають один одному.
Визначення 1. Транзакція - це послідовність операторів маніпулювання даними, що виконується як єдине ціле (все чи нічого) і переводить базу даних з одного цілісного стану в інший цілісний стан.
Транзакція має чотири важливі властивості, відомі як властивості АУІД:
(А) Атомарність. Транзакція виконується як атомарна операція - або виконується вся транзакція повністю, або вона повністю не виконується.
(У) Узгодженість. Транзакція переводить базу даних з одного узгодженого (цілісного) стану в інший узгоджений (цілісний) стан. Усередині транзакції узгодженість бази даних може порушуватися.
(І) Ізоляція. Транзакції різних користувачів не повинні заважати одна одній (наприклад, як якби вони виконувалися строго по черзі).
(Д) Довговічність. Якщо транзакція виконана, то результати її роботи повинні зберегтися в базі даних, навіть якщо в наступний момент відбудеться збій системи.
Транзакція звичайно починається автоматично з моменту приєднання користувача до СУБД і продовжується доти, поки не відбудеться одне з наступних подій:
Подано команду COMMIT WORK (зафіксувати транзакцію).
Подано команду ROLLBACK WORK (відкотити транзакцію).
Відбулося від'єднання користувача від СУБД.
Відбувся збій системи.
Команда COMMIT WORK завершує поточну транзакцію й автоматично починає нову транзакцію. При цьому гарантується, що результати роботи завершеної транзакції фіксуються, тобто зберігаються в базі даних.
Команда ROLLBACK WORK приводить до того, що всі зміни, зроблені поточною транзакцією, відкочуються, тобто скасовуються так, начебто їх узагалі не було. При цьому автоматично починається нова транзакція.
При від'єднанні користувача від СУБД відбувається автоматична фіксація транзакцій.
При збої системи відбуваються більш складні процеси. Коротко суть їх зводиться до того, що при наступному запуску системи відбувається аналіз транзакцій, які виконувалися до моменту збою транзакцій. Ті транзакції, для яких була подана команда COMMIT WORK, але результати роботи яких не були занесені в базу даних виконуються знову (накочуються). Ті транзакції, для яких не була подана команда COMMIT WORK, відкочуються.
1.2. Порушення цілісності при виконанні транзакцій
Будь-яка база даних придатна до використання тільки тоді, коли її стан відповідає стану предметної області. Такі стани називають цілісними. Очевидно, що при зміні даних БД повинна переходити від одного цілісного стану до іншого. Однак, у процесі відновлення даних можливі ситуації, коли стан цілісності порушується.
Для ілюстрації можливого порушення цілісності бази даних розглянемо наступний приклад:
Приклад 1. Нехай є система, у якій зберігаються дані про підрозділи і працюючих у них співробітниках. Список підрозділів зберігається в таблиці DEPART(Dep_Id, Dep_Name, Dept_Kol), де Dept_Id - ідентифікатор підрозділу, Dept_Name - найменування підрозділу, Dept_Kol - кількість співробітників у підрозділі. Список співробітників зберігається в таблиці PERSON(Pers_Id, Pers_Name, Dept_Id), де Pers_Id - ідентифікатор співробітника, Pers_Name - ім'я співробітника, Dept_Id - ідентифікатор підрозділу, у якому працює співробітник:
Dept_Id
Dept_Name
Dept_Kol

1
Кафедра математики
3

2
Кафедра програмування
2

Таблиця 1. DEPART
Pers_Id
Pers_Name
Dept_Id

1
Іванов
1

2
Петров
2

3
Сидорчук
1

4
Пушняк
2

5
Шарко
1

Таблиця 2. PERSON
Обмеження цілісності цієї бази даних полягає в тому, що поле Dept_Kol не може заповнюватися довільними значеннями - це поле повинне містити кількість співробітників, що реально числяться в підрозділі.
З урахуванням цього обмеження можна укласти, що вставка нового співробітника в таблицю не може бути виконана однією операцією. При вставці нового співробітника необхідно одночасно збільшити значення поля Dept_Kol:
Крок 1. Уставити співробітника в таблицю PERSON: INSERT INTO PERSON (6, Мутаков, 1)
Крок 2. Збільшити значення поля Dept_Kol: UPDATE DEPART SET Dept=Dept+1 WHERE Dept_Id=1
Якщо після виконання першої операції і до виконання другий відбудеться збій системи, то реально буде виконані тільки перша операція і база даних залишиться в нецілісному стані.
Саме щоб уникнути таких ситуацій у СУБД уводиться поняття транзакції - атомарної дії над БД, що переводить її з одного цілісного стану в інший цілісний стан. Іншими словами, транзакція - це послідовність операцій, що повинні чи бути усі виконані, чи всі не виконані (усе чи нічого). Методом контролю за транзакціями є ведення журналу, у якому фіксуються всі зміни, чинені транзакцією в БД. Якщо під час обробки транзакції відбувається збій, транзакція відкочується - з журналу поновлюється стан БД на момент початку транзакції.
1.3. Обмеження цілісності та їх класифікація
Властивість (У) - узгодженість транзакцій - визначається наявністю поняття узгодженості бази даних.
Визначення 2. Обмеження цілісності - це деяке твердження, що може бути правдивим чи хибним в залежності від стану бази даних.
Прикладами обмежень цілісності можуть служити наступні твердження:
Приклад 2. Вік співробітника не може бути менше 18 і більше 65 років.
Приклад 3. Кожен співробітник має унікальний табельний номер.
Приклад 4. Співробітник зобов'язаний числитися в одному відділі.
Приклад 5. Сума накладної зобов'язана дорівнювати сумі добутків цін товарів на кількість товарів для всіх товарів, що входять у накладну.
Як видно з цих прикладів, деякі з обмежень цілісності є обмеженнями реляційної моделі даних (дів лекцію по обмеженнях цілісності реляційних БД). Приклад 3 - обмеження, що реалізує цілісність сутності. Приклад 4 - обмеження, що реалізує посилальну цілісність. Інші обмеження є досить довільними твердженнями (приклади 2 і 5). Будь-яке обмеження цілісності є семантичним поняттям, тобто з'являється як наслідок визначених властивостей об'єктів предметної області і/чи їхніх взаємозв'язків.
Визначення 3. База даних знаходиться в узгодженому (цілісному) стані, якщо виконані (задоволені) всі обмеження цілісності, визначені для бази даних.
Узгодженість бази даних є формальною властивістю бази даних. База даних не розуміє "змісту" збережених даних. "Змістом" даних для СУБД є весь набір обмежень цілісності. Якщо всі обмеження виконані, то СУБД вважає, що дані коректні.
Разом з поняттям цілісності бази даних виникає поняття реакції системи на спробу порушення цілісності. Система повинна не тільки перевіряти, чи не порушуються обмеження в ході виконання різних операцій, але і належним образом реагувати, якщо операція приводить до порушення цілісності. Є два типи реакції на спробу порушення цілісності:
Відмовлення виконати "незаконну" операцію.
Виконання дій, що її компенсують.
Класифікація обмежень цілісності. Обмеження цілісності можна класифікувати кількома способами:
За способами реалізації.
За часом перевірки.
За областю дії.
Класифікація обмежень цілісності за способами реалізації. Кожна система має свої засоби підтримки обмежень цілісності. Розрізняють два способи реалізації:
Декларативна підтримка обмежень цілісності.
Процедурна підтримка обмежень цілісності.
Декларативна підтримка обмежень цілісності полягає у визначенні обмежень засобами мови визначення даних (DDL - Data Definition Language). Звичайно засоби декларативної підтримки цілісності (якщо вони є в СУБД) визначають обмеження на значення доменів і атрибутів, цілісність сутностей (потенційні ключі відношень) і посилальну цілісність (цілісність зовнішніх ключів). Декларативні обмеження цілісності можна використовувати при створенні і модифікації таблиць засобами мови DDL чи у вигляді окремих тверджень (ASSERTION).
Процедурна підтримка обмежень цілісності полягає у використанні тригерів і збережених процедур.
Не всі обмеження цілісності можна реалізувати декларативно. Прикладом такого обмеження може служити вимога, що стверджує, що поле Dept_Kol таблиці DEPART повинне містити кількість співробітників, що реально значаться в підрозділі. Для реалізації цього обмеження необхідно створити тригер, який запускається при вставці, модифікації і видаленні записів у таблиці PERSON, що коректно змінює значення поля Dept_Kol. Наприклад, при вставці в таблицю PERSON нового рядка, тригер збільшує на одиницю значення поля Dept_Kol, а при видаленні рядка - зменшує. При модифікації записів у таблиці PERSON можуть знадобитися і більш складні дії. Модифікація запису в таблиці PERSON може полягати в тім, що ми переводимо співробітника з одного відділу в інший, змінюючи значення в поле Dept_Id. При цьому необхідно в старому підрозділі зменшити кількість співробітників, а в новому - збільшити.
По суті, наявність обмеження цілісності (як декларативного, так і процедурного характеру) завжди приведе до чи створення використання деякого програмного коду, що реалізує це обмеження. Різниця полягає в тому, де такий код зберігається і як він створюється.
Якщо обмеження цілісності реалізоване у вигляді тригерів, то цей програмний код є просто тілом тригера. Якщо використовується декларативне обмеження цілісності, то можливі два підходи:
При декларуванні (оголошенні) обмеження текст обмеження зберігається у вигляді деякого об'єкта СУБД, а для реалізації обмеження використовуються убудовані в СУБД функції, і тоді цей код являє собою внутрішні функції ядра СУБД.
При декларуванні обмеження СУБД автоматично генерує тригери, що виконують необхідні дії по перевірці обмежень.
Класифікація обмежень цілісності за часом перевірки. За часом перевірки обмеження поділяються на:
Обмеження, що негайно перевіряються.
Обмеження з відкладеною перевіркою.
Обмеження, що негайно перевіряються, перевіряються безпосередньо в момент виконання операції, що може порушити обмеження. Наприклад, перевірка унікальності потенційного ключа перевіряється в момент вставки запису в таблицю. Якщо обмеження порушується, то така операція відкидається. Транзакція, усередині якої відбулося порушення твердження цілісності, що негайно перевіряється, звичайно відкочується.
Обмеження з відкладеною перевіркою перевіряється в момент фіксації транзакції оператором COMMIT WORK. Усередині транзакції обмеження може не виконуватися. Якщо в момент фіксації транзакції виявляється порушення обмеження з відкладеною перевіркою, то транзакція відкочується.
Класифікація обмежень цілісності за областю дії. За областю дії обмеження поділяються на:
Обмеження домену
Обмеження атрибута
Обмеження кортежу
Обмеження відносини
Обмеження бази даних
Обмеження цілісності домену являють собою обмеження, що накладаються тільки на допустимі значення домену. Фактично, обмеження домену зобов'язані бути частиною визначення домену Наприклад, обмеженням домену "Вік співробітника" може бути умова "Вік співробітника не менш 18 і не більш 65".
Перевірка обмеження. Обмеження домену самі по собі не перевіряються. Якщо на певному домені заснований атрибут, то обмеження відповідного домену стає обмеженням цього атрибута.
Обмеження цілісності атрибута являють собою обмеження, що накладаються на припустимі значення атрибута внаслідок того, що атрибут заснований на певному домені. Обмеження атрибута в точності збігаються з обмеженнями відповідного домену. Відмінність обмежень атрибута від обмежень домену в тому, що обмеження атрибута перевіряються.
Якщо логіка предметної області така, що на значення атрибута необхідно накласти додаткові обмеження, крім обмежень домену, то такі обмеження переходять у наступну категорію.
Перевірка обмеження. Обмеження атрибута є обмеженням, що негайно перевіряється. Дійсно, обмеження атрибута не залежить ні від яких інших об'єктів бази даних, крім домену, на якому заснований атрибут. Тому ніякі зміни в інших об'єктах не можуть уплинути на істинність обмеження.
Обмеження цілісності кортежу являють собою обмеження, що накладаються на припустимі значення окремого кортежу відносини, і не є обмеженням цілісності атрибута. Вимога, що обмеження відноситься до окремого кортежу відношення, означає, що для його перевірки не потрібно ніякої інформації про інші кортежі відносини.
Приклад 6. Атрибут "Вік співробітника" у таблиці "Спецпідрозділ", може мати додаткове обмеження "Вік співробітника не менш 25 і не більш 45", крім того, що цей атрибут уже має обмеження, обумовлене доменом - "Вік співробітника не менш 18 і не більш 65".
Наведене обмеження кортежу, по суті, є додатковим обмеженням на значення одного атрибута. У цьому випадку припустимі два рішення. Можна оголосити новий домен "Вік співробітника спецпідрозділу" і тоді обмеження кортежу стає обмеженням домену й атрибута, або розглядати це обмеження саме як обмеження кортежу. Обидва рішення мають свої позитивні і негативні сторони. Отут є деякі можливості для оптимізації. Формально, при зміні значення даного атрибута необхідно перевірити два обмеження - обмеження атрибута й обмеження кортежу. Але в даному випадку обмеження кортежу сильніше обмеження атрибута і досить перевірити тільки обмеження кортежу. Розумно побудована СУБД могла б виявляти такі випадки і зменшувати зайву роботу.
Перевірка обмеження. До моменту перевірки обмеження кортежу повинні бути перевірені обмеження цілісності атрибутів, що входять у цей кортеж.
Обмеження кортежу є обмеженням, що негайно перевіряється. Дійсно, обмеження кортежу не залежить ні від яких інших об'єктів бази даних, крім атрибутів, що входять до складу кортежу. Тому ніякі зміни в інших об'єктах не можуть уплинути на істинність обмеження.
Обмеження цілісності відносини становлять обмеження, що накладаються тільки на допустимі значення окремого відношення, і не є обмеженням цілісності кортежу. Вимога, що обмеження стосується окремого відношення, означає, що для його перевірки не потрібно інформації про інші відношення (у тому числі не потрібно посилань по зовнішньому ключеві на кортежі цього ж відношення).
Приклад 9. Обмеження цілісності сутності, що задається потенційним ключем відношення, є обмеженням відношення, тому що для його перевірки необхідно мати інформацію про всі кортежі відношення (більш точно, про всі зайняті у даний момент значення потенційного ключа).
Приклад 10. Обмеження цілісності, обумовлені наявністю функціональних, багатозначних залежностей і залежностей з'єднання, є обмеженнями відношення.
Приклад 11. Припустимо, що у відношенні PERSON (див. приклад 1) задане наступне обмеження - у кожному відділі повинне бути не менш двох співробітників. Це обмеження можна сформулювати так - кількість рядків з однаковим значенням Dept_Id повинне бути не менше 2.
Зауваження. Для того щоб ввести в дію (оголосити) це обмеження, необхідно, щоб у відношення вже були вставлені деякі кортежі.
Приклад 12. Обмеження цілісності, обумовлене вимогою, що деяка таблиця повинна бути не порожня, є обмеженнями відношення.
Перевірка обмеження. До моменту перевірки обмеження відношення повинні бути перевірені обмеження цілісності кортежів цього відношення.
Обмеження відношення може бути як обмеженням, що негайно перевіряється, так і обмеженням з відкладеною перевіркою.
Обмеження відношення, що є обмеженням потенційного ключа є обмеженням, що негайно перевіряється.
Обмеження, визначене наявністю функціональної залежності атрибутів також є обмеженням, що негайно перевіряється.
Обмеження ж, визначені багатозначною чи залежністю залежністю з'єднання є обмеженнями з відкладеною перевіркою. Дійсно, ці обмеження вимагають, щоб кортежі вставлялися і видалялися цілими групами. Це неможливо зробити, якщо виконувати перевірку після кожної одиночної вставки чи видалення кортежу.
Обмеження цілісності бази даних становлять обмеження, що накладаються на значення двох чи більше зв'язаних між собою відношень (у тому числі відношення може бути зв'язане саме із собою).
Приклад 13. Обмеження цілісності посилань, що задається зовнішнім ключем відношення, є обмеженням БД.
Приклад 14. Обмеження на таблиці DEPART і PERSON із приклада 1 є відношенням бази даних, тому що воно зв'язує дані, розміщені в різних таблицях.
Перевірка обмеження. До моменту перевірки обмеження бази даних повинні бути перевірені обмеження цілісності відношень.
Обмеження бази даних може бути як обмеженням, що негайно перевіряється, так і обмеженням з відкладеною перевіркою.
Обмеження відношення, що є обмеженням зовнішнього ключа, може бути як обмеженням, що негайно перевіряється, так і відкладеним обмеженням.
2. Реалізація декларативних обмежень БД засобами SQL
2.1. Можливості SQL з задання декларативних обмежень
У СУБД різних постачальників початок транзакції може задаватися явно (наприклад, командою BEGIN TRANSACTION), або передбачатися неявним (так визначено в стандарті SQL), тобто чергова транзакція відкривається автоматично відразу ж після вдалого чи невдалого завершення попередньої. Для завершення транзакції звичайно використовують команди SQL:
COMMIT - успішно завершити транзакцію
ROLLBACK - відкотити транзакцію, тобто повернути БД у стан, у якому вона знаходилася на момент початку транзакції.
Стандарт SQL визначає, що транзакція починається з першого SQL-оператора, який ініціюється користувачем чи міститься в прикладній програмі. Усі наступні SQL-оператори складають тіло транзакції. Транзакція завершується одним з можливих способів:
оператор COMMIT означає успішне завершення транзакції, усі зміни, внесені в базу даних робляться постійними
оператор ROLLBACK перериває транзакцію і скасовує усі внесені нею зміни
успішне завершення програми, що ініціювала транзакцію, означає успішне завершення транзакції (як використання COMMIT)
помилкове завершення програми перериває транзакцію (як ROLLBACK)
Приклад явно заданої транзакції:
BEGIN TRANSACTION; /* Почати транзакцію */
DELETE ...; /* Зміни */
UPDATE ...; /* даних */
if (виявлена_помилка) ROLLBACK;
else COMMIT; /* Завершити транзакцію */
Приклад неявно заданої транзакції:
СOMMIT; /* Закінчення попередньої транзакції */
DELETE ...; /* Зміни */
UPDATE ...; /* даних */
if (виявлена_помилка) ROLLBACK;
else COMMIT; /* Завершити транзакцію */
Стандарт SQL не передбачає процедурних обмежень цілісності, реалізованих за допомогою тригерів і збережених процедур. У стандарті SQL 92 відсутнє поняття "тригер", хоча тригери є у всіх промислових СУБД SQL-типу. Таким чином, реалізація обмежень засобами конкретної СУБД має більшу гнучкість, ніж з використанням винятково стандартних засобів SQL.
Стандарт SQL дозволяє задавати декларативні обмеження такими способами:
Як обмеження домену.
Як обмеження, що входять у визначення таблиці.
Як обмеження, що зберігаються в базі даних у вигляді незалежних тверджень (assertion).
Допускаються як обмеження, що перевіряються негайно, так і обмеження з відкладеною перевіркою. Режим перевірки відкладених обмежень можна в будь-який момент змінити так, щоб обмеження перевірялося:
Після виконання кожного оператора, що змінює вміст таблиці, якого стосується дане обмеження.
При завершенні кожної транзакції, що включає оператори, що змінюють уміст таблиць, яких стосується дане обмеження.
У будь-який проміжний момент, якщо користувач ініціює перевірку.
При визначенні обмеження вказується тип перевірки обмеження - чи є це обмеження таким, що не відкладається (NOT DEFERRED), чи може бути відкладене (DEFERRED). В другому випадку можна задати процедуру за замовчуванням: перевіряти негайно чи перевіряти по завершенню транзакції.
Елементи процедурності все-таки присутні в стандарті SQL у вигляді так званих дій, що виконуються по посиланню (referential triggered actions). Ці дії визначають, що буде відбуватися при зміні значення батьківського ключа, на який посилається деякий зовнішній ключ. Ці дії можна задавати незалежно для операцій відновлення (ON UPDATE) чи для операцій видалення (ON DELETE) записів у батьківському відношенні. Стандартом SQL визначається 4 типи дій, що виконуються по посиланню:
CASCADE. Зміни значення батьківського ключа автоматично приводять до таких же змін зв'язаного з ним значення зовнішнього ключа. Видалення кортежу в батьківському відношенні приводить до видалення зв'язаних з ним кортежів у дочірньому відношенні.
SET NULL. Усі зовнішні ключі, що посилаються на обновлений чи вилучений батьківський ключ, дістають значення NULL.
SET DEFAULT. Усі зовнішні ключі, що посилаються на обновлений чи вилучений батьківський ключ дістають значення, прийняті за замовчуванням для цих ключів.
NO ACTION. Значення зовнішнього ключа не змінюються. Якщо операція приводить до порушення посилальної цілісності (з'являються "висячі" посилання), то така операція не виконується.
Як видно, дії, що виконуються по посиланню, фактично є убудованими в СУБД тригерами. Дії типу CASCADE, SET NULL і SET DEFAULT є компенсуючими операціями, що викликаються при спробі порушити посилальну цілісність.
2.2. Синтаксис обмежень стандарту SQL
Типи обмежень у операторах DDL. Поняття обмеження використовується в багатьох операторах визначення даних (DDL).
Обмеження типу CHECK. Обмеження check::= CHECK Предикат
Обмеження типу CHECK містить предикат, що може приймати значення TRUE, FALSE і UNKNOWN (NULL). Обмеження типу CHECK може бути використане як частина опису домену, таблиці, стовпця, таблиці чи окремого обмеження цілісності - ASSERTION. Обмеження вважається порушеним, якщо предикат обмеження приймає значення FALSE.
Обмеження таблиці й обмеження стовпця. Обмеження таблиці ::= [CONSTRAINT Ім'я обмеження] { {PRIMARY KEY (Ім'я стовпця.,..)} | {UNIQUE (Ім'я стовпця.,..)} | {FOREIGN KEY (Ім'я стовпця.,..) REFERENCES Ім'я таблиці [(Ім'я стовпця.,..)] [Посилальна специфікація]} | { Обмеження check } } [Атрибути обмеження]
Обмеження стовпця::= [CONSTRAINT Ім'я обмеження] { {NOT NULL} | {PRIMARY KEY} | {UNIQUE} | {REFERENCES Ім'я таблиці [(Ім'я стовпця)] [Посилальна специфікація]} | { Обмеження check } } [Атрибути обмеження]
Обмеження таблиці й обмеження стовпця таблиці входять як частина опису відповідно таблиці чи стовпця таблиці. Обмеження таблиці може відноситися до кількох стовпців таблиці. Обмеження стовпця відноситься тільки до одного стовпця таблиці. Будь-яке обмеження стовпця можна описати як обмеження таблиці, але не навпаки.
Обмеження таблиці чи стовпця можуть мати найменування, за допомогою якого надалі можна скасовувати це чи обмеження змінювати час його перевірки.
Обмеження PRIMARY KEY. Обмеження PRIMARY KEY для таблиці чи стовпця означає, що група з одного чи декількох стовпців утворять потенційний ключ таблиці. Це означає, що комбінація значень у PRIMARY KEY повинна бути унікальною для кожного рядка таблиці. Дубльовані значення чи значення, що містять NULL, будуть відкинуті. Для однієї таблиці може бути визначене єдине обмеження PRIMARY KEY. У термінах стандарту SQL це називається первинним ключем таблиці.
Обмеження UNIQUE. Обмеження UNIQUE для таблиці чи стовпця означає, що група з одного чи декількох стовпців утворить потенційний ключ таблиці, у якому допускаються значення NULL. Це означає, що два рядки, що містять однакові і не рівні NULL-значення, вважаються порушуючими унікальність і не допускаються. Два рядки, що містять NULL-значення вважаються різними і допускаються. Для однієї таблиці може бути визначено кілька обмежень UNIQUE.
Обмеження FOREIGN KEY і REFERENCES. Обмеження FOREIGN KEY... REFERENCES... для таблиці й обмеження REFERENCES... для стовпця визначають зовнішній ключ таблиці. Обмеження REFERENCES... для стовпця визначає простий зовнішній ключ, тобто ключ, що складається з одного стовпчика. Обмеження FOREIGN KEY... REFERENCES... для таблиці може визначати як простий, так і складний зовнішній ключ, тобто ключ, що складається з декількох стовпчиків таблиці. Стовпець чи група стовпців таблиці, на яку посилається зовнішній ключ, повинна мати обмеження PRIMARY KEY чи UNIQUE. Стовпці, на які посилається зовнішній ключ, повинні мати той же тип даних, що і стовпці, що входять до складу зовнішнього ключа. Таблиця може мати посилання на себе. Обмеження зовнішнього ключа порушується, якщо значення, що є присутнім у зовнішньому ключі, не збігаються зі значеннями відповідного ключа батьківської таблиці для жодного рядка з батьківської таблиці. Операції, що приводять до порушення обмеження зовнішнього ключа, відкидаються.
Обмеження NOT NULL. Обмеження NOT NULL стовпця не допускає появи в стовпці NULL-значень.
Посилальна специфікація. Посилальна специфікація::= [MATCH {FULL | PARTIAL}] [ON UPDATE {CASCADE | SET NULL | SET DEFAULT | NO ACTION}] [ON DELETE {CASCADE | SET NULL | SET DEFAULT | NO ACTION}]
Посилальна специфікація визначає характеристики зовнішнього ключа таблиці.
Речення MATCH {FULL | PARTIAL}. Речення MATCH FULL вимагає повного збігу значень зовнішнього і первинного ключів. Речення MATCH PARTIAL допускає частковий збіг значень зовнішнього і первинного ключів. Речення MATCH може бути також пропущеним. Для випадку MATCH PARTIAL у дочірній таблиці можуть з'явитися рядки, що мають значення зовнішнього ключа, що неунікально збігаються зі значеннями батьківського ключа.
Речення ON UPDATE і ON DELETE. Речення ON UPDATE і ON DELETE визначають дії, що виконуються по посиланню.
Атрибути обмеження. Атрибути обмеження::= {DEFERRABLE [INITIALLY DEFERRED | INITIALLY IMMEDIATE]} | {NOT DEFERRABLE}
Атрибути обмеження визначають, у який момент перевіряються обмеження. Обмеження може бути визначене як NOT DEFERRABLE ( щоневідкладається) чи DEFERRABLE ( щовідкладається). Якщо атрибути обмеження не зазначені, то за замовчуванням приймається NOT DEFERRABLE.
Оператори, що застосовують обмеження. Стандарт SQL описує наступні операторы, у яких може бути використані обмеження:
CREATE DOMAIN - створити домен
ALTER DOMAIN - змінити домен
DROP DOMAIN - видалити домен
CREATE TABLE - створити таблицю
ALTER TABLE - змінити таблицю
DROP TABLE - видалити таблицю
CREATE ASSERTION - створити твердження
DROP ASSERTION - видалити твердження
COMMIT WORK - зафіксувати транзакцію
SET CONSTRAINTS - установити момент перевірки обмежень
Синтаксис операторов описан в специальной литературе, например, CREATE DOMAIN:
CREATE DOMAIN Ім'я домену [AS] Тип даних
[DEFAULT Значення за замовчуванням] [Ім'я обмеження] Обмеження check [Атрибути обмеження]
Цей оператор задає домен, на основі якого можна визначати стовпці таблиць. Оскільки ім'я стовпця, що буде заснований на цьому домені заздалегідь невідоме, то в обмеженні CHECK домену для посилання на значення цього домену використовується ключове слово VALUE. У конкретній таблиці СУБД замінить слово VALUE на ім'я стовпця таблиці.
Оператор ALTER DOMAIN змінює наявний домен. Стандарт забороняє вносити кілька змін однією командою ALTER DOMAIN. Якщо потрібно видалити обмеження CHECK і додати значення за замовчуванням, то доведеться виконати два операторів ALTER DOMAIN.
ALTER DOMAIN Ім'я домену {SET DEFAULT Значення за замовчуванням} | {DROP DEFAULT} | {ADD [Ім'я обмеження] Обмеження check [Атрибути обмеження]} | {DROP CONSTRAINT Ім'я обмеження}
Оператор DROP DOMAIN знищує наявних домен:
DROP DOMAIN Ім'я домену CASCADE | RESTRICT
Якщо зазначена опція RESTRICT, то домен не знищується, якщо є посилання на нього зі стовпців таблиць. Якщо зазначена опція CASCADE, то відбуваються наступні дії:
Тип даних домену передається стовпцям, заснованим на цьому домені.
Якщо стовпець не має значення за замовчуванням, а для домену значення за замовчуванням визначено, то воно стає значенням за замовчуванням для стовпця.
Всі обмеження домену стають обмеженнями стовпця.
Оператор CREATE TABLE (синтаксис приведений не цілком - пропущені опції створення тимчасових таблиць) створює таблицю бази даних:
CREATE TABLE Ім'я таблиці ( {Визначення стовпця | [Обмеження таблиці]}.,..)
Визначення стовпця::= Ім'я стовпця {Ім'я домену | Тип даних [Розмір]} [Обмеження стовпця…][DEFAULTЗначення за замовчуванням]
У таблиці має бути не менш одного визначення стовпця. У таблиці може бути визначено кілька обмежень (у тому числі і жодного).
Кожен стовпець повинен мати ім'я і бути визначений на деякому типі даних чи на деякому домені. Обмеження домену стають обмеженнями стовпця. Крім того, стовпець може мати додаткові обмеження. Якщо домен має значення за замовчуванням і у визначенні стовпця визначене значення за замовчуванням, то значення для стовпця перекриває значення для домену.
Оператор DROP TABLE дозволяє змінювати наявну таблицю:
DROP TABLE Ім'я таблиці CASCADE | RESTRICT
У таблиці можна видаляти чи додавати стовпці і/чи обмеження. Крім того, для стовпців можна змінювати значення за замовчуванням. Якщо зазначений параметр RESTRICT, то таблиця видаляється тільки якщо немає ніяких посилань на цю таблицю в інших обмеженнях чи представленнях. Якщо зазначений параметр CASCADE, то видаляються також і всі об'єкти, що посилаються на цю таблицю.
Оператор CREATE ASSERTION дозволяє створювати твердження - тобто обмеження, що не є частиною визначення домену, чи стовпчики таблиці:
CREATE ASSERTION Ім'я твердження Обмеження check [Атрибути обмеження]
Предикат CHECK, що входить у визначення твердження, може бути досить складним і містити посилання на кілька таблиць.
Оператор DROP ASSERTION дозволяє видаляти наявне твердження: DROP ASSERTION Ім'я твердження.
Оператор COMMIT WORK фіксує транзакцію. При цьому перевіряються усі відкладені до кінця транзакції обмеження. Якщо одне з обмежень не виконується, то транзакція відкочується.
Оператор SET CONSTRAINT дозволяє під час виконання транзакції змінювати момент перевірки усіх (ALL) чи деяких обмежень:
SET CONSTRAINT {Ім'я обмеження.,.. | ALL} {DEFERRED | IMMEDIATE}
Цей оператор діє тільки на обмеження, визначені як DEFERRABLE (потенційно відкладані). Якщо обмеження A знаходилося в стані IMMEDIATE (перевіряються негайно), то оператор SET CONSTRAINT A DEFERRED переводить його в стан DEFERRED (з відкладеною перевіркою) і тоді всі операції, що потенційно можуть порушити це обмеження, будуть виконуватися без перевірки. Перевірка буде зроблена наприкінці транзакції чи в момент подачі команди SET CONSTRAINT A IMMEDIATE.
Таким чином, база даних знаходиться в узгодженому стані, якщо для цього стану виконані всі обмеження цілісності. Стандарт мови SQL підтримує тільки декларативні обмеження цілісності, реалізовані як:
Обмеження домену.
Обмеження, що входять у визначення таблиці.
Обмеження, що зберігаються в базі даних у виді незалежних тверджень (assertion).
Перевірка обмежень допускається як після виконання кожного оператора, що може порушити обмеження, так і наприкінці транзакції. Під час виконання транзакції можна змінити режим перевірки обмеження.
Описаний механізм транзакцій гарантує забезпечення цілісного стану бази даних тільки в тому випадку, коли всі транзакції виконуються послідовно, тобто в кожну одиницю часу активна тільки одна транзакція.
3. Транзакції та паралелизм. Проблеми багатокористувацького доступу
3.1. Основні проблеми паралелизму
Сучасні СУБД є багатокористувацькими системами, тобто допускають паралельну одночасну роботу великої кількості користувачів. При цьому користувачі не повинні заважати один одному. Оскільки логічною одиницею роботи для користувача є транзакція, то робота СУБД повинна бути організована так, щоб у користувача складалося враження, що його транзакції виконуються незалежно від транзакцій інших користувачів.
Найпростіший і очевидний спосіб забезпечити таку иллюзию в користувача полягає в тому, щоб усі транзакції, що надходять, вибудовувати в єдину чергу і виконувати строго по черзі. Такий спосіб не годиться по очевидних причинах - губиться перевага паралельної роботи. Таким чином, транзакції необхідно виконувати одночасно, але так, щоб результат був би такий же, як якби транзакції виконувалися по черзі. Труднощі полягають у тому, що якщо не приймати ніяких спеціальних заходів, то дані, змінені одним користувачем можуть бути змінені транзакцією іншого користувача раніш, ніж закінчиться транзакція першого користувача. У результаті, наприкінці транзакції перший користувач побачить не результати своєї роботи, а невідомо що.
Розглянутий раніше механізм транзакцій гарантує забезпечення цілісного стану бази даних тільки в тому випадку, коли всі транзакції виконуються послідовно, тобто в кожну одиницю часу активна тільки одна транзакція. Якщо роботу з даними ведуть одночасно кілька користувачів, навряд чи їх влаштує такий спосіб організації обробки запитів, тому що це призведе до збільшення часу реакції системи. У той же час, якщо одночасно виконуються дві транзакції, можуть виникнути помилкові ситуації. Розрізняють три основні проблеми паралелізму:
Проблема втрати результатів оновлення.
Проблема незафіксованої залежності (читання "брудних" даних, неакуратне зчитування).
Проблема несумісного аналізу.
Проблема втрати результатів оновлення чи неповторювальне (розмите) читання (Non-repeatable or Fuzzy Read) - транзакція Т1 прочитала вміст елементу даних. Після цього інша транзакція Т2 модифікувала чи видалила цей елемент. Якщо Т1 прочитає вміст цього елементу заново, то вона отримає інше значення чи знайде, що елемент даних більше не існує.
Брудне читання (Dirty Read) - транзакція Т1 модифікувала деякий елемент даних. Після цього інша транзакція Т2 прочитала вміст цього елементу даних до завершення транзакції Т1. Якщо Т1 завершується операцією ROLLBACK, то виходить, що транзакція Т2 прочитала не існуючі дані.
Проблема несумісного аналізу включає кілька різних варіантів:
Неповторювальне зчитування.
Фіктивні елементи (фантоми).
Власне несумісний аналіз.
Неповторювальне зчитування. Транзакція A двічі читає ту саму рядок. Між цими читаннями вклинюється транзакція B, що змінює значення в рядку. Транзакція A нічого не знає про існування транзакції B, і, тому що сама вона не змінює значення в рядку, то очікує, що після повторного читання значення буде тим же самим.
Результат. Транзакція A працює з даними, що, з погляду транзакції A, самовільно змінюються.
Фантом (фіктивні елементи) (Phantom) - транзакція Т1 прочитала вміст декількох елементів даних, задовольняючих певній умові. Після цього Т2 створила елемент даних, що задовольняє цій умові і зафіксувалася. Якщо Т1 повторить читання з тою же умовою, вона отримає інший набір даних.
Ефект власне несумісного аналізу також відрізняється від попередніх прикладів тим, що в суміші присутні дві транзакції - одна довга, інша коротка. Довга транзакція виконує деякий аналіз по всій таблиці, наприклад, підраховує загальну суму грошей на рахунках клієнтів банку для головного бухгалтера. Наприклад, нехай на всіх рахунках знаходяться однакові суми, наприклад, по $100. Коротка транзакція в цей момент виконує переклад $50 з одного рахунка на інший так, що загальна сума по всіх рахунках не міняється.
Результат. Хоча транзакція B усе зробила правильно - гроші переведені без утрати, але в результаті транзакція A підрахувала невірну загальну суму. Оскільки транзакції по переведенню грошей йдуть зазвичай безупинно, то в даній ситуації варто очікувати, що головний бухгалтер ніколи не дізнається, скільки ж грошей у банку.
Всі описані вище ситуації виникли тільки тому, що виконання, що чергується, транзакцій Т1 і Т2 не було упорядковано, тобто не було еквівалентно виконанню спочатку транзакції Т1, а потім Т2, або, навпаки, спочатку транзакції Т2, а потім Т1. Жодна з цих ситуацій не може виникнути при послідовному виконанні транзакцій. Звідси виникло поняття серіалізованості (здатності до упорядкування) паралельної обробки транзакцій. Тобто паралельне виконання заданої множини транзакцій, які чергуються, буде вірним, якщо при його виконанні буде отриманий такий же результат, як і при послідовному виконанні тих же транзакцій.
Примусове упорядкування транзакцій забезпечується за допомогою механізму блокувань і деяких інших методів.
3.2. Робота транзакцій у суміші
Транзакція розглядається як послідовність елементарних атомарних операцій. Атомарність окремої елементарної операції полягає в тому, що СУБД гарантує, що, з погляду користувача, будуть виконані дві умови:
Ця операція буде виконана цілком чи не виконана зовсім (атомарність - все чи нічого).
Під час виконання цієї операції не виконуються ніякі інші операції інших транзакцій (стругаючи черговість елементарних операцій).
Елементарні операції різних транзакцій можуть виконуватися в довільній черговості (звичайно, усередині кожної транзакції послідовність елементарних операцій цієї транзакції є строго визначеної).
Набір з декількох транзакцій, елементарні операції яких чергуються одна з одною, називається сумішшю транзакцій. Послідовність, у якій виконуються елементарні операції заданого набору транзакцій, називається графіком запуску набору транзакцій. Очевидно, що для заданого набору транзакцій може бути кілька (узагалі говорячи, досить багато) різних графіків запуску.
При дотриманні обов'язкової вимоги підтримки цілісності бази даних можливі наступні рівні ізольованості транзакцій (тобто ізольованості користувачів, імітації їх роботи відокремлено від інших):
Перший рівень - відсутність загублених змін. Розглянемо наступний сценарій спільного виконання двох транзакцій. Транзакція 1 змінює об'єкт бази даних A. До завершення транзакції 1 транзакція 2 також змінює об'єкт A. Транзакція 2 завершується оператором ROLLBACK (наприклад, через порушення обмежень цілісності). Тоді при повторному читанні об'єкта A транзакція 1 не бачить змін цього об'єкта, зроблених раніше. Така ситуація називається ситуацією загублених змін. Природно, вона суперечить вимозі ізольованості користувачів. Щоб уникнути такої ситуації в транзакції 1 потрібно, щоб до завершення транзакції 1 ніяка інша транзакція не могла змінювати об'єкт A. Відсутність загублених змін є мінімальною вимогою до СУБД по синхронізації паралельно виконуваних транзакцій.
Другий рівень - відсутність читання "брудних даних". Розглянемо наступний сценарій спільного виконання транзакцій 1 і 2. Транзакція 1 змінює об'єкт бази даних A. Паралельно з цим транзакція 2 читає об'єкт A. Оскільки операція зміни ще не завершена, транзакція 2 бачить неузгоджені "брудні" дані (зокрема, операція транзакції 1 може бути відвернена при перевірці обмеження цілісності, що негайно перевіряється). Це теж не відповідає вимозі ізольованості користувачів (кожен користувач починає свою транзакцію при узгодженому стані бази даних і в праві очікувати бачити погоджені дані). Щоб уникнути ситуації читання "брудних" даних, до завершення транзакції 1, що змінила об'єкт A, ніяка інша транзакція не повинна читати об'єкт A (мінімальною вимогою є блокування читання об'єкта A до завершення операції його зміни в транзакції 1).
Третій рівень - відсутність неповторюваних читань. Розглянемо наступний сценарій. Транзакція 1 читає об'єкт бази даних A. До завершення транзакції 1 транзакція 2 змінює об'єкт A і успішно завершується оператором COMMIT. Транзакція 1 повторно читає об'єкт A і бачить його змінений стан. Щоб уникнути неповторюваних читань, до завершення транзакції 1 ніяка інша транзакція не повинна змінювати об'єкт A. У більшості систем це є максимальною вимогою до синхронізації транзакцій, хоча й відсутність неповторюваних читань ще не гарантує реальної ізольованості користувачів.
3.3. Конфлікти між транзакціями
Аналіз проблем паралелізму показує, що якщо не приймати спеціальних заходів, то при роботі в суміші порушується властивість (І) транзакцій - ізольованість. Транзакції реально заважають одна одній одержувати правильні результати.
Однак не всякі транзакції заважають одна одній. Очевидно, що транзакції не заважають одна одній, якщо вони звертаються до різних даних чи виконуються в різний час. Транзакції називаються конкуруючими, якщо вони перетинаються за часом і звертаються до тих самих даних.
У результаті конкуренції за даними між транзакціями виникають конфлікти доступу до даних. Розрізняють наступні види конфліктів:
W-W (Запис - Запис). Перша транзакція змінила об'єкт і не закінчилася. Друга транзакція намагається змінити цей об'єкт. Результат - утрата оновлення.
R-W (Читання - Запис). Перша транзакція прочитала об'єкт і не закінчилася. Друга транзакція намагається змінити цей об'єкт. Результат - несумісний аналіз (неповторювальне зчитування).
W-R (Запис - Читання). Перша транзакція змінила об'єкт і не закінчилася. Друга транзакція намагається прочитати цей об'єкт. Результат - читання "брудних" даних.
Конфлікти типу R-R (Читання - Читання) відсутні, тому що дані при читанні не змінюються.
Інші проблеми паралелізму (фантоми і власне несумісний аналіз) є більш складними, тому що принципова відмінність їх у тому, що вони не можуть виникати при роботі з одним об'єктом. Для виникнення цих проблем потрібно, щоб транзакції працювали з цілими наборами даних.
3.4. Способи розв'язання конфліктів між транзакціями. Серіалізація транзакцій
Графік запуску набору транзакцій називається послідовним, якщо транзакції виконуються строго по черзі, тобто елементарні операції транзакцій не чергуються одна з одною. Якщо графік запуску набору транзакцій містить елементарні операції транзакцій, що чергуються, то такий графік називається таким, що чергується. При виконанні послідовного графіка гарантується, що транзакції виконуються правильно, тобто при послідовному графіку транзакції не "відчувають" присутності один одного.
Дві графіки називаються еквівалентними, якщо при їхньому виконанні буде отриманий той самий результат, незалежно від початкового стану бази даних.
Графік запуску транзакції називається вірним (серіалізованим), якщо він еквівалентний якому-небудь послідовному графіку.
Задача забезпечення ізольованої роботи користувачів не зводиться просто до знаходження правильних (серіальних) графіків запусків транзакцій. Якби цього було достатнє, то кращим був би найпростіший спосіб серіалізації - ставити транзакції в загальну чергу в міру їхнього надходження і виконувати строго послідовно. Таким способом автоматично буде отриманий правильний (серіальний) графік. Проблема в тому, що цей графік буде неоптимальним з погляду загальної продуктивності системи. Утворюється ситуація, у якій борються протилежні сили - з одного боку, прагнення забезпечити серіальність за рахунок погіршення загальної ефективності роботи, з іншого боку, прагнення поліпшити загальну ефективність за рахунок погіршення серіальності.
Оптимальний графік - це графік з максимальною ефективністю виконання транзакцій. Для цього спочатку потрібно уточнити поняття "оптимальність". З кожним можливим графіком запуску транзакцій ми можемо зв'язати значення деякої вартісної функції. Як вартісну функцію можна взяти, наприклад, сумарний час виконання всіх транзакцій у наборі. Час виконання однієї транзакції рахується від моменту, коли транзакція виникла і до моменту, коли транзакція виконала свою останню елементарну операцію. Цей час складається з наступних компонентів:
Час чекання початку транзакції - той час, що проходить від моменту, коли транзакція виникла до моменту, коли почалася реально виконуватися її перша елементарна операція.
Сума часів виконання елементарних операцій транзакції.
Сума часів всіх елементарних операцій інших транзакцій, що вклинилися між елементарними операціями транзакції.
Оптимальним буде графік, що дає мінімум вартісної функції. Очевидно, оптимальність графіка запуску залежить від вибору вартісної функції, тобто графік, оптимальний з погляду одних критеріїв (наприклад, з погляду приведеної функції вартості) не буде оптимальним з погляду інших критеріїв (наприклад, з погляду досягнення максимально швидкого початку виконання кожної транзакції).
У реальній ситуації невідомо не тільки те, які транзакції будуть надходити в які моменти часу, але і невідома тривалість періоду часу, що охоплює набір транзакцій. Реально, система може безупинно працювати кілька днів чи місяців і в цьому випадку набором транзакцій буде набір усіх транзакцій за цей період. З іншого боку, припинення роботи сервера може відбутися в будь-який момент або по команді адміністратора системи, або в результаті збою. Необхідно, отже, щоб система працювала так, щоб до будь-якого моменту часу набір виконаних транзакцій і транзакцій що, виконуються в цей момент, був би правильним і не занадто далеким від оптимального.
Оскільки транзакції не заважають одна одній, якщо вони звертаються до різних даних чи виконуються в різний час, то є два способи розв'язати конкуренцію між надходячими в довільні моменти транзакціями:
"Пригальмовувати" деякі з що надходять транзакцій настільки, наскільки це необхідно для забезпечення правильності суміші транзакцій у кожен момент часу (тобто забезпечити, щоб конкуруючі транзакції виконувалися в різний час).
Надати конкуруючим транзакціям "різні" екземпляри даних (тобто забезпечити, щоб конкуруючі транзакції працювали з різними версіями даними).
Перший метод - "пригальмовування" транзакцій - реалізується шляхом використанням блокувань різних видів чи методу часових міток. Другий метод - надання різних версій даних - реалізується шляхом використанням даних з журналу транзакцій.
Висновки
Сучасні багатокористувацькі системи допускають одночасну роботу великого числа користувачів. При цьому якщо не починати спеціальних заходів, транзакції будуть заважати один одному. Цей ефект відомий як проблеми паралелізму.
Є три основні проблеми паралелізму:
Проблема втрати результатів оновлення.
Проблема незафіксованої залежності (читання "брудних" даних, неакуратне зчитування).
Проблема несумісного аналізу.
Графік запуску набору транзакцій називається послідовним, якщо транзакції виконуються строго по черзі. Якщо графік запуску набору транзакцій містить елементарні операції, транзакцій, які чергуються, то такий графік називається таким, що чергується. Дві графіки називаються еквівалентними, якщо при їхньому виконанні буде отриманий той самий результат, незалежно від початкового стану бази даних. Графік запуску транзакції називається вірним (серіалізованим), якщо він еквівалентний якому-небудь послідовному графіку.
Рішення проблем паралелізму складається в перебуванні такої стратегії запуску транзакцій, щоб забезпечити серіалізованість графіка запуску і не занадто зменшити ступінь паралельності.