Цілісність транзакцій

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

Властивості Асю

Якість бази даних вимірюється дотриманням її транзакцій вимогам властивостей Асю (тобто атомарности, цілісності, ізольованості та живучості) Архітектура більшості сучасних реляційних баз даних базується на цих властивостях Розуміння цих властивостей є передумовою розуміння SQL Server

Атомарність

Транзакція має бути атомарної Це означає: або все, або нічого Наприкінці транзакції всі її вміст або реєструється, або скасовується Якщо транзакція була тільки частково записана на диск, то її властивість атомарности порушується

Цілісність

Транзакція має не порушувати цілісність бази даних Це означає, що транзакція починається в цілісному стані бази даних і повинна повернути її в цілісний стан по завершенні, незалежно від результату операцій З точки зору Асю цілісність означає те, що всі рядки і значення повинні відповідати модельованої реальності, а всі обмеження – дотримуватися Якщо всі замовлення були записані на диск, а його рядки залишилися не записаними, то цілісність між таблицями Order і OrderDetails буде порушена

Ізоляція

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

Для прикладу припустимо, що якийсь Саша оновив 100 рядків У той час як транзакція Саші виконувалася, Таня прочитала одну з рядків, над якими в цей час працював Саша Якщо виконується читання, то ці транзакції не можна назвати повністю ізольованими один від одного Ця властивість менш критично в базах даних, доступних тільки для читання, а також у базах даних, доступних тільки вузькому колу осіб

Живучість

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

Збої транзакцій

Ізоляція між транзакціями може бути далеко не досконалою, і це може проявлятися кількома способами: брудне читання, неповторяющееся читання і примарні рядка Всі ці порушення можуть потенційно вплинути на цілісність транзакцій

“Брудна читання

Найнебезпечнішим вадою в роботі транзакції є видимість її роботи для інших транзакцій до підтвердження

Якщо деяка транзакція може прочитати непідтверджені поновлення іншої транзакції, то цей ефект називають брудним читанням (рис 511)

Рис 511 Ефект брудного читання виникає, коли транзакція 2 може прочитати непідтверджені поновлення, виконані транзакцією 1

Для ілюстрації ефекту брудного читання нижче наведений код Транзакція 1 виконує оновлення, в цей час транзакція 2 бачить ці оновлення, хоча вони ще не підтверджені BEGIN TRANSACTION

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

USE CHA2

– Транзакція 1 USE CHA2

BEGIN TRANSACTION UPDATE Customer

SET Nickname = Transaction Fault

WHERE CustomerlD = 1

В окремому вікні редактора запитів (рис 512) виконайте іншу транзакцію У цій транзакції встановлений рівень ізоляції, що допускає брудне читання (Цей рівень ізоляції повинен бути встановлений тут з метою демонстрації брудного читання Команди установки рівня ізоляції будуть описані в наступному розділі)

– Транзакція 2

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED USE CHA2 SELECT Nickname FROM Customer WHERE CustomerlD = 1

Puc 512 Відкриття кількох вікон редактора запитів є кращим способом експериментування з транзакціями У даному прикладі транзакція в лівому вікні оновлює псевдонім на Transaction Fault, але не підтверджує зміни Транзакція в правому вікні витягує значення стовпця псевдоніма і читає в ньому Transaction Fault

Буде отримано наступний результат:

NickName

Transaction Fault

Незважаючи на те що транзакція 1 що не завершила свою роботу з набором даних, транзакція 2 змогла прочитати рядок Transaction Fault5. Таким чином, цілісність транзакції була порушена

Для завершення прикладу в першому вікні слід підтвердити транзакцію:

– Транзакція 1 COMMIT TRANSACTION

Неповторяющееся читання

Неповторяющееся читання аналогічно брудному читання, однак воно виникає, коли транзакція може побачити підтверджені поновлення іншої транзакції (рис 513) Істинна ізоляція передбачає, що одна транзакція ніколи не впливає на іншу Якщо ізоляція повна, то транзакція не може побачити зовнішні зміни, що відбуваються з даними під час її роботи Читання деякою рядки в транзакції повинно завжди призводити до одним і тим же результатів Якщо два послідовних читання однієї і тієї ж рядки дають різні результати, то такий тип помилки транзакції називають неповторюваним читанням

У наступному прикладі наведено дві конкурентні транзакції Транзакція 1 відкривається і виконує читання даних Споконвічний псевдонім замовника з кодом 1 є Transaction Fault.

– Транзакція 1

SET TRANSACTION ISOLATION LEVEL READ COMMITTED BEGIN TRANSACTION USE CHA2 SELECT NickName FROM Customer WHERE CustomerlD = 1

Буде отримано наступний результат:

Nickname

Transaction Fault

У той час як перша транзакція залишається відкритою, друга змінює значення поля псевдоніма на Non-Repeatable Read і підтверджує зміну:

– Транзакція 2 USE СНА2

BEGIN TRANSACTION UPDATE Customer SET Nickname = Non-Repeatable Read1 WHERE CustomerlD = 1

COMMIT TRANSACTION

Після оновлення псевдоніма транзакцією 2 транзакція 1 витягує ту ж рядок і намагається отримати той же псевдонім Якщо вона бачить поновлення, виконані транзакцією 2, має місце помилка неповторюваного читання:

– Transaction 2 USE СНА2 SELECT Nickname FROM Customer WHERE CustomerlD = 1

Буде отримано наступний результат:

Nickname

Non-Repeatable Read

Ми бачимо, що транзакція 2 зіткнулася з помилкою неповторюваного читання На другому інструкцію SELECT транзакції 1 вплинула інструкція UPDATE другий Для завершення роботи підтвердимо транзакцію 1:

COMMIT TRANSACTION

Примарні рядка

— Transaction 2 BEGIN TRANSACTION USE CHA2

SELECT CustomerlD, LastName

FROM Customer

WHERE NickName = Missy;

Найменш серйозною помилкою транзакцій є примарні рядка Подібно неповторюваних читання, примарні рядки виникають, коли оновлення, виконані однією транзакцією, призводять до зміни набору даних, що повертається інструкцією SELECT іншої транзакції (рис 514)

Рис 514 Коли склад рядків, що повертається інструкцією SELECT, змінюється в результаті роботи іншої транзакції, цей ефект називають примарними рядками

У наступному прикладі транзакція 1 змінює значення поля псевдоніма на 1 Missy , в той час як транзакція 2 відбирає рядки зі значеннями псевдонімів:

Результат наступний: CustomerlD LastName

2&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp Anderson

— Transaction 1 USE CHA2

BEGIN TRANSACTION UPDATE Customer

SET Nickname = Missy WHERE CustomerlD = 1 COMMIT TRANSACTION

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

– Transaction 2 USE СНА2

SELECT CustomerlD, LastName FROM Customer WHERE Nickname = Missy;

Результат наступний:

CustomerlD LastName

1&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp Adams

2&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp Anderson

Рядок з прізвищем Adams є примарною, так як вона зявилася тільки в другому результуючому наборі даних в результаті роботи транзакції 2

Завершимо транзакцію 2 інструкцією COMMIT TRANSACTION

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

Рівні ізоляції

Бази даних справляються з трьома описаними вище помилками транзакцій, ізолюючи транзакції один від одного Рівні ізоляції можна порівняти з висотою огорожі між транзакціями – вони дозволяють управляти тим, які помилки вважаються допустимими У специфікації ANSI SQL-92 визначено чотири рівня ізоляції (табл 511)

Таблиця 511 Рівні ізоляції ANSI-92

Рівень

ізоляції

Брудна

читання

Неповторяющееся читання

Примарні

рядка

Блокування

записи

Можливість бачити непідтверджені зміни іншої транзакції

Здатність бачити підтверджені зміни іншої транзакції

Відбір пропозицією where додаткових рядків, внесених інший транзакцією

Перша операція запису блокується друга

Read Uncommited

(Найменш жорсткий)

Припустимо

Припустимо

Припустимо

Ні

Read Commited

(Прийнятий в SQL Server за замовчуванням)

Заборонено

Припустимо

Припустимо

Ні

Repeatable Read

Заборонено

Заборонено

Припустимо

Ні

Serializable

(Найбільш жорсткий)

Заборонено

Заборонено

Заборонено

Ні

Snapshot

Заборонено

Заборонено

Припустимо

Так

Read Commited Snapshot

Заборонено

Припустимо

Припустимо

Так

SQL Server реалізує рівні ізоляції за допомогою блокувань Так як блокування впливають на продуктивність, то існує компроміс між рівнями ізоляції і продуктивністю Прийнятий у SQL Server рівень ізоляції Read Commited є своєрідним балансом, відповідним більшості проектів OLTP

Рівень ізоляції можна встановлювати для підключення і пакета Як альтернативу можна встановлювати рівень ізоляції для окремої інструкції DML, використовуючи параметри блокування таблиці в реченні FROM

Рівень 1 – Read Uncommited

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

Рівень 2 – Read Commited

Цей рівень ізоляції дозволяє уникнути найнебезпечнішою помилки транзакцій, але не навантажує систему зайвими блокуваннями З цієї причини рівень ізоляції Read Commited прийнятий в SQL Server за замовчуванням і є ідеальним вибором для більшості проектів OLTR

Рівень 3 – Repeatable Read

Запобігаючи брудне і неповторяющееся читання, цей рівень забезпечує підвищену ізоляцію транзакцій без надмірних блокувань, характерних для рівня ізоляції Serializable

Рівень 4 – Serializable

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

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

Вийшовши за межі стандарту ANSI, розробники SQL Server додали ще Новікка 4 один УР °вень ізоляції-Snapshot Цей рівень створює копію оновлюваних 2005 Gданих у власному фізичному просторі, яка повністю изолиро

вана від інших транзакцій

Рівень ІЗОЛЯЦІЇ Snapshot

Цей рівень реалізує оптимістичну блокування на рівні бази даних Як правило, конфлікт конкуренції відбувається між процесами читання і запису Це жодною мірою не відноситься до ізоляції Snapshot У даному випадку створюється миттєвий знімок даних для оновлення, і під час цього поновлення операції читання продовжують працювати з вихідними даними Коли оновлення буде підтверджено, змінена копія буде записана поверх вихідних даних

Ізоляція Snapshot дозволяє запобігти конфлікту між процесами читання і запису, але, на жаль, не повною мірою Якщо другий процес запису спробує оновити ресурс, який в даний момент вже оновлюється, другий ресурс буде заблокований

Рівень ізоляції Snapshot використовує версійність рядків, записуючи їх до- йа замітку пии в тимчасову базу даних TempDB, що створює неймовірну її завантаження

Якщо ви використовуєте ізоляцію Snapshot, то краще розмістити журнал транзакцій і дані бази TempDB в окремій дискової підсистемі

Рівень ізоляції Read Commited Snapshot

Цей варіант рівня ізоляції Snapshot подібний Read Commited, але усуває конфлікт між процесами читання і запису

Джерело: Нільсен, Пол Microsoft SQL Server 2005 Біблія користувача : Пер з англ – М: ООО ІД Вільямс , 2008 – 1232 с : Ил – Парал тит англ

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


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

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

Ваш отзыв

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

*

*