Транзакція в SQL Server – це a послідовна група операторів або запитів для виконання одного або кількох завдань у базі даних. Кожна транзакція може мати одну операцію читання, запису, оновлення або видалення або комбінацію всіх цих операцій. У SQL Server кожна транзакція має відбуватися дві речі:
- Або всі модифікації є успішними, коли транзакцію зафіксовано.
- Або всі зміни скасовуються, коли транзакція відкочується.
Транзакція не може бути успішною, доки не будуть виконані всі операції в наборі. Це означає, що якщо будь-який аргумент не виконується, операція транзакції не завершиться. Кожна транзакція починається з першого виконуваного оператора SQL і завершується, коли вона знаходить фіксацію або відкат, явно чи неявно. Він використовує КОМІТ або ВІДКОТ оператори явно, а також неявно, коли використовується оператор DDL.
У зображенні нижче пояснюється процес транзакції:
У наступному прикладі пояснюється концепція транзакції:
У цьому прикладі використовуватиметься система банківської бази даних для пояснення поняття транзакції. Припустимо, клієнт банку хоче зняти гроші зі свого рахунку, використовуючи режим банкомату. Банкомат може виконати цю операцію в три кроки:
- The Перший крок перевірити наявність необхідної суми на рахунку.
- The другий крок знімає суму з рахунку, якщо вона доступна, а потім оновлює баланс рахунку.
- The третій крок це записати операцію зняття грошей у файл журналу. Цей крок повідомляє про те, що транзакція успішна або невдала. У разі успіху записати модифікацію даних у базу даних. В іншому випадку транзакція буде відкочена до попереднього стану.
Основний принцип транзакцій полягає в тому, що якщо один із операторів повертає помилку, весь набір змін відкочується, щоб забезпечити цілісність даних. І якщо транзакції стануть успішними, усі зміни в базі даних залишаться постійними. Отже, якщо під час зняття грошей у банкоматі виникне відключення електроенергії чи інші проблеми, транзакції гарантують стабільність балансу. Заява про транзакцію найкраще виконує ці операції, оскільки чотири ключові властивості транзакції роблять усі операції більш точними та послідовними. Чотири властивості транзакції називаються ACID.
Властивості транзакції
Властивості транзакції називають властивістю ACID (атомність, узгодженість, ізоляція, довговічність), які детально розглядаються нижче:
Атомарність: Ця властивість гарантує, що всі оператори або операції, включені в транзакцію, повинні бути виконані успішно. В іншому випадку вся транзакція буде перервана, а всі операції відкочуються до попереднього стану, якщо будь-яка операція не вдається.
Консистенція: Ця властивість гарантує, що база даних змінює стан лише тоді, коли транзакцію буде успішно здійснено. Він також відповідає за захист даних від збоїв.
Ізоляція: Ця властивість гарантує, що всі транзакції ізольовані від інших транзакцій, тобто кожна операція в транзакції виконується незалежно. Це також гарантує, що заяви є прозорими один для одного.
Довговічність: Ця властивість гарантує, що результат зафіксованих транзакцій постійно зберігається в базі даних, навіть якщо система виходить з ладу або виходить з ладу.
Режими транзакцій у SQL Server
SQL Server може використовувати три різних режими транзакцій:
Автоматичний режим транзакції: Це стандартний режим транзакцій SQL Server. Він оцінюватиме кожну інструкцію SQL як транзакцію, а результати фіксуватимуться або відкочуватимуться відповідно. Таким чином, успішні оператори негайно фіксуються, тоді як невдалі оператори негайно відкочуються.
Режим неявної транзакції. Цей режим дозволяє SQL Server починати неявну транзакцію для кожного оператора DML, але він явно вимагає використання команд commit або rollback в кінці операторів.
Явний режим транзакції: Цей режим визначається користувачем, що дозволяє нам точно визначити початкову та кінцеву точки транзакції. Він автоматично припинить роботу в разі фатальної помилки.
Контроль транзакцій
Для керування транзакціями використовуються наступні команди:
ПРИМІТКА. Ми можемо використовувати лише оператори DML (INSERT, UPDATE і DELETE) для команд Transaction Control Language. Ми не можемо використовувати їх під час створення або видалення таблиць, оскільки ці операції автоматично закріплюються в базі даних.
Стан транзакції
Він вказує, як відбуваються транзакції протягом їхнього життя. Він описує поточний стан транзакції, а також те, як транзакція буде оброблятися в майбутньому. Ці стани визначають правила, які визначають, фіксується чи переривається транзакція.
Опишемо кожен стан транзакції в SQL Server:
Активний стан: Транзакція знаходиться в активному стані, поки виконуються інструкції транзакції. Він змінюється на 'частково готовий стан' якщо всі операції читання та запису виконано без помилок. Якщо будь-яка інструкція не виконується, вона змінюється на «стан не виконано».
Частково виконано: Коли всі операції читання та запису завершені, зміни вносяться до основної пам’яті або локального буфера. Держава пішла б 'здійснений стан' якщо зміни в базі даних є постійними. В іншому випадку він переходить у «несправний стан».
Невдалий стан: Транзакція переходить у невдалий стан, коли будь-яка інструкція транзакції не виконується або постійна модифікація бази даних не виконується.
Перерваний стан: Транзакція рухається від a 'нездатний стан' до ан 'перерваний стан' коли трапляється будь-який збій. Зміни видаляються або відкочуються, оскільки ці зміни вносяться лише до локального буфера або основної пам’яті в попередніх станах.
Здійснений стан: Транзакція завершена і переходить у цей стан, коли зміни вносяться назавжди в базу даних і припиняються в «припинений стан».
Припинений стан: Якщо відкату немає і транзакція знаходиться в 'держава зобов'язань', система є узгодженою та готовою до нової транзакції, тоді як стара завершується.
Реалізація транзакції в SQL Server
Розглянемо кілька прикладів, щоб зрозуміти, як реалізувати транзакцію в SQL Server. Тут ми будемо використовувати 'Товар' таблиця для демонстрації всіх станів транзакцій.
Наступні сценарії SQL створюють таблицю Product у вибраній базі даних:
CREATE TABLE Product ( Product_id INT PRIMARY KEY, Product_name VARCHAR(40), Price INT, Quantity INT )
Далі виконайте наведені нижче сценарії, щоб вставити дані в цю таблицю:
INSERT INTO Product VALUES(111, 'Mobile', 10000, 10), (112, 'Laptop', 20000, 15), (113, 'Mouse', 300, 20), (114, 'Hard Disk', 4000, 25), (115, 'Speaker', 3000, 20);
Виконайте оператор SELECT, щоб перевірити дані:
f фільми
Приклад транзакції COMMIT
Доцільно розділити оператори SQL, які використовуються в транзакції, на кілька логічних частин. А потім ми можемо вирішити, закріпити чи відкотити дані. Наступні кроки ілюструють створення транзакції:
- Почніть транзакцію за допомогою ПОЧАТИ ТРАНЗАКЦІЮ команда.
- Напишіть оператори SQL і розділіть їх відповідно до наших потреб
- Використовувати КОМІТ оператор для завершення транзакції та остаточного збереження змін.
Нижче наведено команди, які пояснюють операції COMMIT у SQL Server:
-- Start a new transaction BEGIN TRANSACTION -- SQL Statements INSERT INTO Product VALUES(116, 'Headphone', 2000, 30) UPDATE Product SET Price = 450 WHERE Product_id = 113 -- Commit changes COMMIT TRANSACTION
Якщо помилки не знайдено, ми побачимо наступний результат, де кожна інструкція SQL транзакції виконується незалежно:
Інструкції INSERT і UPDATE неможливо відкотити після завершення транзакції. Коли ми перевіряємо таблицю після операції фіксації, ми побачимо такі дані:
Приклад транзакції ROLLBACK
Ми будемо використовувати команду ROLLBACK, щоб скасувати будь-які транзакції, які ще не були збережені в базі даних, і повернутися до точки початку транзакції. У наступному прикладі пояснюється операція ROLLBACK у SQL Server:
-- Start a new transaction BEGIN TRANSACTION -- SQL Statements UPDATE Product SET Price = 5000 WHERE Product_id = 114 DELETE FROM Product WHERE Product_id = 116
Як тільки ми виконаємо зазначену вище транзакцію, ми побачимо, що вона буде виконана успішно. Однак це не вплине на будь-які зміни в базі даних, оскільки доки ми не виконаємо оператор COMMIT або ROLLBACK, зміни не можуть стати постійними. Таким чином, у нас є можливість використовувати команду транзакції ROLLBACK для відкоту всіх операцій бази даних. Ось повний текст операції:
-- Start a new transaction BEGIN TRANSACTION -- SQL Statements UPDATE Product SET Price = 5000 WHERE Product_id = 114 DELETE FROM Product WHERE Product_id = 116 --Undo Changes ROLLBACK TRANSACTION
Використання глобальної змінної @@Error у транзакціях:
Ця змінна є використовується для перевірки наявності помилки чи ні. Наведений нижче приклад пояснює його концепцію. Тут ми спочатку розпочнемо транзакцію за допомогою команди BEGIN, а потім напишемо два оператори вставки. Далі ми будемо використовувати глобальну системну змінну @@ПОМИЛКА в оператор IF щоб перевірити помилку. Якщо значення більше 0, це означає, що є якась помилка. Тепер транзакція відкочується; інакше транзакція зафіксована.
BEGIN TRANSACTION INSERT INTO Product VALUES(115,'Speaker', 3000, 25) -- Check for error IF(@@ERROR > 0) BEGIN ROLLBACK TRANSACTION END ELSE BEGIN COMMIT TRANSACTION END
Коли зазначену вище транзакцію буде виконано, ми помітимо, що її було відкотлено. Це пов’язано з нашою спробою вставити повторюване значення в стовпець первинного ключа.
Автоматичний відкат транзакції
Більшість транзакцій містять більше одного запиту. Якщо під час виконання транзакції будь-який із операторів SQL викликає помилку, у базі даних не відбуваються жодні зміни, а інші оператори не виконуються. Ця концепція відома як транзакція автоматичного відкоту в SQL Server. Давайте використаємо простий приклад, щоб продемонструвати цей процес.
BEGIN TRANSACTION INSERT INTO Product VALUES(118, 'Desktop', 25000, 15) UPDATE Product SET Quantity = 'ten' WHERE Product_id = 113 SELECT * FROM Product COMMIT TRANSACTION
Ця транзакція дає такі результати:
У цьому виводі ми бачимо, що оператор вставки виконано успішно. Однак під час виконання оператора оновлення виявлено помилку через проблему перетворення типу даних. У цьому випадку SQL Server не допускає жодних змін у базі даних, що означає, що операція вставки не додає жодного значення, а оператор select не виконується.
Точка збереження в транзакціях
Точка збереження вставляє спеціальну позначку в транзакцію, яка дозволяє нам відкотити всі зміни, виконані після точки збереження. Він також використовується для відкоту будь-якої певної частини транзакції, а не всієї транзакції. Ми можемо визначити це за допомогою ЗБЕРЕГТИ ТРАНЗАКЦІЮ sp_name заява. У наступному прикладі пояснюється використання точки збереження в транзакціях, які фіксують інструкцію вставки та відкочують інструкцію видалення.
BEGIN TRANSACTION INSERT INTO Product VALUES(117, 'USB Drive', 1500, 10) SAVE TRANSACTION InsertStatement DELETE FROM Product WHERE Product_id = 116 SELECT * FROM Product ROLLBACK TRANSACTION InsertStatement COMMIT SELECT * FROM Product;
Перегляньте наведений нижче результат, де видно, що ідентифікатор продукту 116 видалено, а 117 вставлено в перший вихід. Однак у другому виведенні операція видалення відкочується через точку збереження.
Як звільнити точку збереження в транзакції?
Випуск точки збереження використовується для видалення названої точки збереження з поточної транзакції без повернення результатів запитів, виконаних після точки збереження. MySQL має цю команду, але SQL Server не надає жодної команди для звільнення точки збереження. Натомість вони автоматично звільняються в кінці транзакції фіксації або відкоту, тому нам не потрібно турбуватися про них на проміжному етапі.
Неявні транзакції в SQL Server
Ми можемо визначити неявну транзакцію, увімкнувши опцію IMPLICIT_TRANSACTIONS. Наступний приклад легко пояснить цю концепцію:
мультиплексор два до одного
SET IMPLICIT_TRANSACTIONS ON UPDATE Product SET Quantity = 10 WHERE Product_id = 113 SELECT IIF(@@OPTIONS & 2 = 2, 'Implicit Transaction Mode ON', 'Implicit Transaction Mode OFF' ) AS 'Transaction Mode' SELECT @@TRANCOUNT AS OpenTrans COMMIT TRANSACTION SELECT @@TRANCOUNT AS OpenTrans
У цій транзакції ми використали два варіанти @@OPTION і @@TRANCOUNT. @@OPTOPN надає інформацію про поточні параметри SET, а @@TRANCOUNT надає оператор BEGIN TRANSACTION у поточному сеансі.
Тепер виконання транзакції поверне такі результати:
Явна транзакція в SQL Server
Явну транзакцію необхідно визначити за допомогою команди BEGIN TRANSACTION, оскільки вона визначає початкову точку явної транзакції. Ми можемо визначити явну транзакцію в SQL Server так:
BEGIN TRANSACTION [ @trans_name_variable [WITH MARK ['description']]]
У синтаксисі параметр trans_name вказує на унікальне ім’я транзакції. The @trans_name_var вказує на визначену користувачем змінну, яка зберігає назву транзакції. Нарешті, МАРК Параметр дозволяє нам позначити певну транзакцію у файлі журналу.
Явна транзакція через команду BEGIN TRANSACTION отримала блокування залежно від рівня ізоляції пов’язаних із транзакцією ресурсів. Це допомагає зменшити проблеми з блокуванням. Дивіться приклад нижче:
BEGIN TRANSACTION UPDATE Product SET Quantity = 15 WHERE Product_id = 114 SELECT @@TRANCOUNT AS OpenTrans COMMIT TRANSACTION SELECT @@TRANCOUNT AS OpenTrans
Ось результат:
Позначена транзакція в SQL Server
Позначена транзакція використовується для додавання опису до певної транзакції у файлах журналу. Ми можемо використовувати його як точку відновлення замість дати й часу під час відновлення бази даних до попереднього стану. Ми повинні знати, що позначка додається до файлів журналу лише тоді, коли позначена транзакція змінює базу даних. Ми можемо зрозуміти його концепцію на наступному прикладі.
Припустимо, ми випадково змінили базу даних і не знаємо точного моменту зміни даних; у цьому випадку відновлення даних може зайняти багато часу. Однак, якщо ми використовуємо позначені транзакції, це може бути корисним інструментом для визначення точного часу зміни даних.
Наступний синтаксис ілюструє позначену транзакцію в SQL Server:
BEGIN TRANSACTION trans_name WITH MARK 'description';
Тут ми повинні визначити назву транзакції, а потім додати опцію WITH MARK. У наведеному нижче прикладі ми збираємося видалити записи та додати позначку у файл журналу:
BEGIN TRANSACTION DeleteProduct WITH MARK 'Deleted Product with id = 117' DELETE Product WHERE Product_id = 117 COMMIT TRANSACTION DeleteProduct
The logmarkhistory таблиця включена в база даних msdb і зберігає інформацію щодо кожної позначеної транзакції, яка була зафіксована. Виконайте наведений нижче оператор, щоб отримати деталі з таблиці logmarkhistory:
SELECT * FROM msdb.dbo.logmarkhistory
Іменована транзакція в SQL Server
Ми також можемо надати назву для нашої транзакції в SQL Server. Завжди рекомендується використовувати названу транзакцію під час роботи з багатьма транзакціями в одному запиті. У наведеному нижче прикладі пояснюється, як перейменувати транзакцію:
BEGIN TRANSACTION AddProduct INSERT INTO Product VALUES(118, 'Desktop', 25000, 15) UPDATE Product SET Product_name = 'Pen Drive' WHERE Product_id = 117 COMMIT TRANSACTION AddProduct
Ось результат:
Висновок
Ця стаття надасть повний огляд транзакцій у операторах SQL Server. Транзакції корисні в системах реляційних баз даних, оскільки вони забезпечують цілісність бази даних.