о

213-46-5915 409-56-7G08 26/-4:-2394 724-80-9391 213-46-3915

Обратите вт1Има1И1е иа сообщение об ощибке. С1еиерт1[Юваиное вторым оператором ROLLBACK TRAN. Так как первая команда ROLLBACK TRAN отменяет обе транзакции, второй ко.манде ROLLBACK TRAN нечего отменять. Такую ситуацию лучше всего обработать с использован!ге.м переменной @@TRANCOUNT, вот так;

IF (pglRANCOUNT>0 BEGIN ROLLBACK TRAN

SELECT After ROLLBACK TRAN .CglRANCCUNl

Неправильный Transact-SQL синтаксис в транзакциях

При активной транзакции некоторые синтаксически правильные ко.манды Transact-SQL использовать запрещено. Напри.мер, внутри транзакции нельзя использовать процедуру sp dboption для изменения настроек базы или вызывать другие процедуры, которые из.меияют базу master. Внутри транзакции также запрещено прилгенять следующие ко.манды Transact-SQL: ALTER DATABASE, DROP DATABASE, RECONFIGURE, BACKUP LOG, DUMP TRANSACTION, RESTORE DATABASE, CREATE DATABASE, LOAD DATABASE, RESTORE LOG, DISK IN IT LOAD TRANSACTION и UPDATE STATISTICS.

Отладка транзакций

Для отладки нробле.м, связанных с транзакциями, очень удобно использовать две команды DBCC (database consistency checker, команды проверки целостности базы данных). Первая из них - DBCC OPENTRAN(). С но\ющью нее можно получить самую старую активную транзакцию в базе данных. Поскольку резервное копирование и усечение производится только для неактивной части журнача тран.закций, из-за гранзакций-зомби журнал транзакцп!! \южет заполниться преждевременно. Вы можете использовать DBCC OPENTRAN() для оиределеиия преступного процесса, чтобы при необходимости его прервать. Вот при.мер: DBCC CPENTRAN(pubs)

Transaction information for database puos. Oldest active transaction:

SPID (server process ID) ; 15

UID (user ID)

Name

Start time

user transaction (57:376:596)

Aug 5 1999 5;54;46:713AM

Другая удобная команда для решения пробле.м, связанных с транзакциями -DBCC LOG. DBCC LOG показывает содержи.мое журна,ча транзакций базы данных. Вы можете использовать ее, чтобы заглянуть в за.мочную скважину и посмотреть, какие операции выполняются с вашими данны.ми. Вот пример;

CREATE TABLE #logrecs (CurrentLSN varchar(30).

Operation varchar(20).

Context varchar(20).

TransactionID varchar(20)) INSERT #logrecs EXECCDBCC LOGCpubs )) SELECT * EROM flogrecs GO

DROP TABLE #logrecs (результаты сокращены)

Обсуждение темы об отладке транзакций SQL Server было бы неполным без упоминания автоматической переменной @@TRANCOUNT. Хотя мы уже рассматривали ее в этой главе, @@TRANCOUNT часто используется в операторах PRINT и в отслеживаемых отладчиком выражениях, потому что она показывает текущий уровень вложенности транзакции. При отладке сложных вложенных транзакций обычно всюду в коде вставляют операторы SELECT или PRINT для определения текущего уровня вложенности в различных частях процедур.

И наконец, не забывайте о Windows NT Performance Monitor. Он предоставляет множество объектов и счетчиков, связанных с управлением транзакциями и производительностью. В частности, объект SQL Server:Databases предоставляет богатый выбор счетчиков, связанных с транзакциями и журналом транзакций.

Оптимизация кода транзакций

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

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

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

цируютцлми данные. ocouciHiO Kov.ih иа.м ие iia.iu иончорио читать данные в т[)анзакции.

О Pie ожила1 пе ввслча ио.чь.зоваче.ьч в траизакции. lie.iii вы будете так делать, .мед-.тительиьи ! ио.чьзовате.ть .молсет на нео!1})е.и..т1Чпи>11! срок захватить ресурсы cei)ne[)a. Также из-за этою 1>()3.\10 ллк) ирс/К'.чевре.меиное .зано.чнеиие жу[лшла траизактниТ поскольку актиынче транза!чЦ1И1 не .\1(лут быть уда.чеиы из него.

о По возмолчиости стараГгтссь применят!, опптмистическое управление совме-стны.м досчуио.чг То есть вместо явно!! блокировки каждого объекта, который \[ожет из.менить вате прп.чожение, нозво.тьте серве1)у опреде.тять, когда не-котора>1 запись была и.з.меиеиа друш.м noльзoвaтeлeг Если вы увидиче. что ())акчч1чески такие кои(1)ЛИ1чТЫ .межл\ ио.чь.>оватч'.1я.ми происходят очень редко (воз.можио, иото.му, что приложение изнача.илю ра.зделеио, или ното.му, что однажды введенные даштые и.шенякпся редко, или enie по каки.м-то причи-на.м), то это позво.чит увеличить н])опзводите.чьпость за счет снижения затрат на целостность данных.

о Разумно используйте нeжy[)нaлиpyeи^le операции. Как я уже указыва/г не-журпа.чируе.мые опе[)аиии препятпnyior осуптествлепню иорлгачьиого резервного конирования жу])нала тран.закцип. Это хгожет пе вызывать нробле.м, но нежурпалируе.чияе операции .чилут значительно .загрузить ириложенпе. Часто они .могут иа неско.чько порядксиз \.мепьшить вре.\и1 обработки значительных обт>емов данных и 4)актическн избавить от .чиюжества обпигх проблем, связанных с управлеппем тра1гзакцпя\пг По.\пи1те. что за это увеличение произ-водительпосттг иногда исобходи.мо платить. SELECT...INTO, напри.мер, блокирует системные таблицы, пока ие завсрннгг вьпю.чненпе.

о Постарайтесь исио.чьзовать менее запрет иге. н,ные у[)овии изоляцти!, ко1да это возможно. READ COMMITTED, уровеШ) пзо.чяцин по у.молчанню, вполне подходит для большинства приложепи11 и обеспечивает лучший сов.чгестпьп ! доступ, чем REPEATABLE READ или SERIALIZABLE,

О Посчарайтесь свесчч! к NHHurM\.\iy обт,е.\1 данных, из.меняе.мьгч в траизакции. Не пытайтесь без разбо[)а .молифиинровать .\и1ллноны загишей в таблице, ожидая, ччч) пара-чле.чьньн ! доступ и утилпзацня ресурсов .магнчески.м образом позаботятся о себе са.чиг. Операции .моди4)икацни базы данных требуют ресурсов и блокировок, а .эти блокировки, по определению, влияют иа других пользователе!!. Есл!1 только !!aii!e !1р11ложеи!!е не од!1опользоватсльекое. следует !10М!1!1ть об опера!И!ях, котор!)!е .могут !1егат!!вио !!0!и!!!ять па совмест-

И!>1! 1 ДОСТу!!.

О Не 1!с!Юльзу! !те !!еяв!!Ь!е тра!1закц!т, ес.ти !!ij1 де11ств1!Ч-ель!!о !ie нуждаетесь в !1!1х, !! даже !i это.\! случае при.ме!!Я1!те !!х очеш, осторожно. Поскольку не-

ЯВ1!Ь!е Чра!131Ж!ГИ!! И!Н!!Т!!!11}у!0ТСЯ !1()ЧЧ!1 BCC.m!! 0(Ч!(Л)!!ЬТ\!!1 ! ).\!а!1Да.м!! Тга!15аС1-

SQL (в1С1Ючая SELECT), oi!h .чгогчт ста[)товат1>. когда вь! этого меньи!е Bceio ож!1-лаете, те,\! са.\!1)!.м л!ожет С!!из11ться 1юз.можиость сов\!ест1!01ю досту!1а, и .мо-1ут вознт!кнутч> !!роблемы с журиало.ч! тран.закттн. Почт!! всегда лучи!е управлять тра1!зак1И!ЯХ!!1 явно с !!о,\!()!цыо ко.ма1!д BEGIN TRAN, COMMIT TRAN !i ROLLBACK TRAN, в.место roio чтобы ис1и.);п,зовать иеяв!!1>!С траизак!и1н. Когда

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

Заключение

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

Текущий уровень изоляции транзакций (transaction isolation level - TIL) влияет на изоляцию транзакций. Уровень изоляции устанавливается с помощью команды SET TRANSACTION ISOLATION LEVEL. Каждый уровень изоляции - компромисс между параллельным доступом и целостностью.

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

Хранимые процедуры и триггеры

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

Дэпни Тори

Хранимая процедура - это пакет SQL, хранится постоянно на сервере и компилируется при использова1ииг Он не транслируется в ма1пппныи или байт-код Java - ои псевдоко.\ии1лируется для ускорения запуска. Храни.\ияе процедуры со,зда10тся с использоватш.м команды Tran.sact-SQL CREATE PROCEDURE. На само.м деле при создании процедуры происходит добавление ее исходного кода в системную таблицу syscomments. Процедура не компилируется до ее первого запуска (ко.\тпляция происходит и в некоторых других ситуациях - смотрите следующиГ! раздел, Внутренняя оргшптзацня , для более подробно!! ипфор.мацтп!). Несмотря на свое т!азва!1ие, syscomments хранит гораздо бо.чьше, че.м ком.мента-ри!1, - это хра!И!Л!!!це !1Сходно1о кода хранимых процедур, представлений, трип-еров, прав!1л и значеп!!Й по у\!олчан1!1о. Если вы уладите !1сходиый код объекта из syscomments, объект бо;!Ьше не будет доступен. Вы можете полутть иг-ходн1Л11 код процедуры, представления, трштера, правила или зиачет!ия по умолчанию с по.мощью систе\!иой процедуры sp helptext. Есл!1 объект не зашифрован, sp helptext выведет е!ю !1схолный код с его исходным фор.матированием. Вот пример:

FXEC sp helptext spjexslrlng Text

CREATE PRGC dbo.sphexbtnng (int varcher(10)=NULL. ghexstring varchar(30)=NULL OUT /*

Объект; sp hexstnng

Описание: Возвращает целое значение в виде шестнадцатеричной строки

Использование- sp hexstnng РшЕ^Целое значение для преобразования. @ИехзЕг1пд=выходной

параметр, в который будет помещена шестнадца-еричпая строка Возвращает; (Ничего)

Разработана; Ken Honcenson. Email; kheniSkhen.com Версия; 1.0

Пример: sp hexstring 23. @myhex OUT

Создана: 1999-08-02. Дата последней модификации: 1999-08-15.

IF (gint IS NULL) OR (@int = ЧТ) GOTO Help

DECLARE (?i int. gvb varbinaryOO)

SELECT @i=CAST(iaint as int). l?vb=CAST(@i as varbinary)

EXEC master. .xp varbintohexstr gvb. ghexstmng OUT

RETURN 0

Help:

EXEC sp Lisage @objectname=sp hexstring.

@desc Bo3BpawaeT целое значение в виде шестнадцатеричной строки . @parameters=@int=Llenoe значение для преобразования, @hexstnng=OUTPUT выходной параметр, в который будет помещена шестнадцатеричная строка. @example=sp hexstring 23 . @myhex OUT. l?author=Ken Henderson, l?email = khenl?khen.com, (aversion=r , @revision=0.

I?datecreated=19990802, (Sdatelastchanged-19990815 RETURN -1

Преимущества хранимых процедур

Использование хранимых процедур имеет ряд преимуществ; вот некоторые из них:

О позволяют хранить и изменять бизнес-логику и политику в одном месте;

О позволяют совместно использовать прикладную логику различными приложениями;

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

О позволяют упростить использование параметризованных запросов, облегчая выполнение одного и того же запроса с [)азным набором параметров;

О с помощью автоматически запускающихся процедур можно выполнять процедуры каждый раз при запуске сервера;

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

О процедуры .могут предоставлять .механизмы безопасности, разрешая пользо-вателя.м контролируемый доступ к объектам базы данных, которые иначе они не смогли бы применить;

О с их по.мощью можно значительно снизить сетевой трафик, у.меньшив количество кода Transact-SQL, который должен проходить по ccTir для выполнения требуемой задачи;

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

Внутренняя организация

с цсиользозаипем хранимых процедур сзязат,! четыре (кчюипых шага:

1. Со:здаппе - когда зы ссадаете проп.елуру г помопи.ю CREATE PROC.

2. Запуск ио.чызователем - ког.ча in,i :!анускаете ее с помощью EXEC.

3. Ко.\ии1Ляция - когда сериер ком1Пгчпр\ет и ончи.\[изирует процедуру во вре.мя EXEC.

4. Выполнение серверо.м - когда сервер выполняет скомпилированный план выполнения во вре.мя EXEC.

Создание

На это.м Hiare вы используете ко.манду CREATE PROCEDURE для со.здания процедуры на сервере. Каждый раз, когда вы удачно со.здаете новую процедуру, ее название и другая важная ипфор.мация записывается в sysobjects, а ее исходный код - в syscomments. Ссылки на объекты, задействованные в xpaHH.Mofi процедуре, пе разрешаются до ее вьпю.чнення.

Запуск пользователем

Когда вы в первый раз запускаете новую со:у1апную храии.мую процедуру (или сервер nepeKO.vHHпирует ее), она считывается из syscomments и разрешаются ссылки на обт>екты, иснользуе.чиле в ней. Во вре.мя этого процесса командный процессор со.здает дерево последовательности (дерево запроса), которое затем передается опти.\и1затору запросов .чля ко.мпи.чяцни и оити.мизации.

Компиляция

После того как дерево запроса бььчо успеиию со.здапо, оитп.\итзатор запросов SQL Server ко.мпилнрует весь пакет, отн.мизируст его и проверяет права доступа.

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

О наличие предложений GROUP BY, ORDER BY и UNION: О количество данных, которые пропсдура должна получить; О использовапне соедннещи'! для связыва[И1я таблиц в.месте; О пара.метры индексов, и.меющ1гхся у таблиц;

О степень распределения данных в каж/ЧО.м ключево.м столбце индекса;

О применение операторов сравнения и значений в предложениях WHERE и HAVING,

Результато,\1 вьиюлнения этого процесса является илаи выполнения, и после того как OHTH.vnrjaTop завершит его создание, ои будет по.мещеи в процедурный кэш. Этот илаи выполнения состоит из шагов, необходи.мых для:

О выполнения работы хранимой процедуры; О выполнения ограничений;

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

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

Выполнение сервером

Во время фазы выполнения план выполнения обрабатывается последовательно, и каждый его шаг передается соответствующему внутреннему управляющему процессу. Существует множество внутренних управляющих процессов - менеджеры DDL и DML, менеджер транзакций, менеджер ODSOLE (для обработки процедур OLE-автоматизации, например sp OAcreate), менеджер хранимых процедур, сервисный менеджер, менеджер Transact-SQL и так далее. Эти менеджеры вызываются неоднократно, пока не будут обработаны все шаги в плане выполнения.

Планы выполнения не сохраняются на диске. Единственная часть хранимой процедуры, которая хранится постоянно, - это ее исходный код (в таблице syscomments). Поскольку планы выполнения хранятся в памяти, перезагрузка сервера удаляет все текущие планы выполнения (так же как недокументированная команда DBCC FREEPROCCACHEO).

SQL Server автоматически создаст план выполнения при следующих условиях:

О окружение при выполнении процедуры значительно отличается от окружения, в котором она была создана (более подробную информацию читайте в разделе Параметры окружения );

о изменились значения столбца schema ver таблицы sysobjects у любого из объектов, используемых в процедуре. Значения столбцов schema ver и base schema ver изменяются каждый раз, когда меняется информация о структуре таблицы; добавление или удаление столбцов, изменение типов данных, добавление и удаление ограничений, а также правил и значений по у.молчанию;

О изменилась статистика у любого из объектов, используемых в процедуре;

О удален индекс, применявшийся в плане выполнения хранимой процедуры;

о копия плана выполнения процедуры отсутствует в кэше. Планы выполнения удаляются из кэша, чтобы освободить место для новых планов, с использованием алгоритма LRU (least recently used, наименее часто используемый).

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

О создание процедуры с указанием опции WITH RECOMPILE (и затем ее выполнение);

О выполнение процедуры с указанием опции WITH RECOMPILE;

О пометка .iioooii таолпщл, исио.тьзуюте^К'я в процедуре, с помощью процедуры sp recompile (sp recompile просто изменяет значение столбца schema ver таблицы sysobjects). а зате.м выполнение процедуры.

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

После того как n.ian вьшолиення попадает в кэш, последуюите вызовы процедуры .могут повторно нспользоват ь план без перестроения дерева запроса или перекомпиляции плана. Таки.м образо.м опускаются два шага нз трех, необходи.мых для выполнения xpaiiH.Moii процедуры, .это основное преи.мущество с точки зрения производительности, ио сравнению с обычными пакета.ми SQL.

Создание хранимых процедур

Храни.мые процедуры создаются с ио.мощью ко.манды create procedure; H3.v[e-ияются они ко.мандой alter procedure. Преи.мущество использования alter proc для из.менения храии.чюГ! процедуры по сравпешио с create proc состоит в то.м, что alter proc сохраняет права доступа, а create proc - нет. Ключевое различие .между ни.ми состоит в том, что для alter proc нeoбxoдию использование тех же са.мых опций реко.мниляции и шифрова}иш, как и для первоначальной ко.манды create proc. В остально.м же се.мантика при.менения этих двух ко.манд совершенно одинаковая.

Процедура .может содержать любые допусти.мые команды Transact-SQL, кроме: create default create proc, create rule, create schema, create trigger и create view. Процедуры могут создавать базы данных, таблицы, индексы, но не создавать другие процедуры, значения но улюлчания (defaults), правила, схе-.мы. триггеры и иредставлення.

ПРИМЕЧАНИЕ---

GO - это не команда Transact-SQL. Это признак конца пакета команд, который сообщает таким инструментам, как Quer/ Analyzer и OSQL, где заканчивается один пакет SQL и начинается другой. Так что его нельзя использовать в хранимой процедуре - попытка сделать это просто прервет процедуру. Еще один довольно странный аспект того факта, что GO не является командой Transact-SQL, связан с комментариями. Вы не можете закомментировать GO с помощью /* */. Если GO является крайним левым элементом в строке, он прервет командный пакет, несмотря на маркеры комментариев. Поскольку из-за этого закрывающий маркер комментария не будет найден, вы получите сообщение об ошибке - не найден закрывающий маркер комментария. Как обойти эту проблему? Можно применить комментарии вида -, либо удалить GO вообще, либо удалить первую G ,

Для выполпепия create proc вы должны и.меть права sysadmin, db owner, или db ddladmin. Вы также .можете выполнять create proc, если это право явно предоставлено ва.м sysadmin или db owner.

Максимальный размер хранимой процедуры - это меньшее значение нз 65,536 x размер сетевого пакета (по умолчанию 4096 байт) и 250 Мбайт. Максимальное количество пара.метров процедуры - 1024.

Советы по созданию

о Включайте в каждую процедуру заголовок-комментарий, в котором указывайте ее автора, предназначение, дату создания и историю изменений, параметры, которые она получает, и т. д. Обычно этот блок с ком.ментария.ми помещают непосредственно до или сразу после оператора CREATE PROC (но перед остальной частью процедуры), для того чтобы ко.мментарии можно было хранить в таблице syscomments и просматривать их, используя утилиты Enterprise Manager и Query Analyzer. Вот системная хранимая процедура, которая генерирует заголовки-комментарии: USE master GO

IE OBJECT IDCdbD.sp .object script comments) IS NOT NULL DROP PROC dbD.sp object scr1pt comments

CREATE PROCEDURE dbo.sp Dbject script comments - - Необходимые параметры ?objectname sysname-NULL. ?desc sysname=NULL, -- Опциональные параметры parameters varch3r(8000)=NULL. example varchar(8000)=NULL. Pauthor sysname=NULL. ?email sysname=(none), Aversion sysname=NULL. prevision sysname=0. ?datecreated smalldatetime=NULL. ?datelastchanged smalldatet1me=NULL

Обьект: sp object script comments ,

Описание: Генерирует заголовки-комментарии для SOL-сценариев создания обьектов

Использование: sp object script comments ?objectrame- ObjectName . Pdesc= OnHcaHHe o6beKTa .9parameters= paraml[,param2...]

Разработана: Ken Henderson. Email: khenkhen.com

\Версия: 3.1

Прииер использования: 5p object script comments ?objectname= sp who . (adesc= Bo3BpamaeT

список задач, выполняющихся в настоящий иоиент . @parameters=[?loginr.ame] Создана; 1992-04-03. Дата последней модификации: 1999 07-01 01-13:00. */ AS

IF (?objectname+?desc) IS NULL GOTO Help

PRINT /* PRINT CHAR(13)

EXEC sp usage @objectname=9objectname. (adesc=№sc.