Уявіть, що у вас на банківському рахунку є 300 INR, і ви налаштували інструкцію на передавтодебет на суму 200 INR. Одночасно ви вирішуєте здійснити платіж на суму 200 INR торговцю через транзакцію UPI, поки робите покупки в продуктовому магазині. Тепер постає питання: чи обидві транзакції будуть успішними, чи одна з них не вдасться через недостатній баланс?
Швидше за все, відбудеться другий варіант, і це є наслідком застосування властивостей ACID у транзакційній базі даних програмного забезпечення.
Маючи чотири ключові властивості — атомарність (atomicity), узгодженість (consistency), ізоляція (isolation) та надійність (durability) — забезпечується своєчасне виконання транзакції в базі даних.
Коли база даних має ці властивості, її вважають такою, що відповідає стандарту ACID.
Нижче ми розглянемо властивість ізоляції в базах даних і детально вивчимо рівні ізоляції транзакцій у MySQL.
Що таке рівні ізоляції транзакцій в базах даних? Для чого вони використовуються?
Під час роботи з транзакціями в базі даних важливим аспектом є вибір відповідного рівня ізоляції для нашого застосунку. Незважаючи на чітко визначений стандарт, різні системи управління базами даних можуть реалізовувати його по-різному, що призводить до різних поведінок для кожного рівня ізоляції.
Сьогодні ми зосередимося на детальному дослідженні того, як кожен рівень ізоляції працює в MySQL, що буде досягнуто шляхом виконання конкретних SQL-запитів.
Однак, перш ніж заглиблюватися в практичні приклади, давайте спочатку розглянемо теоретичні аспекти рівнів ізоляції транзакцій.
Теорія ізоляції
Як відомо, транзакція в базі даних повинна відповідати властивостям ACID, що включають атомарність (Atomicity), узгодженість (Consistency), ізоляцію (Isolation) та стійкість (Durability).
Ізоляція є однією з основних властивостей транзакції в базі даних, і коли вона досягається на найвищому рівні, це гарантує, що паралельні транзакції не впливають одна на одну. Однак на практиці існують різні способи, якими одна транзакція може бути впливати іншими паралельними транзакціями, що призводить до явищ, які ми називаємо феноменами читання.
Давайте заглибимося в різні спостережувані явища, які можуть виникнути, коли база даних працює з нижчими рівнями ізоляції транзакцій.
Феномен "брудного читання" (Dirty read):
Феномен “брудного читання” виникає, коли транзакція читає дані, записані іншою паралельною транзакцією, яка ще не була зафіксована (не закрита).
Ця ситуація є надзвичайно небажаною, оскільки ми не можемо гарантувати, чи буде інша транзакція врешті-решт зафіксована, чи скасована. Як результат, ми ризикуємо використовувати некоректні дані, якщо транзакція буде скасована.
Феномен "неповторюваного читання" (Non-repeatable read):
Другим феноменом, який може виникнути, є так зване "неповторюване читання", що відбувається, коли транзакція читає певний запис двічі та помічає різні значення під час кожного зчитування. Причиною цієї невідповідності є те, що інша транзакція змінила рядок і зафіксувала свої зміни після першого зчитування.
Феномен "привидового читання" (Phantom read):
"Привидове читання" є феноменом, схожим на "неповторюване читання", але воно стосується запитів, що шукають кілька рядків, а не один. У цьому випадку той самий запит виконується знову, але повертається інший набір рядків через зміни, внесені іншими недавно зафіксованими транзакціями.
Ці зміни можуть включати вставку нових рядків або видалення існуючих, які випадково задовольняють умови пошуку поточного запиту транзакції.
Аномалія серіалізації (Serialization anomaly):
Іншим феноменом, який виникає при роботі з групою транзакцій, є аномалія серіалізації. Ця аномалія відбувається, коли результат серії успішно виконаних паралельних транзакцій неможливо відтворити, послідовно виконуючи їх у будь-якому порядку без перекриття.
Не хвилюйтеся, якщо ви не повністю розумієте ці феномени на даний момент.
Ми будемо реплікувати кожен з цих феноменів у MySQL, щоб краще зрозуміти їхні ефекти та наслідки.
Глибоке занурення в рівні ізоляції транзакцій MySQL
Для вирішення цих феноменів Американський національний стандартний інститут (ANSI) визначив чотири стандартні рівні ізоляції транзакцій.
Найнижчий рівень ізоляції — це read uncommitted, при якому транзакції можуть бачити дані, записані іншими незавершеними транзакціями, що дозволяє виникнути феномену "брудного читання" (dirty read).
Наступний рівень — read committed, який є дещо більш строгим.
Тут транзакції можуть отримувати доступ лише до даних, які були зафіксовані іншими транзакціями, що робить феномен "брудного читання" (dirty read) неможливим.
Наступним рівнем є repeatable-read, який гарантує, що одна й та сама SQL-запит вибиратиме однакові результати, незалежно від того, скільки разів його виконано, навіть якщо інші одночасні транзакції вже зафіксували зміни, які відповідають умовам запиту.
Останній рівень ізоляції — serializable. На цьому рівні забезпечується, що одночасні транзакції дають такий самий результат, як якби їх виконували послідовно в певному порядку без перекриття. Це означає, що існує хоча б один спосіб впорядкувати ці одночасні транзакції так, щоб при їх виконанні по черзі кінцевий результат залишався консистентним.
Добре, час встановити взаємозв'язок між рівнями ізоляції та спостережуваними феноменами читання. Я запускаю MySQL у своєму контейнері Docker і відкриваю сесію MySQL в одному з вікон терміналу.
Якщо я виконую запит “select @@transaction_isolation;”, рівень ізоляції транзакцій за замовчуванням відображається як REPEATABLE READ.
Я зміню його на READ UNCOMMITTED, виконавши запит “set session transaction isolation level read uncommitted;”
Зверніть увагу, що ця зміна вплине лише на всі майбутні транзакції в поточній сесії, тоді як транзакції в окремій сесії MySQL залишатимуться незмінними. Тому я відкрию іншу сесію та виконаю ту ж саму операцію, встановивши рівень ізоляції транзакцій на read uncommitted.
Тепер обидві мої сесії мають рівень ізоляції транзакцій read uncommitted.
У першій сесії я виконую “select * from savings_account;” і отримую такий результат:
Далі я оновлюю баланс Амита до 4800, виконавши запит “update savings_account set balance = balance-200 where id = 1;” Отже, в першій сесії баланс Амита складає 4800, і зверніть увагу, що транзакція 1 у першій сесії ще не зафіксована.
У другій сесії, якщо я виконаю “select * from savings_account where id = 1;” що повинно бути відображено, 5000 чи 4800? В цьому випадку буде показано 4800, оскільки ми використовуємо рівень ізоляції транзакцій READ UNCOMMITTED, що дозволяє нам читати дані, які ще не були зафіксовані (dirty read).
Однак, якщо ми встановимо рівень ізоляції для другої сесії як READ COMMITTED, то dirty read можна уникнути, і результат другої сесії буде 5000, оскільки транзакція 1 не була зафіксована.
Отже, рівень ізоляції read-committed захищає від виникнення явища dirty read.
А як щодо non-repeatable і phantom read?
У транзакції 2 давайте ініціюємо ще одну транзакцію і виконаємо select запит до таблиці ‘savings_accounts’, де баланс більший або рівний 4500 INR. Отриманий результат виглядає наступним чином:
Тепер переходимо до сесії 1, де ми розпочнемо ще одну транзакцію, оновимо баланс Амита, зменшивши його на 500 INR, і зафіксуємо транзакцію. Як результат, баланс Амита в сесії 1 стає 4300 INR. Тепер, якщо ми виконаємо той самий select запит у сесії 2, результат буде відрізнятись від попереднього.
Ця різниця в результатах є наслідком впливу зафіксованої транзакції, відомого як явище phantom read.
Отже, стає очевидним, що рівень ізоляції read-committed ефективно запобігає dirty read, при цьому дозволяючи виникати явищам non-repeatable read та phantom read.
Тепер я встановлю рівень ізоляції транзакцій цієї сесії на repeatable read, скасувавши попереднє оновлення. Далі, я розпочну нові транзакції як у сесії 1, так і в сесії 2. Переходимо до сесії 2, де перевіримо, чи може вона зчитати нові зміни, внесені транзакцією 1. Ми виконаємо ще один select запит до таблиці ‘savings_accounts’, де баланс більший або рівний 4500 INR. Оскільки я скасував попереднє оновлення таблиці, результат буде виглядати так:
Тепер, у транзакції сесії 1, давайте знову оновимо баланс Амита, зменшивши його на 500 INR. Тепер ефективний баланс Амита складає 4300 INR. Ми зафіксуємо транзакцію в сесії 1.
У сесії 2, в межах існуючої транзакції 2, ми виконаємо той самий select запит ‘select * from savings_account where balance > 4500.’ Результат буде наступним:
Запит select повертає стару версію рахунку Амита, з балансом 4800 INR, хоча транзакція 1 вже змінила його на 4300 INR і успішно зафіксувала зміну. Це пов'язано з рівнем ізоляції repeatable-read, який гарантує, що всі запити на читання повертають той самий результат, незалежно від змін, внесених іншими зафіксованими транзакціями. Таким чином, явище phantom-read також запобігається на цьому рівні ізоляції.
Однак мені цікаво, що відбудеться, якщо ми виконаємо запит на оновлення (balance — 500) для зміни балансу Амита в транзакції 2 сесії 2. Баланс стане 4300, 3800 чи викине помилку? Давайте перевіримо!
Помилки не буде.
Тепер виконаємо запит select, щоб отримати оновлені дані рахунку заощаджень.
Тепер баланс Амита складає 3800 INR, що є правильним значенням, оскільки транзакція 1 вже зафіксувала зміни, які оновили баланс до 4300 INR. Однак з точки зору транзакції 2 це здається непослідовним. Останній запит select показав баланс Амита 4800 INR, але після того, як з рахунку було віднято 500 INR, зараз він показує 3800 INR. Ця непослідовність виникає через одночасні оновлення від інших транзакцій, які перешкоджають цій транзакції.
На мою думку, було б логічніше, якщо б MySQL відмовився від зміни, викликавши помилку в таких випадках, щоб забезпечити консистентність даних транзакцій.
Можливо, Postgres обробляє цей тип одночасних оновлень по-іншому на цьому рівні ізоляції, що ми знову розглянемо пізніше.
А зараз давайте скасумо цю транзакцію і перейдемо до найвищого рівня ізоляції (serializable), щоб подивитися, чи може він запобігти цій проблемі. Розпочнемо дві сесії та дві транзакції з рівнем ізоляції serializable. У транзакції 1 ми виберемо всі рахунки, а в транзакції 2 — лише рахунок 1. Далі повернемося до транзакції 1 і віднімемо ще 500 INR від балансу Амита. Цікаво, що цього разу запит на оновлення заблоковано.
Причина цієї блокування полягає в тому, що на рівні ізоляції serializable MySQL автоматично перетворює всі звичайні запити SELECT на SELECT FOR SHARE. Транзакція, що містить SELECT FOR SHARE, дозволяє іншим транзакціям тільки ЧИТАТИ рядки, але не ОНОВЛЮВАТИ або ВИДАЛЯТИ їх. Цей механізм блокування запобігає виникненню непослідовних даних, які ми спостерігали раніше.
Однак цей замок має обмежений час очікування. Якщо друга транзакція не буде зафіксована або скасована для звільнення замка протягом цього часу, виникне помилка ‘lock wait timeout exceeded’. Тому в таких випадках потрібно реалізувати стратегію повторних спроб транзакцій при використанні рівня ізоляції serializable.
Давайте підсумуємо рівні ізоляції транзакцій у MySQL. На найнижчому рівні, “read uncommitted,” можуть виникати всі чотири явища. Перехід до рівня “read committed” дозволяє запобігти лише брудним зчитуванням (dirty reads), залишаючи три інші явища можливими.
Рівень ізоляції “repeatable read” у MySQL вирішує перші три явища: брудне читання (dirty read), неповторне читання (non-repeatable read) та фантомне читання (phantom read), але все ж може стикатися з аномаліями серіалізації та неконсистентними одночасними оновленнями. На своєму піку рівень “serializable” є найстрогішим, ефективно запобігаючи всім чотирьом явищам завдяки потужному механізму блокувань.
При використанні високого рівня ізоляції важливо бути обізнаним про потенційні помилки, тайм-аути або навіть тупикові ситуації (deadlocks). Тому необхідно впровадити ретельно продуману стратегію повторних спроб для транзакцій. Крім того, пам'ятайте, що різні механізми баз даних можуть по-різному реалізовувати рівні ізоляції транзакцій. Завжди звертайтесь до офіційної документації MySQL і ретельно тестуйте її перед впровадженням вашого коду.
Я додам посилання на офіційну документацію щодо рівнів ізоляції транзакцій у MySQL для вашого ознайомлення.
Сподіваюся, ця інформація була корисною для вас. Дякую за те, що прочитали, і з нетерпінням чекаю можливості поділитися новими інсайтами в майбутніх публікаціях!
Офіційна документація рівнів ізоляції транзакцій MySQL: https://dev.mysql.com/doc/refman/8.0/en/innodb-transaction-isolation-levels.html
Відвідайте мій блог, щоб дізнатися більше:
Перекладено з: Diving Deep into Mysql Transaction Isolation Levels