се: Zbs:: \ .о innes

;5 rcw(5 ; affec!.ed)

5.:-/ег: Иьд b~i. Level 16. SiaLe 1. i.-ne i

DC.ITr steiener.t conflcied with COLUMN RtrERCNCr ccnstraim

F\ :,u eauth au C 16528:. Tne conflict occurrec n catstase putt.

tarJe fteauthor. column au ic.

au Inart au fna.Tie

Ee.nret Aoraham

Blotchet-HalIs Reginald

Cai-son Creryl

DeFrance Michel

de: Castillo Innes

(5 row(s) affectec)

Оператор PRINT никогда не будет выполнен, потому что нарушение ограничения, возникшее из-за попытки очистить таблицу authors, прерывает выполпе-пие всего пакета команд (операторов перед GO). II это несмотря на тот факт, что ROLLBACK TRAN на.ходится прямо перед PRINT.

Вот поче.му предпочтительнее проверять @@ERROR посте кажд011 мод,нфика-ини данных, а не вк.чючать SET XACT ABORT. Это особенно важно, когда в транзакции вызьшается храии.мая процедура. Если процедура вызовет опшбку во вре.мя выполнения, операторы, следуюпиш за пей в пакете ко.хшпд, не будут выполнены, и, таким об)азо.м, обработать ошибку будет невоз.можно.

Уровни изоляции транзакций

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

Для установки уровня изоляции используется ко.манда SET TRANSACTION ISOLATION LEVEL. Допустимые уровни изоляции включают: READ UNCOMMFTTED, READ COMMITTED, REPEATABLE READ и SERIALIZABLE.

READ UNCOMMITTED

Указание READ UNCOMMITTED, no существу, эквивалентно использованию подсказки (hint) NOLOCK для каждой таблицы, задействованной в транзакции. Этот уровень наи.менее запретительный из четырех уровней изоляции SQL Server. Он позволяет осуществлять грязное чтение (чтение незафиксированных из-

менений другими транзакциями) и неповторяющееся чтение (то есть когда данные могут изменяться между чтениями в транзакции). Чтобы увидеть, как READ UNCOMMITTED позволяет грязное и неповторяющееся чтение, запустите одновременно следующие запросы: -- Query 1

SELECT TOP 5 titlejd. qty FROM sales ORDER BY t1tle id. stor id BEGIN TRAN

UPDATE sales SET qty=0

SELECT TOP 5 titlejd. qty FROM sales ORDER BY title id. stor id WAITFOR DELAY 00:00:05 ROLLBACK TRAN

SELECT TOP 5 title id. qty FROM sales ORDER BY titlejd. stor id -- Query 2

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED PRINT Теперь вы это видите... SELECT TOP 5 title id. qty FROM sales WHERE qty=0

ORDER BY title 1d. storjd IF (3(aR0WC0UNT>0 BEGIN

WAITFOR DELAY 00:00:05

PRINT ...a теперь нет'

SELECT TOP 5 title id. qty FROM sales

WHERE qty=0

ORDER BY t1tle id. storjd

Сейчас вы это видите... title id qty

BU1032 О BU1032 О BU1032 О BUllll О BU2075 О

(5 row(s) affected) ...а тепперь нет title id qty

(О row(s) affected)

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

READ COMMITTED

READ COMMTTTED - это уровень изоляции SQL Server по умолчанию, так что если вы не укажете другой, будет использован READ СОММПТЕО. READ COMMITTED не разрешает грязное чтение, накладывая коллективные блокировки (share locks) на данные, с которыми проводится работа, но позволяет изменять эти данные во

Уровни изоляции транзакций 311

время траизакции. что может привести к иеиовторяемому чтению и/или фан-толиням данным. Чтобы увидеть, как это работает, выполните однов|)е.\1еиио следуюпще запросы:

-- Quer-y ;

5£Г T.RA,\SACTION ISOLAnO:-. l.tVhL KLAD CG-VVUED BEGIN TRAN

PRINT Здесь Mb, В.ЯИМ ЗаП;;си ,.

SELECI TCP 5 title ia, qty FROM sales CRuER tit:e !a, stor ic! .vAHFCR DELAY 00:00:05 PRINI ,., a здесь уже не;

SELECT TCP 5 titlejd, qty FROM saies ORDER BY tille id, stor id GO

ROLLBACK TRAN -- Gtery 2

SET TRA,NSACriON ISOLAIION LEVEI READ COMMinEu UPDATE sales SET qty=6 WHERE qty=5

Здесь мы видим записи.,. title id qty

BUI 032 5

BU1032 10

BUI 032 30

BUllll 25

BU2075 35

, a здесь уже нет titie 1d qty

BUi032 6

BU1032 10

BUi032 30

BUllll 25

BU2075 35

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

REPEATABLE READ

REPEATABLE READ накладывает блокировки, чтобы предотвратить из.менение другими пользователями даины.х, с которы.ми работает транзакция, но не запрещает добавление новых записей, поэто.му .между чтениями в транзакпии возможно возникновение ([анто.мных записей. Вот прп.мер (как и в случае других ири.меров, запустите первый запрос, затем параллельно второй - у вас есть пять секунд, чтобы запустить второй запрос): -- Оиегу 1

SET IRA.NSACTION ISOLATION LEVEL REPEATABLE READ BEGIN TRAN

PRINT Брюки превращаются ,

SELECT TOP 5 t1tle id, qty FROM sales ORDER BY qty

WAITFOR DELAY 00;00:05

PRINT ...в элегантные шорты'

SELECT ТОР 5 title icl. qty FROM sales ORDER BY qty

ROLLBACK TRAN -- Query 2

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

INSERT sales VALUES (6380.9999999.GETDATEC).2.USG-Whenever.PS2091)

Брюки превращаются... title id qty

PS2091 3

BUI032 5

PS2091 10

MC2222 10

BU1032 10

... в элегантные шорты title id qty

PS2091 2 PS2091 3 BU1032 5 PS2091 10 MC2222 10

Как вы видите, между чтениями таблицы sales появляются новые записи даже при том, что был указан уровень изоляции REPEATABLE READ. Хотя REPEATABLE READ предотвращает изменение данных, к которым уже был получен доступ, данный уровень не предотвращает добавление новых данных; таким образом, возможно появление фантомных записей.

SERIALIZABLE

SERIALIZABLE предотвраП1;ает грязное чтение и появление фанто.мных записей, накладывая серию блокировок на данные. Это наиболее запретитеЛьный уровень из четырех уровней изоляции SQL Server. Указание SERIALIZABLE эквивалентно при.менению подсказки HOLDLOCK для каждой таблицы, используемой в транзакции. Вот пример (удалите запись, которую вы добавили в предыдущем примере, перед те.м как выполнять этот код): -- Query 1

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE BEGIN TRAN

PRINT У меня в рукаве ничего нет. .

SELECT TOP 5 title id. qty FROM sales ORDER BY qty

WAITFOR DELAY 00:00:05

PRINT ...И в моей шляпе тоже

SELECT TOP 5 title id. qty FROM sales ORDER BY qty

ROLLBACK TRAN

-- Query 2

BEGIN TRAN

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

-- Этот INSERT не будет выполнен, пока не завершится первая транзакция INSERT sales VALUES (6380.9999999.GETDATEO.2.USG-Whenever.PS2091) ROLLBACK TRAN

у мен? 3 cyKdie V, пе

t1t ie ic Qty

ES2C91 3

eU1332 5

PS209: 1С

МС2222 1С

EU1032 IC

... и в моей л1ЛЯ1 ь I orie

title id Qty

PS2C91 3

BU1032 5

PS2C91 1С

MC2222 10

BUI 032 1С

В этом примере блокировки, палол<еииые li резу.тьтате установки уровня нзолящп! SERIALIZABLE, ие дадут вьиюлпиться второ.му запросу, пока не завер-иптгся первый. Хотя это и обеспечивает наибольшую целостность даины.х, она достается nenoii значительного умепьше1пи1 возхюжиости параллельного доступа.

Команды для управления транзакциями и их синтаксис

Как я сказал ранее, для уп)авления т|)апзакция\и1 в Transact-SQL служат команды BEGIN TRAN, COMMIT TRAN и ROLLBACK TRAN (храни.мые процедуры sp xxxx xact -это устаревптий код, который прн.мепялся в прошло.м в приложениях, использующих двухфазную фиксацию DB-Library, вы пе должны их ири.менять). Точный ситггаксис для начала траизакции такой; BEGIN T(.N[SACTiONj [nameCoTrdnNaineVar]

Для фиксации транзакции используйте

COMMIT lRAN[SACnONl [nainelPTranNameVar]

И для от.меиы траизакции -

ROLLBACK TRANISACTION] [nameiPTranNameVdr]

Вы также можете при.менить ко.манды COMMIT WORK и ROLLBACK WORK вместо COMMIT TRANSACTION и ROLLBACK TRANSACTION, хотя в них нельзя использовать названия транзакций.

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

Transact-SQL позволяет использовать вложенные транзакции с по.мощью b;io-женных ко.мапд BEGIN TRAN. Для определения уровня вложенности можно задействовать автоматическую пере.менную @@TRANCOUNT: О означает, что вложенности нет, 1 - глубину вложенности, равную единиие, и так далее. Пакеты и храни.мые процедуры, чувствительные к уровню вложенности, должны проверять @@TRANCOUNT при запуске и реагировать соответственно.

Хотя на самом деле все не так, как кажется: SQL Server не поддерживает настоящие вложенные транзакции. Команда COMMIT, выполненная для любой транзакции, кроме самой внешней, не запишет никакие изменения на диск - она просто уменьшит на единицу автоматическую переменную @@TRANCOUNT. С другой стороны, ROLLBACK работает независимо от уровня, на котором выполняется, она откатьшает все транзакции, независимо от уровня вложенности. Хотя это может показаться нелогичным, для такого поведения есть очень важные причины. Если бы вложенная команда COMMIT на са.мом деле записала данные на диск, внешняя команда ROLLBACK не смогла бы их от.менить, поскольку они уже зафиксированы. Аналогично, если бы ROLLBACK не от.меняла изменения на всех уровнях, ее вызов из хранимых процедур и триггеров был бы намного сложнее, так как вызывающей стороне пришлось бы проверять возвращенные значения и уровень вложенности транзакции, чтобы определить, необходимо ли отменить незавершенные транзакции. Вот пример, иллюстрирующий некоторые особенности вложенных транзакций:

SELECT Before BEGIN TRAN,(aiaTRANCOUNT BEGIN TRAN

SELECT After BEGIN TRAN.(3(aTRANC0UNT DELETE sales BEGIN TRAN nested

SELECT After BEGIN TRAN nested.PiaTRANCOUNT DELETE titleauthor COMMIT TRAN nested -- Просто уменьшает на единицу MTRancOUNT SELECT After COMMIT TRAN nested.WRANCOUNT GO -- Когда Это возможно, помещайте ROLLBACK TRAN в отдельный пакет, чтобы из-за ошибок

в пакете не осталось открытых транзакций ROLLBACK TRAN

SELECT After ROLLBACK TRAN.(aPTRANCOUNT SELECT TOP 5 au 1d FROM titleauthor

Before BEGIN TRAN 0 After BEGIN TRAN 1 After BEGIN TRAN nested After COMMIT TRAN nested

After ROLLBACK TRAN au id

213-46-8915 409-56-7008 267-41-2394 724-80-9391 213-46-8915

В этом примере .\п)1 видим, что, несмотря иа вложенную команду COMMIT TRAN, внешняя ко.манда ROLLBACK все ])ав1ю отменяет действие команды DELETE titleauthor. Вот другой пример вложенньгх rpansaKunii:

SELECT Before BEGIN TRAN.@@rRANCOUNT BEGIN IRAN

SELECT Afte- BEGIN TRAN.g@TRANCOUNr DELETE sales ELGIN TRAN nested

SELECT After BEGIN TRAN nested.@@TRANCOLNГ DELETE titleauthor ROLLBACK TRAN

SELECT After ROLLBACK TRAN,P(aTRANCOUNT IE @@IRANCOUNT>C BEGIN

COMMIT TRAN -- Эта команда никогда не выполнится из-за ROLLBACK SELECT After COMMIT TRAN,g@TRANCOUNT

SELECT fOP 5 au id FROM Litleauthor

Before BEGIN TRAN 0

After BEGIN TRAN 1

Atter BEGIN TRAN nested

After ROLLBACK TRAN au id

213-46-8915 409-56-7008 267-41-2394 724-80-9391 213-46-8915

В .этом примере вьиюлиение никогда не дойдет до COMMIT TRAN, поскольку ROLLBACK TRAN от.меняет все открытые транзакции и устанавливает @@TRANCOUNT в нуль.

Обратите внимание, что мы пе сможем выполнить ROLLBACK для именованной транзакции nested. ROLLBACK может отменить и.менованную транзакцию, только еслтг .это са.мая внешняя транзакция. Попытка откатить эту транзакцию приведет к сообшению:

Server: Msg 6401, Level 16, State 1, Line 10

Cannot roll back nested. No transaction or savepoint of that name was found.

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

SELECT Before BEGIN TRAN main.(agTRANCOUNT BEGIN TRAN main

SELECT After BEGIN TRAN main.(agTRANCOUNT DELETE sales BEGIN TRAN nested

SEtECT After BEGIN TRAN nested.№TRANCOUNT DELETE titleauthor ROLLBACK TRAN main

SEtECT After ROLLBACK TRAN main,@@TRANCOUNT IF @@TRANCOUNT>0 BEGIN

ROLtBACK TRAN -- Выполнение никогда не дойдет до этого места из-за предыдущей команды ROLLBACK

SELECT After ROLI BACK TRAN .lagTRANCOUNT

SEtECT TOP 5 au id FROM titleauthor

Before BEGIN TRAN main 0 After BEGIN TRAN main 1 After BEGIN TRAN nested 2

After ROLLBACK TRAN main 0 au id

213-46-8915 409-56-7008 267-41-2394 724-80-9391 213-46-8915

В этом примере мы назвали внешнюю транзакцию main , а зате.м использовали ее имя в ROLLBACK TRAN. Заметьте, что ROLLBACK TRAhJ не требует указания Названия транзакции, независимо от того, была ли транзакция инициирована с имени. По этой причине многие разработчики вообще избегают применения названий транзакций с ROLLBACK, так как это не имеет смысла. Это в значительной степени вопрос личного выбора, и все работает хорошо, пока вы понимаете, как это работает. Если только не указана точка сохранения (смотрите далее), ROLLBACK TRAN всегда отменяет все транзакции и устанавливает @@TRANCOUNT в нуль, независимо от контекста, в котором она была вызвана.

SAVE TRAN и точки сохранения

Вы можете контролировать, какие транзакции отменяет ROLLBACK, с по.мошью команды SAVE TRAN. SAVE TRAN создает точку сохранения, до которой вы можете выполнить отмену, если захотите. Синтаксически вы просто передаете название точки сохранения команде ROLLBACK TRAN. Вот пример:

SELECT Before BEGIN TRAN main.?@TRANCOUNT

BEGIN TRAN main

SELECT After BEGIN TRAN main.OgTRANCOUNT

DELETE sales

SAVE ;,RAi\ Sa eS - >L MrasTi/idcM ЮНчу LjAPa.-eiul

SELECT Afte- SAVE TRAN saies.(?(?:RANCOUNГ -- 3-id4eri/e i=3jRANC0uN re изненилссь BEGIN TRAN rested

SELECT After BEGIN TRAN rested.S? ANCCJNT

DELETE titled.thor

SAVE TRAl titieautiiCr yciandii/iBden очку со/оан'ен/я SELECT After SAVE TRAN ttleautior.?(?TRANCOJNT -- Значение @@TRANCCUNt не изменипось ROLLSACK TRAN sales

SELECT After ROLLBACK IRAN sales .C={aIANCCCN -- Знанение (a(?TRANCCUNT не изменилось

SELECT TOP 5 aj id FRC. t:tlea.trior IF (a?TRANCOUNT>0 BEGIN ROLLBACK TRAN

SELECT After ROLLBACK TRAN ,C*TRAICCLIN

SELECI TOP 5 au id FROM tUieautnor

Before BEGIN TRAN inain 0

After BEGIN TRAN ir.air 1

After SAVE TRAN sales 1 After BEGIN TRAN nested 2

After SAVE TRAN titleautnor 2

Ater ROLLBACK TRAN sales au id

213-46-8915 409-56-7006 267-41-2394 724-80-9391 213-46-8915

After RCLLBACK. TRAN 0 au id

213-46-8915 409-56-7008 267-41-2394 724-80-9391 213-46-8915

After ROLLBACK TRAN au id

213-46-8915 409-56-7008 267-41-2394 724-80-9391 213-46-8915

Server: Msg 3903. Level 16. State 1. Line 17

The ROLLBACK TRANSACTION request has ПО corresponding BEGIN TRANSACTION. After ROLLBACK TRAN 0

Как и в версии 6.5, SQL Server 7.0 позволяет повторно применить название точки сохранения, но, если вы это делаете, запоминается только последняя точка сохранения. Отмена с использованием названия точки сохранения будет осуществлена до последнего места задействования точки сохранения.

Избегайте лишних вызовов ROLLBACK

Поскольку ROLLBACK TRAN отменяет все открытые транзакции, важно избегать неосторожных повторных вызовов этой команды. Как только она вызвана один раз, нет необходимости (да и не разрещено) вызывать ее снова, пока не инициирована новая транзакция. Например, рассмотрим следующий код:

SELECT Before BEGIN TRAN .№TRANCOUNT BEGIN TRAN

SELECT After BEGIN TRAN.(a@TRANCOUNT

DELETE sales BEGIN TRAN nested

SELECT After BEGIN TRAN nested.@(?TRANCOUNT

DELETE titleauthor

IF @@ROWCOUNT > 1000 COMMIT TRAN nested

ELSE BEGIN

ROLLBACK TRAN -- Полностью отменяет обе транзакции SELECT After ROLLBACK TRAN,@@TRANCOUNT

SELECT TOP 5 au id FROM titleauthor ROLLBACK TRAN - A здесь произойдет ошибка -- нет транзакций для отката SELECT After ROLLBACK TRAN.@@TRANCOUNT

SELECT TOP 5 au id FROM titleauthor Before BEGIN TRAN 0

After BEGIN TRAN 1

After BEGIN TRAN nested 2