Транзакції в SQL - Кроки виконання транзакцій з прикладами
Транзакція в SQL зазвичай відома як здійснення платежів (надсилання, отримання, покупка і т. д.), але коли справа доходить до технічних полів, це спосіб оновлення логічної одиниці інформації в базі даних.
Транзакція - це запровадження однієї чи кількох змін у базу даних. Ми можемо групувати декілька SQL-запитів та запускати їх одночасно у транзакції. Всі SQL-запити або будуть виконані за один раз, або будуть відкатані. Це матиме лише два результати: успіх чи невдача.
Якось зафіксована транзакція не може бути відкатом. Відкат може бути виконаний тільки якщо транзакція не зафіксована. MYSQL автоматично фіксує зміни у базі даних, якщо всі запити виконані успішно. Щоб явно зафіксувати зміни в базі даних, спочатку необхідно вимкнути автофіксацію за допомогою команди -
Синтаксис: SET autocommit = 0;
Властивості угоди
Нижче наведено важливі властивості транзакцій, кожна транзакція повинна слідувати цим властивостям
1. Атомність
Транзакція має бути атомарною, тобто маніпулювання даними повинно виконуватись для певної логічної одиниці. Ця властивість гарантує, що зміни даних відбулися повністю, інакше зробіть відкат транзакції.
2. Узгодженість
Після завершення транзакції всі доступні записи будуть узгоджені протягом транзакції. Ця властивість гарантує, що властивість бази даних перейде в стан після успішної фіксації чи ні.
3. Ізоляція
Ізоляція відноситься до змін даних у певній логічній одиниці, яка не повинна впливати на іншу одиницю. Це дозволяє транзакції виконуватись незалежно.
4. Довговічність
Зміни, зроблені під час транзакцій, мають бути постійними у системі. У разі системної помилки, ця властивість також гарантує, що зміни даних відбуваються чи ні.
Вказана вище властивість транзакції також відома як властивість ACID.
Кроки транзакції
1. Почніть
Транзакція може відбуватися у кількох виконаннях SQL, але всі SQL мають виконуватися одночасно. Якщо якась із транзакцій завершиться невдачею, вся транзакція буде скасована. Оператор для запуску транзакції – «START TRANSACTION». Починається абревіатура для START TRANSACTION.
Синтаксис: ПОЧАТИ УГОДУ;
2. Фіксація
Комміти постійно відображають зміни у базі даних. Оператор для запуску транзакції – «COMMIT».
Синтаксис: COMMIT;
3. Відкат
Відкат використовується для скасування змін, тобто запис не буде змінено, він буде в попередньому стані. Оператор для запуску транзакції – «ROLLBACK».
Синтаксис: ROLLBACK;
4. Точка збереження
SAVEPOINT також є оператором транзакції. Цей оператор використовується для створення точки збереження в системі, щоб операція ROLLBACK могла досягти стану точки збереження.
5. Відпустіть точку збереження
RELEASE SAVEPOINT - це оператор для звільнення точки збереження та пам'яті, що використовується системою під час створення точки збереження.
Синтаксис: RELEASE SAVEPOINT SP
Примітки – SP – це ім'я точки збереження, коли ця точка збереження була створена до початку транзакції.
6. Встановити транзакцію
Команда SET TRANSACTION використовується для вказівки атрибута транзакції, наприклад, дана транзакція призначена лише для читання або для читання та запису.
Синтаксис : SET TRANSACTION (READ-WRITE | READ ONLY);
Транзакція використовується для виконання складних змін у базі даних.Здебільшого він використовується у банківських інформаційних змінах у реляційній базі даних.
Транзакція підтримується MySQL двигуном InnoDB. За замовчуванням автоматична фіксація залишається включеною, тому щоразу, коли виконується будь-який SQL після автоматичної фіксації виконання.
Транзакції з використанням SQL
Приклад №1
Банківська операція: рахунок було списано з 50000 осіб від імені Ощадного рахунку та відправив цю суму на кредитний рахунок A.
Початкова транзакція: ця стартова транзакція перетворює всі запити SQL одну одиницю транзакції.
UPDATE `account` SET `balance` = `balance` - 50000 WHERE user_id = 7387438;
Цей запит SQL віднімає суму від існуючого балансу рахунку.
UPDATE `loan_account` SET `paid_amount` = `paid_amount` + 50000 WHERE user_id = 7387438;
Цей SQL запит додає суму до облікового запису користувача кредиту.
Insert in`transaction_details`(`user_id`, 'amount') values (7387438, '50000');
Цей запит SQL вставляє новий запис до таблиці відомостей про транзакції, ця таблиця містить відомості про всі транзакції користувачів. Якщо всі запити виконані успішно, необхідно виконати команду COMMIT, оскільки зміни мають постійно зберігатися у базі даних.
Фіксація: цей оператор фіксації зберігає зміни, спричинені транзакцією, до бази даних. Якщо якась із транзакцій завершиться невдало під час виконання, то має бути виконана команда ROLLBACK, щоб скасувати всю транзакцію
Відкат: Відкат відбувається після збою будь-якого запиту під час виконання.
Приклад №2
Інвентаризація: у цій таблиці предметів є 6 предметів.
Виконайте наступного оператора START TRANSACTION для запуску транзакції.
Тепер виконайте команду SET AUTOCOMMIT = 0 ; вимкнути авто-фіксацію
Тепер виконайте таку інструкцію, щоб видалити запис із таблиці елементів
Тепер доступно записів у таблиці 4, тобто. записи тимчасово видалені з елементів таблиці
Тепер, виконавши команду ROLLBACK, щоб скасувати зміни, віддалений запис буде доступний в елементах таблиці, як і раніше, до початку транзакції.
Знову ж таки, якщо застосувати ту саму операцію видалення, то операція COMMIT після її зміни буде збережена у базі даних назавжди.
Тепер ми можемо бачити, що після виконання команди ROLLBACK запис був у новому стані. Це означає, що після виконання операції COMMIT зміни не можуть бути скасовані, оскільки вона постійно вносить зміни до бази даних;
Переваги використання транзакцій у SQL
а) Використання транзакції підвищує продуктивність , коли при вставці 1000 записів з використанням транзакцій у цьому випадку час буде меншим, ніж при звичайній вставці. Як і в звичайній транзакції, кожен раз, коли COMMIT буде мати місце після кожного виконання запиту, і це буде збільшувати час виконання кожного разу, коли в транзакції немає необхідності виконувати оператор COMMIT після кожного запиту SQL. COMMIT в кінці постійно відображатиме всі зміни в базі даних. Також при використанні транзакції скасування змін буде набагато простіше, ніж за звичайної транзакції. ROLLBACK скасує всі зміни одразу і збереже систему в колишньому стані.
б) транзакція забезпечує цілісність даних у реляційній базі даних.Більшість бази даних використовує декілька таблиць для зберігання даних, і при виконанні оновлень у цей час можуть бути зміни в декількох таблицях, якщо якийсь із SQL-запитів завершиться невдало, транзакція збереже дані без змін.
Висновок
Використання транзакцій – це найкраща практика оновлення інформації для логічної одиниці у реляційній базі даних. Для реалізації транзакції ядро бази даних має підтримувати транзакцію як двигун InnoDB. Транзакція як одиниця операторів SQL може бути скасована за допомогою одного оператора ROLLBACK. Транзакція забезпечує цілісність даних та підвищує продуктивність бази даних.
Рекомендовані статті
Це посібник з транзакцій у SQL. Тут ми обговорюємо введення, властивості, кроки, приклади транзакцій SQL, а також переваги використання транзакцій SQL.
- Що таке SQL
- Інструменти керування SQL
- Подання SQL
- Типи об'єднань у SQL Server
- 6 кращих типів сполук MySQL з прикладами
Теорія транзакцій з прикладами Microsoft SQL Server
Думаю, багато хто з вас працював з транзакціями і уявляє, як застосувати до бази даних консистентну послідовність операцій. Сьогодні ми дізнаємося, що відбувається з транзакцією, коли ми відправляємо її до СУБД. Ми познайомимося з класичною теорією транзакцій та тим, які існують підходи для формування коректних розкладів. Крім того, намагатимемося пов'язати цю теорію з практикою на прикладі відомої СУБД Microsoft SQL Server. (Сьогодні буде багато інформації, приготуйтеся!)
Розклади, що серіалізуються
Транзакції
Почнемо з визначення того, що таке транзакція:
Транзакція - це сукупність операцій, що виконуються прикладною програмою, які переводять узгоджений стан бази даних у узгоджене, якщо:
- відсутні перешкоди з боку інших програм;
- транзакцію виконано повністю.
У MS SQL Server існує 2 типи транзакцій:
- Неявні - окремі операції INSERT, UPDATE чи DELETE.
- Явні - Набір операцій мови T-SQL, що починається з інструкції BEGIN TRANSACTION і COMMIT або ROLLBACK, що закінчується.
Наприклад розглянемо транзакцію, оновлюючу значення відразу у двох таблицях. Внаслідок її виконання або обидва значення будуть вдало записані, або, у разі відкату транзакції, не буде записано жодного.
BEGIN
TRANSACTION
UPDATE
tbl1
SET
x
=
10
WHERE
y
=
1
IF
@@ERROR
<>
0
ROLLBACK
UPDATE
tbl2
SET
x
=
10
WHERE
y
=
1
IF
@@ERROR
<>
0
ROLLBACK
COMMITАномалії транзакцій
p align="justify"> При паралельному виконанні транзакцій виникають різні проблеми, пов'язані з логікою роботи з операціями. Розглянемо найпоширеніші їх на прикладах з SQL сервера:
1) Втрачене оновлення. При оновленні поля двома транзакціями одна із змін втрачається.
| Транзакція 1 | Транзакція 2 |
|---|---|
| SELECT x FROM tbl WHERE y=1; | SELECT x FROM tbl WHERE y=1; |
| UPDATE tbl SET x=5 WHERE y=1; | |
| UPDATE tbl SET x=3 WHERE y=1; |
2) Брудне читання. Читання даних, отриманих внаслідок дії транзакції, яка після цього відкотиться.
| Транзакція 1 | Транзакція 2 |
|---|---|
| SELECT x FROM tbl WHERE y=1; | |
| UPDATE tbl SET x=x+1 WHERE y=1; | |
| SELECT x FROM tbl WHERE y=1; | |
| ROLLBACK; |
3) Неповторне читання. Виникає, коли протягом однієї транзакції під час повторного читання дані виявляються перезаписаними.
| Транзакція 1 | Транзакція 2 |
|---|---|
| SELECT x FROM tbl WHERE y=1; | SELECT x FROM tbl WHERE y=1; |
| UPDATE tbl SET x=x+1 WHERE y=1; | |
| COMMIT; | |
| SELECT x FROM tbl WHERE y=1; |
4) Фантомне читання. Відмінність від попередньої аномалії в тому, що при повторному читанні одна і та ж вибірка дає різні множини рядків.
| Транзакція 1 | Транзакція 2 |
|---|---|
| SELECT SUM(x) FROM tbl; | |
| INSERT INTO tbl (x, y) VALUES (5, 3); | |
| SELECT SUM(x) FROM tbl; |
Обчислювальна модель
Для того, щоб уникати проблем, описаних вище, необхідна модель теорії транзакцій Вона включає:
- Елементарні операції, визначені об'єктами даних.
- Транзакції: послідовності або частково впорядковані множини елементарних операцій.
- Розклади (чи історії), що описують конкурентне виконання транзакцій.
- Критерії коректності розкладів (історій).
- Алгоритми керування транзакціями, що забезпечують отримання коректних розкладів.
Сьогодні ми розглядатимемо транзакції в контексті сторінкової моделі. У цій моделі база даних представляється як набір незалежних сторінок `(x, y, z)`, над якими можливі дві атомарні операції: read і write з повним чи частковим порядком усередині транзакції.
Критерії коректності
Спочатку визначимо поняття історії. Історія - упорядкована сукупність операцій кількох транзакцій, включаючи операції завершення транзакції (commit, abort). Розкладом називається префікс історії. Приклад (індекси відповідають транзакціям): r_1(x) r_2(x) w_1(x) w_2(x) c_1 c_2 '
Одним із ключових понять теорії транзакцій є термін серійний розклад. Історія називається серійною, якщо будь-яких двох транзакцій або всі операції першої передують усім операціям другий, або навпаки.
Такі розклади не можуть завдати жодних проблем при виконанні транзакцій, але вкрай неефективні.Мета: визначити еквівалентність на безлічі транзакцій та вважати коректними ті з них, які еквівалентні серійному. Серед важливих вимог до критеріїв коректності виділимо: ефективну проверяемость і досить велику потужність безлічі коректних розкладів.
Семантика Ербрана
За допомогою цього поняття визначаються наступні серіалізуемості. Семантика Ербрана ґрунтується на двох припущеннях:
- Кожна операція читання повертає останнє значення сторінки, записане попередньої операції запису.
- Операція запису записує значення, потенційно залежить від усіх значень, прочитаних попередніми операціями читання тієї транзакції.
Випишемо семантику Ербрана для сторінки x. Для цього, починаючи з останньої операції запису, розкриваємо операції відповідно до правил вище. Нульова транзакція виконується спочатку та ініціалізує значення бази даних.
Серіалізується за кінцевим станом
Розклади еквівалентні за кінцевим станом, якщо:
- рівні безлічі операцій
- семантики Ербрана рівні для всіх елементів даних
Розклад називається серіалізується за кінцевим розкладом (FSR), якщо воно еквівалентне серійному за кінцевим станом. Приклад нееквівалентних розкладів:
| Розклад | Семантика Ербрана |
|---|---|
| r1(x) r2(y) w1(y) w2(y) c1 c2 | f2y(f0y()) |
| r1(x) w1(y) r2(y) w2(y) c1 c2 | f2y(f1y(f0y())) |
Цей тип серіалізується вирішує проблему втрати оновлення, але з іншими аномаліями впоратися він не в змозі.
Серіалізується за видимим станом
На додаток до умов попередньої еквівалентності для еквівалентності за видимим станом потрібно, щоб усі операції читання у розкладах слідували після однакових операцій запису.Приклад розкладу, що серіалізується по видимому (VSR), але не за кінцевим станом: `w_1(x) r_2(x) r_2(y) w_1(y) c_1 c_2 `
VSR, як і раніше, не може впоратися з аномаліями брудного читання, оскільки не враховує обірвані транзакції. Крім того, перевірка розкладу на цю серіалізацію є NP-повною.
Серіалізується по конфліктам
Конфліктами називаються пари операцій різних транзакцій, які обробляють один і той самий елемент даних і хоча б одна з операцій - операція запису. Розклади еквівалентні конфліктам, якщо їм збігаються як безлічі операцій, а й безлічі пар, конфліктуючих операцій, де одне їх передує інший.
Інший спосіб визначити отриманий клас розкладів – через серіалізується за комутативністю. Два розклади еквівалентні, якщо можна перетворити одне на інше застосуванням правил:
- Перестановка сусідніх операцій читання
- Перестановка сусідніх операцій над різними елементами
Диспетчери та протоколи
Функціонування диспетчера
У системах управління базами даних важливу роль відіграє планувальник (Диспетчер). У його функції входить перетворення довільного розкладу, отриманого від менеджера транзакцій (МТ), в еквівалентне серійне, передачу менеджеру даних та повернення результатів назад МТ.
У MS SQL Server, як і в багатьох інших СУБД є два види конкурентного доступу (протоколів):
- Песимістичні. Для запобігання порушення серіалізуемості двома транзакціями застосовуються блокування.
- Оптимістичні. Протокол ґрунтується на припущенні малоймовірності одночасної зміни даних двома транзакціями. У разі порушення серіалізуемості транзакція обривається.
Протокол Two Phase Locking
Протокол двофазного блокування ґрунтується на тому правилі, що будь-яка операція установки блокування передує будь-якій операції зняття блокування всередині однієї транзакції. У результаті виникають дві фази обробки блокувань.
У SQL сервері використовується модифікація 2PL, що називається SS2PL.До правила, що використовується в базовому протоколі додається strict - усі отримані замки на запис зберігаються до завершення транзакції та strong - усі замки утримуються до завершення транзакції. При цьому, у СУБД є кілька режимів блокування. Вибір режиму залежить від типу ресурсу, який потрібно заблокувати.
- Розділяється.Блокування займає ресурс тільки для читання.
- Монопольна. Резервує ресурс для виконання операцій INSERT, UPDATE, DELETE.
- Оновлення.Блокування такого типу може бути встановлене на ресурс, тільки якщо на ньому ще не встановлене інше оновлююче або монопольне блокування. , то вона перетворюється на монопольну.
Для таблиць крім роздільної та монопольної можна також використовувати три інших типи блокувань:
- Розділяється з наміром. Захищає запитані або отримані блокування, що розділяються, на деяких ресурсах на нижчому рівні ієрархії.
- Монопольна із наміром. Захищає запитані чи отримані монопольні блокування на деяких ресурсах нижчому рівні ієрархії.
- Розділяється з монопольним наміром. Захищає запитані або отримані сумісні блокування на всіх ресурсах нижчого рівня ієрархії, а також блокування з наміром на деяких ресурсах нижчого рівня.
Проблеми 2PL
Однією з головних проблем блокуючих протоколів є виникнення глухих кутів (взаємоблокування). Під глухими кутами розуміються ситуації, коли дві транзакції блокують один одного. Невеликий приклад:
Одна зі стратегій боротьби з глухими кутами, це їхнє розпізнавання та обрив. Для розпізнавання необхідно збудувати граф очікувань, в якому вершини - транзакції, а дуги - запити транзакції на блокування, що конфліктує з уже встановленим. Наявність контуру у графі означає наявність глухого кута. Для дозволу взаємоблокування потрібно обірвати одну з транзакцій з контурів, що утворюють. Така стратегія застосовується у MS SQL Server.
При цьому може виникнути нова проблема голодування. Цим терміном описується ситуація, коли одна і та ж транзакція стає жертвою дозволу глухого кута при кожному новому запуску. Щоб запобігти цій проблемі, можна використовувати початкові позначки часу надходження транзакції при виборі жертви. У SQL сервері можна присвоїти параметру DEADLOCK_PRIORITY одне з 21 значень (від -10 до 10) для вибору різних рівнів пріоритету взаємоблокування.
Крім того, існує ще один спосіб боротьби з глухими кутами. Для передчасного завершення транзакції можна встановити обмеження за часом. Якщо час очікування блокування перевищує обмеження, транзакція обривається. У сервері SQL використовується наступний синтаксис: SET LOCK_TIMEOUT 4000
Snapshot Isolation
Snapshot Isolation - один із оптимістичних протоколів. Кожна транзакція читає зі снапшота - стан бази даних, на момент старту транзакції. При цьому при виконанні транзакції формується write set - Усі операції запису. Перед комітом транзакції відбувається перевірка на те, чи перетинається. write set з іншими write set'ами, паралельно виконуваних транзакцій. У разі перетину з уже прийнятою транзакцією поточна обривається. Протокол справляється з найбільш важкими з аномалій, але не забезпечує серіалізуемості навіть за кінцевим станом.
Насправді застосовується модифікація цього протоколу з допомогою блокувань. Вони встановлюються лише на запис, а за неможливості встановити блокування транзакція обривається. У Microsoft SQL сервері цьому протоколу відповідає однойменний рівень ізоляції. Про те, що це таке, читайте в наступному розділі.
Рівні ізоляції в MS SQL Server
Рівень ізоляції задає ступінь захищеності даних, що вибираються транзакцією, від можливості зміни іншими транзакціями. Іншими словами, вони дозволяють вказати, які аномалії транзакцій можуть мати місце, а які потрібно уникнути.
Read Uncommitted
Найпростіша форма ізоляції між транзакціями. Цей рівень не використовує жодних блокувань, і, отже, зовсім не ізолює операції читання від інших транзакцій. З описаних на початку посту аномалій Read Uncommitted допускає три: брудне читання, неповторне читання та фантоми.
Read Committed
Існує дві форми рівня ізоляції Read Committed - для песимістичної та оптимістичної моделей виконання.У цьому підрозділі описується песимістичний варіант, який оптимістично відповідає Read Committed Snapshot.
На цьому рівні ізоляції транзакція, що читає рядок, перевіряє наявність монопольного блокування для даного рядка. У разі відсутності блокування транзакція витягує рядок з використанням блокування, що вже розділяється. Таким чином запобігається читання транзакцією даних, які не були підтверджені та які можуть бути пізніше скасовані. Після того, як дані було прочитано, їх можна змінювати іншими транзакціями.
Застосовувані цим рівнем ізоляції блокування, що розділяються, скасовуються відразу ж після обробки даних. За такого підходу до паралельного виконання транзакцій залишаються аномалії неповторного та фантомного читання.
Repeatable Read
На відміну від попереднього рівня ізоляції, Repeatable Read вішає розділені блокування на всі дані, що зчитуються, і утримує їх до тих пір, поки транзакція не буде підтверджена або скасована. Тому в цьому випадку багаторазове виконання запиту всередині транзакції завжди повертатиме той самий результат. Недоліком цього рівня ізоляції є подальше погіршення одночасного конкурентного доступу, оскільки період часу, протягом якого інші транзакції не можуть оновлювати ті ж дані, значно довше, ніж у випадку рівня Read Committed.
Тим не менш, цей рівень ізоляції не перешкоджає іншим інструкціям вставляти нові рядки, які включаються до наступних операцій читання, внаслідок чого можуть з'являтися фантоми.
Serializable
Рівень ізоляції Serializable є найсуворішим, тому що він не допускає виникнення всіх чотирьох аномалій конкурентного доступу, перерахованих на початку посту. Цей рівень встановлює блокування на всю область даних, що зчитуються відповідною транзакцією. Тому цей рівень ізоляції також запобігає вставці нових рядків іншою транзакцією доти, доки перша транзакція не буде підтверджена або скасована.
Read Committed Snapshot
Останні два рівні використовують у оптимістичному контексті. Read Committed Snapshot застосовується на рівні інструкції, що означає, що будь-яка інша транзакція читатиме зафіксовані значення в тому вигляді, в якому вони існують на момент початку цієї інструкції. Для вибору рядків для оновлень цей рівень ізоляції повертає версії рядків у фактичні дані та встановлює на вибраних рядках блокування оновлень. Реальні рядки даних, які потрібно змінити, одержують монопольні блокування.
Snapshot
Рівень ізоляції Snapshot надає ізоляцію на рівні транзакцій, що означає, що будь-яка інша транзакція читатиме підтверджені значення у тому вигляді, в якому вони існували безпосередньо перед початком виконання транзакції цього рівня ізоляції. Крім цього, транзакція рівня ізоляції Snapshot повертатиме вихідне значення даних до завершення свого виконання, навіть якщо протягом цього часу воно буде змінено іншою транзакцією. Тому інша транзакція зможе читати модифіковане значення лише після завершення виконання транзакції рівня ізоляції. Snapshot.
Висновок
Сьогодні ми познайомилися з основами теорії транзакцій і подивилися, які з них знайшли своє застосування в промисловій СУБД.Пост ґрунтувався на матеріалах лекцій Новікова Б. А. та книзі Душана Петковича Microsoft SQL Server 2012.
Written on November 25th, 2017 by Alexey Kalina
Як SQL обробляє транзакцію?
Якщо ви спробуєте перевести 1000 доларів з вашого ощадного рахунку на поточний і раптом виявите, що гроші були списані, але не зараховані на поточний рахунок, ви, швидше за все, засмутитеся. 😿
Для захисту від таких помилок програма, що обробляє ваш запит на переказ грошей, спочатку починає транзакцію, потім запускає SQL-запити, необхідні для переказу грошей з одного рахунку на інший, і, якщо все проходить успішно, завершує транзакцію, виконавши команду COMMIT — фіксації змін.
Однак, якщо виникнуть проблеми, буде виконано команду ROLLBACK , яка вказує серверу скасувати всі дії, вчинені з початку транзакції.
Процес може виглядати так:
MySQL- Початок транзакції START TRANSACTION; - Перевірка наявності достатнього балансу у відправника SELECT @balance := user_balance FROM accounts WHERE user_id = 1; -- Якщо коштів недостатньо, скасування транзакції IF @balance 1000 THEN ROLLBACK; END IF; - Перевірка на існування одержувача SELECT @exists := COUNT(*) FROM accounts WHERE user_id = 2; IF @exists = 0 THEN ROLLBACK; END IF; -- Оновлення балансу рахунків, якщо всі перевірки пройдено UPDATE accounts SET user_balance = user_balance - 1000 WHERE user_id = 1; UPDATE accounts SET user_balance = user_balance + 1000 WHERE user_id = 2; -- Застосування змін COMMIT;За допомогою транзакції програма забезпечує збереження ваших 1000 доларів, гарантуючи, що вони залишаться на початковому рахунку, або будуть переведені на інший рахунок, виключаючи ризик їх втрати.
Кожна явна транзакція MySQL починається з використання оператора START TRANSACTION .
Завершення транзакції можливе:
- за допомогою команди COMMIT , яка дає вказівку серверу позначити зміни як постійні та звільнити всі ресурси (тобто блокування рядків), що використовуються під час транзакції
- за допомогою команди ROLLBACK, яка вимагає від сервера повернути дані до стану до початку транзакції. Після завершення відкату також будь-які ресурси, що використовуються транзакцією, звільняються.
Крім використання команд COMMIT і ROLLBACK , транзакція може завершитися внаслідок зовнішніх чинників. Наприклад, якщо сервер вимикається, у цьому випадку транзакція буде автоматично скасована при перезапуску сервера.
У певних ситуаціях вам може знадобитися виконати відкат у транзакції, не скасовуючи всю виконану роботу. Для цього можна встановити одну або кілька точок збереження в рамках транзакції. Це дозволяє відкотитися до конкретної точки в транзакції, а не до її початку.
Кожній точці збереження в рамках однієї транзакції необхідно надати унікальне ім'я, що дозволить використовувати безліч різних точок збереження. Для створення точки збереження під назвою my_savepoint використовуйте таку команду:
MySQLSAVEPOINT my_savepoint;Для відкату до певної точки збереження просто вводиться команда ROLLBACK , за якою слідують ключові слова TO SAVEPOINT та ім'я точки збереження, наприклад:
MySQLSTART TRANSACTION; -- Створюємо точку збереження перед зміною балансу першого користувача SAVEPOINT before_updating_user_1; UPDATE accounts SET balance = balance + 100 WHERE user_id = 1; -- Перевірте умови для першого користувача -- наприклад, перевіряємо логіку бізнес-правил - Тут ми припускаємо, що умова не виконалася, і нам потрібно скасувати зміну балансу ROLLBACK TO SAVEPOINT before_updating_user_1; -- Оновлюємо баланс для другого користувача UPDATE accounts SET balance = balance + 200 WHERE user_id = 2; - Завершуємо транзакцію COMMIT;В результаті цієї транзакції баланс першого користувача залишиться без змін через відкат до точки збереження, а баланс другого користувача збільшиться на 200. Це показує, як можна керувати змінами в базі даних з високим рівнем контролю за допомогою транзакцій та точок збереження.
При використанні точки збереження пам'ятайте наступні моменти:
- Незважаючи на назву, під час створення точки збереження нічого не зберігається. Щоб змінити транзакцію стали постійними, необхідно виконати команду COMMIT .
- При виконанні відкату транзакції без вказівки конкретної точки збереження всі раніше встановлені точки збереження будуть проігноровані, і буде проведений відкат всієї транзакції.
