Транзакции. Транзакцией называется выполнение последовательности команд (SQL-конструкций) в базе данных, которая либо фиксируется при успешной реализации каждой команды

Транзакцией называется выполнение последовательности команд (SQL-конструкций) в базе данных, которая либо фиксируется при успешной реализации каждой команды, либо отменяется при неудачном выполнении хотя бы одной команды. Большинство современных СУБД поддерживают механизм транзакций, и подавляющее большинство клиентских приложений, работающих с ними, используют для выполнения своих команд транзакции.

Возникает вопрос - зачем нужны транзакции? Представим себе, что в базу данных BDTur_firm2 требуется вставить связанные записи в две таблицы - «Туристы» и «Информация о туристах». Если запись, вставляемая в таблицу «Туристы», окажется неверной, например, из-за неправильно указанного кода туриста, база данных не позволит внести изменения, но при этом в таблице «Информация о туристах» появится ненужная запись. Рассмотрим такую ситуацию на примере.

Запустим Management Studio, в новом бланке введем запрос для добавления двух записей:

INSERT INTO Туристы ([Код туриста], Фамилия, Имя, Отчество)
VALUES (8, 'Тихомиров', 'Андрей', 'Борисович');
INSERT INTO [Информация о туристах]([Код туриста], [Серия паспорта], Город, Страна, Телефон, Индекс)
VALUES (8, 'CA 1234567', 'Новосибирск', 'Россия', 1234567, 996548);

Две записи успешно добавляются в базу данных:

(1 row(s) affected) //или (строк обработано: 1)
(1 row(s) affected) //или (строк обработано: 1)

Теперь спровоцируем ошибку - изменим код туриста только во втором запросе:

INSERT INTO Туристы ([Код туриста], Фамилия, Имя, Отчество)
VALUES (8, 'Тихомиров', 'Андрей', 'Борисович');
INSERT INTO [Информация о туристах]([Код туриста], [Серия паспорта], Город, Страна, Телефон, Индекс)
VALUES (9, 'CA 1234567', 'Новосибирск', 'Россия', 1234567, 996548);

Появляется сообщение о невозможности вставки первой записи с уже имеющимся значением ключевого поля. Вторая запись, однако, добавляется в таблицу:

Сообщение 2627, уровень 14, состояние 1, строка 1
Violation of PRIMARY KEY constraint ‘PK_Туристы'. Cannot insert duplicate key in object ‘dbo.Туристы'.
The statement has been terminated.
(строк обработано: 1)

Извлечем содержимое обеих таблиц следующим двойным запросом:

SELECT * FROM Туристы
SELECT * FROM [Информация о туристах]

В таблице «Информация о туристах» последняя запись добавилась безо всякой связи с записью таблицы «Туристы» (рис. 99). Для того чтобы избегать подобных ошибок, нужно применить транзакцию.

Рис. 99. Содержимое таблиц «Туристы» и «Информация о туристах» - нарушение связи

Удалим все внесенные записи из обеих таблиц и оформим исходные SQL-конструкции в виде транзакции:

BEGIN TRAN
DECLARE @OshibkiTabliciTourists int, @OshibkiTabliciInfoTourists int
INSERT INTO Туристы ([Код туриста], Фамилия, Имя, Отчество)
VALUES (8, 'Тихомиров', 'Андрей', 'Борисович');
SELECT @OshibkiTabliciTourists=@@ERROR
INSERT INTO [Информация о туристах]([Код туриста], [Серия паспорта],
Город, Страна, Телефон, Индекс)
VALUES (8, 'CA 1234567', 'Новосибирск', 'Россия', 1234567, 996548); SELECT @OshibkiTabliciInfoTourists=@@ERROR IF @OshibkiTabliciTourists=0 AND @OshibkiTabliciInfoTourists=0 COMMIT TRAN ELSE
ROLLBACK TRAN

Начало транзакции объявляется с помощью команды BEGIN TRAN. Далее создаются два параметра - @OshibkiTabliciTourists, OshibkiTabliciInfoTourists для сбора ошибок. После первого запроса возвращаем значение, которое встроенная функция @@ERROR присваивает первому параметру:

SELECT @OshibkiTabliciTourists=@@ERROR

То же самое делаем после второго запроса для другого параметра:

SELECT @OshibkiTabliciInfoTourists=@@ERROR

Проверяем значения обоих параметров, которые должны быть равными нулю при отсутствии ошибок:

IF @OshibkiTabliciTourists=0 AND @OshibkiTabliciInfoTourists=0

В этом случае подтверждаем транзакцию (в данном случае внесение изменений) при помощи команды COMMIT TRAN. В противном случае - если значение хотя бы одного из параметров @OshibkiTabliciTourists и @OshibkiTabliciInfoTourists оказывается отличным от нуля, отменяем транзакцию при помощи команды ROLLBACK TRAN.

После выполнения транзакции появляется сообщение о добавлении двух строк:

(строк обработано: 1)
(строк обработано: 1)

Снова изменим код туриста во втором запросе:

BEGIN TRAN
DECLARE @OshibkiTabliciTourists int, @OshibkiTabliciInfoTourists int
INSERT INTO Туристы ([Код туриста], Фамилия, Имя, Отчество)
VALUES (8, 'Тихомиров', 'Андрей', 'Борисович');
SELECT @OshibkiTabliciTourists=@@ERROR
INSERT INTO [Информация о туристах]([Код туриста], [Серия паспорта],
Город, Страна, Телефон, Индекс)
VALUES (9, 'CA 1234567', 'Новосибирск', 'Россия', 1234567, 996548);
SELECT @OshibkiTabliciInfoTourists=@@ERROR
IF @OshibkiTabliciTourists=0 AND @OshibkiTabliciInfoTourists=0
COMMIT TRAN
ELSE
ROLLBACK TRAN

Запускаем транзакцию - появляется в точности такое же сообщение, что и в случае применения обычных запросов:

Сообщение 2627, уровень 14, состояние 1, строка 3
Violation of PRIMARY KEY constraint ‘PK_Туристы'. Cannot insert duplicate key in object ‘dbo.Туристы'.
The statement has been terminated. (строк обработано: 1)

Однако теперь изменения не были внесены во вторую таблицу (рис. 100).

Рис. 100. Содержимое таблиц «Туристы» и «Информация о туристах» после выполнения неудачной транзакции

Сообщение (1 row(s) affected), указывающее на «добавление» одной записи, в данном случае оно всего лишь означает, что вторая SQL-конструкция была верной, и запись могла быть добавлена в случае успешного выполнения транзакции.

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

Дата добавления: 2015-05-09