Транзакции в Mysql

Федеральное агентство связи
ФЕДЕРАЛЬНОЕ ГОСУДАРСТВЕННОЕ БЮДЖЕТНОЕ 
ОБРАЗОВАТЕЛЬНОЕ УЧРЕЖДЕНИЕ ВЫСШЕГО ОБРАЗОВАНИЯ 
«САНКТ-ПЕТЕРБУРГСКИЙ ГОСУДАРСТВЕННЫЙ УНИВЕРСИТЕТ 
ТЕЛЕКОММУНИКАЦИЙ ИМ. ПРОФ. М. А. БОНЧ-БРУЕВИЧА»
(СПбГУТ)
Факультет инфокоммуникационных сетей и систем
Кафедра фотоники и линий связи
ЛАБОРАТОРНАЯ РАБОТА №4
по дисциплине «Базы данных»
на тему «Транзакции в MySQL»
Выполнили:
студенты 3-го курса
дневного отделения
группы ИКПИ-85
Ежуров Антон Павлович
Коваленко Леонид Александрович
Преподаватель:
ассистент кафедры ПИиВТ
Овчинников Антон Олегович
Санкт-Петербург
2020

Цель работы
Получить практические навыки в работе с транзакциями и их 
свойствами.
Ход работы
Создадим базу данных
university
в программе-дизайнере MySQL 
Workbench. В этой базе данных создадим таблицу 
users
с полями:
1. 
id
типа 
INT
, первичный ключ (PK), счетчик (AI);
2. 
name
типа 
VARCHAR
, ненулевое (NN);
3. 
department_id
типа 
INT
.
Заполним таблицу 
users
произвольными пятью записями (табл. 1).
Таблица 1 — Таблица 
users
базы данных 
university
id
name
department_id
1
Алексей
1
2
Михаил
2
3
Елена
3
4
Василий
4
5
Владимир
5
Сохраним созданную в программе-дизайнере схему базы данных на 
локальный компьютер.
Запустим генерацию базы данных на сервере MySQL (Пункт меню: 
Database → Forward Engineer
). В опциях поставим галки напротив пунктов 
Generate INSERT statements for tables
и 
DROP objects before each CREATE 
object
.
Подключимся к базе данных MySQL (команда
mysql -u root -p
) и 
активизируем базу данных 
university
(команда 
use university
).
В командной строке выполним следующие команды (рис. 1):
SET AUTOCOMMIT=0;
# Отключение режима автокоммита
SELECT * FROM users;
# Выводим 5 строк из таблицы users
START TRANSACTION;
# Начинаем транзакцию
SAVEPOINT sve_point;
# Сохраняем текущее состояние как sve_point
DELETE FROM users;
# Удаляем все строки таблицы users
SELECT * FROM users;
# Выводим 0 строк из таблицы users
ROLLBACK TO SAVEPOINT sve_point;
# Возвращаемся к sve_point
2

SELECT * FROM users;
# Выводим 5 строк из таблицы users
DELETE FROM users;
# Удаляем все строки таблицы users
SELECT * FROM users;
# Выводим 0 строк из таблицы users
exit;
# Выход из mysql
Подключимся к базе данных MySQL (команда
mysql -u root -p
) и 
активизируем базу данных 
university
(команда 
use university
).
В командной строке выполним следующие команды (рис. 2):
SET AUTOCOMMIT=0;
# Отключение режима автокоммита
SELECT * FROM users;
# Выводим 5 строк из таблицы users
В командной строке выполним следующие команды (рис. 3):
3
Рисунок 1 — Результат выполнения команд
Рисунок 2 — Результат выполнения команд

START TRANSACTION;
# Начинаем транзакцию
# Добавляем в таблицу users новую строку
INSERT INTO users (id, name, department_id) VALUES (100, 
'Antonio', 1);
COMMIT;
# Сохранение изменений
SELECT * FROM users;
# Выводим 6 строк из таблицы users
exit;
# Выход из mysql
Подключимся к базе данных MySQL (команда
mysql -u root -p
) и 
активизируем базу данных 
university
(команда 
use university
).
В командной строке выполним команду (рис. 4):
SELECT * FROM users;
# Выводим 6 строк из таблицы users
4
Рисунок 3 — Результат выполнения команд
Рисунок 4 — Результат выполнения команд

Выводы
Транзакция — минимальная логически осмысленная операция, которая 
имеет смысл и может быть совершена только полностью.
Транзакция начинается со специального запроса 
START TRANSACTION
.
Чтобы закончить транзакцию, нужно либо зафиксировать изменения 
(запрос 
COMMIT
), либо откатить их (запрос 
ROLLBACK
).
В MySQL не существует механизма вложенных транзакций. Одно 
соединение с БД — одна транзакция. Новая транзакция в пределах одного 
соединения может начаться только после завершения предыдущей.
ACID описывает требования к транзакционной системе (например, к 
СУБД), обеспечивающие наиболее надёжную и предсказуемую её работу.
Atomicity — Атомарность. Атомарность гарантирует, что никакая 
транзакция не будет зафиксирована в системе частично.
Consistency — Согласованность. Транзакция, достигающая своего 
нормального завершения (EOT — end of transaction, завершение транзакции) 
и, тем самым, фиксирующая свои результаты, сохраняет согласованность 
базы данных. Например, в банковской системе может существовать 
требование равенства суммы, списываемой с одного счёта, сумме, 
зачисляемой на другой.
Isolation — Изолированность. Во время выполнения транзакции 
параллельные транзакции не должны оказывать влияния на её результат.
Durability — Стойкость. Независимо от проблем на нижних уровнях (к 
примеру, обесточивание системы или сбои в оборудовании) изменения, 
сделанные успешно завершённой транзакцией, должны остаться 
сохранёнными после возвращения системы в работу.
У транзакций есть 4 уровня изоляции:
0 — Чтение неподтверждённых данных (грязное чтение) (Read 
Uncommitted, Dirty Read) — самый низкий уровень изоляции. При этом 
уровне возможно чтение незафиксированных изменений параллельных 
транзакций. Как раз в этом случае второй пользователь увидит вставленную 
запись из первой незафиксированной транзакции. Нет гарантии, что 
5

незафиксированная транзакция будет в любой момент откачена, поэтому 
такое чтение является потенциальным источником ошибок.
1 — Чтение подтверждённых данных (Read Committed) — здесь 
возможно чтение данных только зафиксированных транзакций. Но на этом 
уровне существуют две проблемы. В этом режиме строки, которые 
участвуют в выборке в рамках транзакции, для других параллельных 
транзакций не блокируются, из этого вытекает проблема №1: 
«Неповторяемое чтение» (non-repeatable read) — это ситуация, когда в рамках 
транзакции происходит несколько выборок (
SELECT
) по одним и тем же 
критериям, и между этими выборками совершается параллельная транзакция, 
которая изменяет данные, участвующие в этих выборках. Так как 
параллельная транзакция изменила данные, результат при следующей 
выборке по тем же критериям в первой транзакции будет другой. Проблема 
№2 — «Фантомное чтение» — этот случай рассмотрен ниже.
2 — Повторяемое чтение (Repeatable Read, Snapshot) — на этом уровне 
изоляции так же возможно чтение данных только зафиксированных 
транзакций. Так же на этом уровне отсутствует проблема «Неповторяемого 
чтения», то есть строки, которые участвуют в выборке в рамках транзакции, 
блокируются и не могут быть изменены другими параллельными 
транзакциями. Но таблицы целиком не блокируются. Из-за этого остается 
проблема «фантомного чтения». «Фантомное чтение» — это когда за время 
выполнения одной транзакции результат одних и тех же выборок может 
меняться по причине того, что блокируется не вся таблица, а только те 
строки, которые участвуют в выборке. Это означает, что параллельные 
транзакции могут вставлять строки в таблицу, в которой совершается 
выборка, поэтому два запроса
SELECT * FROM table
могут дать разный 
результат в разное время при вставке данных параллельными транзакциями.
3 — Сериализуемый (Serializable) — сериализуемые транзакции. 
Самый надежный уровень изоляции транзакций, но и при этом самый 
медленный. На этом уровне вообще отсутствуют какие-либо проблемы 
6

параллельных транзакций, но за это придется платить быстродействием 
системы, а быстродействие в большинстве случаев крайне важно.
По умолчанию в MySQL установлен уровень изоляции №2 (Repeatable 
Read). Разработчики MySQL не зря сделали по умолчанию именно этот 
уровень, так как он наиболее оптимальный для большинства случаев.
Если
AUTOCOMMIT
установлен в значение
false
(
0
) и транзакция не 
выполнена, изменения будут видны только для текущего соединения. После 
того, как инструкция 
COMMIT
фиксирует изменения в таблице, результат будет 
видимым для всех подключений. Если значение
AUTOCOMMIT
установлено 
верно, то 
COMMIT
и 
ROLLBACK
бесполезны.
7