Управляємо вкладеними транзакціями, MS SQL Server, Бази даних, статті

Brian Noyes

ADO.NET дозволяє вам легко оформити кілька звернень до БД в вигляді однієї транзакції. Однак програмісти часто неохоче використовують такий підхід, оскільки вони повністю не розуміють взаємодію між транзакціями, оголошеними в коді ADO.NET і транзакціями, що використовуються в збережених процедурах (ХП). Ситуація часто посилюється винятками, викликаними SQL Server’ом при використанні вкладених транзакцій з ХП, які самі використовують транзакції. У цій статті ми прояснимо зазначені моменти, і я покажу вам, як використовувати вкладені транзакції і правильно обробляти помилки.


Транзакції на стороні SQL Server


Насамперед необхідно усвідомити, яким чином транзакції працюють всередині БД. Транзакції дозволяють вам бути впевненим, що зміни, зроблені в БД в рамках транзакції, будуть або все прийняті або всі відкинуті. Є кілька рівнів ізоляції, які визначають, чи можуть дані, лічені під час роботи транзакції, бути змінені в той час, поки не закінчила роботу ця транзакція. За замовчуванням рівень встановлений в Read Committed, що говорить про те, що дані можуть бути змінені в контексті незавершеною транзакції, але ви не отримаєте даних, які тільки частково змінені з іншої транзакції. Якщо ж ви хочете переконатися в тому, що дані запиту не змінюються інший транзакцією до тих пір, поки не завершена ваша транзакція, вам необхідно встановити рівень ізоляції в Repeatable Read або Serializable. Більш докладно про ці рівнях можна прочитати в SQL Books online, також відомому як BOL. Кожен індивідуальний запит, виконуваний SQL Server’ом, автоматично оформляється як транзакція. Припустимо, у вас є команда update, модифікуюча 100 записів в таблиці. Якщо щось пішло не так на записи номер 99, то всі зміни відкочуються і, після виникнення помилки, ніяких в таблиці модифікацій ви не побачите. Якщо ж ви використовуєте ХП для доступу до вашої БД, ці ХП також повинні оформляти SQL-команди в транзакції рівня цієї ХП. Наприклад, розглянемо просту ХП:




CREATE PROCEDURE AddOrder
@CustomerName
nvarchar(50),
@StatusID int
AS
 SET TRANSACTION ISOLATION
LEVEL REPEATABLE READ
 BEGIN TRANSACTION
 IF NOT EXISTS

  (SELECT StatusID FROM OrderStatus
  WHERE
StatusID = @StatusID)
 BEGIN
  ROLLBACK TRANSACTION

RAISERROR (‘Ви повинні передати існуючий Status
ID’,11,1)
 RETURN
 END
INSERT INTO Orders
(CustomerName, StatusID)
 VALUES (@CustomerName, @StatusID)

 COMMIT TRANSACTION
RETURN


Ця ХП використовує свою власну транзакцію для команд SELECT і INSERT. SELECT переконується в тому, що переданий існуючий StatusID (наприклад для того, щоб переконатися, що не порушена посилальна цілісність), а INSERT додає запис до таблиці Orders. Якщо у вас декілька запитів в ХП виконані вигляді транзакції, можливо, ви захочете керувати транзакціями на цьому рівні. Відкат транзакції в будь-якому місці перед командою COMMIT TRANSACTION попередить запис змін до БД.

Ви можете подумати, що необов’язково включати оператор SELECT в транзакцію, оскільки він не змінює БД. У багатьох ситуаціях ви будете не праві. Виконання подібних запитів схоже на програмування багатопоточних додатків. Ви повинні припускати, що між виконанням команд SELECT і INSERT хтось може модифіковані таблицю OrderStatus і видалити значення, яке ви тільки що перевіряли на існування, в результаті команда INSERT не виконається. Вам часто доведеться переконуватися в тому, що всі запити, які ви виконуєте, виконуються з одним і тим же станом БД і кращий метод переконатися в цьому – оформити такі запити у вигляді транзакції і при необхідності встановити рівень транзакції в Repeatable Read або Serializable.

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


Транзакції в ADO.NET


А що якщо вам треба виконати кілька ХП з коду на C # згідно тим же принципом ‘все або нічого “? Як раз для цих цілей ADO.NET містить клас SqlTransaction. Технологія досить проста – викликаєте BeginTransaction для об’єкта SqlConnection, щоб отримати новий об’єкт транзакції, якщо все Ок – викликаємо SqlTransaction.Commit, якщо не все так добре, як хотілося –
SqlTransaction.Rollback.




/ / Створюємо з’єднання
(conn)
/ / Оголошуємо транзакцію
SqlTransaction trans;
// створюємо команди
try{
conn.Open (); / / відкриваємо з’єднання

trans = conn.BeginTransaction();
/ / Асоціюємо транзакції з командами
/ / Виконуємо запити
/ / Якщо все ОК – підтверджуємо

trans.Commit();
}
catch (Exception ex)
{
/ / Якщо ні – відкатуємо
if (trans != null) trans.Rollback();
}
finally

{
conn.Close();
}



Загалом, це просто, чи не так? Хитрість полягає в тому, щоб правильно обробляти виключення в блоці catch і розуміти ті винятки, які викликаються. Якщо ви викликаєте ХП, які не управляють своїми транзакціями, не повинно бути ніяких проблем. Якщо помилка виникає десь у час виконання вашого коду, то буде викликано виняток SqlException, яке потрапить в блок catch. Код в цьому блоці викличе Rollback і ніякі оновлення з початку транзакції не потраплять у вашу БД.


Не лякайтеся своїх вкладених інстинктів


А що ж відбувається, якщо ХП, які ви викликаєте, самі управляють своїми транзакціями? Чи не означають ці виклики COMMIT TRANSACTION всередині ХП того, що зміни будуть внесені в БД не дивлячись на відкат у коді на C #? Відповідь – немає, і причина такої відповіді в тому, як виконуються вкладені транзакції на SQL Server’e. Коли ви стартуєте транзакцію в своєму коді, насправді ви стартуєте її на сервері, всі наступні транзакції є для неї вкладеними, до тих пір, поки кимось не буде дана команда Commit або Rollback. Цим кимось може бути ваш код на C #, код в ХП або сам SQL Server, якщо на ньому відбудеться помилка.

Якщо ХП сама намагається стартувати транзакцію за допомогою BEGIN TRANSACTION, то вона просто потрапляє в область видимості існуючої транзакції. Тому будь-який відкат, який стався всередині ХП, відкотить і зовнішню транзакцію, що стартувала в вашому коді, чого насправді нам і хотілося.
Єдине, про що треба згадати, так це про те, що в разі відкоту транзакції викликається SqlException з таким описом:




“Transaction count after EXECUTE indicates
that a COMMIT or
ROLLBACK TRANSACTION statement is missing. Previous

count = 1, current count = 0.”


Це виключення викликається SQL Server’ом, який бачить, що при вході в ХП була транзакція, однак при виході з ХП її не залишилося, оскільки був відкат. Оскільки така ситуація обробляється як виняткова, то ви її відловити. Ніякої шкоди повторний Rollback не завдасть, тому просто викликайте Rollback в обробнику винятків, і не треба його відловлювати по якомусь умові, що грунтується на інформації про виключення.


Що ще можна зробити, так це прибрати інформацію помилки, що містить кількість транзакцій, особливо якщо ви збираєтеся десь вести лог помилок. Ймовірно така інформація викличе у переглядає здивування, особливо якщо він не розуміє механізм дії вкладених транзакцій. Шляхом ітерації через колекцію Errors можна отримати всі помилки, номер помилки SQL для лічильника транзакцій дорівнює 266, тому таку помилку можна виділити і обробити окремо – наприклад, просто ні в якій лог її не писати.

У коді для скачування знаходиться невеликий проект, з яким можна погратися і в якому продемонстровано основні моменти роботи з БД, вищевикладені в даній статті.

Код до цієї статті можна завантажити тут – скачати.

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


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

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

Ваш отзыв

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

*

*