Создание и управление транзакциями

 

 

Цель работы– изучение способов обеспечения надежной работы SQL Server 2000 с помощью механизма транзакций и контрольных точек, приобретение навыков управления локальными и распределенными транзакциями различных видов, а также ознакомление с физической и логической архитектурой журнала транзакций и способами восстановления баз данных.

Одним из способов повышения надежности работы системы MS SQL Server 2000 является применение встроенного в систему механизма транзакций и контрольных точек и умелое его управление.

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

Обработка транзакций в любой системе управления базами данных должна производиться с соблюдением следующих правил – правил ASID (Atomicity, Consistency, Isolation и Durability):

Atomicity – атомарность: выполняемые в транзакции изменения либо выполняются все,

либо не выполняются вовсе;

Consistency – согласованность: все данные после выполнения транзакции должны находиться в согласованном состоянии с соблюдением всех правил и ограничений целостности;

Isolation – изолированность: изменения данных, выполняемых различными одновременно работающими транзакциями, должны быть изолированы;

Durability – долговечность: после завершения транзакции ничто не может вернуть систему в состояние, в котором она была до начала транзакции (происходит фиксация транзакции).

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

SQL Server 2000 поддерживает три вида определений транзакций: явное,

автоматическое и подразумеваемое.

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

 

BEGIN TRANSACTION [Имя транзакции] – начало транзакции;

COMMIT TRANSACTION [Имя транзакции] – конец транзакции; ROLLBACK TRANSACTION[Имя транзакции] – откат транзакции;

 

В последних двух командах слово TRANSACTION можно либо опускать, либо заменять словом WORK. Во всех трех командах допускается использование сокращения TRAN вместо слова TRANSACTION и переменной строкового типа, которой присваивается имя транзакции, вместо непосредственного указания этого имени. Дополнительный аргумент WITH MARK

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

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

а) в режиме автоматического начала транзакций, в котором каждая рассматривается как отдельная транзакция, при этом если команда выполнена успешно, то сделанные ей изменения фиксируются, и выполняется следующая команда, в противном случае производится откат транзакции и выполнение команды повторяется;

б) в режиме неявного начала транзакции, когда начала транзакции не указывается, а ее завершение задается явно командой COMMIT или инициируется командами: ALTER TABLE, CREATE, DELETE, DROP, FETCH, GRANT, INSERT, OPEN, REVOKE, SELECT, TRANCATE TABLE и UPDATE; в этом режиме можно использовать команды COMMIT и ROLLBACK; после завершения текущей транзакции, начинается выполнение следующей, если не был задан откат транзакции.

Режим автоматическогоначала транзакций устанавливается по умолчанию или командой

 

SET IMPLICIT – TRANSACTION OFF

 

Режим неявного(или подразумевающегося) начала транзакций задается только командой

 

SET IMPLICIT – TRANSACTION ON.

 

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

Для управления распределенными транзакциями в MS SQL Server 2000 используется координатор DTC (Distribution Transaction Coordinator), удовлетворяющий спецификации “X/ OPUN XA for Distributed Transaction Processing”. Координатор MS DTC начинает и заканчивает локальные транзакции, а также откатывает их назад, если одна из них закончилась с ошибкой. При выполнении распределенных транзакций пользователь может обращаться не только к серверам SQL Server 2000, но и к другим источникам данных: Oracle, Access, источники ODBC и другие.

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

Распределенная транзакция может быть начата несколькими способами.

1. Если приложение в локальной транзакции использует распределенный запрос, то сервер автоматически начинает выполнение распределенной транзакции.

2. Если приложение начинает локальную транзакцию и из нее вызывает удаленную хранимую процедуру при установленном параметре REMOTE_PROC_TRANSACTION, то эта транзакция автоматически расширяется до распределенной транзакции (см.sp_configure).

3. Приложение может начать распределенную транзакцию, используя методы OLE DB

или ODBC.

4. Сервер начинает выполнение распределенной транзакции, если встречает команду

 

BEGIN DISTRIBUTED TRANSACTION Имя транзакции.

В этом случае для завершения и отката транзакций используются команды COMMIT TRANSACTION и ROLLBACK TRANSACTION.

При работе с явными транзакциями можно использовать вложенные транзакции, выполнение которых инициируется из тела уже активной транзакции. Для управления вложенными транзакциями используются те же команды, при этом каждая команда COMMIT работает только с последней начатой транзакцией. Если в команде ROLLBACK не задано имя транзакции, то откатываются все вложенные транзакции и транзакция самого высокого уровня. Если же имеется необходимость откатить лишь часть транзакций, то предварительно надо создать точку сохранения с помощью команды SAVE TRANSACTION, которую следует указывать при откате. Функция @@TRANSACTION предназначена для определения количества активных транзакций, начатых в активном соединении.

Во всех транзакциях нельзя использовать следующие команды: ALTER DATABASE, BACKUP LOG, CREATE DATABASE, DISK INIT, DROP DATABASE, DUMP TRANSACTION, LOAD DATABASE, LOAD TRANSACTION, RECONFIGURE, RESTORE DATABASE, RESTORE LOG, UPDATE STATISTICS, а также системной хранимой процедуры sp_droption и любой другой хранимой процедуры, изменяющей значения в системной базе master. Для отката таких действий необходимо использовать архивирование базы данных и последующее ее восстановление.

MS SQL Server 2000 для обеспечения целостности данных использует журнал транзакции, который имеет сложную физическую и логическую архитектуру и в котором производятся все промежуточные модификации до завершения транзакции, после которой происходит фиксация изменений в самой базе данных. Контрольные точки минимизируют данные в журнале транзакций.

 

Задание 1.Проверить режимы автоматического начала транзакций и неявного начала транзакций, используя переключатель IMPLICIT_TRANSACTION и команду COMMIT.

 

Задание 2.Создать несколькими способами распределенные транзакции и убедиться в корректности их выполнения.

Задание 3.Создать вложенные транзакции, выполнив следующие команды: CREATE TABLE #aaa (cola int) -- 0-й уровень

BEGIN TRAN -- 1-й уровень

INSERT INTO #aaaVALUES (111)

BEGIN TRAN -- 2-й уровень

INSERT INTO #aaaVALUES (222)

BEGIN TRAN -- 3-й уровень

INSERT INTO #aaaVALUES (333) SELECT * FROM #aaa

SELECT ‘Вложенность транзкций’, @@TRANCOUNT ROLLBACK TRAN

SELECT * FROM #aaa -- откат на 0-й уровень SELECT ‘Вложенность транзакций’, @@TRANCOUNT Проанализировать полученные результаты.

 

Задание 4.Написать пример пакета запросов с использованием команд COMMIT и

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

 

Задание 5.Написать пример пакета команд, иллюстрирующих использование средств оптимизации при откате транзакций.

 

Задание 6.Используя средства MS SQL Server 2000, изучить физическую и логическую архитектуру журнала транзакций.

Задание 7.С помощью системной хранимой процедуры sp_configure изменить интервал контрольных точек для базы данных Pubs.

 

Задание 8.Уточнить синтаксис команд управления транзакциями и написать пример пакета с использованием всех вариантов этих команд.