13. Транзакции

В реляционных базах данных защита целостности данных реализована через механизмы транзакций и блокировки таблиц.

Транзакция – группа операций, которая может быть выполнена либо полностью успешно, с обеспечением целостности данных и независимо от параллельно выполняющихся других транзакций, либо не выполнена вообще, и тогда она не должна произвести вообще никакого эффекта. Транзакция похожа на беспосадочный перелет через океан тем, что она не может быть выполнена наполовину. Если видим, что не долететь, то поворачиваем назад. Сервер изолирует одновременные потоки друг от друга, ограничивая их доступ к модифицируемым данным. Сервер пытается выполнять транзакции так, будто они происходят последовательно, а не параллельно.

В конце транзакции происходит либо ее отмена, либо подтверждение. Отмена транзакции называется откатом (rollback). Подтверждение транзакции называется фиксацией (commit). Фиксация транзакции регистрируется в специальных журнальных файлах, информация из которых считывается при сбоях в работе компьютера и используется для восстановления состояния базы данных до сбоя.

В MySQL можно создавать базы данных на основе таблиц нескольких типов. Транзакции применимы лишь к некоторым типам таблиц.

В MySQL 3 по умолчанию создаются таблицы типа MYISAM, в которых обеспечение целостности данных при одновременных запросах происходит за счет блокировки таблиц целиком в случае необходимости.

В MySQL 5 по умолчанию создаются таблицы типа InnoDB (при работе в Windows), в которых реализован механизм транзакций.

Чтобы создать таблицу типа InnoDB, можно указать ENGINE = InnoDB или

TYPE = InnoDB в SQL-запросе на создание таблицы:

mysql>CREATE TABLE customers (a INT, b CHAR (20), INDEX (a))

->ENGINE=InnoDB;

mysql>CREATE TABLE customers (a INT, b CHAR (20), INDEX (a))

->TYPE=InnoDB;

По умолчанию каждый клиент соединяется с сервером MySQL в режиме autocommit, в котором автоматически фиксируется каждый одиночный SQL-запрос клиента. Для того чтобы использовать транзакции, состоящие из нескольких запросов, нужно подать команду

mysql>SET AUTOCOMMIT = 0;

а затем использовать команды COMMIT или ROLLBACK для фиксации или отмены транзакции. Следующие два примера демонстрируют фиксацию и отмену транзакций:

Вставка данных о Париже прошла успешно, в этом можно убедиться, запросив выборку из таблицы до отката. Но в результате отката эти данные не были зафиксированы в базе и не были сохранены, что и показывает последняя выборка. То есть мы можем посмотреть какой эффект произведет то или иное действие, а потом отменить его – произвести откат.

Приложение

Краткие справочные сведения по MySQL

AUTO_INCREMENT – атрибут, используемый для создания поля с уникальными значениями. В каждой таблице может быть не больше одного такого поля.

CREATE DATABASE name (name – имя создаваемой базы) - создание базы данных.

CREATE TABLE name - команда для создания таблиц. Указывается не только название создаваемой таблицы, но и ее полное определение, состоящее из определений отдельных полей; (name – имя создаваемой таблицы).

DEFAULT - после этого ключевого слова указывается значение по умолчанию для данного поля.

DELETE – команда удаления записей из таблицы. DELETE FROM table_name; (table_name – имя таблицы).

DESCRIBE table_name – команда просмотра структуры таблицы; (table_name – имя таблицы).

DISTINCT - параметр в запросе SELECT, дающий возможность исключить повторяющиеся данные из результатов запроса.

FOREIGN KEY – внешний ключ. Назначение этого ключа – проверять соответствие значений в столбцах родительского и внешнего ключей. Необходимо, чтобы перед созданием внешних ключей на этих столбцах были созданы индексы. Индекс (INDEX) формируется из значений одного или нескольких столбцов таблицы и позволяет ускорить поиск нужной строки по заданному значению. Для ускорения запросов индексы обычно создаются на тех столбцах таблицы, которые часто используются в запросах.

FROM – ключевое слово, которое должно быть в каждом запросе. За ним следуют имена таблиц, используемых в качестве источника информации.

GRANT – команда, позволяющая создавать пользователей сервера а также предоставлять права пользователям.

INSERT – оператор, предназначенный для внесения данных в таблицы.

Два способа ввода данных:

1. внесение данных во все поля таблицы.

INSERT INTO table_name VALUES (‘’, ’’, ‘’…)

Каждое значение в кавычках (‘’);

2. добавление данных только в избранные поля, при этом нужно указать имена полей явно.

INSERT INTO table_name (имя поля 1,…,…)VALUES (‘’, ’’, ‘’…)

NOT NULL - атрибут означающий, что все строки таблицы должны иметь значение в этом столбце. Если этот атрибут не указан, то поле может содержать пустое значение (NULL).

ORDER BY – конструкция, используемая для сортировки полей результата. ORDER BY name_pole DESC - сортировка по убыванию значений в указанном поле. LIMIT – служит для указания, сколько строк результата следует отображать.

REVOKE – команда, позволяющая лишать пользователя привилегий.

SELECT – ключевое слово в запросе на извлечение данных.

SHOW TABLES - команда просмотра списка таблиц в базе.

UPDATE – команда изменения значений поля. UPDATE имя_таблицы SET имя_поля = ‘новое_значение’ WHERE имя_поля = ‘старое значение’ .

USE database_name – команда, при помощи которой выполняется выбор базы данных для выполнения дальнейших действий над ней; (database_name – имя базы данных).

WHERE – предложение, позволяющее задать критерий отбора строк.

Знаки операций сравнения для конструкции WHERE:

Оператор	Название	Описание
=	Равенство	Проверяется, равенство двух значений
>	Больше	Проверяется, что левое значение больше правого
<	Меньше	Проверяется, что левое значение меньше правого
>=	Больше или равно	Проверяется, что левое значение не меньше правого
<=	Меньше или равно	Проверяется, что левое значение не больше правого
! = или <>	Не равно	Проверяется неравенство двух значений
IS NOT NULL		Проверяется наличие значения в поле
IS NULL		Проверяется, отсутствие значения в поле
BETWEEN	Величина между	Проверяется, попадание значения в заданный диапазон
IN	Значение содержится	Проверяется, попадание значения в заданное множество
NOT IN	Значение не содержится	Проверяется, отсутствие значения в заданном множестве
LIKE	Соответствие	Проверяется, соответствие значения образцу
NOT LIKE	Не соответствие	Проверяется, несоответствие значения образцу
REGEXP	Регулярное выражение	Проверяется соответствие значения регулярному выражению

Агрегатные Функции

AVG (имя_поля) – выдает среднее значение в указанном столбце.

COUNT (имя_поля) - при указании столбца выдает число ненулевых значений в этом столбце. Если перед именем столбца указать DISTINCT, то выдается только количество различных значений.

COUNT(*) – выдает число строк в таблице, но если указать COUNT(name_pole) – подсчитать число значений в заданном поле, в котором есть значения NULL, то пустые значения учтены не будут (Name_pole – имя поля).

MAX (имя_поля) – максимальное значение в столбце.

MIN (имя_поля) – минимальное значение в столбце.

SUM (имя_поля) – сумма значений в столбце.

Список литературы

1. Бенкен Е.С.

PHP, MySQL, XML: программирование для Интернета: 2-е изд. перераб. и доп. – СПб.: БВХ-Петербург, 2008. – 352 с.: ил. + CD-ROM

2. Кузнецов М.В., Симдянов И.В.

MySQL на примерах. – СПб.: БВХ-Петербург, 2007. – 592с.: ил. +CD-ROM

3. Сайт www.mysql.ru.