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

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

Такий механізм бази даних з підтримкою транзакцій, як InnoDB або BDB, починає транзакцію по команді start transaction. Завершується транзакція при підтвердженні або скасування змін. Завершити транзакцію можна двома командами. Команда commit зберігає всі зміни в базі даних. Команда rollback скасовує всі зміни.

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

CREATE TABLE sample_innodb (id int (11) NOT NULL auto_increment, name varchar (150) default NULL, PRIMARY KEY (id)) ENGINE \u003d InnoDB DEFAULT CHARSET \u003d utf8; INSERT INTO sample_innodb VALUES (1, "Олександр"), (2, "Дмитро"); start transaction; DELETE FROM sample_innodb WHERE id \u003d 1; DELETE FROM sample_innodb WHERE id \u003d 2; rollback;

Оскільки стався відкат транзакції, дані з таблиці не були видалені.

А якби замість rollback ми написали commit, то обидві рядки були б видалені.

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

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

За службовим обов'язком мені доводиться іноді проводити співбесіди на позицію "[старший | молодший] розробник python / django", "тімліда». На мій превеликий подив я виявив, що 9 з 10 претендентів, в резюме яких значаться слова "Mysql / Innodb / transactions / triggers / stored proc etc.", абсолютно нічого не можуть розповісти про своє минуле досвіді роботи з ними. Жодного опису варіанта використання, на жаль, я так і не отримав.

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

Припустимо, ми є онлайн сервісом, який в свою чергу користується якимось зовнішнім платним API (активація послуги, платний контент, або що вашій душі завгодно), тобто наш сервіс сам платить гроші за користування API. Користувач в нашій системі створює запит на активацію послуги, заповнює всі поля і на останній сторінці тисне кнопку «Активувати послугу». Тобто на момент відправки HTTP запиту ми маємо в нашій БД запис (запит на активацію послуги). Який наш алгоритм? - запитую я і сам продовжую:

Дістаємо з бази баланс користувача;
- якщо балансу досить, то смикаємо API;
- якщо все добре, то списуємо з балансу суму за послугу, робимо UPDATE, коммітов, інакше відкатуємося;
- відповідаємо користувачеві.

Начебто все тривіально, але коли привожу першу і найочевиднішу проблему у вигляді 10 конкурентних запитів (що всі вони на початку отримають однаковий баланс і почнуть дзвонити в API), рішення починають пропонувати найвитонченіші, починаючи від виконання 5 СЕЛЕКТА (варто зізнатися, я нічого не зрозумів у цьому варіанті), використання Автоінкрементний лічильників, зовнішніх кешей, нових таблиць в бд, сліпів і ще не зрозумій чого.

Як відомо (а це знали всі кандидати!), Innodb в mysql надає транзакційний механізм і можливість порядкової блокування. Для того, щоб застосувати цей самий порядковий лок, досить додати в кінці SELECT-а вираз FOR UPDATE, наприклад так:

SELECT * FROM requests WHERE id \u003d 5 FOR UPDATE

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

Також варто зауважити, що використання FOR UPDATE краще робити з вимкненим autocommit-му, так як незалежно від того, що ви залочений, після першого апдейта лок зніметься.

Начебто дрібниця, на зразок очевидно, але 9 з 10 ...

Upd
колишня назва «Транзакції в mysql", не розкрите в статті було замінено на "Транзакції в mysql і SELECT FOR UPDATE"

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

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

За замовчуванням MySQL працює в режимі autocommit. Це означає, що результати виконання будь-якого SQL-оператора, що змінює дані, будуть відразу зберігатися.
Режим autocommit можна відключити так: SET AUTOCOMMIT \u003d 0;

Якщо необхідно переключитися з режиму AUTOCOMMIT тільки для виконання однієї послідовності команд, то для цього можна використовувати команду BEGIN або START TRANSACTION (починаючи з версії mysql 4.0.11.)

Приклад SQL-запиту, що фіксує кількість обраного покупаелем товару в таблиці corrections і вносить зміни в таблицю товарів goods:
START TRANSACTION; INSERT INTO corrections SET id_goods \u003d: id_goods, number \u003d: number, id_orders \u003d: id_orders ON DUPLICATE KEY UPDATE number \u003d number +: number; UPDATE goods SET reserve \u003d reserve +: number, available \u003d available -: number WHERE id \u003d: id_goods; COMMIT;

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

Якщо Вам потрібно створити більш складний механізм фіксації змін, використовуйте команди SAVEPOINT і ROLLBACK TO SAVEPOINT

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

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

PHP PDO пропонує свої кошти роботи з транзакціями. Про них можна почитати.

Паралельні транзакції і рівні ізоляції (спільний доступ)

Уявімо, що під час виконання 1-ої транзакції транзакції, інший користувач створив другу паралельну транзакцію і зробив запит 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 - цей оператор встановлює рівень ізоляції наступної транзакції, глобально або тільки для поточного сеансу.

SET TRANSACTION ISOLATION LEVEL (READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE)

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

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

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

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

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

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

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

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

Приклад з COMMIT:

Start transaction; (Також, можна написати BEGIN;) ... якісь дій з БД (insert, update, delete ...) commit; // Фіксація дій, запис їх в фізичну БД

Приклад з ROLLBACK:

Set autocommit \u003d 0; // відключаємо autocommit Start transaction; ... якісь дії з БД (insert, update, delete ...) rollback; // скасовуємо серію дій, не робимо запис в фізичну БД

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

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

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

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

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

CREATE PROCEDURE prc_test () BEGIN DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; // Ось тут відкатуємо транзакцію в разі помилки END; START TRANSACTION; INSERT INTO tmp_table VALUES ( "null"); COMMIT; END; CALL prc_test ();

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

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

Start transaction; INSERT INTO user (id, nik) VALUES (1, "nikola"); INSERT INTO user_info (id, id_user, item_name, item_value) VALUES (1, 1, "Ім'я", "Микола"); INSERT INTO user_info (id, id_user, item_name, item_value) VALUES (2, 1, "Вік", "24"); commit;

В цілому я думаю принцип роботи транзакції зрозумілий. Але все не так просто. Існують проблеми паралельних транзакцій. Розглянемо приклад. Уявімо, що під час виконання цієї транзакції, інший користувач створив другу паралельну транзакцію і зробив запит 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 - цей оператор встановлює рівень ізоляції наступної транзакції, глобально або тільки для поточного сеансу.

• SET TRANSACTION ISOLATION LEVEL
  (READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE)

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

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