Вызов хранимых процедур. Работа с транзакциями
Работа с транзакциями
Транзакцией называется выполнение последовательности команд (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):
Для того чтобы избегать подобных ошибок, нам нужно применить транзакцию. Удалим все внесенные записи из обеих таблиц (это можно сделать с помощью запроса или в 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.11):
В виде транзакции можно заключать выполнение одной или нескольких хранимых процедур, - в самом деле, общая конструкция имеет следующий вид:
//Создаем соединение
//Создаем транзакцию
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Она называется SQL Server Books Online. Как вы могли заметить, эту справку можно вызвать из любого приложения, входящего в пакет Microsoft SQL Server 2000. (Указатель - вводим "isolation levels" - заголовок "overview") приводится таблица, иллюстрирующая различные уровни изоляции (рис. 7.12):
Использование наибольшего уровня изоляции ( 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. Отдельный уровень изоляции, который может применяться, но не может быть определен. Транзакция с этим уровнем может применяться для задания собственного уровня изоляции.
Транзакции обеспечивают целостность базы данных, при разработке многоуровневых приложений их применение является обязательным правилом.
В программном обеспечении к курсу вы найдете приложение Easy Transaction (Code\Glava3 \EasyTransaction).