Лабораторная #4 по базам данных

Лабораторная работа №4. Использование транзакций

Цель работы: Используя данные базы данных, подготовленной в предыдущей лабораторной работе, подготовить и реализовать серию запросов, связанных с выборкой информации и модификацией данных таблиц.

Теоретические сведения

Изменения БД часто требуют выполнения нескольких запросов, например при покупке в электронном магазине требуется добавить запись в таблицу заказов и уменьшить число товарных позиций на складе. В промышленных БД одно событие может затрагивать большее число таблиц и требовать многочисленных запросов.

Если на этапе выполнения одного из запросов происходит сбой, это может нарушить целостность БД (товар может быть продан, а число товарных позиций на складе не обновлено). Чтобы сохранить целостность БД, все изменения должны выполняться как единое целое. Либо все изменения успешно выполняются, либо, в случае сбоя, БД принимает состояние, которое было до начала изменений. Это обеспечивается средствами обработки транзакций.

Транзакция– последовательность операторов SQL, выполняющихся как единая операция, которая не прерывается другими клиентами. Пока происходит работа с записями таблицы (обновление или удаление), никто другой не может получить доступ к этим записям, т. к. MySQL автоматически блокирует доступ к ним.

Таблицы ISAM, MyISAM и HEAP не поддерживают транзакции. В настоящий момент их поддержка осуществляется только в таблицах BDB и InnoDB.

Транзакции позволяют объединять операторы в группу и гарантировать, что все операторы группы будут выполнены успешно. Если часть транзакции выполняется со сбоем, результаты выполнения всех операторов транзакции до места сбоя отменяются, приводя БД к виду, в котором она была до выполнения транзакции.

Следующие операторы неявно завершают транзакцию (как если бы перед их выполнением был выдан COMMIT):

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

UNLOCK TABLES также завершает транзакцию, если какие-либо таблицы были блокрованы. До MySQL 4.0.13 CREATE TABLE завершал транзакцию, если была бы включена бинарная регистрация. Транзакции не могут быть вложенными. Это следствие того, что неявный COMMIT выполняется для любой текущей транзакции, когда выполняется оператор start TRANSACTION или его синонимы.

По умолчанию MySQL работает в режиме автоматического завершения транзакций, т. е. как только выполняется оператор обновления данных, который модифицирует таблицу, изменения тут же сохраняются на диске. Чтобы объединить операторы в транзакцию, следует отключить этот режим: set AUTOCOMMIT=0;

После отключения режима для завершения транзакции необходимо ввести оператор COMMIT, для отката – ROLLBACK.

Отключить режим автоматического завершения транзакций для отдельной последовательности операторов можно оператором START TRANSACTION.

Для таблиц InnoDB есть операторы savepoint и rollback to savepoint, которые позволяют работать с именованными точками начала транзакции. Пример:

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO catalogs UALUES(NULL,'Периферия');
Query OK, 1 row affected (0.00 sec)

mysql> SAVEPOINT point1;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO catalogs VALUES(NULL,'Разное');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM CATALOGS;

cat_ID	cat_name
1	Программирование
2	Интернет
3	азы данных
4	Сети
5	Мультимедиа
12	Периферия
13	Разное
7 rows in set (0.00 sec)

mysql> ROLLBACK TO SAVEPOINT point1;
Query OK, 0 rows affected (0.02 sec)

mysql> SELECT * FROM catalogs;
cat_ID	cat_name
1	Программирование
2	Интернет
3	Базы данных
4	Сети
5	Мультимедиа
12	Периферия
6 rows in set (0.00 sec)
В данном примере оператор savepoint устанавливает именованную точку начала транзакции с именем point1. Оператор rollback to save point point1откатывает транзакцию к состоянию, в котором находилась БД на момент установки именованной точки. Все точки сохранения транзакций удаляются, если выполняются операторы commit или rollback без указания имени точки сохранения.

Задание

  • Используя базу, полученную в лабораторной 2, создать транзакцию, произвести ее откат и фиксацию. Показать, что данные существовали до отката, удалились после отката, снова были добавлены, и затем были успешно зафиксированы.

Пример выполнения работы

Для выполнения задания объединим несколько операций по добавлению в таблицу catalogs новых каталогов, а затем произведем откат транзакции, т. е. отмену произведенных действий. Отключаем режим автоматического завершения, добавляем новые записи и проверяем, добавились записи или нет.
mysql> SET AUTOCOMMIT=0;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO catalogs VALUES(NULL,'Аппаратура' );
Query OK, 1 row affected <0.06 sec)

mysql> INSERT INTO catalogs VALUES(NULL,'Безопасность')
Query OK, 1 row affected <0.00 sec)

mysql> SELECT * FROM catalogs;

cat_ID	cat_name
1	Программирование
2	Интернет
3	Базы данных
4	Сети
5	Мультимедиа
6	Аппаратура
7	Безопасность
7 rows in set (0.00 sec)
Откатываем транзакцию оператором ROLLBACK(изменения не сохранились).
mysql> ROLLBACK;
Query OK, 0 rows affected (0.03 sec)

mysql> SELECT * FROM catalogs;
cat_ID	cat_nane
1	Программирование
2	Интернет
3	Базы данных
4	Сети
5	Мультимедиа
5 rows in set (0.00 sec)
Воспроизведем транзакцию и сохраним действия оператором COMMIT.
mysql> INSERT INTO catalogs VALUES(NULL,'Аппаратура' );
Query OK, 1 row affected (0.06 sec)

mysql> INSERT INTO catalogs VALUES(NULL,'Безопасность')
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM catalogs;
cat_ID	cat_nane
1	Программирование
2	Интернет
3	Базы данных
4	Сети
5	Мультимедиа
8	Аппаратура
9	Безопасность
7 rows in set (0.00 sec)

mysql> COMMIT;
Query OK, 0 row affected (0.00 sec)

mysql> SELECT * FROM catalogs;
cat_ID	cat_nane
1	Программирование
2	Интернет
3	Базы данных
4	Сети
5	Мультимедиа
8	Аппаратура
9	Безопасность
7 rows in set (0.00 sec)

Контрольные вопросы

  1. Что такое транзакция?
  2. Какие запросы допустимы внутри тразакции?
  3. К чему приведёт использование DDL запроса внутри транзакции?