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

Транзакцией называется выполнение последовательности команд (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-конструкция была верной, и запись могла быть добавлена в случае успешного выполнения транзакции.

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

Транзакции в ADO .NET

Перейдем теперь к рассмотрению транзакций в ADO .NET. Создадим новое консольное приложение Easy Transaction. Поставим задачу: передать те же самые данные в две таблицы - «Туристы» и «Информация о туристах».

Полный листинг данного приложения выглядит следующим образом:

using System;
using System.Data.SqlClient;
namespace EasyTransaction {
class Class1 {
          [STAThread]
          static void Main(string[] args)
          {
            //Создаем соединение
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = @"Data Source=.SQLEXPRESS;AttachDbFilename=" +
                        @"D:ВМИFor ADOBDTur_firmSQL2.mdf" +
                        "integrated Security=True;Connect Timeout=30;User Instance=True";
            conn.Open();
            SqlCommand myCommand = conn.CreateCommand();
            //Создаем транзакцию
            myCommand.Transaction = conn.BeginTransaction(System.Data.IsolationLevel.Serializable);
            try {
                        myCommand.CommandText =
                                   "INSERT INTO Туристы ([Код туриста], Фамилия, Имя, Отчество) " +
                                   "VALUES (9, 'Тихомиров', 'Андрей', 'Борисович')";
                        myCommand.ExecuteNonQuery();
                        myCommand.CommandText = "INSERT INTO [Информация о туристах]" +
                           " ([Код туриста], [Серия паспорта], Город, Страна, Телефон, Индекс) " +
                           "VALUES (9, 'CA 1234567', 'Новосибирск', 'Россия', 1234567, 996548)";
                        myCommand.ExecuteNonQuery();
//Подтверждаем транзакцию
                        myCommand.Transaction.Commit();
                        Console.WriteLine("Передача данных успешно завершена");
            }
            catch(Exception ex) {
//Отклоняем транзакцию
                        myCommand.Transaction.Rollback();
                        Console.WriteLine("При передаче данных произошла ошибка: "+ ex.Message);
            }
            finally {
                        conn.Close();
            }
          } end Main
} end Class
} end namespace

Перед запуском приложения снова удаляем все добавленные записи из таблиц. При успешном выполнении запроса появляется соответствующее сообщение, а в таблицы добавляются записи (рис. 101).

Рис. 101. Приложение EasyTransaction. Транзакция выполнена

Повторный запуск этого приложения приводит к отклонению транзакции - нельзя вставлять записи с одинаковыми значениями первичных ключей (рис. 102).

Рис. 102. Приложение EasyTransaction. Транзакция отклонена

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

//Создаем соединение
... см. в примере приложения EasyTransaction
//Создаем транзакцию
myCommand.Transaction = conn.BeginTransaction();
try {
          //Выполняем команды, вызываем одну или несколько хранимых процедур
          //Подтверждаем транзакцию
          myCommand.Transaction.Commit();
}
catch(Exception ex) {
          //Отклоняем транзакцию
          myCommand.Transaction.Rollback();
}
finally {
          //Закрываем соединение
          conn.Close();
}

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

1. Dirty reads - «грязное» чтение. Первый пользователь начинает транзакцию, изменяющую данные. В это время другой пользователь (или создаваемая им транзакция) извлекает частично измененные данные, которые не являются корректными.

2. Non-repeatable reads - неповторяемое чтение. Первый пользователь начинает транзакцию, изменяющую данные. В это время другой пользователь начинает и завершает другую транзакцию. Первый пользователь при повторном чтении данных (например, если в его транзакцию входит несколько инструкций SELECT) получает другой набор записей.

3. Phantom reads - чтение фантомов. Первый пользователь начинает транзакцию, выбирающую данные из таблицы. В это время другой пользователь начинает и завершает транзакцию, вставляющую или удаляющую записи. Первый пользователь получит другой набор данных, содержащий фантомы - удаленные или измененные строки.

Для решения этих проблем разработаны четыре уровня изоляции транзакции:

1. Read uncommitted. Транзакция может считывать данные, с которыми работают другие транзакции. Применение этого уровня изоляции может привести ко всем перечисленным проблемам.

2. Read committed. Транзакция не может считывать данные, с которыми работают другие транзакции. Применение этого уровня изоляции исключает проблему «грязного» чтения.

3. Repeatable read. Транзакция не может считывать данные, с которыми работают другие транзакции. Другие транзакции также не могут считывать данные, с которыми работает эта транзакция. Применение этого уровня изоляции исключает все проблемы, кроме чтения фантомов.

4. Serializable. Транзакция полностью изолирована от других транзакций. Применение этого уровня изоляции полностью исключает все проблемы.

По умолчанию установлен уровень Read committed. В справке Microsoft SQL Server 2005 приводится таблица, иллюстрирующая различные уровни изоляции (рис. 103).

Рис. 103. Уровни изоляции Microsoft SQL Server 2005

Использование наибольшего уровня изоляции (Serializable) означает наибольшую безопасность и вместе с тем наименьшую производительность - все транзакции выполняются в виде серии, последующая вынуждена ждать завершения предыдущей. И наоборот, применение наименьшего уровня (Read uncommitted) означает максимальную производительность и полное отсутствие безопасности. Впрочем, нельзя дать универсальных рекомендаций по применению этих уровней - в каждой конкретной ситуации решение будет зависеть от структуры базы данных и характера выполняемых запросов.

Для установки уровня изоляции применяется следующая команда:

SET TRANSACTION ISOLATION LEVEL
READ UNCOMMITTED
или   READ COMMITTED
или   REPEATABLE READ
или    SERIALIZABLE

Например, в транзакции, добавляющей две записи, уровень изоляции указывается следующим образом:

BEGIN TRAN SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
DECLARE @OshibkiTabliciTourists int, @OshibkiTabliciInfoTourists int
...
ROLLBACK TRAN

В ADO .NET уровень изоляции можно установить при создании транзакции:

myCommand.Transaction = conn.BeginTransaction(System.Data.IsolationLevel.Serializable); Дополнительно поддерживаются еще два уровня (рис. 104):

1. Chaos. Транзакция не может перезаписать другие непринятые транзакции с большим уровнем изоляции, но может перезаписать изменения, внесенные без использования транзакций. Данные, с которыми работает текущая транзакция, не блокируются;

2. Unspecified. Отдельный уровень изоляции, который может применяться, но не может быть определен. Транзакция с этим уровнем может применяться для задания собственного уровня изоляции.

Рис. 104. Определение уровня транзакции

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