SET NCCOUNi CN

- Ооьявляем ;iOKd;ibhyfi курсорную переменную для получения выходного параметра DECLARE (aycursor cursor

EXEC 1istsale5 cur BU1032, Onycursor OUT -- Вызываон процедуру - Убедимся, что курсор открыт и в нем есть го крайне мере одна запись IE (CURSC.R STATUS(vari3Die,@!T,ycurscr)=j) BEGIN FETCh @rr,ycur5or

mil (@!aEETCH STATUS-0) BEGIN FETCH (amycjrsor

E.NO

CLCSE Cycursor DEALLOCATE (Pmycursor

Обратите вни.маиие на то, как этот код ссылается на курсор с помощью трех различных переменных, а также имети! курсора. Для каждой ко.манды, за исключением DEALLOCATE, ссылка на курсор с помощью курсорной переменной - аналог ссылки иа курсор по HMeini. Если вы открываете курсор с по.мощью команды OPEN, независи.мо от того, ссылаетесь вы на курсор по имени или с помощью курсорной переменной, курсор будет открыт, и вы можете выбирать из него записи с по.\ющью команды FETCH, используя любую пере.менную, которая ссылается на него. DEALLOCATE отличается в том смысле, что эта команда на са.молг деле не освобождает курсор, если только это не последняя ссылка на пего. Это, однако, действительно блокирует доступ к нему с помощью указанного идентификатора. Так что если у вас есть курсор с пмене.м foo и курсорная переменная с названием foovar, которой было присвоено значение foo, освобождение foo лишь запретит доступ к курсору с помощью foo - foovar останется неизменной.

Хранимые процедуры для работы с курсорами

SQL Server предоставляет множество хранимых процедур, связанных с курсо-pa.vHi, с KOTopbi.vtn вы должны быть знакомы, если собираетесь много работать с KypcopaNHi. В табл. 13.3 приведен пх краткий список с описанием каждой из них. Каждая из этих процедур возвращает результат с помощью курсорного выходного пара.метра, так что вы долж)1ы объявить локальную курсорную переменную, чтобы обрабатывать их.

Таблица 13.3. Хранимые процедуры, связанные с триггерами

Процедура Выполняемые функции

sp cursor list Возвращает список курсоров, открытых соединением,

а также их атрибуты

sp describe cursor Возвращает атрибуты отдельного курсора

sp describe cursor columns Возвращает столбцы курсора (и их атрибуты)

sp describe cursor tables Возвращает список таблиц, использованных в курсоре

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

Лучший способ оптимизации курсоров - по возможности вообще не использовать их. Как я уже говорил, SQL Server лучше работает с .множествами данных, чем с отдельными записями. Он представляет собой реляционную СУБД, а работа с отдельными записями никогда не была сильно сто])оной реляцион)1Ых СУБД. Хотя иногда без курсоров не обойтись, так что вот несколько советов по их оптимизации.

О Не используйте статические/нечувствительные курсоры без необходимости. При открытии статического курсора все его записи копируются во временную таблицу. Вот почему они нечувствительны к изменения.м - он на самом деле ссылается на копию таблицы в tempdb. Естественно, че.м больше результи-руюи1ее множество, тем более вероятно, что использование статического курсора вызовет проблемы с ресурсами в tempdb.

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

О Применяйте опцию FAST FORWARD вместо FORWARD ONLY при работе с однонаправленными множествами только для чтения. При использовании FAST FORWARD объявляется FORWARD ONLY, READ ONLY курсор с некоторыми внутренними оптимизациями производительности.

О Объявляйте курсоры только для чтения с помощью кпючевого слова READ ONLY. Это препятствует случайным изменениям, кро.ме того, сервер будет знать, что курсор не из.менит записи, по которььм проходит.

О Будьте осторожны при .модификации множества записей с по.мощью цикла по курсору, который находится внутри транзакции. В зависи.мости от уровня изоляции транзакции эти .записи мог}т остаться заблокированными до тех пор, пока транзакция не будет зафиксирована или отменена, а это .может привести к проблемам с ресурсами сервера.

О- Будьте осторожны с .модифищтруемыми динамическими курсора.ми, особенно с теми, которые созданы по таблицам с неуникальиы.ми кластерными индексами, так как они могут вызвать проблему Хэллоуина - повторные, опт-

Заключение 301

бочиые обиов.гепия одио!! и roii же строки или строк. Гак как SQL Server внутренне делает неуи1Пч;иил1ые ключи кластерных индексов уиикальны.ми, добавляя к ни.м порядковый noNrep, воз.можио, что вы пз.хгеиите значение ключа записи па уже существующее и заставите сервер добавить суффикс, из-за кого)ого запись переместится дальше в результирующем .чиюжестве. Пр[1 выборе остав1пегося рез}льтирующего множества вы опять наткнетесь на эту запись, и процесс повторится, что приведет к бесконечному циклу. Вот при.мер, иллюстрируюиип! данную проблему:

- Этот код создает чурсоо. ко-срый представляет пробгеку Хэллоуина. - Не запускайте его, если только ва.ч не нравятся бесконечнус циклы Sf; .NOCCUNT ON

CREAIt TABLh Ее; (к1 Tnl ideniily, ci ir,r, NULl)

CREATE CLUSTERED INDEX cl CN #tenp(cl)

INSERT #teTip VALUES (8)

I.NSERT #temp VALUES (6)

INSERT #teinp VALUES (7)

INSERT #temp VALUES (5)

I.NSERT #teap VALUES (3)

INSERT #tefnp VALUES (0)

I.NSERT #temp VALUES (9;

DECLARE с CURSOR 0NAMIC

FOR SELECT kl, ci FROM #temp

OPEN с

FETCH с

HILE (K<iFErCH SlATUS=0; BEGIN

UPDATE #temp

SET С1-С1Ч

WHERE CURRENl OF c

FETCH С

SELECT * FROM #teinp ORDER BY ki END

CLOSE С DEALLOCATE c GO

DROP TABLE #temp

О Использу11те асинхронные курсоры при работе с больши.ми .множествами, чтобы возврапщть управление вызывающе!! стороне как можно быстрее. Асинхронные курсоры особенно полезны при возвращении результирующего множества значительного раз.мера прокручиваемой форме, так как они позволяют приложению начать отображение практически немедленно.

Заключение

В этой главе вы узнали о различных типах курсоров, гюддерживаемых в Transact-SQL, а также о том, как их создавать и как управлять ими. Также вы узнали о некоторых иотеициаль)1ых проблемах и способах оптимизации пронзводнтель-ности, о которых вы должны Ho.vHHiTb, если используете курсоры. Курсоры - не лучший способ решения большинства задач, они могут вызывать серьезные проблемы с производительностью, если их неправильно использовать.

Транзакции

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

награды.

Томас Л. Холэдэй

Подробное рассмотрение механизмов управления транзакциями выходит за рамки этой книги. Существует множество книг, в которых достаточно полно описа-)1Ы внутрен)1ие механизмы управления транзакциями SQL Server. Books Online также содержит много полезной информации о механизмах транзакций SQL Server.

Характеристика транзакций

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

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

Тест ACID

Транзакции SQL Server часто описываются, как имеющие свойства АСШ или удовлетворяющие тесту АСШ , где ACID - это акроним слов atomic (атомар-

иость), consisTent (це.-юстиость), isolated (изолированность) и durable (устойчивость). Соблюдение иринцииов ACID - обычное явление в современных СУБД, а также необходимое условие сохранности и достоверности данных.

Атомарность (Atomicity)

Транзакция является атомарной, если она выгюлпяется ио принципу все или ничего . Если транзакция выполняется успешно, все ее изменения сохраняются; ес.чи нет - все из.\геие1И1я нол1ЮСтью отменяются. Так что, например, если транзакция включает десять ко.хганд DELETE и пос.чедняя из них выполняется неудачно, откат транзакции от.менит все девять предыдущих. Ана-чогично, если одна команда пытается осуществить десять удале1ин1 записей и одно из них не удается - вся операция будет неудачна.

Целостность (Consistency)

Транзакция обладает свойством целостности, если гараитируется, что обрабатываемые ею да)И1ые никогда ие будут в промежуточно.м или несогласованном состоянии, то есть они никогда не будут в противоречивом состоянии. Так что данные, которые затрагивает ко.манда UPDATE, ткзмеияющая десять записей, )И1-когда ие будут видны внешнему .миру в иромежуточпо.м состоянии - все заппсп будут видны либо в первонача-чьном, либо в Kone4noNr состоянии. Это предотвращает случайное нарушение работы одного пользователя други.м пользова-теле.хг Целостность обычно вытекает из других АСГО-свойств.

Изолированность (Isolation)

Транзакция является изолировапной, если она не воздействует на другие параллельные транзакции, работающие с те.ми же данными, и они на )iee не воздействуют. Степень изолированности траизакцти! от других транзакций контролируется ее уровнем изолированностп (isolation level, определяется с по.мощью ко.манды SET TRANSACTION ISOLATION LEVEL). Уров)1и изоляции варьируются от отсутствия изоляции вообще - в этом случае транзакции могут читать незафиксированные (uncommitted) данные и не MOiyr эксклюзивно блокировать ресурсы - до сериа.чизуе\юй (serializable) изоляции - в это.м случае все .множество данных блокируется, и пользователи не могут их модифицировать, пока транзакция не завершится (более подробная информация приведена в разделе Уровни изоляции тра)1закций ). Компро.хиюс с кажды.м ypoBHC.vi изоляции - это выбор reждy парачлельиостью (парачлель)1Ы.м доступо.м и модификацией да)шых множестволг пользователей) и целостностью. Чем выше изоляция, тем выше степень целостности данных. Чем выше целостность, тем ниже параллельность. Это происходит пото.му, что SQL Server блокирует ресурсы для обеспечения целостности данных. Чем больше блокировок, тем .меньше возможно одновременных операций модификации данных и тем меньше доступность данных вообще.

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

Устойчивость (Durability)

Транзакция считается устойчивой, если она может завершиться, несмотря на системный сбой, или, в случае незафиксированных транзакций, может быть полностью отменена после сбоя системы. Упреждающая журнализация (write-ahead logging) и процесс восстановления базы данных гарантируют, что зафиксированные, но не сохраненные в базе данных транзакции запишутся в базу данных после системного сбоя (rolled forward), а незаверпшнные транзакции - отменятся (rolled back).

Как работают транзакции в SQL Server

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

Между пакетами команд и транзакциями существует отнонгение многие-ко-многим . Пакеты команд могут содержать .множество транзакций, а одна транзакция может охватьшать несколько пактов. Как правило, следует избегать т})анзакций, охватываюп.1их длинные пакеты команд, поскольку это .может вызвать проблемы с параллельностью и производительностью.

Всегда, когда происходит модификация данных, SQL фиксирует запись об изменении в журнале транзакций. Это происходит до того, как осуществляется непосредственно само изменение, - вот почему говорят, что SQL Server имеет журнал с упреждающей записью ( write-ahead ) - изменения фиксируются до записи изменений соответствующих данных. Если это не удастся сделать, нельзя будет произвести откат изменений, когда сбой сервера произошел прежде, чем запись была зафиксирована в журнале.

Модификации никогда не осуществляются напря.мую на диск. Вместо этого SQL Server читает страницы дан}н,1Х в буфер по мере необходимости и изменяет их в памяти. Перед тем как изменить страницу в памяти, сервер гарантирует, что изменение записано в жур)1ал транзакций. Поскольку журнал транзакций также кэшируется, и эти изменения первоначально осуществляются в памяти. Журнал с упреждающей записью гарантирует, что процесс lazywriter (процесс, отвечающий в MS SQL за работу с буфером даниых (memory pool)) не запишет модифицированные страницы данных ( грязные страницы) на диск до записи соответствующих им записей журнала.

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

Транзакции и нежурналируемые (nonlogged) операции

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

(jiitpauiii!. Это возможно д.тя иежуриа.тнруе.мых oiiepamiii, так как оие))ации выделения cTpaHiHi записываются в журнал транзакци!].

Транзакции и триггеры

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

Типы транзакций

SQL Server нодде|)живает четьц^е основных типа транзакции: автоматические (automatic), неявные (implicit), но.чьзовательские (user-defined) и распределенные (distributed). Каждый из них и.меет свои особенности, так что рассмотрим их отдельно.

Автоматические транзакции

По умолчанию каждая команда Transact-SQL вьнюлияется в своей транзакцитг Такие транзакщи! известны как автоматические (или autoconnnit). Они авто.матически иач1И1аются и завершаются сервером. Примером автоматических транзак-ци]] .могут быть DML-команды, вьптолняемые вне транзакции (пока отключены неявные транзакции). Вы .можете рассматривать авто.матические транзакции как операторы Transact-SQL, заключенные между BEGIN TRAN и COMMIT TRAN. Если оператор выполняется удачно, он фиксируется. Если пет, откатывается.

Неявные транзакции

Неявные транзакцтн! - это ANSI SQL-92-coB,MecTH.Mbie авто.матические транзакции. Они инициируются автоматически, когда запускается любая команда DDL или DML. Они продолжаются до тех пор, пока не будут явно завершены пользователем. Для включения и отключения режима поддержки неявных транзакций используется команда SET IMPLICIT TRANSACTIONS. По у.молчаиию OLEDB-и ODBC-соединения включают переключатель ANSI DEFAULTS, который, в свою оче|)Сдь, включает режи.м неявных транзакций. Однако зате.м режи.м неявных транзакци!! сразу же отключается из-за нробле.м, которые могут вызвать плохо управляе.мые транзакции. Разрешение неявных транзакций напоминает ситуацию, когда двери вашего автомобиля будут автоматически блокироваться всякий раз при закрытии. Это занимает больше времени, че.м экономит, и рано или поздно вы забудете ключи в за.мке зажигания.

Пользовательские транзакции

Пользовательские транзакции - это основной .механиз.м управления транзакцтш-ми в приложениях SQL Server. Этот тип транзакций называется пользовательским, так как вы контролируете, когда транзакция должна начаться, а когда закончиться. Для управления пользовательски.ми транзакция.ми используются команды BEGIN TRAN, COMMIT TRAN и ROLLBACK TRAN. Вот пример:

SELECT TOP 5 tmejd, storjd FROM sales ORDER BY title id. storjd BEGIN TRAN DELETE sales

SELECT TOP 5 titlejd. storjd FROM sales ORDER BY titlejd. storjd GO

ROLLBACK TRAN

SELECT TOP 5 titlejd. storjd FROM sales ORDER BY titlejd. storjd title id stor id

BU1032 6380

BUI 032 8042

BU1032 8042

BUllll 8042

BU2075 7896

(5 row(s) affected)

(25 row(s) affected)

titlejd storjd

(0 row(s) affected) title id stor id

BU1032 6380

BUI 032 8042

BUI 032 8042

BUllll 8042

BU2075 7896

(5 row(s) affected)

Распределенные транзакции

Транзакции, <)хватывающие несколько серверов, известны как распределенные транзакции. Они управляются центральным управляющим приложением, которое координирует действия вовлеченных в транзакции серверов. SQL Server может участвовать в распределенных транзакциях, координирующихся управляющими приложениями, которые соответствуют спецификации Х/Ореп ХА для распределенной обработки транзакций; таким приложением, например, является Microsoft Distributed Transaction Coordinator (DTC). В Transact-SQL вы можете начать распределенную транзакцию с помощью команды BEGIN DISTRIBUTED TRANSACTION.

Как избежать транзакций вообще

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

Как избежать транзакций вообще 30

Команды с минимальной журнализацией

Комавды BULK INSERT, TRUNCATE TABLE, SELECT...INTO и WRITETEXT/UPDATETEXT

.минимально журнал1Ц5уются, в журнат транзакций записываются только операции со страницами (BULK INSERT может, в зависи.мости от обстоятельств, создавать подробные записи в журнале транзакций). Вонреки распространеино.му заблуждению, эти операции журиалируются - они иросто ие генерируют подробную 1И1фор.мацию в журнале транзакций. Вот поче.му в Books Online они называются пежурпалируе.мьиии операция.чт - они не генерируют информацию уровня записей таблицы в журнале транзакций.

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

Базы данных в однопользовательском режиме и режиме только для чтения

Один очевидный способ избежать журиализации, а также блокировки ресурсов и .мертвых блокировок (deadlocks) - сделать базу данных доступной только для чтения. Естественно, если база данных не может быть iизменена, нет необходимости в журна^чизации траизакиий и блокировке ресурсов. Если база данных находится в однопользовательском режиме, не существует даже необходимости в блокировках чтения, так что приложение не сможет заблокировать са.мо себя.

Хотя рекомендаштя уменьшить доступность базы данных с целью мини.ми-зации проблем с управлением транзакциями может напоминать совет не водить машину, чтобы пе разбить ее, риюгда это можно увидеть в реальных приложещ!-ях. Напри.мер, использование баз данньгх в режиме только для чтения довольно обычно для приложений DSS (Decision Support System, системы поддержки принятия решений). Эти базы данных могут из.меняться в нерабочее время (напри.мер, ночью или в выходные дни), а зате.м они возвращаются в режим только для чтения для применения в обычное рабочее время. Очевидно, что проблемы с управлением транзакция.ми значительно уменьшаются, когда базу данных может одновременно модифицировать только один пользователь: она либо может изменяться вся, либо не может из.меняться вообще.

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

Автоматическое управление транзакциями

SQL Server предоставляет множество механиз.мов для автоматизации управления транзакциями. Примером наиболее известного из них может служить механизм автоматических транзакций. Как я упоминал ранее, автоматические транзакции начинаются и фиксируются или откатываются неявно сервером. В этом случае нет необходимости в яв1юм указании операторов BEGIN TRAN или COMMIT/ ROLLBACK TRAN. Сервер инициирует транзакцию, когда начинает выполняться команда модификации, и в зависимости от того, как она завершится, фиксирует или откатывает ее. SQL Server по умолчанию работает в режиме автоматических транзакций, но этот режим отключается, когда включаются режимы неявных или пользовательских транзакций.

Неявные транзакции представляют собой другой тип автоматизированного управления транзакциями. Транзакция стартует автоматически всякий раз, когда запускаются определенные команды (ALTER TABLE, FETCH, REVOKE, CREATE, GRANT, SELECT, DELETE, INSERT, TRUNCATE TABLE, DROP, OPEN, UPDATE). В некотором смысле неявные транзакции представляют собой автоматизированную альтернативу явным транзакция.м - в тер.минах функциональных возможгюстей этот механизм находится между автоматическими транзакциями и пользовательскими. Эти транзакции только полуавтоматические, так как для их завершения требуется явное выполнение ROLLBACK TRAN или COMMIT TRAN. Автоматизирована только первая часть процесса - инициация транзакции. Ее завершение должно быть выполнено явно. Для переключения режима неявных транзакций используется команда Transact-SQL SET IMPLICIT TRANSACTIONS.

SET XACT ABORT влияет на то, будет ли транзакция прервана, если команда вызовет ошибку во время выполнения. Эта ошибка может быть системной или пользовательской. Данная настройка по существу является эквивалентом проверки @@ERROR после каждого оператора и отката транзакции при обнаружении ошибки. Заметьте, что эта команда называется не совсем правильно. Когда XACT ABORT включена и возникает ошибка во время выполнения, прерывается не только текущая транзакция, но и весь пакет команд. Например, рассмотрим следующий код:

SET XACT ABORT ON

SELECT TOP 5 au lname. au fname FROM authors ORDER BY au lnanie. au fname BEGIN TRAN DELETE authors DELETE sales

SELECT TOP 5 au lname. au fname FROM authors ORDER BY au lname. au fname ROLLBACK TRAN

PRINT End of batch -- never makes it here GO

SELECT TOP 5 au lname. au fname FROM authors ORDER BY au lname. au fname SET XACT ABORT ON

au Iname au fname

Bennet Abraham

Blotchet-HalIs Reginald Carson Cheryl