Transakcje w funkcjach

Transakcje w funkcjach są ograniczone — funkcje skalarne i tabelaryczne nie mogą samodzielnie modyfikować danych.

Zakres transakcyjności funkcji w SQL Server

W SQL Server funkcja użytkownika, czyli User-Defined Function (UDF), służy do obliczania wartości albo zwracania zestawu wierszy. Może być wywołana między innymi w instrukcji SELECT, w warunku WHERE, w wyrażeniu obliczającym kolumnę, w definicji widoku lub w innej funkcji. Funkcja skalarna zwraca pojedynczą wartość, natomiast funkcja tabelaryczna (Table-Valued Function, TVF) zwraca tabelę wynikową. Żaden z tych typów nie jest samodzielną procedurą transakcyjną i nie może rozpocząć niezależnej transakcji, zatwierdzić jej ani wycofać.

Ograniczenie wynika z modelu wykonywania funkcji. Funkcja jest częścią instrukcji, która ją wywołuje, dlatego działa w kontekście transakcji ustanowionym przez wywołującego. Jeżeli funkcja zostanie użyta w instrukcji wykonywanej wewnątrz jawnie rozpoczętej transakcji, jej odczyty uczestniczą w tej samej transakcji. Jeżeli instrukcja nie została objęta osobnym blokiem transakcyjnym, funkcja nie tworzy własnego, autonomicznego obszaru zapisu. SQL Server nie udostępnia w funkcjach odpowiednika autonomicznych transakcji znanych z niektórych innych systemów baz danych.

Instrukcje niedozwolone wewnątrz funkcji

Funkcja T-SQL nie może wykonywać operacji, których skutkiem byłaby trwała zmiana danych znajdujących się poza jej własnym zakresem obliczeń. W praktyce oznacza to zakaz używania w funkcji instrukcji INSERT, UPDATE i DELETE skierowanych do zwykłych tabel użytkownika. Nie można również umieścić w funkcji poleceń BEGIN TRANSACTION, COMMIT TRANSACTION ani ROLLBACK TRANSACTION. Funkcja nie może więc sama ustalić momentu zatwierdzenia albo wycofania pracy.

Ograniczenia obejmują także mechanizmy proceduralne, które mogłyby wywołać skutki uboczne. W funkcjach T-SQL nie używa się dynamicznego SQL wykonywanego przez EXEC lub sp_executesql, procedur składowanych ani instrukcji obsługi błędów TRY...CATCH. Nie można także korzystać z tabel tymczasowych tworzonych przez CREATE TABLE #nazwa. Wyjątkiem od ogólnej zasady dotyczącej modyfikacji jest zmienna tabelaryczna zadeklarowana wewnątrz funkcji. Funkcja może zapisywać dane do takiej zmiennej, ponieważ jest to wewnętrzny obszar roboczy służący do zbudowania wyniku, a nie trwała zmiana tabeli bazy danych.

Funkcja a procedura składowana

Różnica między funkcją a procedurą składowaną jest istotna przy projektowaniu przepływu danych. Funkcja może zostać użyta jako element wyrażenia lub źródło danych w zapytaniu. Musi zwrócić wartość skalarną albo wynik tabelaryczny zgodny ze swoją definicją. Procedura składowana jest natomiast przeznaczona do wykonywania sekwencji operacji, w tym modyfikowania danych oraz kontrolowania transakcji. Jeżeli wymagane jest zapisanie rekordu, aktualizacja wielu tabel, obsługa kolejności operacji i wycofanie zmian po błędzie, właściwym mechanizmem jest procedura składowana wywołana w kontrolowanym bloku transakcyjnym.

Przykładowy przepływ może wyglądać następująco: procedura rozpoczyna transakcję, sprawdza warunki biznesowe, wykonuje zapis do tabel i zatwierdza całość albo wycofuje wszystkie zmiany. Funkcja może w takim przepływie obliczyć wartość potrzebną do sprawdzenia warunku, ale nie powinna być traktowana jako etap zapisujący dane. Umieszczenie logiki modyfikującej w funkcji nie jest rozwiązaniem problemu, ponieważ parser SQL Server odrzuci niedozwoloną instrukcję albo projekt wymusi przebudowanie funkcji na procedurę. Podział ten ułatwia także rozpoznanie, gdzie znajduje się odpowiedzialność za spójność operacji.

Skutki ograniczeń dla diagnostyki zapytań

Podczas analizy problemu należy sprawdzić, czy funkcja tylko odczytuje dane i oblicza wynik, czy też oczekuje od niej działania właściwego dla procedury składowanej. Objawem nieprawidłowego projektu może być próba wykonania polecenia modyfikującego tabelę w funkcji, błąd kompilacji definicji albo brak oczekiwanego wpisu w tabeli po wywołaniu funkcji. Warto wtedy prześledzić miejsce wywołania, definicję funkcji oraz procedurę, która powinna odpowiadać za zapis. Szczególną uwagę należy zwrócić na modyfikacje zmiennych tabelarycznych, ponieważ są dozwolone, ale nie oznaczają zapisania danych do trwałej tabeli.

Znaczenie ma również sposób wywołania funkcji tabelarycznej. Inline TVF jest pojedynczym zapytaniem zwracającym wynik, natomiast wieloinstrukcyjna TVF korzysta z wewnętrznej zmiennej tabelarycznej, do której budowany jest rezultat. W obu przypadkach wynik jest częścią instrukcji wywołującej i podlega jej kontekstowi transakcyjnemu. Diagnostyka powinna więc obejmować poziom izolacji, blokady, czas trwania zapytania oraz kod procedury wywołującej, a nie tylko samą definicję funkcji. Jeżeli potrzebne jest atomowe wykonanie kilku zapisów, należy szukać transakcji w procedurze lub w kodzie aplikacji, który wywołuje procedurę, zamiast oczekiwać autonomicznego zapisu od funkcji.