Řízení stálosti transakcí

platí pro:SQL ServerAzure SQL Databaseazure SQL Managed Instance

Transakce v SQL Serveru mohou být buď plně odolné, což je výchozí nastavení, nebo s opožděným zápisem (také známé jako opožděné potvrzení).

Plně odolné potvrzení transakcí jsou synchronní a ohlásí potvrzení jako úspěšné a vrátí řízení klientovi až po zápisu záznamů protokolu transakce na disk. Zpožděné potvrzení trvalých transakcí jsou asynchronní a hlásí potvrzení jako úspěšné před zápisem záznamů protokolu transakce na disk. Zápis položek transakčního protokolu na disk je vyžadován, aby transakce byla odolná. Zpožděné trvalé transakce se stanou trvalými, když jsou položky transakčního protokolu vyprázdněny na disk.

Tento článek podrobně popisuje zpožděné trvalé transakce.

Úplná versus zpožděná trvanlivost transakcí

Plná i zpožděná stálost transakcí má výhody a nevýhody. Aplikace může mít kombinaci plně trvalých a opožděných trvalých transakcí. Měli byste pečlivě zvážit své obchodní potřeby a způsob, jakým jednotlivé potřeby odpovídají těmto potřebám.

Úplná stálost transakcí

Transakce, které jsou plně odolné, zapisují transakční protokol na disk dříve, než vrátí řízení klientovi. Plně odolné transakce byste měli použít vždy, když:

• Váš systém nemůže tolerovat žádnou ztrátu dat. Podívejte se na část Kdy můžu ztratit data? informace o tom, kdy můžete ztratit některá data.

• Kritickým bodem není latence zápisu transakčního protokolu.

Zpožděná stálost transakcí snižuje latenci způsobenou vstupně-výstupními operacemi tím, že uchovává záznamy transakčního protokolu v paměti a zapisuje je do transakčního protokolu v dávkách, což snižuje počet požadovaných vstupně-výstupních operací. Zpožděná stálost transakcí potenciálně snižuje kolize vstupně-výstupních operací protokolu, což snižuje čekání v systému.

Záruky úplné trvanlivosti transakcí

• Jakmile potvrzení transakce proběhne úspěšně, změny provedené transakcí jsou viditelné pro ostatní transakce v systému. Další informace o úrovních izolace transakcí naleznete v tématu SET TRANSACTION ISOLATION LEVEL (Transact-SQL) nebo Transakce s Memory-Optimized Tabulky.

• Stálost je zaručena při zapsání. Odpovídající záznamy protokolu se zachovají na disk před úspěšným potvrzením transakce a vrátí řízení klientovi.

Zpožděná stálost transakcí

Zpožděné zajištění trvalosti transakce se dosahuje pomocí asynchronních zápisů do logu na disk. Záznamy transakčního protokolu se uchovávají ve vyrovnávací paměti a zapisují se na disk, když dojde k vyplnění vyrovnávací paměti nebo události vyprázdnění vyrovnávací paměti. Zpožděná stálost transakcí snižuje latenci i kolize v systému, protože:

• Zpracování potvrzení transakce nečeká na dokončení vstupně-výstupních operací protokolu ani nevrací řízení klientovi.

• Souběžné transakce jsou méně pravděpodobné, že budou soupeřit o vstupně-výstupní operace protokolu; místo toho lze vyrovnávací paměť protokolu vyprázdnit na disk ve větších blocích, snížit kolize a zvýšit propustnost.

  Poznámka

  Stále můžete mít konflikt vstupně-výstupních operací protokolu, pokud je vysoká úroveň souběžnosti, zejména pokud zaplníte vyrovnávací paměť protokolu rychleji, než ji vyprázdníte.

Kdy použít zpožděnou trvanlivost transakcí

Mezi případy, ve kterých můžete těžit z použití opožděné trvanlivosti transakcí, patří:

Můžete tolerovat určitou ztrátu dat.
Pokud můžete tolerovat ztrátu některých dat, například v případě, že jednotlivé záznamy nejsou kritické a máte většinu dat, může být zpožděná odolnost stojí za zvážení. Pokud nemůžete tolerovat žádnou ztrátu dat, nepoužívejte zpožděnou odolnost transakcí.

Dochází k úzkému místu při zápisu transakčního logu.
Pokud jsou problémy s výkonem způsobené latencí při zápisech transakčních protokolů, bude vaše aplikace pravděpodobně těžit z použití zpožděné odolnosti transakcí.

vaše úlohy mají vysokou míru kolize.
Pokud má váš systém úlohy s vysokou úrovní kolize, ztratí se mnoho času čekáním na uvolnění zámků. Zpožděná stálost transakcí zkracuje dobu potvrzení a tím urychluje uvolnění zámků, což vede k vyšší propustnosti.

Garance odolnosti zpožděných transakcí

• Jakmile potvrzení transakce proběhne úspěšně, změny provedené transakcí jsou viditelné pro ostatní transakce v systému.

• Stálost transakcí je zaručena pouze po vyprázdnění transakčního protokolu v paměti na disk. Protokol transakcí v paměti se vyprázdní na disk, když:

  • Plně odolná transakce v téže databázi provede změnu v databázi a úspěšně se zapíše.

  • Uživatel spustí systémovou uloženou proceduru sp_flush_log úspěšně.

    Pokud plně zabezpečená transakce nebo sp_flush_log úspěšně potvrzeny, všechny dříve potvrzené transakce s odloženou stálostí jsou zajištěny jako trvalé.

  • SQL Server se pokusí vyprázdnit protokol na disk na základě generování protokolů i časování, i když jsou všechny transakce zpožděné trvalé. To obvykle proběhne úspěšně, pokud IO zařízení udržuje krok. SQL Server však neposkytuje žádné pevné záruky stálosti jiné než trvalé transakce a sp_flush_log.

Jak řídit stálost transakcí

Řízení na úrovni databáze

Vy, DBA, můžete řídit, jestli uživatelé mohou používat opožděnou trvanlivost transakcí pomocí následujícího příkazu v databázi. Musíte nastavit zpožděnou trvanlivost pomocí ALTER DATABASE.

syntaxsql

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

zakázáno
[výchozí] Při tomto nastavení jsou všechny transakce, které jsou potvrzeny v databázi, plně trvanlivé bez ohledu na nastavení úrovně potvrzení (DELAYED_DURABILITY=[ON | VYPNUTO]). Není třeba měnit uloženou proceduru ani ji překompilovávat. To vám umožní zajistit, aby žádná data nebyla ohrožena opožděnou trvanlivostí.

POVOLENO
Při tomto nastavení je stálost každé transakce určena na úrovni transakce - DELAYED_DURABILITY = { OFF | ON }. Další informace najdete v tématu řízení na úrovni atomických bloků – nativně kompilované uložené procedury a řízení na úrovni COMMIT.

VYNUCENÉ
Při tomto nastavení je každá transakce, která se potvrdí v databázi, zpožděna a posléze uložena trvale. Bez ohledu na to, zda transakce specifikuje plnou trvalost (DELAYED_DURABILITY = OFF) nebo žádnou specifikaci neuvádí, je transakce zpožděně trvalá. Toto nastavení je užitečné, když je pro databázi užitečná zpožděná stálost transakcí a nechcete měnit žádný kód aplikace.

Řízení na úrovni atomických bloků – nativně zkompilované uložené procedury

Následující kód je uvnitř atomového bloku.

syntaxsql

DELAYED_DURABILITY = { OFF | ON }

vypnuto
[výchozí] Transakce je plně odolná, pokud není v platnosti možnost databáze DELAYED_DURABILITY = FORCED, v takovém případě je potvrzení asynchronní a tedy zpožděné trvalé. Další informace naleznete v tématu řízení na úrovni databáze.

ZAPNUTO
Transakce má odloženou trvanlivost, ledaže je v platnosti možnost databáze DELAYED_DURABILITY = ZAKÁZÁNO, v takovém případě je potvrzení transakce synchronní a tedy zcela trvalé. Další informace naleznete v tématu řízení na úrovni databáze.

příklad kódu:

SQL

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

Tabulka 1: Stálost v atomových blocích

Možnost odolnosti atomového bloku	Žádná existující transakce	Transakce v procesu (plně nebo zpožděná trvalá)
DELAYED_DURABILITY = VYPNUTO	Atomový blok spustí novou plně odolnou transakci.	Atomový blok vytvoří v existující transakci bod uložení a poté zahájí novou transakci.
DELAYED_DURABILITY = ZAPNUTO	Atomový blok spustí novou zpožděnou odolnou transakci.	Atomový blok vytvoří v existující transakci bod obnovení a pak zahájí novou transakci.

Ovládací prvek úrovně commit –Transact-SQL

Syntaxe COMMIT je rozšířena, takže můžete vynutit odloženou trvanlivost transakce. Pokud je DELAYED_DURABILITY VYPNUTÁ nebo VYNUCENÁ na úrovni databáze (viz výše), bude tato možnost COMMIT ignorována.

syntaxsql

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

vypnuto
[výchozí] Transakce COMMIT je plně odolná, pokud není v platnosti možnost databáze DELAYED_DURABILITY = FORCED, kdy je COMMIT asynchronní a tedy zpožděně trvanlivý. Další informace naleznete v tématu řízení na úrovni databáze.

ZAPNUTO
Transakce COMMIT je zpožděně trvalá, pokud není v platnosti nastavení databáze DELAYED_DURABILITY = ZAKÁZÁNO, v takovém případě je COMMIT synchronní a tedy plně trvalý. Další informace naleznete v tématu řízení na úrovni databáze.

Shrnutí možností a jejich interakcí

Tato tabulka shrnuje interakce mezi nastavením zpožděné odolnosti na úrovni databáze a nastavením úrovně potvrzení. Nastavení na úrovni databáze mají vždy přednost před nastavením úrovně potvrzení.

Nastavení COMMIT / Nastavení databáze	DELAYED_DURABILITY = ZAKÁZÁNO	DELAYED_DURABILITY = POVOLENO	DELAYED_DURABILITY = VYNUCENO
DELAYED_DURABILITY = OFF Transakce na úrovni databáze.	Transakce je plně trvalá.	Transakce je plně trvalá.	Transakce je trvale zpožděná.
DELAYED_DURABILITY = ON Transakce na úrovni databáze.	Transakce je plně zabezpečená.	Transakce má trvalé zpoždění.	Transakce je trvale zpožděná.
DELAYED_DURABILITY = OFF mezi databázemi nebo distribuovanými transakcemi.	Transakce je plně trvalá.	Transakce je plně spolehlivá.	Transakce je plně trvalá.
DELAYED_DURABILITY = ON Transakce mezi databázemi nebo distribuovaná transakce.	Transakce je plně trvalá.	Transakce je plně trvalá.	Transakce je plně trvanlivá.

Vynucení vyprázdnění transakčního protokolu

Existují dva způsoby, jak vynutit vyprázdnění transakčního protokolu na disk.

• Spusťte jakoukoli plně odolnou transakci, která mění stejnou databázi. To vynutí zapsání záznamů protokolu všech předchozích potvrzených transakcí se zpožděnou stálostí na disk.

• Spusťte systém uloženou proceduru sp_flush_log. Tento postup vynutí vyprázdnění protokolových záznamů všech předchozích potvrzených transakcí se zpožděnou trvalostí na disk. Další informace naleznete v tématu sys.sp_flush_log (Transact-SQL).

Zpožděná stálost a další funkce SQL Serveru

transakční replikace, sledování změn a zachytávání dat změn

• U databází povolených pro transakční replikaci nebo funkci Change Data Capture (CDC) se nepodporuje použití zpožděné stálosti.

• Podporuje se sledování změn se zpožděnou stálostí. Všechny transakce s funkcí sledování změn jsou plně odolné. Transakce má vlastnost sledování změn, pokud provede jakékoli operace zápisu do tabulek, které povolily sledování změn.

Od SQL Serveru 2022 CU 2 a SQL Serveru 2019 CU 20 se může zobrazit:

• Error 22891: Could not enable '_FeatureName_' for database '_DatabaseName_'. '_FeatureName_' cannot be enabled on a DB with delayed durability set, pokud se pokusíte povolit transakční replikaci nebo změnit zachytávání dat v databázi, která povolila zpožděnou odolnost.

• Error 22892: Could not enable delayed durability on DB. Delayed durability cannot be enabled on a DB while '_FeatureName_' is enabled, pokud se pokusíte v databázi nakonfigurované s transakční replikací nebo sledováním změn dat povolit zpožděnou trvanlivost.

obnova po havárii
Konzistence je zaručena, ale některé změny zpožděných trvalých transakcí, které byly potvrzeny, mohou být ztraceny.

Křížové operace mezi databázemi a DTC
Pokud je transakce mezi databázemi nebo distribuovaná, je plně odolná bez ohledu na nastavení databáze nebo potvrzení transakce.

skupiny dostupnosti AlwaysOn a zrcadlení
Zpožděné trvalé transakce nezaručují žádnou stálost na primárním nebo žádném z sekundárních transakcí. Kromě toho nezaručují žádné znalosti o transakci na sekundárním trhu. Po potvrzení se řízení vrátí klientovi před přijetím potvrzení z jakékoli synchronizované sekundární databáze. Replikace na sekundární repliky pokračuje ve chvíli, kdy dochází k zápisu na disk u primární repliky.

clustering s podporou převzetí služeb při selhání
Některé zápisy zpožděných trvalých transakcí mohou být ztraceny.

Azure Synapse Link pro SQL
Zpožděné trvalé transakce se v Azure Synapse Linku pro SQL nepodporují.

Přeprava transakčních protokolů
Do protokolu, který je přenášen, jsou zahrnuty pouze transakce, které byly zajištěny jako trvalé.

zálohování transakčních protokolů
Do zálohy jsou zahrnuty pouze transakce, které byly provedeny trvale.

Kdy můžu přijít o data?

Pokud implementujete zpožděnou odolnost u některé z tabulek, měli byste vědět, že určité okolnosti můžou vést ke ztrátě dat. Pokud nemůžete tolerovat žádnou ztrátu dat, neměli byste u tabulek používat opožděnou trvanlivost.

Katastrofické události

V případě katastrofické události, jako je selhání serveru, ztratíte data pro všechny potvrzené transakce, které nebyly uloženy na disk. Zpožděné trvalé transakce jsou uloženy na disk vždy, když je spuštěna plně trvalá transakce proti libovolné tabulce (paměti optimalizované pro odolnost nebo disku) v databázi, nebo je volána sp_flush_log. Pokud používáte zpožděné trvalé transakce, mohli byste chtít vytvořit malou tabulku v databázi, kterou byste mohli pravidelně aktualizovat, nebo pravidelně volat sp_flush_log pro uložení všech nevyřízených potvrzených transakcí. Transakční protokol také vyprázdní vždy, když se zaplní, ale to je obtížné předpovědět a nemožné řídit.

Vypnutí a restartování SQL Serveru

U zpožděné stálosti neexistuje žádný rozdíl mezi neočekávaným vypnutím a očekávaným vypnutím nebo restartováním SQL Serveru. Stejně jako katastrofické události byste měli naplánovat ztrátu dat. V plánovaném vypnutí nebo restartování mohou být některé transakce, které nebyly zapsány na disk, uloženy na disk před vypnutím, ale neměli byste na něj plánovat. Plánujte, jako by vypnutí/restartování, ať už plánované nebo neplánované, způsobilo ztrátu dat stejně jako katastrofická událost.

Další kroky

• transakce s tabulkami Memory-Optimized