Для установки режиму автоматичного визначення транзакцій використовується команда:
SET IMPLICIT_TRANSACTIONS
При роботі в режимі неявного (мається на увазі) початку транзакцій SQL Server автоматично починає нову транзакцію, як тільки завершена попередня. Установка режиму визначення транзакцій, що мається на увазі, виконується за допомогою іншої команди:
SET IMPLICIT_TRANSACTIONS ON
Явні транзакції вимагають, щоб користувач вказав початок і кінець транзакції, використовуючи наступні команди:
початок транзакції: в журналі транзакцій фіксуються первинні значення змінних даних і момент початку транзакції ;
BEGIN TRAN[SACTION]
[ім’я_транзакції | @ім’я_змінної_транзакції
[WITH MARK [‘опис_транзакції’]]]
кінець транзакції: якщо в тілі транзакції не було помилок, то ця команда наказує серверу зафіксувати всі зміни, зроблені в транзакції, після чого в журналі транзакцій позначається, що зміни зафіксовані і транзакція завершена;
COMMIT [TRAN[SACTION] [ім’я_транзакції | @ім’я_змінної_транзакції]]
створення усередині транзакції точки збереження: СУБД зберігає стан БД в поточній крапці і привласнює збереженому стану ім'я точки збереження;
SAVE TRAN[SACTION]
{ім’я_точки_збереження | @ім’я_змінної_точки_збереження}
переривання транзакції ; коли сервер зустрічає цю команду, відбувається відкіт транзакції, відновлюється первинний стан системи і в журналі транзакцій наголошується, що транзакція була відмінена. Приведена нижче команда відміняє всі зміни, зроблені в БД після оператора BEGIN TRANSACTION або відміняє зміни, зроблені в БД після точки збереження, повертаючи транзакцію до місця, де був виконаний оператор SAVE TRANSACTION.
ROLLBACK [TRAN[SACTION]
[ім’я_транзакції | @ім’я_змінної_транзакції | ім’я_точки_збереження
|@ім’я_змінної_точки_збереження]]
Функція @@TRANCOUNT повертає кількість активних транзакцій.
Функція @@NESTLEVEL повертає рівень вкладеності транзакцій.
BEGIN TRAN
SAVE TRANSACTION point1
Приклад 16.1. Використовування точок збереження
В точці point1 зберігається первинний стан таблиці Товар
DELETE FROM Товар WHERE КодТовара=2
SAVE TRANSACTION point2
В точці point2 зберігається стан таблиці Товар без товарів з кодом 2.
DELETE FROM Товар WHERE КодТовара=3
SAVE TRANSACTION point3
В точці point3 зберігається стан таблиці Товар без товарів з кодом 2 і з кодом 3.
DELETE FROM Товар WHERE КодТовара<>1
ROLLBACK TRANSACTION point3
Відбувається повернення в стан таблиці без товарів з кодами 2 і 3, відміняється останнє видалення.
SELECT * FROM Товар
Оператор SELECT покаже таблицю Товар без товарів з кодами 2 і 3.
ROLLBACK TRANSACTION point1
Відбувається повернення в первинний стан таблиці.
SELECT * FROM Товар
COMMIT
Первинний стан зберігається.
Вкладеними називаються транзакції, виконання яких ініціюється з тіла вже активній транзакції .
Для створення вкладеної транзакції користувачу не потрібні які-небудь додаткові команди. Він просто починає нову транзакцію, не закривши попередню. Завершення транзакції верхнього рівня відкладається до завершення вкладених транзакцій. Якщо транзакція самого нижнього ( вкладеного ) рівня завершена невдало і відмінена, то всі транзакції верхнього рівня, включаючи транзакцію першого рівня, будуть відмінені. Крім того, якщо декілька транзакцій нижнього рівня були завершено успішно (але не зафіксовані), проте на середньому рівні (не сама верхня транзакція ) невдало завершилася інша транзакція, то відповідно до вимог ACID відбудеться відкіт всіх транзакцій всіх рівнів, включаючи успішно завершені. Тільки коли всі транзакції на всіх рівнях завершені успішно, відбувається фіксація всіх зроблених змін в результаті успішного завершення транзакції верхнього рівня.
Кожна команда COMMIT TRANSACTION працює тільки з останньою початою транзакцією. При завершенні вкладеної транзакції команда COMMIT застосовується до "найглибшої" вкладеної транзакції. Навіть якщо в команді COMMIT TRANSACTION вказано ім'я транзакції більш високого рівня, буде завершена транзакція, почата останньої.
Якщо команда ROLLBACK TRANSACTION використовується на будь-якому рівні вкладеності без вказівки імені транзакції, то відкатуються всі вкладені транзакції, включаючи транзакцію найвищого (верхнього) рівня. В команді ROLLBACK TRANSACTION дозволяється указувати тільки ім'я самої верхньої транзакції. Імена будь-яких вкладених транзакцій ігноруються, і спроба їх вказівки приведе до помилки. Таким чином, при відкоті транзакції будь-якого рівня вкладеності завжди відбувається відкіт всіх транзакцій. Якщо ж вимагається відкотити лише частину транзакцій, можна використовувати команду SAVE TRANSACTION, за допомогою якої створюється точка збереження.
BEGIN TRAN
INSERT Товар (Назва, залишок)
VALUES ('v',40)
BEGIN TRAN
INSERT Товар (Назва, залишок)
VALUES ('n',50)
BEGIN TRAN
INSERT Товар (Назва, залишок)
VALUES ('m',60)
ROLLBACK TRAN
Приклад 16.2. Вкладені транзакції.
Тут відбувається повернення на початковий стан таблиці, оскільки виконання команди ROLLBACK TRAN без вказівки імені транзакції відкатує всі транзакції.
Блокування в середовищі MS SQL Server
Користувачу частіше за все не потрібно робити ніяких дій по управлінню блокуваннями. Всю роботу по установці, зняттю і дозволу конфліктів виконує спеціальний компонент серверу, званий менеджером блокувань. MS SQL Server підтримує різні рівні блокування об'єктів (або деталізацію блокувань), починаючи з окремим рядком таблиці і закінчуючи базою даних в цілому. Менеджер блокувань автоматично оцінює, яку кількість даних необхідно блокувати, і встановлює відповідний тип блокування. Це дозволяє підтримувати рівновагу між продуктивністю роботи системи блокування і можливістю користувачів діставати доступ до даних. Блокування на рівні рядка дозволяє найбільш точно управляти таким доступом, оскільки блокуються тільки дійсно змінні рядки. Безліч користувачів можуть одночасно працювати з даними з мінімальними затримками. Платнею за це є збільшення числа операцій установки і зняття блокувань, а також велика кількість службової інформації, яка доводиться берегти для відстежування встановлених блокувань. При блокуванні на рівні таблиці продуктивність системи блокування різко збільшується, оскільки необхідно встановити лише одне блокування і зняти її тільки після завершення транзакції. Користувач при цьому має максимальну швидкість доступу до даних. В той же час вони не доступні нікому іншому, тому що вся таблиця заблокована. Доводиться чекати, поки поточний користувач завершить роботу.
Дії, виконувані користувачами при роботі з даними, зводяться до операцій двох типів: їх читанню і зміні. В операції по зміні включаються дії по додаванню, видаленню і власне зміні даних. Залежно від виконуваних дій сервер накладає певний тип блокування з наступного переліку:
Колективні блокування. Вони накладаються при виконанні операцій читання даних (наприклад, SELECT ). Якщо сервер встановив на ресурс колективне блокування, то користувач може бути упевнений, що вже ніхто не зможе змінити ці дані.
Блокування оновлення. Якщо на ресурс встановлено колективне блокування і для цього ресурсу встановлюється блокування оновлення, то ніяка транзакція не зможе накласти колективне блокування або блокування оновлення .
Монопольне блокування. Цей тип блокувань використовується, якщо транзакція змінює дані. Коли сервер встановлює монопольне блокування на ресурс, то ніяка інша транзакція не може прочитати або змінити заблоковані дані. Монопольне блокування не сумісне ні з якими іншими блокуваннями, і жодне блокування, включаючи монопольну, не може бути накладена на ресурс.
Блокування масивного оновлення. Накладається сервером при виконанні операцій масивного копіювання в таблицю і забороняє звернення до таблиці будь-яким іншим процесам. В той же час декілька процесів, що виконують масивне копіювання, можуть одночасно вставляти рядки в таблицю.
Крім перерахованих основних типів блокувань SQL Server підтримує ряд спеціальних блокувань, призначених для підвищення продуктивності і функціональності обробки даних. Вони називаються блокуваннями намірів і використовуються сервером в тому випадку, якщо транзакція має намір дістати доступ до даних вниз за ієрархією і для інших транзакцій необхідно встановити заборону на накладення блокувань, які конфліктуватимуть з блокуванням, першою транзакцією, що накладається .
Раніше розглянуті блокування відносяться до даних. Крім перерахованих в середовищі SQL Server існує два інші типи блокувань: блокування діапазону ключів і блокування схеми (метаданих, що описують структуру об'єкту).
Блокування діапазону ключів вирішує проблему виникнення фантомів і забезпечує вимоги ізольованості транзакції. Блокування цього типу встановлюються на діапазон рядків, відповідних певній логічній умові, за допомогою якої здійснюється вибірка даних з таблиці.
Блокування схеми використовується при виконанні команд модифікації структури таблиць для забезпечення цілісності даних.
"Мертві", або тупикові, блокування характерні для розрахованих на багато користувачів систем. "мертве" блокування виникає, коли дві транзакції блокують два блоки даних і для завершення будь-якої з них потрібен доступ до даних, заблокованих раніше іншою транзакцією. Для завершення кожної транзакції необхідно дочекатися, поки блокована іншою транзакцією частина даних буде розблокована. Але це неможливо, оскільки друга транзакція чекає того, що розблокував ресурсів, що використовуються першою.
Без вживання спеціальних механізмів виявлення і зняття "мертвих" блокувань нормальна робота транзакцій буде порушена. Якщо в системі встановлений нескінченний період очікування завершення транзакції (а це задано за умовчанням), то при виникненні "мертвого" блокування для двох транзакцій цілком можливо, що, чекаючи звільнення заблокованих ресурсів, в безвихідь попадуть і нові транзакції. Щоб уникнути подібних проблем, в середовищі MS SQL Server реалізований спеціальний механізм дозволу конфліктів тупикового блокування.
Для цих цілей сервер знімає одне з блокувань, що викликали конфлікт, і відкатує транзакцію, що ініціалізувала її. При виборі блокування, якому необхідно пожертвувати, сервер виходить з міркувань мінімальної вартості.
Повністю уникнути виникнення "мертвих" блокувань не можна. Хоча сервер і має ефективні механізми зняття таких блокувань, все ж таки при написанні додатків слід враховувати вірогідність їх виникнення і робити всі можливі дії для попередження цього. "мертві" блокування можуть істотно понизити продуктивність, оскільки системі потрібне достатньо багато часу для їх виявлення, відкоту транзакції і повторного її виконання.
Для мінімізації можливості утворення "мертвих" блокувань при розробці коду транзакції слід дотримуватися наступних правил:
виконувати дії по обробці даних в постійному порядку, щоб не створювати умови для захоплення одних і тих же даних;
уникати взаємодії з користувачем в тілі транзакції ;
мінімізувати тривалість транзакції і виконувати її по можливості в одному пакеті;
застосовувати якомога більш низький рівень ізоляції.
Рівень ізоляції визначає ступінь незалежності транзакцій один від одного. Щонайвищим рівнем ізоляції є сериализуемость, забезпечуюча повну незалежність транзакцій один від одного. Кожний подальший рівень відповідає вимогам всіх попередніх і забезпечує додатковий захист транзакцій .
SQL Server підтримує всі чотири рівні ізоляції, визначені стандартом ANSI. Рівень ізоляції встановлюється командою:
SET TRANSACTION ISOLATION LEVEL
{ READ UNCOMMITTED
| READ COMMITTED
| REPEATABLE READ
| SERIALIZABLE }
READ UNCOMMITED – незавершене читання, або допустиме чорнове читання. Низький рівень ізоляції, відповідний рівню 0. Він гарантує тільки фізичну цілісність даних: якщо декілька користувачів одночасно змінюють один і той же рядок, то в остаточному варіанті рядок матиме значення, визначене користувачем, що останнім змінив запис. По суті, для транзакції не встановлюється ніякого блокування, яке гарантувало б цілісність даних. Для установки цього рівня використовується команда:
SET TRANSACTION ISOLATION
LEVEL READ UNCOMMITTED
READ COMMITTED – завершене читання, при якому відсутнє чорнове, "брудне" читання. Проте в процесі роботи однієї транзакції інша може бути успішно завершена і зроблені нею зміни зафіксовані. У результаті перша транзакція працюватиме з іншим набором даних. Це проблема неповторюваного читання . Даний рівень ізоляції встановлений в SQL Server за умовчанням і встановлюється за допомогою команди:
SET TRANSACTION ISOLATION
LEVEL READ COMMITTED
REPEATABLE READ – читання, що повторюється. Повторне читання рядка поверне спочатку лічені дані, не дивлячись на будь-які оновлення, проведені іншими користувачами до завершення транзакції. Проте на цьому рівні ізоляції можливе виникнення фантомів . Його установка реалізується командою: