Obsługa wyjątków na SQL - (nie) daj się złapać
Będzie trochę o obsłudze wyjątków na SQL Server. O tym, kiedy może okazać się przydatna i jakie czyhają pułapki. A będzie na przykładzie. Otóż mamy sytuację, w której musimy na chwilę zdropić sporą ilość obiektów proceduralnych na bazie danych, aby je po wykonaniu pewnej operacji odtworzyć. Po co? O tym za następnym razem. Dziś skupię się na zawiłościach dropienia i odtwarzania obiektów. Dla uproszczenia wywodu przyjmijmy, że wszystkie interesujące nas obiekty należą do schematu dbo i są procedurami, widokami, funkcjami TSQL wszelkiej maści lub triggerami. Przyjmijmy też, że nazwy obiektów podlegających naszej operacji mamy w następującej tabeli:
create table dbo.ObjectsToRecreate (
ObjectName sysname NOT NULL,
ObjectDefinition sysname NULL,
ObjectId int NULL,
CONSTRAINT PK_ObjectsToRecreate PRIMARY KEY (ObjectName))
Aby było co odtwarzać po zdropieniu - zapiszmy sobie definicje obiektów do naszej tabeli. Z SQL Server 2005 sprawa jest prosta - używamy nowej, fajnej funkcji object_definition.
update ObjectsToRecreate SET ObjectDefinition = OBJECT_DEFINITION(OBJECT_ID(ObjectName))
Kiedy już mamy kopię treści ciał obiektów możemy zabrać się do dropienia. O ile obiekty nie są użyte w innych z klauzulą SCHEMABINDING, tudzież w niskopoziomowych obiektach takich jak defaulty, checki czy kolumny wyliczalne - operacja powinna pójść bezproblemowo. Jak? Sprawę załatwi kawałek dynamicznego SQL-a.
declare @sql nvarchar(max)
select @sql = N''
select @sql = @sql +N'drop '+ case when o.type in ('IF','TF','FN') then N'function '
when o.type = 'P' then N'procedure '
when o.type = 'V' then N'view '
when o.type = 'TR' then N'trigger '
end +schema_name(o.schema_id)+N'.'+quotename(o.name)+N';'
from dbo.objectsToRecreate otr
join sys.objects o on otr.ObjectName = o.name
exec (@sql)
Teraz śmiało wykonujemy naszą operację, której przeszkadzałyby zdropione obiekty i zabieramy się do ich odtworzenia. Tym razem raczej nie obejdzie się bez kursora. Nie wydaje się on na pierwszy rzut oka czymś specjalnie trudnym:
declare @definition nvarchar(max)
declare c cursor local static read_only forward_only
for select ObjectDefinition from dbo.objectsToRecreate
open c
fetch next from c into @definition
while @@fetch_status = 0 begin
exec (@definition)
fetch next from c into @definition
end
close c deallocate c
Wszystko zadziała pięknie, o ile usunięte uprzednio obiekty nie będą zależne od siebie. Szczególnie boleśnie możemy to odczuć na widokach i funkcjach. Jeśli nie odtworzymy obiektów we właściwej kolejności – operacja po prostu się nie uda. Pytanie tylko: Jak znaleźć tę właściwą kolejność? Jak się okazuje – pytanie niebanalne. Mamy wprawdzie tabelę sys.sql_dependencies, ale po zdropieniu obiektów pustą, a i przed trudno wciąż jej ufać. Nawet gdy ją mamy porządnie uzupełnioną – rozplątanie właściwej kolejności obiektów w skomplikowanym przypadku wcale nie jest oczywiste. O sys.sql_dependencies napiszę innym razem – to temat na osobny artykuł. Tymczasem jest dużo prostsza metoda na uzyskanie właściwej kolejności. Jednym zdaniem: próbuj odtwarzać obiekty do skutku. Osiągniemy to łatwo obudowując nasz kod pętlą while. Kończymy, gdy uda nam się odtworzyć wszystkie obiekty. Aby nie oglądać paskudnych komunikatów błędów – opakujmy nasz kod bloki obsługi wyjątków:
declare @definition nvarchar(max), @name sysname
while exists (select 1 from dbo.objectsToRecreate where objectId is null) begin
declare c cursor local static read_only forward_only
for
select ObjectDefinition, ObjectName
from dbo.objectsToRecreate where objectId is null
open c
fetch next from c into @definition, @name
while @@fetch_status = 0 begin
begin try
exec (@definition)
end try
begin catch
set @definition = @name+':'+error_message()
print @definition
end catch
update dbo.objectsToRecreate set objectId = object_id(@name) where ObjectName = @name
fetch next from c into @definition, @name
end
close c deallocate c
end
Jeśli nasza baza była spójna na moment zdropienia, po kilku iteracjach będziemy mieli prawidłowo odtworzone obiekty. Niestety nasza radość trwa do momentu opakowania w/w kodu w transakcję. Po pierwszym błędzie, każda kolejna próba odtworzenia, prawidłowa lub nie, kończy się komunikatem:
Msg 3930, Level 16, State 1, Line 18
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.
Co gorsza, ten kod nigdy się nie kończy, bo nigdy nie dojdziemy do warunku wyjścia z pętli. I niestety dochodzimy tu do poważnego ograniczenia stosowalności mechanizmu obsługi wyjątków na SQL-u w obecnie dostępnych wersjach. I właśnie z tego powodu w tym miejscu porzuciłem ten mechanizm do rozwiązania bieżącego problemu i dobrałem się do niego w zupełnie inny sposób. Wniosek: jeśli używamy try catch, nie liczmy na to, że uda nam się w tej samej transakcji cokolwiek jeszcze wykonać poza zaprezentowaniem błędu. Musimy być zawsze gotowi na zakończenie transakcji po takiej operacji.