Технология Microsoft ADO.NET
       

Работа с транзакциями


Транзакцией называется выполнение последовательности команд (SQL-конструкций) в базе данных, которая либо фиксируется при успешном извлечении каждой команды, либо отменяется при неудачном извлечении хотя бы одной команды. Большинство современных СУБД поддерживают механизм транзакций, и подавляющее большинство клиентских приложений, работающих с ними, используют для выполнения команд транзакции. Зачем нужны транзакции? Представим себе, что в базу данных BDTur_firm2 требуется вставить связанные записи в две таблицы - "Туристы" и "Информацияотуристах". Если запись, вставляемая в таблицу "Туристы", окажется неверной, например, из-за неправильно указанного кода туриста, база данных не позволит внести изменения, а тогда в таблице "Информацияотуристах" появится ненужная запись. Запускаем SQL Query Analyzer, в новом бланке вводим запрос для добавления двух записей:

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

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

(1 row(s) affected)

(1 row(s) affected)

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

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

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

Server: Msg 2627, Level 14, State 1, Line 1 Violation of PRIMARY KEY constraint 'PK_Туристы'. Cannot insert duplicate key in object 'Туристы'. The statement has been terminated. (1 row(s) affected)

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

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


В таблице "Информацияотуристах" последняя запись добавилась безо всякой связи с записью таблицы "Туристы" (рис. 7.8):


увеличить изображение
Рис. 7.8.  Содержимое таблиц "Туристы" и "Информацияотуристах"

Для того чтобы избегать подобных ошибок, нам нужно применить транзакцию. Удалим все внесенные записи из обеих таблиц (это можно сделать с помощью запроса или в SQL Server Enterprise Manager) и оформим исходные SQL-конструкции в виде транзакции:



BEGIN TRAN DECLARE @OshibkiTabliciTourists int, @OshibkiTabliciInfoTourists int INSERT INTO Туристы (Кодтуриста, Фамилия, Имя, Отчество) VALUES (6, 'Тихомиров', 'Андрей', 'Борисович'); SELECT @OshibkiTabliciTourists=@@ERROR INSERT INTO Информацияотуристах(Кодтуриста, Серияпаспорта, Город, Страна, Телефон, Индекс) VALUES (6, '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 и @Oshibki TabliciInfoTourists оказывается отличным от нуля, отменяем транзакцию при помощи команды ROLLBACK TRAN.

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

(1 row(s) affected)

(1 row(s) affected)



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

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

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

Server: Msg 2627, Level 14, State 1, Line 1 Violation of PRIMARY KEY constraint 'PK_Туристы'. Cannot insert duplicate key in object 'Туристы'. The statement has been terminated.

(1 row(s) affected)

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


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

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

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

Появляется аналогичное сообщение:



(1 row(s) affected)

Server: Msg 2627, Level 14, State 1, Line 1 Violation of PRIMARY KEY constraint 'PK_Информацияотуристах'. Cannot insert duplicate key in object 'Информацияотуристах'. The statement has been terminated.

Изменения снова не были внесены в базу данных - в этом можно убедиться, вернув содержимое обеих таблиц. Читатель, хорошо знакомый с теорией баз данных, может заметить, что обеспечить целостность данных двух таблиц (в данном случае это именно так и называется) вполне можно и другими средствами, например, просто связать их и установить соответствующие правила. Это правильно, но для нас сейчас важно понимать, что в одной транзакции можно выполнить несколько самых разных запросов, которые можно разом применить или отклонить. Начало транзакции мы объявляем с помощью команды BEGIN TRAN, а затем принимаем ее - COMMIT TRAN - или отклоняем (откатываем) - ROLLBACK TRAN.

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

using System; using System.Data.SqlClient;

namespace EasyTransaction { class Class1 { [STAThread] static void Main(string[] args) { SqlConnection conn = new SqlConnection(); conn.ConnectionString = "integrated security=SSPI;data source=\".\"; persist security info=False; initial catalog=BDTur_firm2"; conn.Open(); SqlCommand myCommand = conn.CreateCommand(); //Создаем транзакцию myCommand.Transaction = conn.BeginTransaction(); try { myCommand.CommandText = "INSERT INTO Туристы (Кодтуриста, Фамилия, Имя, Отчество) VALUES (6, 'Тихомиров', 'Андрей', 'Борисович')"; myCommand.ExecuteNonQuery(); myCommand.CommandText = "INSERT INTO Информацияотуристах(Кодтуриста, Серияпаспорта, Город, Страна, Телефон, Индекс) VALUES (6, 'CA 1234567', apos;Новосибирск', 'Россия', 1234567, 996548)"; myCommand.ExecuteNonQuery(); //Подтверждаем транзакцию myCommand.Transaction.Commit(); Console.WriteLine("Передача данных успешно завершена"); } catch(Exception ex) { //Отклоняем транзакцию myCommand.Transaction.Rollback(); Console.WriteLine("При передаче данных произошла ошибка: "+ ex.Message); } finally { conn.Close(); } } } }



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


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

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


увеличить изображение
Рис. 7.11.  Приложение EasyTransaction. Транзакция отклонена

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

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

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

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


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



  • Read uncommitted. Транзакция может считывать данные, с которыми работают другие транзакции. Применение этого уровня изоляции может привести ко всем перечисленным проблемам.
  • Read committed. Транзакция не может считывать данные, с которыми работают другие транзакции. Применение этого уровня изоляции исключает проблему "грязного" чтения.
  • Repeatable read. Транзакция не может считывать данные, с которыми работают другие транзакции. Другие транзакции также не могут считывать данные, с которыми работает эта транзакция. Применение этого уровня изоляции исключает все проблемы, кроме чтения фантомов.
  • Serializable. Транзакция полностью изолирована от других транзакций. Применение этого уровня изоляции полностью исключает все проблемы.


По умолчанию устанавливается уровень Read committed. В справке Microsoft SQL Server 20002) (Указатель - вводим "isolation levels" - заголовок "overview") приводится таблица, иллюстрирующая различные уровни изоляции (рис. 7.12):


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

Использование наибольшего уровня изоляции (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);

Дополнительно поддерживаются еще два уровня (см. рис. 7.13):

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



увеличить изображение
Рис. 7.13.  Определение уровня транзакции

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

В программном обеспечении к курсу вы найдете приложение Easy Transaction (Code\Glava3 \EasyTransaction).


Содержание раздела