Транзакції і блокування об'єктів баз даних

Типи управління одночасним доступом декількох користувачів до даних:

• песимістичний - в один і той же час переглядати та змінювати дані може тільки один користувач.
• оптимістичний - в один і той же час переглядати дані можуть декілька користувачів, але змінювати дані може тільки один користувач.

Обидва типи управління одночасним доступом до даних використовують у своїй роботі блокування даних. Блокування - це об'єкт, за допомогою якого показується залежність користувача від будь-якого ресурсу. Система управління блокуваннями забороняє іншим користувачам виконувати над ресурсами операції, що негативно впливають на залежність користувача, що володіє блокуванням, від цього ресурсу. Блокування створює тимчасове обмеження на виконання деяких операцій обробки тих даних, від яких залежить користувач. Основні типи блокувань:

• колективна (shared, S) - накладається при виконанні операцій читання даних (наприклад, SELECT). Ніяка інша транзакція не зможе змінити або видалити дані, якщо на них встановлена спільна блокування. Спільна блокування звичайно звільняється після завершення читання даних, але якщо тільки рівень ізоляції транзакції встановлений в REPEATABLE READ або вище, то спільна блокування зберігається до завершення транзакції.
• монопольна (exclusive, X) - накладається при виконанні операцій зміни даних (наприклад, UPDATE). Ніяка інша транзакція не зможе ні змінити, ні навіть прочитати дані, якщо на них встановлення монопольна блокування. Виняток: прочитати дані з встановленою монопольної блокуванням можливо, якщо тільки рівень ізоляції транзакції встановлений в READ UNCOMMITTED. Монопольна блокування звільняється після завершення транзакції.

Для перегляду встановлених блокувань в Microsoft SQL Server використовується системна інформація процедура sp_lock.
Транзакція - послідовність операцій, що виконується як цілісна логічна одиниця роботи. Властивості транзакції:

• атомарність - в транзакції виконуються або всі вхідні в неї операції, або ні одна.
• узгодженість - після завершення транзакції всі дані повинні знаходиться в узгодженому стані.
• ізоляція - модифікації, виконувані транзакцією, повинні бути ізольовані від модифікацій, паралельно виконуваних іншими операціями.
• стійкість - після завершення транзакції її результат має зберегтися в системі в будь-якому випадку.

Оператор BEGIN TRAN використовується для початку транзакції. Для завершення транзакції використовується або оператор COMMIT TRAN (використовується в тому випадку, якщо транзакція завершується успішно і всі дії, виконання в рамках транзакції повинні бути збережені в базі даних), або оператор ROLLBACK TRAN (використовується в тому випадку, якщо транзакція завершується з помилками і всі дії, виконання в рамках транзакції повинні бути скасовані). Синтаксис транзакції виглядає так:

BEGIN TRAN
{оператори SQL}

{якщо помилок не було}
COMMIT TRAN
{інакше, якщо виникли помилки}
ROLLBACK TRAN

Управління операціями здійснюється на рівні з'єднання, а не користувача. Після початку транзакції в з'єднанні і до її успішного чи невдалого завершення всі виконані в цьому з'єднанні оператори SQL стають частиною транзакції.

При одночасному виконанні двох транзакцій, які звертаються до одним і тим же даними, можуть виникати такі проблемні ситуації:

• втрачені оновлення - з'являються при виборі одного запису двома або більше транзакціями, які потім оновлюють цю запис на основі її початкового значення. Ні в одній з транзакцій немає відомостей про дії, виконані іншими операціями, тому останнє оновлення записується поверх оновлень, зроблених іншими операціями, що призводить до втрати даних.
• брудне читання - виникає в тому випадку, коли перша транзакція читає дані, які оновила, але ще не зафіксувала друга транзакція. Таким чином, перша транзакція читає ще не зафіксовані дані, зміни яких можуть бути скасовані у другій транзакції.
• неповторяемое читання - виникає, коли перша транзакція кілька раз звертається одним і тим же даними, проте дані змінюються внаслідок того, що між зверненнями друга транзакція оновлює дані і фіксує.
• читання фантомів - з'являється в тому випадку, коли записи з діапазону записів, що читається в першій транзакції, додаються або видаляються другий транзакцією.

Для управління взаємодії транзакцій між собою і, відповідно, управлінням блокуваннями даних, що виникають при виконанні транзакцій, використовується установка рівня ізоляції транзакції. Рівень ізоляції транзакції визначає, які блокування накладаються на дані, що обробляються в рамках транзакцій. Оператор SET TRANSACTION ISOLATION LEVEL використовується для зміни рівня ізоляції транзакції.

Синтаксис оператора SET TRANSACTION ISOLATION LEVEL виглядає так:

SET TRANSACTION ISOLATION LEVEL {рівень ізоляції}

У мовою SQL допустимі рівні ізоляції транзакції:

• READ UNCOMMITTED (непідтверджена читання) - транзакція з цим рівнем ізоляції може читати записи, які були змінені, але ще не зафіксовані інший транзакцією.
• READ COMMITTED (підтверджене читання) - транзакція з цим рівнем ізоляції може читати тільки ті записи, які були змінені і вже зафіксовані інший транзакцією.
• REPEATABLE READ (повторюване читання) - транзакція з цим рівнем ізоляції може читати тільки ті записи, які були змінені і вже зафіксовані інший транзакцією, і ніяка інша транзакція не може змінити записи, які були прочитані в рамках цієї транзакції.
• SERIALIZABLE (упорядкування) - транзакція з цим рівнем ізоляції може читати тільки ті записи, які були змінені і вже зафіксовані інший транзакцією; ніяка інша транзакція не може змінити записи, які були прочитані в рамках цієї транзакції і ніяка інша транзакція не може додати або видалити записи з діапазону записів, прочитаного у рамках цієї транзакції.

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