4.3.5. транзакції
Транзакцією називається виконання послідовності команд (SQL-конструкцій) в базі даних, яка або фіксується при успішній реалізації кожної команди, або скасовується при невдалому виконанні хоча б однієї команди. Більшість сучасних СУБД підтримують механізм транзакцій, і переважна більшість клієнтських додатків, що працюють з ними, використовують для виконання своїх команд транзакції.
Виникає питання - навіщо потрібні транзакції? Уявімо собі, що в базу даних BDTur_firm2 потрібно вставити пов'язані записи в дві таблиці - «Туристи» та «Інформація про туристів». Якщо запис, що вставляється в таблицю «Туристи», виявиться невірною, наприклад, через неправильно вказаного коду туриста, база даних не дозволить внести зміни, але при цьому в таблиці «Інформація про туристів» з'явиться непотрібна запис. Розглянемо таку ситуацію на прикладі.
Запустимо Management Studio, в новому бланку введемо запит для додавання двох записів:
Дві записи успішно додаються в базу даних:
(1 row (s) affected) // або (рядків оброблено: 1)
(1 row (s) affected) // або (рядків оброблено: 1)
Тепер спровокуємо помилку - змінимо код туриста тільки в другому запиті:
З'являється повідомлення про неможливість вставки першого запису з уже наявними значенням ключового поля. Другий запис, однак, додається в таблицю:
Повідомлення 2627, рівень 14, стан 1, рядок 1
Violation of PRIMARY KEY constraint 'PK_Турісти'. Can not insert duplicate key in object 'dbo.Турісти'.
The statement has been terminated.
(Рядків оброблено: 1)
Винесемо вміст обох таблиць наступним подвійним запитом:
SELECT * FROM Туристи
SELECT * FROM [Інформація про туристів]
У таблиці «Інформація про туристів» останній запис додалася без усякого зв'язку із записом таблиці «Туристи» (рис. 99). Для того щоб уникати подібних помилок, потрібно застосувати транзакцію.
Мал. 99. Вміст таблиць «Туристи» та «Інформація про туристів» - порушення зв'язку
Видалимо всі внесені записи з обох таблиць і оформимо вихідні SQL-конструкції у вигляді транзакції:
Початок транзакції оголошується за допомогою команди BEGIN TRAN. Далі створюються два параметра - @OshibkiTabliciTourists, OshibkiTabliciInfoTourists для збору помилок. Після першого запиту повертаємо значення, яке вбудована функція @@ ERROR привласнює першому параметру:
Те ж саме робимо після другого запиту для іншого параметра:
Перевіряємо значення обох параметрів, які повинні бути рівними нулю при відсутності помилок:
IF @ OshibkiTabliciTourists = 0 AND @ OshibkiTabliciInfoTourists = 0
В цьому випадку підтверджуємо транзакцію (в даному випадку внесення змін) за допомогою команди COMMIT TRAN. В іншому випадку - якщо значення хоча б одного з параметрів @OshibkiTabliciTourists і @OshibkiTabliciInfoTourists виявляється відмінним від нуля, скасовуємо транзакцію за допомогою команди ROLLBACK TRAN.
Після виконання транзакції з'являється повідомлення про додавання двох рядків:
(Рядків оброблено: 1)
(Рядків оброблено: 1)
Знову змінимо код туриста в другому запиті:
Запускаємо транзакцію - з'являється в точності таке ж повідомлення, що і в разі застосування звичайних запитів:
Повідомлення 2627, рівень 14, стан 1, рядок 3
Violation of PRIMARY KEY constraint 'PK_Турісти'. Can not insert duplicate key in object 'dbo.Турісти'.
The statement has been terminated. (Рядків оброблено: 1)
Однак тепер зміни не були внесені в другу таблицю (рис. 100).
Мал. 100. Вміст таблиць «Туристи» та «Інформація про туристів» після виконання невдалої транзакції
Повідомлення (1 row (s) affected), яке вказує на «додавання» запису, в даному випадку воно всього лише означає, що друга SQL-конструкція була вірною, і запис могла бути додана в разі успішного виконання транзакції.
Таким чином, механізм транзакцій підтримує цілісність даних двох таблиць, не дозволяючи її порушити додаванням невірних даних.
Перейдемо тепер до розгляду транзакцій в ADO .NET. Створимо новий консольний додаток Easy Transaction. Поставимо задачу: передати ті ж самі дані в дві таблиці - «Туристи» та «Інформація про туристів».
Повний лістинг цього додатка виглядає наступним чином:
Перед запуском програми знову видаляємо все додані записи з таблиць. При успішному виконанні запиту з'являється відповідне повідомлення, а в таблиці додаються записи (рис. 101).
Мал. 101. Додаток EasyTransaction. транзакція виконана
Повторний запуск цього додатка призводить до відхилення транзакції - можна вставляти записи з однаковими значеннями первинних ключів (рис. 102).
Мал. 102. Додаток EasyTransaction. транзакція відхилена
У вигляді транзакції можна оформляти виконання однієї або декількох збережених процедур, - справді, загальна конструкція має наступний вигляд:
// Створюємо з'єднання
. см. в прикладі додатка EasyTransaction
// Створюємо транзакцію
myCommand.Transaction = conn.BeginTransaction ();
try <
// Виконуємо команди, викликаємо одну або кілька процедур
// Підтверджуємо транзакцію
myCommand.Transaction.Commit ();
>
catch (Exception ex) <
// Відхиляємо транзакцію
myCommand.Transaction.Rollback ();
>
finally <
// Закриваємо з'єднання
conn.Close ();
>
При виконанні транзакцій декількома користувачами однієї бази даних можуть виникати такі проблеми:
1. Dirty reads - «брудне» читання. Перший користувач починає транзакцію, що змінює дані. В цей час інший користувач (або створювана ним транзакція) витягує частково змінені дані, які не є коректними.
2. Non-repeatable reads - є повторюваною читання. Перший користувач починає транзакцію, що змінює дані. В цей час інший користувач починає і завершує іншу транзакцію. Перший користувач при повторному читанні даних (наприклад, якщо в його транзакцію входить кілька інструкцій SELECT) отримує інший набір записів.
3. Phantom reads - читання фантомів. Перший користувач починає транзакцію, що вибирає дані з таблиці. В цей час інший користувач починає і завершує транзакцію, вставляти або видаляє записи. Перший користувач отримає інший набір даних, що містить фантоми - віддалені або змінені рядки.
Для вирішення цих проблем розроблені чотири рівні ізоляції транзакції:
1. Read uncommitted. Транзакція може зчитувати дані, з якими працюють інші транзакції. Застосування цього рівня ізоляції може призвести до всіх перерахованих проблем.
2. Read committed. Транзакція не може зчитувати дані, з якими працюють інші транзакції. Застосування цього рівня ізоляції виключає проблему «брудного» читання.
3. Repeatable read. Транзакція не може зчитувати дані, з якими працюють інші транзакції. Інші транзакції також не можуть зчитувати дані, з якими працює ця транзакція. Застосування цього рівня ізоляції виключає всі проблеми, крім читання фантомів.
4. Serializable. Транзакція повністю ізольована від інших транзакцій. Застосування цього рівня ізоляції повністю виключає всі проблеми.
Використання найбільшого рівня ізоляції (Serializable) означає найбільшу безпеку і разом з тим найменшу продуктивність - все транзакції виконуються у вигляді серії, подальша змушена чекати завершення попередньої. І навпаки, застосування найменшого рівня (Read uncommitted) означає максимальну продуктивність і повна відсутність безпеки. Втім, не можна дати універсальних рекомендацій щодо застосування цих рівнів - в кожній конкретній ситуації рішення буде залежати від структури бази даних і характеру виконуваних запитів.
Для установки рівня ізоляції застосовується наступна команда:
SET TRANSACTION ISOLATION LEVEL
READ UNCOMMITTED
або READ COMMITTED
або REPEATABLE READ
або SERIALIZABLE
Наприклад, в транзакції, додає два записи, рівень ізоляції вказується наступним чином:
BEGIN TRAN SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
DECLARE @OshibkiTabliciTourists int, @OshibkiTabliciInfoTourists int
.
ROLLBACK TRAN
У ADO .NET рівень ізоляції можна встановити при створенні транзакції:
myCommand.Transaction = conn.BeginTransaction (System.Data.IsolationLevel.Serializable); Додатково підтримуються ще два рівня (рис. 104):
1. Chaos. Транзакція не може перезаписати інші неприйняті транзакції з великим рівнем ізоляції, але може перезаписувати роботу без використання транзакцій. Дані, з якими працює поточна транзакція, що не блокуються;
2. Unspecified. Окремий рівень ізоляції, який може застосовуватися, але не може бути визначений. Транзакція з цим рівнем може застосовуватися для завдання власного рівня ізоляції.
Мал. 104. Визначення рівня транзакції
Транзакції забезпечують цілісність бази даних, при розробці багаторівневих додатків їх застосування є обов'язковим правилом.