Udostępnij za pośrednictwem

Kontrolowanie trwałości transakcji

  • Artykuł

Dotyczy:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

Zatwierdzenia transakcji w programie SQL Server mogą być w pełni trwałe (co jest domyślnym ustawieniem SQL Server) lub opóźnione (nazywane również zatwierdzeniem leniwym).

W pełni trwałe zatwierdzenia transakcji są synchroniczne, zgłaszają sukces zatwierdzenia i zwracają kontrolę do klienta dopiero po zapisaniu na dysku rekordów dziennika dotyczących transakcji. Opóźnione zatwierdzenia transakcji trwałe są asynchroniczne i zgłaszają zatwierdzenie jako pomyślne, zanim rekordy dziennika transakcji zostaną zapisane na dysku. Zapisanie wpisów dziennika transakcji na dysku jest wymagane, aby transakcja jest trwała. Opóźnione trwałe transakcje stają się trwałe, gdy wpisy dziennika transakcji są przechowywane na dysku.

Ten artykuł zawiera szczegółowe informacje o opóźnionych transakcjach trwałych.

Pełna a opóźniona trwałość transakcji

Zarówno pełna, jak i opóźniona trwałość transakcji mają zalety i wady. Aplikacja może mieć kombinację w pełni i opóźnionych trwałych transakcji. Należy dokładnie rozważyć potrzeby biznesowe i sposób, w jaki każdy z nich pasuje do tych potrzeb.

Pełna trwałość transakcji

W pełni trwałe transakcje zapisują dziennik transakcji na dysku przed zwróceniem kontroli do klienta. Zawsze należy używać w pełni trwałych transakcji:

  • System nie może tolerować żadnej utraty danych. Zobacz sekcję Kiedy mogę utracić dane?, aby uzyskać informacje na temat czasu utraty niektórych danych.

  • Wąskie gardło nie wynika z opóźnienia zapisu dziennika transakcji.

Opóźniona trwałość transakcji zmniejsza opóźnienie ze względu na operacje I/O dziennika, utrzymując rekordy dziennika transakcji w pamięci i zapisując je do dziennika transakcji w partiach, co wymaga mniejszej liczby operacji I/O. Opóźniona trwałość transakcji potencjalnie zmniejsza przeciążenie I/O dziennika, co zmniejsza czekanie w systemie.

Pełne gwarancje trwałości transakcji

  • Po pomyślnym zatwierdzeniu transakcji zmiany wprowadzone przez transakcję są widoczne dla innych transakcji w systemie. Aby uzyskać więcej informacji na temat poziomów izolacji transakcji, zobacz SET TRANSACTION ISOLATION LEVEL (Transact-SQL) lub transakcji z tabelami Memory-Optimized.

  • Trwałość jest gwarantowana po zatwierdzeniu. Odpowiednie rekordy dziennika są utrwalane na dysku, zanim zatwierdzenie transakcji zakończy się pomyślnie i zwróci kontrolę do klienta.

Opóźniona trwałość transakcji

Trwałość opóźnionych transakcji jest osiągana poprzez asynchroniczne zapisywanie dziennika na dysku. Rekordy dziennika transakcji są przechowywane w buforze i zapisywane na dysku, gdy bufor wypełnia lub ma miejsce zdarzenie opróżniania buforu. Opóźniona trwałość transakcji zmniejsza zarówno opóźnienia, jak i spór w systemie, ponieważ:

  • Przetwarzanie zatwierdzenia transakcji nie czeka na ukończenie operacji wejścia/wyjścia dziennika i przekazanie kontroli z powrotem do klienta.

  • Transakcje współbieżne mają mniejszą szansę na konkurowanie o operacje IO dziennika; zamiast tego bufor dziennika można zrzucić na dysk w większych porcjach, zmniejszając konkurencję i zwiększając przepływność.

    Uwaga

    Nadal może występować konflikt we/wy dziennika, jeśli występuje wysoki stopień współbieżności, zwłaszcza jeśli wypełniasz bufor dziennika szybciej, niż go opróżniasz.

Kiedy należy używać opóźnionej trwałości transakcji

Niektóre przypadki, w których można skorzystać z używania opóźnionej trwałości transakcji, to:

Możesz tolerować utratę danych.
Jeśli możesz tolerować pewną utratę danych, na przykład kiedy indywidualne rekordy nie są krytyczne tak długo, jak masz większość danych, opóźniona trwałość może być warta rozważenia. Jeśli nie możesz tolerować utraty danych, nie używaj opóźnionej trwałości transakcji.

Występuje wąskie gardło operacji zapisu dziennika transakcji.
Jeśli problemy z wydajnością są spowodowane opóźnieniem zapisu dziennika transakcji, aplikacja prawdopodobnie skorzysta z używania opóźnionej trwałości transakcji.

Obciążenia mają wysoki współczynnik zatłoczenia zasobów.
Jeśli twój system ma obciążenia o wysokim poziomie współzawodnictwa, wiele czasu jest tracone na oczekiwanie na zwolnienie blokad. Opóźniona trwałość transakcji skraca czas zatwierdzania, a tym samym zwalnia blokady szybciej, co skutkuje większą przepływnością.

Opóźnione gwarancje trwałości transakcji

  • Po pomyślnym zatwierdzeniu transakcji zmiany wprowadzone przez transakcję są widoczne dla innych transakcji w systemie.

  • Trwałość transakcji jest gwarantowana tylko po opróżnieniu dziennika transakcji w pamięci na dysku. Dziennik transakcji w pamięci jest opróżniany na dysk, gdy:

    • W pełni niezawodna transakcja w tej samej bazie danych wprowadza zmianę i pomyślnie ją zatwierdza.

    • Użytkownik pomyślnie wykonał systemową procedurę składowaną sp_flush_log.

      Jeśli transakcja w pełni trwała lub sp_flush_log zakończy się sukcesem, wszystkie wcześniej zatwierdzone transakcje z opóźnioną trwałością mają gwarancję, że zostały utrwalone.

    • Program SQL Server próbuje opróżnić dziennik na dysk zarówno na podstawie generowania dziennika, jak i czasu, nawet jeśli wszystkie transakcje są opóźnione. Zwykle kończy się to powodzeniem, jeśli urządzenie we/wy nadąża z pracą. Jednak program SQL Server nie zapewnia żadnych gwarancji trwałości innych niż trwałe transakcje i sp_flush_log.

Jak kontrolować trwałość transakcji

Kontrola poziomu bazy danych

Administrator bazy danych może kontrolować, czy użytkownicy mogą używać opóźnionej trwałości transakcji w bazie danych za pomocą następującej instrukcji. Należy ustawić ustawienie opóźnionej trwałości przy użyciu polecenia ALTER DATABASE.

ALTER DATABASE ... SET DELAYED_DURABILITY = { DISABLED | ALLOWED | FORCED }

WYŁĄCZONE
[domyślnie] Dzięki temu ustawieniu wszystkie transakcje zatwierdzane w bazie danych są w pełni trwałe, niezależnie od ustawienia poziomu zatwierdzenia (DELAYED_DURABILITY=[ON | OFF]). Nie ma potrzeby zmiany procedury składowanej i ponownej jej kompilacji. Pozwala to zagwarantować, że żadne dane nie są nigdy zagrożone przez opóźnioną trwałość.

DOZWOLONE
W przypadku tego ustawienia trwałość każdej transakcji jest określana na poziomie transakcji — DELAYED_DURABILITY = { OFF | WŁĄCZONE }. Aby uzyskać więcej informacji, zobacz Kontrola poziomu bloków atomowych — natywnie skompilowane procedury składowane oraz Kontrola poziomu zatwierdzenia.

WYMUSZONE
Dzięki temu ustawieniu każda transakcja zatwierdzana w bazie danych jest opóźniona. Niezależnie od tego, czy transakcja określa pełną trwałość (DELAYED_DURABILITY = OFF) lub nie dokonuje żadnej specyfikacji, to jest opóźniona trwałość transakcji. To ustawienie jest przydatne, gdy opóźniona trwałość transakcji jest przydatna dla bazy danych i nie chcesz zmieniać żadnego kodu aplikacji.

Kontrola na poziomie atomowych bloków — procedury składowane kompilowane natywnie

Poniższy kod znajduje się wewnątrz bloku niepodzielnego.

DELAYED_DURABILITY = { OFF | ON }

WYŁĄCZONE
[ustawienie domyślne] Transakcja jest w pełni trwała, chyba że opcja bazy danych DELAYED_DURABILITY = FORCED jest w mocy, w tym przypadku zatwierdzenie jest asynchroniczne i w związku z tym opóźnione. Aby uzyskać więcej informacji, zobacz Kontrola na poziomie bazy danych.

ON
Transakcja jest opóźniona, chyba że opcja bazy danych DELAYED_DURABILITY = WYŁĄCZONE jest aktywna, w którym to przypadku zatwierdzenie jest synchroniczne i dlatego w pełni trwała. Aby uzyskać więcej informacji, zobacz Kontrola na poziomie bazy danych.

przykładowy kod :

CREATE PROCEDURE [<procedureName>] /* parameters go here */
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS BEGIN ATOMIC WITH
(
    DELAYED_DURABILITY = ON,
    TRANSACTION ISOLATION LEVEL = SNAPSHOT,
    LANGUAGE = N'English'
)
/* procedure body goes here */
END

Tabela 1. Trwałość bloków atomowych

Opcja trwałości bloków atomowych Brak istniejącej transakcji Transakcja w toku (w pełni lub opóźniona trwała)
DELAYED_DURABILITY = WYŁĄCZONE Blok atomowy rozpoczyna nową w pełni trwałą transakcję. Blok atomowy tworzy punkt zapisu w istniejącej transakcji, a następnie rozpoczyna nową transakcję.
DELAYED_DURABILITY = ON Blok atomowy rozpoczyna nową transakcję opóźnioną i trwałą. Blok atomowy tworzy punkt zapisu w istniejącej transakcji, a następnie rozpoczyna nową transakcję.

Kontrola poziomu zatwierdzenia (COMMIT) —Transact-SQL

Składnia COMMIT jest rozszerzona, dzięki czemu można wymusić opóźnioną trwałość transakcji. Jeśli DELAYED_DURABILITY jest wyłączona lub wymuszona na poziomie bazy danych (patrz powyżej), ta opcja COMMIT jest ignorowana.

COMMIT [ { TRAN | TRANSACTION } ] [ transaction_name | @tran_name_variable ] ] [ WITH ( DELAYED_DURABILITY = { OFF | ON } ) ]

WYŁĄCZONE
[ustawienie domyślne] Zatwierdzenie transakcji jest w pełni trwałe, chyba że opcja bazy danych DELAYED_DURABILITY = FORCED jest w mocy, w tym przypadku zatwierdzenie jest asynchroniczne, a tym samym opóźnione. Aby uzyskać więcej informacji, zobacz Kontrola na poziomie bazy danych.

ON
Zatwierdzenie transakcji jest opóźnione w zakresie trwałości, chyba że opcja bazy danych DELAYED_DURABILITY = WYŁĄCZONE jest aktywna; w takim przypadku zatwierdzenie jest synchroniczne i w pełni trwałe. Aby uzyskać więcej informacji, zobacz Kontrola na poziomie bazy danych.

Podsumowanie opcji i ich interakcji

Ta tabela zawiera podsumowanie interakcji między ustawieniami opóźnionej trwałości na poziomie bazy danych i ustawieniami poziomu zatwierdzenia. Ustawienia na poziomie bazy danych zawsze mają pierwszeństwo przed ustawieniami poziomu zatwierdzenia.

Ustawienie ZATWIERDZENIA/Ustawienie bazy danych DELAYED_DURABILITY = WYŁĄCZONE OPÓŹNIONA TRWAŁOŚĆ = DOZWOLONA DELAYED_DURABILITY = WYMUSZONE
DELAYED_DURABILITY = WYŁĄCZONE transakcje na poziomie bazy danych. Transakcja jest w pełni trwała. Transakcja jest w pełni trwała. Transakcja jest długotrwale opóźniona.
DELAYED_DURABILITY = ON Transakcje na poziomie bazy danych. Transakcja jest w pełni trwała. Transakcja jest trwale opóźniona. Transakcja jest trwale opóźniona.
DELAYED_DURABILITY = OFF Transakcja międzybazodanowa lub rozproszona. Transakcja jest w pełni trwała. Transakcja jest w pełni trwała. Transakcja jest w pełni trwała.
DELAYED_DURABILITY = ON Transakcja międzybazowa albo rozproszona. Transakcja jest w pełni trwała. Transakcja jest w pełni trwała. Transakcja jest w pełni trwała.

Jak wymusić opróżnienie dziennika transakcji

Istnieją dwa sposoby wymuszenia opróżnienia dziennika transakcji na dysku.

  • Wykonaj dowolną w pełni trwałą transakcję, która zmienia tę samą bazę danych. Wymusza to zapisanie na dysk rekordów dziennika wszystkich poprzednich zatwierdzonych transakcji z opóźnioną trwałością.

  • Wykonaj procedurę składowaną systemu sp_flush_log. Ta procedura wymusza opróżnienie rekordów dziennika wszystkich poprzednich zatwierdzonych opóźnionych transakcji trwałych na dysku. Aby uzyskać więcej informacji, zobacz sys.sp_flush_log (Transact-SQL).

Opóźniona trwałość i inne funkcje programu SQL Server

Replikacja transakcyjna, Śledzenie zmian i Przechwytywanie danych zmian

  • W przypadku baz danych z włączoną obsługą replikacji transakcyjnej lub przechwytywania zmian danych (CDC) użycie opóźnionej trwałości nie jest obsługiwane.

  • Śledzenie zmian z opóźnioną trwałością jest obsługiwane. Wszystkie transakcje z rozwiązaniem Change Tracking są w pełni trwałe. Transakcja ma właściwość śledzenia zmian, jeśli wykonuje jakiekolwiek operacje zapisu w tabelach, które włączyły śledzenie zmian.

Począwszy od programu SQL Server 2022 CU 2 i programu SQL Server 2019 CU 20, możesz zauważyć:

  • Error 22891: Could not enable '_FeatureName_' for database '_DatabaseName_'. '_FeatureName_' cannot be enabled on a DB with delayed durability set, jeśli spróbujesz włączyć replikację transakcyjną lub przechwytywanie zmian danych w bazie danych, która ma włączoną opóźnioną trwałość.

  • Error 22892: Could not enable delayed durability on DB. Delayed durability cannot be enabled on a DB while '_FeatureName_' is enabled w przypadku próby włączenia opóźnionej trwałości bazy danych skonfigurowanej przy użyciu replikacji transakcyjnej lub przechwytywania zmian danych.

Odzyskiwanie po awarii
Spójność jest gwarantowana, ale niektóre zmiany z opóźnionych trwałych transakcji, które zostały zatwierdzone, mogą zostać utracone.

Międzybazowe i DTC
Jeśli transakcja jest między bazami danych lub rozproszona, jest w pełni trwała, niezależnie od ustawienia zatwierdzenia bazy danych lub transakcji.

zawsze włączone grupy dostępności i dublowanie
Opóźnione trwałe transakcje nie gwarantują żadnej trwałości na głównym ani na żadnej z wtórnych replik. Ponadto nie gwarantują żadnej wiedzy na temat transakcji na rynku wtórnym. Po zatwierdzeniu kontrola jest przekazywana do klienta przed odebraniem potwierdzenia z jakiejkolwiek synchronicznej jednostki wtórnej. Replikacja do replik pomocniczych nadal odbywa się w miarę opróżniania dysku na serwerze podstawowym.

klastrowanie awaryjne
Niektóre odroczone zapisy transakcji trwałych mogą zostać utracone.

Azure Synapse Link for SQL
Opóźnione trwałe transakcje nie są obsługiwane w usłudze Azure Synapse Link dla języka SQL.

przesyłanie dzienników
Tylko transakcje, które zostały wykonane trwałe, są zawarte w dzienniku, który jest dostarczany.

Kopia zapasowa dziennika transakcji
W kopii zapasowej są uwzględniane tylko transakcje, które zostały utrwalone.

Kiedy można utracić dane?

W przypadku zaimplementowania opóźnionej trwałości w dowolnej tabeli należy zrozumieć, że niektóre okoliczności mogą prowadzić do utraty danych. Jeśli nie możesz tolerować utraty danych, nie należy używać opóźnionej trwałości w tabelach.

Katastrofalne zdarzenia

W przypadku katastrofalnego zdarzenia, takiego jak awaria serwera, utracisz dane dla wszystkich zatwierdzonych transakcji, które nie zostały zapisane na dysku. Opóźnione trwałe transakcje są zapisywane na dysku za każdym razem, gdy w pełni trwała transakcja jest wykonywana względem dowolnej tabeli (zoptymalizowanej pod kątem pamięci lub opartej na dysku) w bazie danych lub gdy wywoływane jest sp_flush_log. Jeśli używasz opóźnionych transakcji trwałych, możesz utworzyć małą tabelę w bazie danych, którą można okresowo aktualizować lub okresowo wywoływać sp_flush_log, aby zapisać wszystkie zaległe zatwierdzone transakcje. Dziennik transakcji również opróżnia się za każdym razem, gdy stanie się pełny, ale trudno jest przewidzieć i nie można kontrolować.

Zamykanie i ponowne uruchamianie programu SQL Server

W przypadku opóźnionej trwałości nie ma różnicy między nieoczekiwanym zamknięciem a oczekiwanym zamknięciem/ponownym uruchomieniem programu SQL Server. Podobnie jak zdarzenia katastrofalne, należy zaplanować utratę danych. W przypadku planowanego zamknięcia/ponownego uruchomienia niektóre transakcje, które nie zostały zapisane na dysku, mogą być zapisane na dysku przed zamknięciem, ale nie należy na tym polegać. Planuj tak, jakby zamknięcie lub ponowne uruchomienie, niezależnie od tego, czy jest planowane, czy nie, powodowało utratę danych tak wielką, jak katastrofa.

Następne kroki