14. Транзакції

Транзакції в

MySQL

Що таке транзакції?

Транзакція — це сукупність однієї або кількох SQL-інструкцій, що має початок і кінець.

Наприкінці транзакції відбувається або її скасування, або завершення. Скасування

транзакції називається відкатом (rollback), тому що відбувається послідовне скасування

всіх зроблених змін. Завершення транзакції називається фіксацією (commit).

Вважається, що правильна транзакція має такі властивості: атомарність, ізольованість та

стійкість. Під атомарністю розуміють принцип неподільності: всі інструкції, що

становлять транзакцію, обов’язково виконуються, інакше не виконується жодна з них.

Ніяких проміжних станів не існує. База даних підтримує внутрішню узгодженість завдяки

серіалізації транзакцій. Незважаючи на нібито одночасність подій, результати транзакцій

заносяться в базу даних послідовно. Усі транзакції ізолюються одна від одної, а зміни,

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

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

вносяться постійні зміни, які вже не відмінити. Стійка база даних здатна витримати

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

Транзакції реалізуються шляхом ведення журналу всіх змін, внесених у базу даних у ході

кожної транзакції. Коли відбувається відкат, СУБД звіряється з журналом і скасовує всі

зміни. Завдяки журналу легко можна відновити погоджений стан бази даних у випадку

збою.

Транзакції з'явилися в MySQL порівняно недавно. Вони підтримуються для таблиць

розширених типів, таких як InnoDB BerkeleyDB і Gemini. Однак треба відзначити, що в

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

достатньо.

У великих банківських базах даних транзакції, безсумнівно, необхідні. У подібних

системах багато паралельних потоків можуть «сперечатися» за право доступу до ресурсів,

а втрата даних обходиться занадто дорого. З іншого боку, Web-форуми, приміром, не

вимагають ретельного керування, оскільки вартість втрати повідомлення невисока.

Звичайно, активність відвідувачів форуму може зрівнятися з активністю клієнтів

невеликого банку, але форуми не приносять реального прибутку, тому придбання

додаткового устаткування для обробки транзакцій в цьому випадку не є виправданим. В

основному робота з базою даних форуму зводиться до вставки нових повідомлень і

читання існуючих. Для цього досить використати первинні ключі, що є автоматичними

лічильниками.

Internet-магазину потрібна така ж цілісність бази даних, як і в банку, тільки інтенсивність

трафіку помітно нижча. У цій ситуації можна користуватися табличними блокуваннями.

Коли програма створює замовлення, вона блокує певну кількість таблиць. У результаті

жоден інший клієнт не зможе завершити своє замовлення в цей же час. Але у випадку

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

два клієнти зробили замовлення одночасно. У міру зростання активності відвідувачів

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

незаблоковані в ході процедури замовлення, залишаються недоторканими. Наприклад,

нема чого блокувати доступ до каталогу пропонованих товарів. Як наслідок, клієнтам, що

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

замовлень.

Термін

транзакція

можна виразити і формальніше, але простіше всього пояснити її на

прикладі. Розглянемо просту (але потенційно дуже важливу) базу даних, в якій

зберігаються записи про банківські рахунки. Кожен рахунок має, як мінімум, унікальний

ідентифікатор і баланс (значення, відповідне залишку на рахунку).

стовпця, тому не має ніякого значення порядок, в якому ці запити

будуть виконані. Це не матиме значення, наприклад, і у тому випадку, коли відповідні

запити виконуються одночасно трьома різними клієнтами, і немає можливості

передбачити, який з цих запитів буде опрацьований першим.

Такий підхід може породжувати проблеми у разі складніших запитів або наборів запитів.

Розглянемо таку пару операторів, за допомогою яких можна депонувати 500 доларів на

рахунок 2:

Ці запити зв'язані, тому повинні виконуватися разом. Якщо інші клієнти можуть змінити

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

можемо одержати не той результат, якого чекаємо.

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

часу їх виконання. Якщо ми намагаємося депонувати 500 доларів за допомогою наших

запитів, а інший клієнт намагається депонувати 100 доларів за допомогою наступної пари

запитів, кінцевим результатом може бути баланс в 1100 або 1500 доларів — ні те, ні інше

не є правильним результатом:

Проблема розв'язується достатньо легко. Перетворивши значення, що обновляються, з

абсолютних у відносні, ми одержимо окремі неподільні одиниці. Такий запит буде

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

представлені запити:

update rahunok

set balans = balans +500 where nomer=2;

Окремий оператор update в MySQL завжди буде атомарним. Він не може бути перерваний

іншим запитом або виконаний наполовину. Він або успішно завершиться, або генерує

помилку.

Складніші сценарії можуть породжувати і складніші проблеми. Розглянемо таку пару

запитів, завданням яких є переміщення 1000 доларів з рахунку 2 на рахунок 1:

update rahunok set balans= balans-1000 where nomer=2;

update rahunok set balans=balans +1000 where nomer=1;

Тут обидва оновлення є відносними, але з погляду правильності результату дуже важливо,

щоб ці два запити виконувалися разом. Загальна кількість грошей в системі після

виконання запитів повинна залишитися тією ж. Гроші переміщаються з одного рахунку на

інший, а не створюються або знищуються. Якщо між виконанням першого і другого

запитів відбудеться збій електроживлення, дані втратять узгодженість.

У простих випадках розв

’

язком може бути розміщення запитів в одному операторі SQL. У

цьому випадку можна було б написати таке:

) для таблиці рахунків ми змогли створити одне

атомарне оновлення, яке або буде виконане, або цілком знехтуване. Тепер не потрібно

турбуватися про можливість зупинки серверу між різними частинами наших операцій.

У цьому випадку проблемою є тільки зручність сприйняття. Об'єднаний запит важче

читати і виправити, ніж перший варіант.

Але у багатьох випадках об'єднати всі зв'язані запити в один буває просто неможливо. У

таких випадках і тоді, коли ви хочете мати код, зручніший для читання, вирішенням

повинне бути використовування синтаксису транзакцій MySQL. Позначивши набір

операторів як транзакцію, ви інформуєте базу даних про те, що ці оператори є зв'язаною,

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

всі оператори повинні бути виконані, або жоден з них не повинен мати ніякого ефекту.

Щоб виконати вказані два запити як одну транзакцію, використовують таку послідовність

операторів SQL:

Важливою властивістю транзакцій є те, що вони залишаються невидимими для інших

сеансів до тих пір, поки не будуть завершені і зафіксовані. Ніякий інший потік не зможе

прочитати неузгоджено дані таблиць, доки вони знаходяться в процесі оновлення.

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

відкат. При спробі виконати відкат транзакції до її фіксації, всі зміни, виконані запитами,

що формують транзакцію, будуть відмінені.

У прикладі переказу грошових коштів можна використовувати додатковий оператор

SELECT для перевірки того, що з початкового рахунку не буде знято більше грошей, ніж

він містить, а також використовувати ключове слово ROLLBACK, щоб у разі потреби

відмінити транзакцію. Відповідна послідовність операторів може виглядати так:

Команда

ROLLBACK

відміняє транзакцію і всі зміни, що при цьому могли бути зроблені.

Транзакція, що не була зафіксована, а відмінена, не вносить ніяких змін в дані. А оскільки

її часткові результати були недоступні іншим сеансам, все виглядатиме так, як ніби її не

існувало взагалі.

Використання транзакцій у MySQL

Щоб використовувати транзакції, необхідно використовувати тип таблиць, що гарантує

безпеку транзакцій, тобто InnoDB або BDB. При цьому для отримання необхідного

результату можна використовувати кілька варіантів синтаксису.

Оператор

START TRANSACTION

має синоніми

BEGIN

і

BEGIN WORK

. Можна віддати

перевагу якійсь з вказаних форм, щоб код був сумісний з іншими базами даних, але,

оскільки

START TRANSACTION

пропонується стандартом

SQL-99

, рекомендують

використовувати саме його.

Режим автоматичної фіксації

Як правило, MySQL працює в режимі автоматичної фіксації транзакцій (режим

autocommit). Кожен виконуваний запит насправді ізолюється у вигляді транзакції. Ви

можете інтерпретувати це, як автоматичне додавання команд

START TRANSACTION

і

COMMIT

до кожного із запитів. Пара простих запитів

Зверніть увагу на те, що якщо ви надрукуєте вручну

start

transaction

;

нічого зафіксовано не буде до тих пір, поки ви вручну не введете

commit

;

Автоматичну фіксацію можна відмінити за допомогою команди

SET

:

set

autocommit

=0;

Команда для повернення MySQL у режим автоматичної фіксації транзакцій:

set

autocommit

=

l

;

Змінна

autocommit

є локальною для кожного сеансу, тому зміна вказаного режиму

впливатиме тільки на запити вашого сеансу до його завершення.

Якщо вимкнути режим

autocommit

, то для запуску транзакції не потрібно викликати

START

TRANSACTION

. Дуже важливо при цьому не забувати час від часу викликати

COMMIT

, щоб фіксувати зміни, які вносяться в базу даних.

Незалежно від того, включений або вимкнений режим

autocommit

, в деяких ситуаціях

зміни фіксуватимуться автоматично. Якщо ви використовуєте тип таблиць, що не

забезпечує безпеку транзакцій (наприклад,

MyISAM

), всі ваші зміни будуть негайно

зафіксовані, незалежно від установки autocommit. Ви можете групувати свої оператори за

допомогою

START

TRANSACTION

і

COMMIT

, але у разі таблиць, що не забезпечують

безпеку транзакцій, це не матиме ніякого значення. Можна навіть викликати

ROLLBACK

,

і це не призведе до помилки — ніяк не вплине на зміни, які вже внесені у відповідну

таблицю. Це може стати в нагоді при перевірці коду або завантаженні дамп-файлу на

сервери з різними типами таблиць.

Для таблиць, що забезпечують безпеку транзакцій, деякі дії (окрім безпосереднього

введення

COMMIT

) автоматично викликають запуск

COMMIT

. Наприклад, вимога

блокування неявно викликає фіксацію всіх ще невиконаних запитів.

Використання блокування

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

і розблокування таблиць.

Якщо необхідно записати код для депозиту банківського рахунку у вигляді двох окремих

транзакцій, це можна зробити так:

lock tables rahunok write;

select balans from rahunok where nomer=2;

update rahunok set balans=1500 where nomer=2;

unlock tables;

Виклик

LOCK TABLES

означає спробу блокувати всі таблиці, перелічені у цьому

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

UNLOCK TABLES

відміняє всі блокування, призначені цим потоком. Відмінити

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

повинні відкрити її щонайшвидше, щоб зменшити колізії для інших потоків. Блокування

— складніша проблема.

Необхідно задати запит про всі необхідні блокування одразу. У попередньому прикладі

було потрібне одне блокування, але якщо потрібен доступ до багатьох таблиць або навіть

до кількох псевдонімів однієї таблиці, доведеться додати їх у той же виклик. Наприклад:

lock tables rahunok write, rahunok as а read,

othertable low_priority write;

Виклик

LOCK TABLES

знімає всі встановлені до цього блокування, так що спроби

накопичити потрібні блокування за допомогою кількох операторів не мають сенсу — всі

раніше створені блокування будуть відмінені і ви одержите тільки ті, що будуть вказані в

завершальному операторі.

Є два основні типи блокування: блокування для читання і блокування для запису. Якщо

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

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

іншому потоку як читання таблиці, так і запис в ній, поки ви це блокування не знімете.

Блокування для читання є менш строгим. Якщо необхідно тільки прочитати дані таблиці,

немає нічого небезпечного в дозволі іншим потокам читати дані з таблиці одночасно.

Блокування для читання забороняє іншим потокам тільки запис в таблицю, поки ваш потік

здійснює блокування.

Блокування для запису може також позначатися як low_priority. У будь-якій системі, що

використовує блокування, включаючи MySQL, є правила, що дають можливість вирішити,

хто одержить блокування першим при конфлікті запитів. Як правило, MySQL надає

пріоритет запитам блокування для запису перед запитами блокування для читання, щоб

модифікація даних виконувалася щонайшвидше. Якщо така поведінка вас не влаштовує,

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

othertable в попередньому прикладі. При цьому виникає одна проблема. Кожного разу при

запиті блокування вам, можливо, доведеться чекати, поки воно буде надане.

Низькопріоритетне блокування буде надане тільки тоді, коли не виявиться інших потоків,

що запитали блокування для читання або для запису з тією ж таблицею. На дуже

завантаженому сервері цього може не відбутися ніколи.

Швидше за все, ви не дуже часто вручну контролюватимете блокування так, як описано

тут, але є певні ситуації, коли рекомендують ручний контроль. Якщо у вас є програма, що

вимагає дуже високої продуктивності, але рідко використовує транзакції, ви можете

вибрати який-небудь швидкий тип таблиць, що не забезпечує безпеку транзакцій, і

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

Іншою типовою ситуацією, коли вам може бути потрібним LOCK TABLES, є безпосередня

робота з файлами даних MySQL. Наприклад, щоб бути упевненими, що файли на диску

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

необхідно встановити для них блокування.

Найголовніше, що слід пам’ятати про блокування, — це те, що зняти блокування потрібно

щонайшвидше, оскільки інші системи і користувачі будуть вимушені чекати. Деякі задачі,

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

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

Модель транзакцій InnoDB

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

цілям, але розрізняються підходами до розв’язання відповідних завдань. Щоб ізолювати

транзакції, InnoDB використовує дрібномасштабний механізм блокування на рівні рядків.

Це значить, що різні транзакції можуть виконуватися для однієї і тієї ж таблиці одночасно,

якщо вони здійснюють тільки читання або використовують різні рядки для запису.

Незафіксовані зміни блокують для інших потоків рядки, а не всю таблицю. Це забезпечує

високу продуктивність InnoDB та інші характеристики, які звичайно очікуються від

сучасної системи управління реляційними базами даних (СУРБД). Однією з таких

характеристик є відповідність ACID.

Відповідність вимогам ACID

Важливим терміном в базах даних, який ми не ще розглянули, є абревіатура ACID

(Atomicity (атомарність), Consistency (цілісність), Isolation (ізольованість) і

Durability

(стійкість)). З таблицями

MyISAM

система

MySQL

не проходить "тест на

ACID

". З

таблицями

InnoDB

система

MySQL

відповідає вимогам

ACID

.



Атомарність

означає, що транзакції є атомарними і неподільними. Або всі

пропоновані транзакцією зміни зберігаються в базі даних, або не зберігається жодне з

них. У разі зовнішньої помилки ідеальним вирішенням було б, очевидно, завершення

всіх транзакцій, що вже почалися, проте прийнятний також повний відкат назад.



Цілісність

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

стану в інший. Не повинно бути проміжних стадій, коли дані виявляються

неузгодженими. База даних повинна відкидати дії, що порушують обмеження, що

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

повинно бути неможливим створення рахунку для неіснуючого клієнта і так само

повинне бути неможливим видалення клієнта з таблиці клієнтів, якщо в таблиці

рахунків ще є рахунки, пов'язані з ним.



Ізольованість

означає, що транзакції не впливають одна на одну в процесі їх

виконання. Кожна транзакція повинна мати нагоду

“

вважати

”

себе єдиним об'єктом,

наділеним повноваженнями читати і змінювати дані. На практиці це звичайно не так,

але для досягнення відповідної ілюзії використовують блокування. Залежно від бази

даних і її налаштувань, на практиці ви можете мати різні рівні ізольованості.



Стійкість

означає, що після того, як транзакція зафіксована в базі даних, її

результати стають постійними. Цю вимогу легко задовольнити в простій програмі, але

в складній структурі СУРБД, що використовує блокування і багатоваріантність для

організації розрахованого на багато користувачів доступу і кешування з метою

підвищення продуктивності, це є справжньою проблемою. Крім того, стійкість

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

разі відмови системи. Якщо під час пересилки транзакції від клієнта до бази даних і

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

диска, тощо, ми повинні мати нагоду на основі резервної копії і файлів реєстрації подій

привести базу даних назад до стану, що передувала збою, а можливо, і обробити

транзакції, які вже були зареєстровані, але ще не виконані або не зафіксовані.

При використанні таблиць InnoDB (або таблиць BerkeleyDB) MySQL відповідає вимогам

ACID. Використовування синтаксису транзакцій забезпечує атомарність. Обмеження

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

ізольованості транзакцій. Бінарна реєстрація і засоби відновлення забезпечують стійкість.

(Використовуючи реплікацію, ви можете створити виключно надійну систему, на яку не

впливатимуть відмови її окремих елементів.)

Ізольованість транзакцій

Таблиці InnoDB пропонують чотири різні рівні ізольованості транзакцій. У порядку від

найсильнішого до найслабшого, рівні ізольованості можуть бути такими:



впорядкування (serializable);



повторне читання (repeatabte read);



читання підтвердженого (read committed);



читання непідтвердженого (read uncommitted).

Як і для багатьох інших опцій, тут теж доводиться вибирати між стійкістю і

продуктивністю.

Із впорядкуванням читання і запис у базі даних повинні здаватися такими, що

виконуються за чергою, коли записувані зміни вносяться повністю до початку подальшого

читання. Багато транзакцій не заважають одна одній, але у разі колізії заважатимуть.

Блокування і очікування разом з непродуктивними зусиллями на прогноз можливих

колізій перетворюють впорядкування на найповільніший режим ізольованості. Якщо ви

захочете використовувати цей режим, скористайтеся командою:

set

transaction

isolation

level

serializable

;

Для InnoDB рівнем ізольованості за замовчуванням є

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

(repeatabl

e

read). У цьому режимі ізольованості кожна транзакція працює в ізольованій версії таблиці,

де кожен рядок залишається в тому вигляді, в якому він знаходився перед початком

транзакції. При читанні будь-якого рядка гарантується повторюваність результату. Якщо

викликати

select

*

from

rahunok

where

nomer

=1;

на початку транзакції і виконувати той же запит в рамках транзакції пізніше, ви одержите

один і той же результат. Проте існує небезпека так званого

фантомного читання.

Можливо,

що інша транзакція, яка була тільки що зафіксована, додала в таблицю нові рядки. Якщо

виконати один і той же запит з деякою умовою двічі, наприклад

select * from rahunok where balans>1000;

то цілком можливо, що удруге ви одержите нові рядки — фантомні,

На практиці фантомне читання в MySQL спостерігається рідко. Для вирішення цієї

проблеми механізм InnoDB використовує алгоритм, що називається блокуванням

наступного ключа, але

стовпець, який використовується у відповідній умові, повинен бути

індексований. InnoDB пропонує блокування на рівні рядків. Коли транзакція використовує

рядок, вона блокує його, щоб транзакція могла ізолюватися від інших. Разом з таким

блокуванням рядків блокування наступного ключа заблокує і проміжки між рядками,

знайдені в індексі. У результаті такого підходу до розв

’

язання проблеми фантомного

читання лише невеликому числу систем дійсно може знадобитися режим впорядкування

для ізольованості.

Якщо встановити для серверу режим

читання підтвердженого

(read committed), транзакції

вже будуть менш ізольованими. Якщо виконати запит і повторити його пізніше в рамках

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

змінить дані і буде зафіксована. Якщо вам потрібно буде встановити цей режим,

відповідна команда повинна виглядати так:

set transaction isolation level read committed;

На найпростішому рівні ізольованості, в режимі

читання непідтвердженого

(read

uncommitted), очевидне вже не тільки те, що транзакції більше не ізольовані, не

забезпечують цілісність і, таким чином, відповідність ACID, але і те, що транзакцій по суті

взагалі неможливо мати. У цьому режимі транзакції можуть читати зміни, які вносять інші

транзакції, до того, як ці зміни будуть підтверджені (тобто зафіксовані). Це називають

"

брудним" читанням.

Ви можете допустити це тільки за виключних умов, наприклад, коли

ви знаєте, що всі активні потоки або тільки читатимуть, або тільки записуватимуть дані,

але не те і інше одночасно. Щоб встановити режим читання непідтвердженого,

використовуйте таку команду:

set

transaction

isolation

level

read

uncommitted

;

У таб. 1 наводяться характеристики кожного з розглянутих вище режимів ізольованості.