Введення в транзакції в mysql, вебісторій

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

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

У транзакционной моделі є два фундаментальних поняття: COMMIT і ROLLBACK. COMMIT означає фіксацію всіх змін в транзакції. ROLLBACK означає скасування (відкат) змін, що відбулися в транзакції.

При старті транзакції всі наступні зміни зберігаються у тимчасовому сховищі. У разі виконання COMMIT, всі зміни, виконані в рамках однієї транзакції, збережуться в фізичну БД. У разі виконання ROLLBACK відбудеться відкат і всі зміни, виконані в рамках цієї транзакції, не будуть збережені.

В MySQL транзакції підтримуються тільки таблицями innoDB. Таблиці MyISAM транзакції не підтримують. У innoDB за замовчуванням включений autocommit, це означає, що за замовчуванням кожен запит еквівалентний однієї транзакції.

Транзакція починається зі спеціального запиту «START TRANSACTION», або «BEGIN». Щоб закінчити транзакцію, потрібно або зафіксувати зміни (запит COMMIT), або відкотити їх (запит ROLLBACK).

Приклад з COMMIT:

Приклад з ROLLBACK:

В MySQL не існує механізму вкладених транзакцій. Одне з'єднання з БД - одна транзакція. Нова транзакція в межах одного з'єднання може початися тільки після завершення попередньої.

Для деяких операторів не можна виконати відкат за допомогою ROLLBACK. Це оператори мови визначення даних (Data Definition Language - DDL). Сюди входять запити CREATE, ALTER, DROP, TRUNCATE, COMMENT, RENAME.

Наступні оператори неявно завершують транзакцію (як якщо б перед їх виконан-ням був виданий COMMIT):

ALTER TABLE
DROP DATABASE
LOAD MASTER DATA
SET AUTOCOMMIT = 1
BEGIN
DROP INDEX
LOCK TABLES
START TRANSACTION
CREATE INDEX
DROP TABLE
RENAME TABLE
TRUNCATE TABLE

Зверніть увагу, що в разі SQL помилки, транзакція сама по собі не відкотиться. Зазвичай помилки обробляються вже за допомогою sql wrapper'ов в самому додатку, таких як PHP PDO наприклад. Якщо ви захочете відкочувати зміни в разі помилки прямо в MySQL, можна створити спеціальну процедуру і вже в ній виконувати ROLLBACK в обробнику.

Але цей спосіб швидше просто для ознайомлення, а не керівництво до дії. Чому? Я вкрай не рекомендую так робити, так як в основному помилки бази даних обробляються за допомогою SQL обгорток на стороні додатки, таких як PHP PDO наприклад, щоб звідти повністю управляти транзакціями.

Розглянемо практичний приклад: є 2 таблиці, користувачі - users і інформація про користувачів - user_info. Уявімо, що нам потрібно або виконати 3 запити до бази даних, або не виконувати їх взагалі, так як інакше це призведе до збоїв в роботі програми.

В цілому я думаю принцип роботи транзакції зрозумілий. Але все не так просто. Існують проблеми паралельних транзакцій. Розглянемо приклад. Уявімо, що під час виконання цієї транзакції, інший користувач створив другу паралельну транзакцію і зробив запит SELECT * FROM user після того, як в нашій транзакції був виконаний перший запит «INSERT INTO user (id, nik) VALUES (1, 'nikola') ». Що побачить користувач другий транзакції? Чи зможе він побачити вставлену запис навіть тоді, коли результати першої транзакції ще не зафіксувалися (не сталося COMMIT)? Або він зможе побачити зміни тільки після того, як результати першої транзакції будуть зафіксовані? Виявляється мають місце бути обидва варіанти. Все залежить від рівня ізоляції транзакції.

У транзакцій є 4 рівня ізоляції:

0 - Читання непідтверджених даних (брудне читання) (Read Uncommitted, Dirty Read) - найнижчий рівень ізоляції. При цьому рівні можливо читання незафіксованих змін паралельних транзакцій. Якраз в цьому випадку другий користувач побачить вставлену запис з першої незафіксованою транзакції. Немає гарантії, що незафіксована транзакція буде в будь-який момент відкинуті, тому таке читання є потенційним джерелом помилок.
1 - Читання підтверджених даних (Read Committed) - тут можливо читання даних тільки зафіксованих транзакцій. Але на цьому рівні існують дві проблеми. В цьому режимі рядка, які беруть участь у вибірці в рамках транзакції, для інших паралельних транзакцій блокуються, з цього випливає проблема № 1: «є повторюваною читання» (non-repeatable read) - це ситуація, коли в рамках транзакції відбувається кілька вибірок (SELECT ) по одним і тим же критеріям, і між цими вибірками відбувається паралельна транзакція, яка змінює дані, які беруть участь в цих вибірках. Так як паралельна транзакція змінила дані, результат при наступній вибірці за тими ж критеріями в першій транзакції буде інший. Проблема № 2 - «Напруга читання» - цей випадок розглянутий нижче.
2 - Повторюване читання (Repeatable Read, Snapshot) - на цьому рівні ізоляції так само можливо читання даних тільки зафіксованих транзакцій. Так само на цьому рівні відсутня проблема «є повторюваною читання», тобто рядки, які беруть участь у вибірці в рамках транзакції, блокуються і не можуть бути замінені іншими паралельними транзакціями. Але таблиці цілком не блокуються. Через це залишається проблема «фантомного читання». «Напруга читання» - це коли за час виконання однієї транзакції результат одних і тих же вибірок може змінюватися у зв'язку з тим, що блокується не вся таблиця, а тільки ті рядки, які беруть участь у вибірці. Це означає, що паралельні транзакції можуть вставляти рядки в таблицю, в якій відбувається вибірка, тому два запити SELECT * FROM table можуть дати різний результат в різний час при вставці даних паралельними транзакціями.
3 - Серіалізуемое (Serializable) - Серіалізуемое транзакції. Найнадійніший рівень ізоляції транзакцій, а й при цьому найповільніше. На цьому рівні взагалі відсутні будь-які проблеми паралельних транзакцій, але за це доведеться платити швидкодією системи, а швидкодія в більшості випадків вкрай важливо.

За замовчуванням в MySQL встановлений рівень ізоляції № 2 (Repeatable Read). І, як я вважаю, розробники MySQL не дарма зробили за замовчуванням саме цей рівень, так як він найбільш вдалий для більшості випадків. З першого разу може здатися, що найкращий варіант № 3 - він найнадійніший, але на практиці ви можете випробувати великі незручності через дуже повільну роботу вашого застосування. Пам'ятайте, що багато що залежить не від того, наскільки хороший рівень ізоляції транзакцій в БД, а від того, як спроектовано вашу програму. При грамотному програмуванні, можна навіть використовувати найнижчий рівень ізоляції транзакцій - все залежить від особливостей структури та грамотності розробки вашої програми. Але непотрібно прагнути до найнижчого рівня ізоляції - немає, просто якщо ви використовуйте не найзахищеніший режим, слід пам'ятати про проблеми паралельних транзакцій, в цьому випадку ви не розгубитеся і все зробіть правильно.

SET TRANSACTION - цей оператор встановлює рівень ізоляції наступної транзакції, глобально або тільки для поточного сеансу.

Існуючі з'єднання не будуть зачіпатися. Для виконання цього оператора потрібно мати привілей SUPER. Застосування ключового слова SESSION уста-встановлюються рівень ізоляції за замовчуванням всіх майбутніх транзакцій тільки для теку-ного сеансу.

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

Схожі статті

Попередня ◈ Наступна