Dela via

Kontrollera transaktionshållbarhet

  • Artikel

gäller för:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

SQL Server-transaktionsutföranden kan vara antingen fullt hållbara, vilket är SQL Servers standardinställning, eller fördröjt hållbara (kallas även lazy commit).

Fullständigt beständiga transaktionssammanföranden är synkrona och rapporterar en sammanföring som lyckad och returnerar kontrollen till klienten endast efter att loggposterna för transaktionen har skrivits till disk. Fördröjda beständiga transaktionskommitteringar är asynkrona och rapporterar en kommittering som lyckad innan loggposterna för transaktionen skrivs till disk. Att skriva transaktionsloggposterna till disken krävs för att en transaktion ska vara varaktig. Fördröjda varaktiga transaktioner blir varaktiga när transaktionsloggposterna töms till disk.

Den här artikeln beskriver fördröjda varaktiga transaktioner.

Fullständig kontra fördröjd transaktionshållbarhet

Både fullständig och fördröjd transaktionshållbarhet har fördelar och nackdelar. Ett program kan ha en blandning av fullständigt och fördröjda varaktiga transaktioner. Du bör noga överväga dina affärsbehov och hur var och en passar in i dessa behov.

Fullständig transaktionshållbarhet

Fullständigt varaktiga transaktioner skriver transaktionsloggen till disken innan kontrollen returneras till klienten. Du bör använda fullständigt varaktiga transaktioner när:

  • Systemet kan inte tolerera dataförlust. Se avsnittet När kan jag förlora data? för information om när du kan förlora en del av dina data.

  • Flaskhalsen beror inte på transaktionsloggens skrivfördröjning.

Fördröjd transaktionshållbarhet minskar svarstiden på grund av logg-I/O genom att behålla transaktionsloggposterna i minnet och skriva till transaktionsloggen i batchar, vilket kräver färre I/O-åtgärder. Fördröjd transaktionshållbarhet minskar potentiellt logg-I/O-konkurrensen, vilket minskar väntetiderna i systemet.

fullständiga transaktionshållbarhetsgarantier

  • När transaktionsöverföringen har slutförts visas de ändringar som görs av transaktionen för de andra transaktionerna i systemet. Mer information om transaktionsisoleringsnivåer finns i SET TRANSACTION ISOLATION LEVEL (Transact-SQL) eller Transaktioner med Memory-Optimized Tabeller.

  • Hållbarhet garanteras vid åtagande. Motsvarande loggposter sparas på disken innan transaktionsöverföringen lyckas och returnerar kontrollen till klienten.

Fördröjd transaktionshållbarhet

Fördröjd transaktionshållbarhet uppnås med hjälp av asynkrona loggskrivningar till disk. Transaktionsloggposter sparas i en buffert och skrivs till disk när bufferten fylls eller en buffertrensningshändelse äger rum. Fördröjd transaktionshållbarhet minskar både svarstid och konkurrens i systemet eftersom:

  • Bearbetningen av transaktionsöverföringen väntar inte på att logg-I/O ska slutföras och returnera kontrollen till klienten.

  • Samtidiga transaktioner är mindre benägna att konkurrera om logg-I/O. I stället kan loggbufferten rensas till disken i större segment, vilket minskar konkurrensen och ökar dataflödet.

    Notera

    Du kan fortfarande ha logg-I/O-konkurrens om det finns en hög grad av samtidighet, särskilt om du fyller upp loggbufferten snabbare än du tömer den.

När du ska använda fördröjd transaktionshållbarhet

Några av de fall där du kan dra nytta av att använda fördröjd transaktionshållbarhet är:

Du kan tolerera viss dataförlust.
Om du kan tolerera viss dataförlust, till exempel om enskilda poster inte är kritiska så länge du har de flesta data, kan fördröjd hållbarhet vara värt att överväga. Om du inte kan tolerera dataförlust ska du inte använda fördröjd transaktionshållbarhet.

Du upplever en flaskhals i transaktionsloggskrivningar.
Om dina prestandaproblem beror på svarstiden i transaktionsloggskrivningar kommer ditt program sannolikt att dra nytta av att använda fördröjd transaktionshållbarhet.

Dina arbetsbelastningar har hög konfliktnivå.
Om systemet har arbetsbelastningar med hög konkurrensnivå går mycket tid förlorad i väntan på att lås ska frigöras. Fördröjd transaktionshållbarhet minskar comittiden och frigör därmed lås snabbare, vilket resulterar i högre genomströmning.

Garantier för fördröjd transaktionshållbarhet

  • När transaktionsöverföringen har slutförts visas de ändringar som görs av transaktionen för de andra transaktionerna i systemet.

  • Transaktionshållbarhet garanteras endast efter en tömning av den minnesinterna transaktionsloggen till disken. Transaktionsloggen i minnet töms till disk när:

    • En helt säker transaktion i samma databas gör en ändring i databasen och bekräftas.

    • Användaren kör den system lagrade proceduren sp_flush_log korrekt.

      Om en helt varaktig transaktion eller sp_flush_log lyckas, är det garanterat att alla tidigare bekräftade transaktioner med fördröjd hållbarhet har blivit varaktiga.

    • SQL Server försöker tömma loggen till disken både baserat på logggenerering och tidsinställning, även om alla transaktioner är fördröjda. Detta lyckas vanligtvis om I/O-enheten håller jämna steg. SQL Server ger dock inga andra garantier för hård hållbarhet än varaktiga transaktioner och sp_flush_log.

Så här kontrollerar du transaktionshållbarheten

Kontroll på databasnivå

Du, DBA, kan styra om användare kan använda fördröjd transaktionshållbarhet i en databas med följande instruktion. Du måste ange inställningen för fördröjd hållbarhet med ALTER DATABASE.

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

inaktiverad
Med den här inställningen är alla transaktioner som begår på databasen fullt varaktiga, oavsett inställningen för commit-nivå (DELAYED_DURABILITY=[ON | OFF]). Det finns inget behov av ändring och omkompilering av lagrade procedurer. På så sätt kan du se till att inga data någonsin utsätts för risker på grund av fördröjd hållbarhet.

TILLÅTEN
Med den här inställningen bestäms varje transaktions hållbarhet på transaktionsnivå – DELAYED_DURABILITY = { OFF | PÅ }. Mer information finns i KONTROLL på atomisk blocknivå – Nativt kompilerade lagrade procedurer och KONTROLL på COMMIT-nivå.

TVINGAD
Med den här inställningen fördröjs varje transaktion som checkar in i databasen varaktigt. Om transaktionen anger helt varaktig (DELAYED_DURABILITY = OFF) eller inte gör någon specifikation, fördröjs transaktionen. Den här inställningen är användbar när fördröjd transaktionshållbarhet är användbar för en databas och du inte vill ändra någon programkod.

Kontroll på atomisk blocknivå – Internt kompilerade lagrade procedurer

Följande kod finns i atomblocket.

DELAYED_DURABILITY = { OFF | ON }

AV
[standard] Transaktionen är helt hållbar, såvida inte databasalternativet DELAYED_DURABILITY = FORCED är i kraft, i vilket fall commit är asynkron och därmed fördröjd hållbar. Mer information finns i kontroll på databasnivået.

PÅ
Transaktionen är fördröjd, om inte databasalternativet DELAYED_DURABILITY = DISABLED gäller, i vilket fall kommitten är synkron och därmed fullt hållbar. För mer information, se kontroll på databasnivå.

Exempelkod:

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

Tabell 1: Hållbarhet i atomiska block

Alternativ för atomisk blockhållbarhet Ingen befintlig transaktion Transaktion pågår (helt eller delvis fördröjd)
DELAYED_DURABILITY = OFF Atomic-blocket startar en ny helt beständig transaktion. Atomblock skapar en sparande punkt i den befintliga transaktionen och startar sedan den nya transaktionen.
FÖRDRÖJD_HÅLLBARHET = PÅ Atomic-blocket startar en ny fördröjd varaktig transaktion. Atomblock skapar en sparande punkt i den befintliga transaktionen och startar sedan den nya transaktionen.

Kontroll på COMMIT-nivå –Transact-SQL

COMMIT-syntaxen utökas så att du kan framtvinga fördröjd transaktionshållbarhet. Om DELAYED_DURABILITY är inaktiverat eller tvingat på databasnivå (se ovan) ignoreras det här COMMIT-alternativet.

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

AV
[standard] Transaktionen COMMIT är helt hållbar, om inte databasalternativet DELAYED_DURABILITY = FORCED är i kraft, i vilket fall COMMIT är asynkront och därmed fördröjd hållbarhet. Mer information finns i databasnivåkontroll.

PÅ
Transaktionen COMMIT är fördröjd hållbar, såvida inte databasalternativet DELAYED_DURABILITY = DISABLED är i kraft, då COMMIT är synkront och därmed helt hållbart. Mer information finns i databasnivåkontroll.

Sammanfattning av alternativ och deras interaktioner

Den här tabellen sammanfattar interaktionerna mellan inställningar för fördröjd hållbarhet på databasnivå och inställningar för incheckningsnivå. Inställningar på databasnivå har alltid företräde framför inställningar på åtagandenivå.

COMMIT-inställning/Databasinställning DELAYED_DURABILITY = INAKTIVERAD DELAYED_DURABILITY = TILLÅTEN DELAYED_DURABILITY = FORCED
DELAYED_DURABILITY = AVSTÄNGD transaktioner på databasnivå. Transaktionen är helt beständig. Transaktionen är helt beständig. Transaktionen är försenad på obestämd tid.
FÖRDRÖJD_DURABILITET = PÅ databasnivå. Transaktionen är helt beständig. Transaktionen är långvarigt fördröjd. Transaktionen är varaktigt fördröjd.
DELAYED_DURABILITY = OFF Korsdatabas eller distribuerad transaktion. Transaktionen är helt beständig. Transaktionen är helt beständig. Transaktionen är helt beständig.
DELAYED_DURABILITY = ON Korsdatabas eller distribuerad transaktion. Transaktionen är helt beständig. Transaktionen är helt beständig. Transaktionen är helt beständig.

Så här tvingar du en transaktionslogg att tömmas

Det finns två sätt att tvinga tömning av transaktionsloggen till disk.

  • Kör alla helt varaktiga transaktioner som ändrar samma databas. Detta tvingar fram en överföring av loggposterna för alla tidigare bekräftade transaktioner med fördröjd hållbarhet till hårddisken.

  • Kör den system lagrade proceduren sp_flush_log. Den här proceduren tvingar fram en tömning av loggposterna för alla tidigare bekräftade fördröjda varaktiga transaktioner till disken. Mer information finns i sys.sp_flush_log (Transact-SQL).

Fördröjd hållbarhet och andra SQL Server-funktioner

transaktionsreplikering, ändringsspårning och insamling av ändringsdata

  • För databaser som är aktiverade för transaktionsreplikering eller ändringsdatainsamling (CDC) stöds inte användningen av fördröjd hållbarhet.

  • Ändringsspårning med fördröjd hållbarhet stöds. Alla transaktioner med ändringsspårning är helt hållbara. En transaktion har egenskapen ändringsspårning om den utför skrivåtgärder till tabeller som har aktiverat ändringsspårning.

Från och med SQL Server 2022 CU 2 och SQL Server 2019 CU 20 kan du se:

  • Error 22891: Could not enable '_FeatureName_' for database '_DatabaseName_'. '_FeatureName_' cannot be enabled on a DB with delayed durability set om du försöker aktivera transaktionsreplikering eller ändra datafångst på en databas som har aktiverat fördröjd hållbarhet.

  • Error 22892: Could not enable delayed durability on DB. Delayed durability cannot be enabled on a DB while '_FeatureName_' is enabled om du försöker aktivera fördröjd hållbarhet för en databas som har konfigurerats med transaktionsreplikering eller Ändringsdatainsamling.

Kraschåterställning
Konsistens garanteras, men vissa ändringar från försenade långvariga transaktioner som har genomförts kan gå förlorade.

Korsdatabas och DTC-
Om en transaktion är korsdatabas eller distribuerad är den helt beständig, oavsett inställning för databas- eller transaktionskomplettering.

Always On-tillgänglighetsgrupper och spegling
Fördröjda varaktiga transaktioner garanterar inte någon hållbarhet på den primära eller någon av sekundärerna. Dessutom garanterar de inte någon kunskap om transaktionen på den sekundära marknaden. Efter incheckningen returneras kontrollen till klienten innan någon bekräftelse tas emot från någon synkron sekundär. Replikering till sekundära repliker fortsätter att ske i takt med att data skrivs till disken på den primära.

redundansklustring
Vissa fördröjda varaktiga transaktionsskrivningar kan gå förlorade.

Azure Synapse Link för SQL
Fördröjda varaktiga transaktioner stöds inte med Azure Synapse Link för SQL.

Loggöverföring
Endast transaktioner som har blivit varaktiga ingår i loggen som levereras.

säkerhetskopiering av transaktionsloggar
Endast transaktioner som har gjorts varaktiga ingår i säkerhetskopieringen.

När kan jag förlora data?

Om du implementerar fördröjd hållbarhet i någon av dina tabeller bör du förstå att vissa omständigheter kan leda till dataförlust. Om du inte kan tolerera dataförlust bör du inte använda fördröjd hållbarhet i dina tabeller.

Katastrofala händelser

I händelse av en katastrofal händelse, till exempel en serverkrasch, förlorar du data för alla transaktioner som inte har sparats på disken. Fördröjda varaktiga transaktioner sparas på disken när en helt beständig transaktion körs mot en tabell (beständig minnesoptimerad eller diskbaserad) i databasen eller sp_flush_log anropas. Om du använder fördröjda varaktiga transaktioner kanske du vill skapa en liten tabell i databasen som du regelbundet kan uppdatera eller regelbundet anropa sp_flush_log för att spara alla utestående checkade transaktioner. Transaktionsloggen töms också när den blir full, men det är svårt att förutsäga och omöjligt att kontrollera.

Avstängning och omstart av SQL Server

För fördröjd hållbarhet är det ingen skillnad mellan en oväntad avstängning och en förväntad avstängning/omstart av SQL Server. Precis som katastrofala händelser bör du planera för dataförlust. Vid en planerad avstängning/omstart kan vissa transaktioner som inte har skrivits till disk sparas på disken före avstängning, men du bör inte planera för det. Planera som om en avstängning/omstart, oavsett om den är planerad eller oplanerad, förlorar data på samma sätt som en katastrofal händelse.

Nästa steg