Цель работы

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

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

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

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

Транзакция – последовательность операторов 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 без указания имени точки сохранения.