Программирование транзакций

По умолчанию SQL Server рассматривает команды SQL как независимые транзакции. Но транзакцию можно объявить и программным путем, включив туда произвольное количество команд[1].

Включение транзакций в программный код

Основу использования транзакций в программном коде на языке Transact-SQL составляют четыре команды begin transaction, commit transaction, rollback transaction, save transaction к более подробному разбору, которых мы сейчас и приступим. Вот общий вид команды begin transaction 

 

begin tran[saction]

[transaction_name | @tran_name_variable

[with mark [ 'description' ]]]

 

Команда начала явной транзакции. Здесь 

 

■           transaction_name – имя транзакции. Длина имени не должна превышать 32 символа. Само же имя должно удовлетворять требованиям, предъявляемым к именам объектов в Transact-SQL. Именованные транзакции используются при наличии вложения транзакций.

■           @tran_name_variable – имя транзакции  можно указать с помощью переменной. Для задания имени транзакции используются переменные, имеющие тип char, varchar, nchar, nvarchar.

■           with mark ['description'] – данная конструкция позволяет пометить транзакцию. Для обозначения транзакции используется ее имя, которое указывается в журнале транзакции. Метка эта в дальнейшем может быть использована при восстановлении базы данных с помощью журнала транзакций. description – описание метки, с помощью которого метка может быть идентифицирована при восстановлении резервной копии. Помечается только транзакция, изменяющая данные.

Команда завершения транзакции имеет следующий формат.

commit { tran | transaction }

[ transaction_name | @tran_name_variable ] ]

Здесь

■           transaction_name – имя завершаемой транзакции. Сервер игнорирует это имя, так что оно служит скорее для того, чтобы сделать понятным программный код.

■           @tran_name_variable – переменная, определяющая имя завершаемой транзакции. Для задания имени транзакции используются переменные, имеющие тип char, varchar, nchar, nvarchar.

Команда точки сохранения

save { tran | transaction } { savepoint_name | @savepoint_variable }

Где

save_point – имя точки сохранения. Длина имени не должна превышать 32 символа и должна удовлетворять правилам для имен объектов SQL Server 2000.

@savepoint_variable – переменная, содержащая имя точки сохранения. Для задания имени используются переменные, имеющие тип char, varchar, nchar, nvarchar.

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

rollback { tran | transaction }

     [ transaction_name | @tran_name_variable

     | savepoint_name | @savepoint_variable ]

Здесь

■           transaction_name – имя транзакции.

■           @tran_name_variable – переменная, задающая имя транзакции.

■           savepoint_name – имя контрольной точки, заданной с помощью  save transaction. Откат, таким образом, будет происходить только до контрольной точки. 

■           @savepoint_variable  - переменная, задающая имя контрольной точки.

Приведем несколько примеров использования транзакций в программировании.

Листинг 3.77

begin transaction

declare @a bigint

select @a=number from list

where id=343567

update titles

set adv=@a+adv

where num>1000 AND adv<>0

commit transaction

В Листинге 3.77 представлен простой пример использования  транзакции. Если в процессе выполнения транзакции одна из включенных в нее команд не будет выполнена, то произойдет откат всей транзакции. Однако выражение «не будет выполнена» можно трактовать по-разному. В частности можно говорить о ситуации, когда результат выполнения команды не будет отвечать каким-либо критериям. Чтобы транзакция откатывалась и в случае невыполнения, каких либо критериев следует использовать условные конструкции и оператор rollback transaction.

Листинг 3.78

--фрагмент 1

--создаем таблицу, при условии наличия определенных записей

--в таблице subscr, и заполняем созданную таблицу этими

--записями

begin transaction

if  exists(select * from subscr where number<100000)

begin

       create table

       address (

       strt char(20) not null,

       hs char(20) not null,

       fl char(20) not null)

 

       insert into address

       select a.strt,a.hs,a.fl from subscr a

       where a.number<100000

end

commit transaction

.

.

.

--фрагмент 2

--создаем таблицу и заполняем ее записями из таблицы

--subscr, если записей нет, то проводим откат транзакции

begin transaction

create table

address (

strt char(20) not null,

hs char(20) not null,

fl char(20) not null)

 

if  exists(select * from abon where number<10000)

begin

       insert into address

       select a.strt,a.hs,a.fl from subscr a

       where a.number<100000

end

else

begin

       rollback transaction

       return

end

commit transaction

В Листинге 3.78 представлено два фрагмента. Приводим два фрагмента. Во втором случае мы для реализации той же, что и в первом фрагменте задачи, используем откат транзакции. Разбирая фрагмент 2 (из Листинга 3.78), обратите внимание на то, как проводится откат транзакции. После команды rollback transaction стоит команда return. Это важно, так как команда rollback transaction по сути осуществляет выход из транзакции, и выполнение после этого команды commit transaction приведет естественно к ошибке.

Листинг 3.79

create procedure dbo.new 

as

begin

delete from address

begin transaction name1

insert into addresd values(1,' ',' ',' ')

insert into address values(2,' ',' ',' ')

insert into address values(3,' ',' ',' ')

--закрепляем содержимое таблицы после трех вставок

save transaction name2

--еще вставки

insert into  address values(4,' ',' ',' ')

insert into address values(5,' ',' ',' ')

insert into address VALUES(6,' ',' ',' ')

rollback transaction name2

select * from address

return

end

Рассмотрим пример из Листинга 3.79. В результате выполнения данной хранимой процедуры в таблице останутся только три первые строки (значение первого поля 1,2,3). Другими словами закрепленными окажутся команды до точки сохранения.

Вложенные транзакции

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

Листинг 3.80

create procedure dbo.new1

as

begin

begin transaction

--вставляем три строки в таблицу adres

insert into address values(1,' ',' ',' ')

insert into address values(2,' ',' ',' ')

insert into address values(3,' ',' ',' ')

commit transaction

return

end

 

--вторая вложенная процедура

create procedure dbo.new 

as

begin

delete from address

begin transaction

exec dbo.new1

--вставляем две строки в таблицу address

insert into address values(4,' ',' ',' ')

insert into address values(5,' ',' ',' ')

commit transaction

--при выполнении процедуры к таблице address будет

--добавлено две строки

return

end

В примере, который  представлен в Листинге 3.80, показаны две явные вложенные транзакции. Внешняя транзакция определена в процедуре new, вложенная (внутренняя) транзакция определена в процедуре new1. Последняя транзакция будет работать и в том случае, если процедура new1 будет вызвана непосредственно пользователем (не из транзакции).

И это правило является общим - сколько бы ни было вложенных транзакций, все они откатываются, если откатывается внешняя транзакция.

 

 

7.4. Резервное копирование

Этот раздел посвящен исключительно важным функциям SQL Server — ре­зервному копированию баз данных и журналов транзакций, а также восста­новлению БД в случае их повреждения или уничтожения.

7.4.1. Общие положения

Тот, кто некоторое время работал с базами данных, знает, что потеря дан­ных — не такое уж редкое явление. Причины могут быть самыми разными:

·         сбой в электропитании или работе оборудования;

·         ошибка в функционировании системного программного обеспечения;

·         ошибка в прикладном программном обеспечении;

·         человеческий фактор.

Любая из перечисленных причин может привести к частичной или полной потере данных. И данные придется восстанавливать. А насколько это вос­становление будет успешным, зависит от объема потерянных данных, нали­чия резервной копии и времени, когда она была сделана.

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

1.        Какое резервное копирование существует в SQL Server 2000?

2.        Когда осуществлять резервное копирование?

3.        Как часто необходимо выполнять резервное копирование?

4.        Что следует копировать?

5.        Куда следует копировать?

6.        Каков алгоритм восстановления данных?

7.        Как осуществлять резервное копирование?

8.        Как осуществлять восстановление данных?

Вот на все эти восемь вопросов я и постараюсь дать ответ в данном разделе книги.

Начну по порядку. В распоряжении администратора существуют четыре ти­па резервного копирования.

Полное резервное копирование базы данных (database backup). В этом случае все содержимое базы данных будет помещено в один или несколько фай­лов, имена которых задаются в формате соответствующей команды или диалоговом окне (см. разд. 7.4.2). Резервное копирование может осущест­вляться и на такое устройство, как стример, основным назначением ко­торого, в сущности, и является создание резервных копий. Отмечу одну очень важную и интересную деталь: полная резервная копия содержи, всю информацию на момент окончания копирования! Другими словами если в процессе резервного копирования в базе происходили изменения то они отразятся в окончательном варианте архива. Такой тип резервно/ копирования является фундаментом, на котором должна строиться методология сохранения данных. Если база данных работает только на чтение то для ее восстановления достаточно одной полной резервной копии.

·         Дифференциальное резервное копирование (differential database backup). Пр_й таком методе сервером отслеживаются только изменения, произошедшие со времени полного резервного копирования. Такая копия компактнее _и требует меньше времени для своего создания. Необходимо отметить, что SQL Server 2000 фиксирует эти изменения на уровне страниц. Если дан-ные на странице были модифицированы, то автоматически устанавлива­ется флаг архивирования этой страницы, и она копируется при очередном дифференциальном резервном копировании. Указанный флаг сбрасыва­ется при полном резервном копировании. Таким образом, все определяет только последнее резервное копирование.

·         Резервное копирование файлов и групп файлов (file and filegroup backup). При таком резервном копировании архивируются только страницы, принад­лежащие указанному файлу или группе файлов. В разд. 4.2 мы говорили, что при желании данные можно разместить в различных файлах и груп­пах файлов. Другими словами, вы можете таблицу или даже отдельные ее столбцы поместить в конкретный файл. И если вы знаете, что измене­нию подвергается только данная таблица (или некоторые ее столбцы), то есть смысл осуществлять регулярное резервное копирование именно этого файла, имея, конечно же, полную резервную копию всей базы, сделанную некоторое время назад.

·         Резервное копирование журнала транзакций (transaction log backup). При данном типе резервного копирования создается копия информации о транзакциях, зафиксированных в соответствующем журнале. В нем отображаются состояния базы данных до начала транзакции и после ее завершения. При таком подходе сервер сканирует журнал транзакций и помещает в архив информацию только о тех транзакциях, которые произошли с момента последнего резервного копирования. Другими сло­вами, имея состояние базы данных на какой-то момент времени, можно, используя копию журнала транзакций, привести базу данных к ее со­стоянию на момент завершения процедуры резервного копирования.

В полной мере возможности резервного копирования можно использовать, есЛ^ в период резервного копирования данные были правильными, т. е. не содержи ли каких-либо нарушений. Для их проверки можно использовать утилит) DBCC. При ее запуске нужно указать одно из следующих ключевых слов:

chekdb — проверяет согласованность внутренней структуры данных * индексов;

checalloc — проверяет наличие страниц данных;

chekcatalog — определяет степень согласованности таблиц системного каталога. Вo всех этих вариантах в качестве параметра следует указать имя базы дан­ных. Если оно не указано, то проверяется текущая база данных.

Вопрос: "Когда производить резервное копирование?" — совсем не праздный. Ответ на него зависит, прежде всего, от размера базы данных, от степени интенсивности и временного диапазона работы с БД.

Для небольшой базы данных можно делать полную резервную копию несколь­ко раз в день без особого ущерба для работы пользователей. Большую же базу данных придется копировать в часы, свободные от работы пользователей, на­пример, в обеденное время или вечером. Дело в том, что во время резервного копирования может существенно замедлиться реакция сервера на запросы пользователей. Кроме того, существуют операции, которые не могут выпол­няться во время резервного копирования. К ним можно отнести:

·         уменьшение базы данных;

·         создание и удаление файлов базы данных;

·         создание индексов;

·         выполнение операций, не заносимых в журнал транзакций.

Для сверхбольших баз данных ежедневное создание полной копии — весьма затруднительный процесс. Однако если база данных изменяется не слишком часто, то достаточно делать одну полную копию с определенной дискретно­стью (например, неделя, месяц). В промежутке можно, для верности, вы­полнять дифференциальные копии базы данных. Для больших и интенсивно изменяющихся баз данных придется выстраивать всю цепочку мероприятий: полное резервное копирование (например, раз в неделю), дифференциаль­ное резервное копирование (например, раз в день), резервное копирование журнала транзакций (каждые 2—3 часа).

Слово "когда" в вопросе носит не только временной, но и причинный смысл. Поэтому его можно перефразировать следующим образом: "В каких случаях следует производить резервное копирование?" Ответ на этот вопрос °чень прост. Резервного копирования требуют следующие операции:

·         создание базы данных;

·         создание, модификация свойств и структуры объектов базы данных, изменение содержимого объектов базы данных.

Прежде чем говорить о методике восстановления БД, сделаю следующее замечание. У базы данных имеется опция Recovery (см. разд. 5.1), которая ^ожет принимать три значения:

simple — указывает, что информация о выполненных операциях с базой . данных не сохраняется в журнале транзакций. Соответственно, резервное

копирование журнала транзакций теряет смысл. Следует надеяться только на полное и дифференциальное резервное копирование;

bulk_logged — определяет, что в журнал операций записываются вс операции с базой данных, кроме: select into <новая_таблица>, BULK, INSERT, CREATE INDEX, WRITETEXT, UPDATE TEXT, а также Операции, выполненные с помощью утилиты ВСР.ЕХЕ (см. разд. 5.5). В этом случаи можно использовать резервное копирование журнала транзакций, a вышеуказанные манипуляции придется производить вручную;

full — устанавливает, что все операции протоколируются. Таким обра­зом, в полной мере работают все типы резервного копирования.

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

1.        Создать (если возможно) резервную копию журнала транзакций на дан­ный момент времени.

2.        Восстановить базу данных из самых последних копий: полной резервной и дифференциальной, если она была сделана позднее последней полной копии.

3.        Восстановить копию журнала транзакций, созданную после самой по­следней резервной копии базы данных.

4.        Восстановить, если она есть, копию журнала транзакций, созданную на шаге 1.

5.        Вручную восстановить операции, оказавшиеся не сохраненными.

По поводу восстановления журнала транзакций следует заметить, что эту операцию можно выполнять для транзакций, производимых до определен­ной временной точки. Кроме того, можно указать имя транзакции, на (или до) которой необходимо остановить восстановление базы данных. Для этого нужная транзакция должна быть определенным образом поме­чена (см. гл. 8).

Резервное копирование необходимо проводить не только по отношению к пользовательской базе данных, но и по отношению к системным базам данных. Вот события, которые должны подсказать вам необходимость та­кого копирования:

·         изменение, добавление или удаление учетных записей;

·         создание, изменение или удаление базы данных;

·         создание, изменение или удаление группы файлов или устройства рб' зервного копирования;

□ изменение опций настройки сервера или баз данных;
добавление или удаление связанных серверов.

Остановлюсь на восстановлении системных баз данных подробнее.

Воза данных Master. Содержит информацию о пользовательских БД, учетных записях, настройках SQL Server 2000 и др. Это, пожалуй, самая главная из системных баз данных. Для нее возмож­но только полное резервное копирование. Соответственно, восстановле-Нкие этой базы данных имеет некоторые особенности. Этот процесс может состоять из последовательности следующих действий:

·         на сервере запустить только службу MSSQLServer. Желательно, чтобы сервер был запущен в однопользовательском режиме (sqiserver -m);

·         восстановить базу данных Master;

·         выполнить изменения, произошедшие после резервного копирования.

Если по каким-либо причинам восстановить базу данных Master средст­вами SQL Server невозможно, следует воссоздать ее из установочного на-Вбора с помощью утилиты rebuildm.exe. В этом случае недостающие данные придется восстанавливать вручную.

Системная база MSDB. Содержит информацию, используемую службой г SQLServerAgent (операторы, оповещения, задания). Для этой базы данных возможно как полное резервное копирование, так и дифференциальное резервное копирование. Создание резервных копий этой базы ванных вам пригодится, если вы собираетесь автоматизировать процесс администрирования SQL Server.

Системная база данных MODEL. Содержит шаблоны пользовательских баз Идейных. Если вы не создаете своих шаблонов, то и резервное копирова­вшие этой базы данных делать необязательно.

Системная база данных TEMPDB. Предназначена для хранения временных объектов. Поскольку эта база данных воссоздается при каждом запуске SQL Sewer, делать ее резервную копию не имеет смысла.

Скажу несколько слов по поводу устройств, на которые осуществляется ре­зервное копирование. Используемые некоторое время назад для этих целей Накопители на гибких магнитных лентах (стримеры) постепенно уходят из Употребления. Причин здесь несколько: невысокая скорость записи, огра­ниченность времени хранения несколькими годами, неудобство работы Ванным устройством (по сравнению, например, с жестким диском). По­этому есть смысл хранить резервные копии на сетевых устройствах или же­стких дисках, дублируя их на компакт-диски. Важно помнить, что резервных копий должно быть достаточно для восстановления данных с минимальными потерями при любой нештатной ситуации. Если вы делаете дифферен­циальные резервные копии каждый день, не поленитесь копировать их Отдельные файлы хотя бы один раз в неделю. Соответственно, резервные копии журнала транзакций также в течение некоторого промежутка времени должны храниться в разных файлах. Необходимость в таком подходе вполне очевидна — нарушения в данных могут обнаружиться не сразу, следовательно,