---

Практическая работа: «Работа с транзакциями в MySQL Workbench»

Дисциплина: МДК11
Тема: Реализация транзакций и управление ими в среде MySQL Workbench
Цель работы: освоить инструменты MySQL Workbench для написания, отладки и контроля транзакций; научиться использовать автокоммит, точки сохранения и уровни изоляции, а также анализировать поведение блокировок.
Продолжительность: 2 академических часа (90 минут)
Среда выполнения: MySQL Server + MySQL Workbench, движок таблиц InnoDB
 
Подготовка к работе
Перед началом убедимся, что таблицы используют движок InnoDB — без него транзакции работать не будут. Выполните в окне запроса:
SHOW CREATE TABLE accounts;
Если в выводе указано ENGINE=InnoDB, всё в порядке. Если MyISAM, измените движок:
ALTER TABLE accounts ENGINE = InnoDB;
Создадим тестовую базу и таблицы. Сначала создайте и выберите базу:
CREATE DATABASE IF NOT EXISTS lab_transactions;
USE lab_transactions;
Не забудьте нажать обновление в столбце с БД!!! 
Затем создайте таблицу счетов:
CREATE TABLE accounts (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    balance DECIMAL(10, 2) NOT NULL DEFAULT 0.00
) ENGINE=InnoDB;
Создайте таблицу лога транзакций:
CREATE TABLE transactions_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    from_account INT,
    to_account INT,
    amount DECIMAL(10, 2),
    status ENUM('pending', 'completed', 'rolled_back') NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;
Заполните начальные данные:
INSERT INTO accounts (id, name, balance) VALUES
(1, 'Alice', 1000.00),
(2, 'Bob', 500.00),
(3, 'Charlie', 200.00);
Проверьте текущие настройки сессии — выполните:
SELECT @@autocommit, @@transaction_isolation;
Запишите значения — они понадобятся в отчёте.
Задание 1. Автокоммит и ручное управление транзакциями
Задача: разобраться, как Workbench обрабатывает автокоммит и как вручную управлять фиксацией и откатом транзакций.
Отключите автокоммит:
SET autocommit = 0;
Начните транзакцию:
START TRANSACTION;
Выполните перевод 200 рублей со счёта 1 на счёт 2:
UPDATE accounts SET balance = balance - 200 WHERE id = 1;
UPDATE accounts SET balance = balance + 200 WHERE id = 2;
Пока НЕ делайте COMMIT. Откройте вторую вкладку запроса (кнопка «+» вверху Workbench) — это будет независимая сессия — и выполните:
SELECT * FROM accounts;
Вы увидите, что во второй вкладке баланс Alice по-прежнему 1000, а Bob — 500. Изменения из первой сессии ещё не видны, потому что транзакция не зафиксирована.
Вернитесь в первую вкладку и выполните:
COMMIT;
Теперь во второй вкладке снова запустите:
SELECT * FROM accounts;
Баланс Alice станет 800, а Bob — 700. Изменения зафиксированы и видны всем.
Повторите эксперимент, но вместо COMMIT сделайте ROLLBACK. Начните новую транзакцию:
START TRANSACTION;
Выполните перевод 100 рублей со счёта 1 на счёт 3:
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 3;
Сделайте откат:
ROLLBACK;
Проверьте баланс — он вернулся к значениям после первого перевода. Все изменения отменены.
Что зафиксировать в отчёте:
•	Скриншоты: состояние баланса до транзакции, во время (во второй вкладке), после COMMIT, после ROLLBACK.
•	Объяснение: почему во второй вкладке изменения не видны до COMMIT.
Задание 2. Точки сохранения (SAVEPOINT)
Задача: научиться откатывать не всю транзакцию, а только часть операций.
Представьте сценарий: вы списываете деньги, зачисляете на другой счёт, а затем выполняете «рисковую» операцию, которая может не пройти. Если она не прошла — нужно откатить только её, сохранив первые две.
Начните транзакцию:
START TRANSACTION;
Операция 1 — списать 100 со счёта 1:
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
Операция 2 — зачислить 100 на счёт 3:
UPDATE accounts SET balance = balance + 100 WHERE id = 3;
Запишите в лог:
INSERT INTO transactions_log (from_account, to_account, amount, status)
VALUES (1, 3, 100, 'pending');
Поставьте точку сохранения перед рисковой операцией:
SAVEPOINT sp_before_risky;
Выполните рисковую операцию — списать ещё 50 со счёта 1:
UPDATE accounts SET balance = balance - 50 WHERE id = 1;
Допустим, операция оказалась неудачной — откатываемся к точке:
ROLLBACK TO SAVEPOINT sp_before_risky;
Альтернативное действие — зачисляем эти 50 на счёт 2:
UPDATE accounts SET balance = balance + 50 WHERE id = 2;
Обновляем статус в логе:
UPDATE transactions_log SET status = 'completed'
WHERE from_account = 1 AND to_account = 3;
Завершаем транзакцию:
COMMIT;
Чтобы лучше понять, что происходит, выполняйте скрипт по шагам. После ROLLBACK TO SAVEPOINT вставьте:
SELECT * FROM accounts;
Вы увидите, что списание 50 со счёта 1 отменено, а первые операции (перевод 100 и запись в лог) сохранены.
Что зафиксировать в отчёте:
•	Скриншоты баланса после ROLLBACK TO SAVEPOINT и после COMMIT.
•	Объяснение: чем ROLLBACK TO SAVEPOINT отличается от полного ROLLBACK и зачем это нужно.
Задание 3. Уровни изоляции транзакций
Задача: увидеть разницу между REPEATABLE READ и READ COMMITTED на практике.
Эксперимент 1: REPEATABLE READ
Откройте две отдельные вкладки в Workbench — это две независимые сессии.
В первой вкладке задайте уровень изоляции:
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Начните транзакцию:
START TRANSACTION;
Прочитайте баланс Alice:
SELECT id, name, balance FROM accounts WHERE id = 1;
Запишите результат (должно быть 800).
Во второй вкладке измените баланс и зафиксируйте:
UPDATE accounts SET balance = balance + 10 WHERE id = 1;
COMMIT;
Вернитесь в первую вкладку и снова выполните:
SELECT id, name, balance FROM accounts WHERE id = 1;
Баланс Alice по-прежнему 800 — хотя в базе он уже 810. При REPEATABLE READ транзакция «видит» данные так, какими они были на момент первого чтения.
Завершите транзакцию:
COMMIT;
Теперь выполните SELECT ещё раз — баланс станет 810.
Эксперимент 2: READ COMMITTED
В первой вкладке задайте другой уровень изоляции:
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
Начните транзакцию:
START TRANSACTION;
Прочитайте баланс:
SELECT id, name, balance FROM accounts WHERE id = 1;
Запишите баланс (810).
Во второй вкладке снова измените баланс и зафиксируйте:
UPDATE accounts SET balance = balance + 10 WHERE id = 1;
COMMIT;
Вернитесь в первую вкладку и выполните:
SELECT id, name, balance FROM accounts WHERE id = 1;
Теперь баланс уже 820 — при READ COMMITTED транзакция видит изменения, зафиксированные другими транзакциями, даже если первое чтение было раньше.
Завершите:
COMMIT;
Что зафиксировать в отчёте:
•	Скриншоты обоих чтений в первой вкладке для каждого эксперимента.
•	Объяснение: в чём разница между уровнями и какой уровень в каких ситуациях предпочтительнее.
Задание 4. Явные блокировки (FOR UPDATE)
Задача: увидеть, как FOR UPDATE удерживает блокировку и заставляет другие сессии ждать.
В первой вкладке отключите автокоммит:
SET autocommit = 0;
Начните транзакцию:
START TRANSACTION;
Заблокируйте строку счёта 1:
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
MySQL заблокировал строку счёта 1. Пока вы не сделаете COMMIT или ROLLBACK, никто другой не может изменить эту строку.
Во второй вкладке попробуйте выполнить то же самое:
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
Запрос «зависнет» — он будет ждать снятия блокировки. В Workbench это выглядит как крутящийся значок выполнения с надписью «Query in progress».
Вернитесь в первую вкладку и выполните обновление:
UPDATE accounts SET balance = balance - 50 WHERE id = 1;
Зафиксируйте:
COMMIT;
Сразу после COMMIT во второй вкладке запрос выполнится — вы увидите обновлённый баланс (770, так как 820 − 50 = 770).
Что зафиксировать в отчёте:
•	Скриншот «зависшего» запроса во второй вкладке.
•	Скриншот результата после COMMIT в первой вкладке.
•	Объяснение: зачем нужен FOR UPDATE и что было бы без него (две транзакции могли бы одновременно прочитать баланс и обе сделать списание — в итоге деньги «потерялись» бы).
Задание 5. Диагностика транзакций и блокировок
Задача: научиться видеть активные транзакции и блокировки через инструменты Workbench.
Повторите эксперимент из Задания 4: в первой вкладке выполните блокировку строки без COMMIT:
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
Затем в третьей вкладке выполните диагностический запрос — он покажет все активные транзакции с их идентификаторами, временем старта и состоянием:
SELECT * FROM information_schema.innodb_trx;
Если во второй вкладке запрос ждёт блокировку, выполните запрос, который покажет, кто кого ждёт:
SELECT * FROM information_schema.innodb_lock_waits;
Также в Workbench можно открыть визуальный монитор: меню Server → Dashboard, вкладка InnoDB. Там отображаются активные транзакции и блокировки в графическом виде.
После диагностики не забудьте завершить транзакцию в первой вкладке:
COMMIT;
Что зафиксировать в отчёте:
•	Скриншот результата information_schema.innodb_trx с активной транзакцией.
•	Скриншот Dashboard или innodb_lock_waits (если есть ожидание).
•	Краткое описание: что показывают эти инструменты и как они помогают при отладке.
 
Контрольные вопросы
1. Что означает ACID применительно к транзакциям в MySQL?
2. Чем отличается ROLLBACK от ROLLBACK TO SAVEPOINT? Приведите пример сценария для каждого.
3. Почему при REPEATABLE READ повторное чтение в одной транзакции даёт тот же результат, даже если данные уже изменились?
4. Что произойдёт, если в одной вкладке выполнить SELECT ... FOR UPDATE, а во второй — обычный SELECT (без FOR UPDATE)? Почему?
5. Что такое deadlock и как MySQL Workbench помогает его обнаружить?
Требования к отчёту
Отчёт должен содержать:
•	Цель работы и описание среды (версия MySQL, версия Workbench, движок InnoDB).
•	SQL-скрипты по каждому заданию — можно переписать из текста работы.
•	Скриншоты: баланс до и после операций (Задание 1); состояние после ROLLBACK TO SAVEPOINT (Задание 2); два чтения при разных уровнях изоляции (Задание 3); «зависший» запрос и результат после разблокировки (Задание 4); активная транзакция в information_schema (Задание 5).
•	Выводы по каждому заданию: что получилось, какие проблемы решаются с помощью транзакций.
•	Ответы на контрольные вопросы.
Критерии оценки
•	Отлично: все 5 заданий выполнены, отчёт содержит скрипты, скриншоты, чёткие выводы; ответы на вопросы полные и корректные.
•	Хорошо: задания выполнены, но есть небольшие неточности в отчёте или ответах.
•	Удовлетворительно: часть заданий выполнена, отчёт неполный.
•	Неудовлетворительно: задания не выполнены или отчёт отсутствует.
Методические подсказки
•	Каждая вкладка в Workbench — это отдельная сессия. Используйте это для эмуляции параллельных транзакций.
•	Не оставляйте транзакции незавершёнными. Если забыть COMMIT или ROLLBACK, блокировки будут удерживаться, а другие сессии — ждать.
•	Проверяйте движок таблицы. Если таблица на MyISAM, ROLLBACK не сработает, а FOR UPDATE не заблокирует строку.
•	Если Workbench выдаёт ошибку таймаута при ожидании блокировки — увеличьте innodb_lock_wait_timeout:
SET SESSION innodb_lock_wait_timeout = 50;
Это даст больше времени на наблюдение за «зависшим» запросом. 
Самостоятельное задание: «Списание бонусов и применение скидки»
Сценарий: В интернет магазине у клиента есть бонусный счёт и обычный баланс. При оформлении заказа система должна:
1.	Списать бонусы с бонусного счёта клиента.
2.	Начислить скидку на заказ (уменьшить итоговую сумму).
3.	Записать факт операции в журнал транзакций.
Если на любом шаге произойдёт ошибка (например, бонусов недостаточно или запись в журнал не прошла), вся операция должна быть отменена.
Подготовка данных
Создайте базу и таблицы (убедитесь, что движок — InnoDB):
sql
CREATE DATABASE IF NOT EXISTS shop_demo;
USE shop_demo;

CREATE TABLE customers (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    balance DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
    bonus_balance DECIMAL(10, 2) NOT NULL DEFAULT 0.00
) ENGINE=InnoDB;

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT,
    amount DECIMAL(10, 2),
    discount_amount DECIMAL(10, 2),
    final_amount DECIMAL(10, 2),
    status ENUM('pending', 'completed', 'cancelled') NOT NULL DEFAULT 'pending'
) ENGINE=InnoDB;

CREATE TABLE transactions_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT,
    operation_type VARCHAR(50),
    amount DECIMAL(10, 2),
    description VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;
Заполните начальные данные:
sql
INSERT INTO customers (id, name, balance, bonus_balance) VALUES
(1, 'Ivan', 5000.00, 1200.00);
________________________________________
Задание для студента
Выполните в MySQL Workbench следующую транзакцию. Не делайте COMMIT сразу, сначала прочитайте условие и попробуйте предсказать результат.
sql
SET autocommit = 0;
START TRANSACTION;

-- Шаг 1: проверяем, хватает ли бонусов (это можно сделать и в коде приложения,
-- но для тренировки сделаем проверку прямо в SQL через условие)
-- Для простоты считаем, что хотим списать 1000 бонусов.

-- Шаг 2: списываем бонусы
UPDATE customers
SET bonus_balance = bonus_balance - 1000
WHERE id = 1;

-- Шаг 3: создаём заказ на 4500 руб., применяем скидку 1000 руб. за счёт бонусов
INSERT INTO orders (customer_id, amount, discount_amount, final_amount, status)
VALUES (1, 4500.00, 1000.00, 3500.00, 'pending');

-- Получаем ID только что созданного заказа
SET @last_order_id = LAST_INSERT_ID();

-- Шаг 4: записываем операцию в лог
INSERT INTO transactions_log (customer_id, operation_type, amount, description)
VALUES (1, 'BONUS_WRITE_OFF', 1000.00, CONCAT('Списание бонусов для заказа ', @last_order_id));

-- Здесь представьте, что произошла ошибка (например, платёжный шлюз не ответил).
-- Чтобы имитировать ошибку, намеренно **не делайте COMMIT**, а вместо этого выполните:
ROLLBACK;
Что нужно сделать студенту
1.	Запустить скрипт по частям. Сначала только START TRANSACTION и первый UPDATE, затем проверить баланс бонусов. Потом добавить INSERT в orders, проверить, появился ли заказ. Потом добавить вставку в transactions_log.
2.	Выполнить ROLLBACK (как в примере) и убедиться, что:
o	Бонусы вернулись к исходному значению.
o	Заказ не остался в таблице orders (или его статус можно поменять на cancelled).
o	Запись в transactions_log исчезла.
3.	Повторить, но в конце сделать COMMIT вместо ROLLBACK — убедиться, что все изменения сохранились.
4.	Добавить проверку на отрицательный баланс бонусов. Например, обернуть логику в условие или использовать ограничение на уровне БД (CHECK-ограничение в новых версиях MySQL или проверку в хранимой процедуре).
________________________________________
Контрольные вопросы к заданию
1.	Почему важно, чтобы списание бонусов, создание заказа и запись в лог были в одной транзакции?
2.	Что произойдёт, если забыть ROLLBACK при ошибке? Какие проблемы это вызовет в учёте бонусов и заказов?
3.	Как бы вы защитили систему от списания большего количества бонусов, чем есть у клиента (валидация)?
4.	Если бы вместо ROLLBACK вы сделали COMMIT, а потом обнаружили ошибку, можно ли было бы «откатить» эту транзакцию? Почему да или нет?
5.	Какой уровень изоляции вы бы выбрали для таких операций и почему?
________________________________________
Подсказки и типичные ошибки
•	Забыли отключить автокоммит. Если autocommit = 1, каждый UPDATE и INSERT фиксируется сразу, и ROLLBACK не отменит предыдущие шаги.
•	Таблица не InnoDB. В MyISAM ROLLBACK ничего не делает. Проверьте: SHOW CREATE TABLE customers;.
•	Неверный порядок операций. Сначала лучше проверять условия (хватает ли бонусов), потом менять данные, потом писать в лог.
•	Потеря ID заказа. Если вы делаете несколько INSERT, используйте LAST_INSERT_ID() сразу после вставки в orders.