Delen via

Duurzaamheid van transacties beheren

  • Artikel

van toepassing op:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

SQL Server-transactiedoorvoeringen kunnen volledig duurzaam zijn, de standaardinstelling van SQL Server of vertraagd duurzaam (ook wel luie doorvoer genoemd).

Volledig duurzame transacties zijn synchroon en melden een commit als geslaagd en geven pas de controle terug aan de client nadat de logrecords voor de transactie naar de schijf zijn geschreven. Vertraagde duurzame transactiedoorvoeringen zijn asynchroon en rapporteren een doorvoer als geslaagd voordat de logboekrecords voor de transactie naar de schijf worden geschreven. Het schrijven van de vermeldingen in het transactielogboek op schijf is vereist voor een transactie die duurzaam is. Vertraagde duurzame transacties worden duurzaam wanneer de vermeldingen in het transactielogboek naar de schijf worden weggeschreven.

In dit artikel worden vertraagde duurzame transacties beschreven.

Volledige versus vertraagde duurzaamheid van transacties

Zowel volledige als vertraagde duurzaamheid van transacties hebben voor- en nadelen. Een toepassing kan een combinatie van volledig en vertraagde duurzame transacties hebben. Houd zorgvuldig rekening met de behoeften van uw bedrijf en hoe elk in deze behoeften past.

Duurzaamheid van volledige transacties

Volledig duurzame transacties schrijven het transactielogboek naar schijf voordat u de controle naar de client retourneert. U moet volledig duurzame transacties gebruiken wanneer:

  • Uw systeem kan geen gegevensverlies tolereren. Zie de sectie Wanneer kan ik gegevens kwijtraken? voor informatie over wanneer u een deel van uw gegevens kwijtraakt.

  • Het knelpunt wordt niet veroorzaakt door schrijflatentie voor transactielogboeken.

Vertraagde duurzaamheid van transacties vermindert de latentie door log-I/O door de transactielogboekrecords in het geheugen te bewaren en in batches naar het transactielogboek te schrijven, waardoor minder I/O-bewerkingen nodig zijn. Vertraagde transactieduur vermindert mogelijk logboek-I/O-conflicten, waardoor wachttijden in het systeem worden verminderd.

garanties voor volledige transactieduurbaarheid

  • Zodra de transactie is doorgevoerd, zijn de wijzigingen die door de transactie zijn aangebracht, zichtbaar voor de andere transacties in het systeem. Voor meer informatie over transactie-isolatieniveaus, zie SET TRANSACTION ISOLATION LEVEL (Transact-SQL) of Transacties met Memory-Optimized Tabellen.

  • Duurzaamheid is gegarandeerd bij vastlegging. Overeenkomende logboekrecords worden bewaard op schijf voordat de transactiebekrachtiging slaagt en de controle naar de client retourneert.

Duurzaamheid van vertraagde transacties

De duurzaamheid van vertraagde transacties wordt bereikt met behulp van asynchrone logboekschrijfbewerkingen naar schijf. Transactielogboekrecords worden bewaard in een buffer en naar schijf geschreven wanneer de buffer wordt gevuld of een gebeurtenis voor het leegmaken van een buffer plaatsvindt. De duurzaamheid van vertraagde transacties vermindert zowel latentie als conflicten binnen het systeem omdat:

  • De verwerking van de transactiedoorvoering wacht niet op de voltooiing van logboek-IO en geeft de controle terug aan de client.

  • Gelijktijdige transacties hebben minder vaak te maken met logboek-IO; In plaats daarvan kan de logboekbuffer in grotere segmenten naar schijf worden leeggemaakt, waarbij conflicten worden verminderd en de doorvoer wordt verhoogd.

    Notitie

    Mogelijk hebt u nog steeds logboek-I/O-conflicten als er sprake is van een hoge mate van gelijktijdigheid, met name als u de logboekbuffer sneller vult dan door het leegmaken.

Wanneer gebruik te maken van de duurzaamheid van vertraagde transacties

Enkele van de gevallen waarin u kunt profiteren van het gebruik van de duurzaamheid van vertraagde transacties zijn:

U kunt gegevensverlies tolereren.
Als u gegevensverlies kunt tolereren, bijvoorbeeld wanneer afzonderlijke records niet kritiek zijn zolang u de meeste gegevens hebt, is vertraagde duurzaamheid misschien de moeite waard om rekening mee te houden. Als u geen gegevensverlies tolereert, gebruikt u geen vertraagde duurzaamheid van transacties.

U ondervindt een knelpunt bij schrijfbewerkingen in transactielogboeken.
Als uw prestatieproblemen te wijten zijn aan latentie in schrijfbewerkingen in transactielogboeken, profiteert uw toepassing waarschijnlijk van het gebruik van de duurzaamheid van vertraagde transacties.

Uw workloads hebben een hoge mate van concurrentie.
Als uw systeem workloads met een hoog niveau van conflicten heeft, gaat er veel tijd verloren door te wachten tot vergrendelingen zijn vrijgegeven. Vertraagde transactieduurzaamheid vermindert de commit tijd en zorgt er dus voor dat vergrendelingen sneller worden vrijgegeven, wat resulteert in een hogere doorvoer.

Garanties voor duurzaamheid van vertraagde transacties

  • Zodra de transactie is doorgevoerd, zijn de wijzigingen die door de transactie zijn aangebracht, zichtbaar voor de andere transacties in het systeem.

  • De duurzaamheid van transacties wordt gegarandeerd alleen na het leegmaken van het transactielogboek in het geheugen naar schijf. Het transactielogboek in het geheugen wordt weggeschreven naar schijf wanneer:

    • Een volledig duurzame transactie in dezelfde database brengt een wijziging aan in de database en voert deze succesvol door.

    • De gebruiker voert met succes de door het systeem opgeslagen procedure sp_flush_log uit.

      Als een volledig duurzame transactie of sp_flush_log succesvol wordt gecommit, dan zijn alle eerder vastgelegde transacties met vertraagde duurzaamheid gegarandeerd duurzaam bevestigd.

    • SQL Server probeert het logboek op schijf te leegmaken op basis van logboekgeneratie en tijdsinstellingen, zelfs als alle transacties duurzaam worden vertraagd. Dit lukt meestal als het IO-apparaat bijhoudt. SQL Server biedt echter geen vaste garanties voor duurzaamheid, behalve duurzame transacties en sp_flush_log.

Duurzaamheid van transacties beheren

Besturingselement op databaseniveau

U, de DBA, kan bepalen of gebruikers de duurzaamheid van vertraagde transacties in een database kunnen gebruiken met de volgende instructie. U moet de instelling voor vertraagde duurzaamheid instellen met ALTER DATABASE.

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

UITGESCHAKELD
[standaard] Met deze instelling zijn alle transacties die in de database worden doorgevoerd, volledig duurzaam, ongeacht de instelling voor doorvoerniveau (DELAYED_DURABILITY=[AAN | UIT]). Het is niet nodig om opgeslagen procedure te wijzigen en opnieuw te compileren. Hierdoor kunt u ervoor zorgen dat er nooit gegevens in gevaar worden gebracht door vertraagde duurzaamheid.

TOEGESTAAN
Met deze instelling wordt de duurzaamheid van elke transactie bepaald op transactieniveau - DELAYED_DURABILITY = { OFF | AAN }. Zie atomisch besturingselement op blokniveau: systeemeigen gecompileerde opgeslagen procedures en voor meer informatie.

GEFORCEERDE
Met deze instelling wordt elke transactie die wordt uitgevoerd op de database, uitgesteld duurzaam. Of de transactie nu volledig duurzaam (DELAYED_DURABILITY = OFF) opgeeft of geen specificatie geeft, de transactie wordt duurzaam vertraagd. Deze instelling is handig wanneer de duurzaamheid van vertraagde transacties nuttig is voor een database en u geen toepassingscode wilt wijzigen.

Atomische controle op blokniveau - Natuurlijk gecompileerde opgeslagen procedures

De volgende code gaat binnen het atomische blok.

DELAYED_DURABILITY = { OFF | ON }

UIT
[standaard] De transactie is volledig duurzaam, tenzij de databaseoptie DELAYED_DURABILITY = FORCED van kracht is, in welk geval de doorvoer asynchroon is en dus duurzaam is vertraagd. Zie databaseniveaubeheervoor meer informatie.

ON
De transactie is duurzaam vertraagd, tenzij de databaseoptie DELAYED_DURABILITY = DISABLED van kracht is, in welk geval de doorvoering synchroon is en dus volledig duurzaam is. Zie databaseniveaubeheervoor meer informatie.

voorbeeldcode:

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

Tabel 1: Duurzaamheid in atomische blokken

Optie voor duurzaamheid van atomair blok Geen bestaande transactie Transactie in proces (volledig of vertraagd duurzaam)
DELAYED_DURABILITY = UIT Atomic block start een volledig nieuwe en duurzame transactie. Atomisch blok creëert een opslagpunt in de bestaande transactie en begint dan aan de nieuwe transactie.
DELAYED_DURABILITY = AAN Atomisch blok start een nieuwe vertraagde duurzame transactie. Atomisch blok maakt een opslagpunt in de bestaande transactie en begint vervolgens met de nieuwe transactie.

Controle op COMMIT-niveau -Transact-SQL

De COMMIT-syntaxis wordt uitgebreid zodat u de duurzaamheid van transacties met vertraging kunt afdwingen. Als DELAYED_DURABILITY UITGESCHAKELD of GEFORCEERD is op databaseniveau (zie hierboven) wordt deze COMMIT-optie genegeerd.

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

UIT
[standaard] De COMMIT is volledig duurzaam, tenzij de databaseoptie DELAYED_DURABILITY = FORCED van kracht is, dan is de COMMIT asynchroon en dus vertraagd duurzaam. Zie databaseniveaubeheervoor meer informatie.

AAN
De transactieCOMMIT is vertraagd duurzaam, tenzij de databaseoptie DELAYED_DURABILITY = DISABLED van kracht is, in welk geval de COMMIT synchroon is en dus volledig duurzaam is. Zie databaseniveaubeheervoor meer informatie.

Samenvatting van opties en hun interacties

Deze tabel bevat een overzicht van de interacties tussen vertraagde duurzaamheidsinstellingen op databaseniveau en instellingen op doorvoerniveau. Instellingen op databaseniveau hebben altijd voorrang op instellingen voor doorvoerniveau.

COMMIT-instelling/Database-configuratie DELAYED_DURABILITY = UITGESCHAKELD DELAYED_DURABILITY = TOEGESTAAN DELAYED_DURABILITY = GEFORCEERD
DELAYED_DURABILITY = UIT Transacties op database-niveau. Transactie is volledig duurzaam. Transactie is volledig duurzaam. Transactie is duurzaam vertraagd.
DELAYED_DURABILITY = ON Transacties op databaseniveau. Transactie is volledig duurzaam. Transactie is duurzaam vertraagd. Transactie is duurzaam vertraagd.
DELAYED_DURABILITY = OFF Cross database of gedistribueerde transactie. Transactie is volledig duurzaam. Transactie is volledig duurzaam. Transactie is volledig duurzaam.
DELAYED_DURABILITY = ON Cross-database ofwel gedistribueerde transactie. Transactie is volledig duurzaam. Transactie is volledig duurzaam. Transactie is volledig duurzaam.

Een transactielogboek leegmaken afdwingen

Er zijn twee manieren om het transactielogboek naar schijf af te dwingen.

  • Voer een volledig duurzame transactie uit die dezelfde database wijzigt. Hierdoor worden de logboekrecords van alle voorafgaande vastgelegde transacties met vertraagde duurzaamheid naar de schijf weggeschreven.

  • Voer de door het systeem opgeslagen procedure uit sp_flush_log. Deze procedure forceert het wegschrijven naar de schijf van de logbestanden van alle voorgaande vastgelegde vertraagde duurzame transacties. Zie sys.sp_flush_log (Transact-SQL)voor meer informatie.

Vertraagde duurzaamheid en andere SQL Server-functies

Transactionele Replicatie, Wijzigingen Bijhouden en Vastleggen van Gewijzigedata

  • Voor databases die zijn ingeschakeld voor transactionele replicatie of Change Data Capture (CDC), wordt het gebruik van vertraagde duurzaamheid niet ondersteund.

  • Wijzigingen volgen met vertraagde duurzaamheid wordt ondersteund. Alle transacties met de functie 'Wijzigingen bijhouden' zijn volledig betrouwbaar. Een transactie heeft de eigenschap wijzigingen bijhouden als er schrijfbewerkingen worden uitgevoerd naar tabellen waarvoor het bijhouden van wijzigingen is ingeschakeld.

Vanaf SQL Server 2022 CU 2 en SQL Server 2019 CU 20 ziet u mogelijk:

  • Error 22891: Could not enable '_FeatureName_' for database '_DatabaseName_'. '_FeatureName_' cannot be enabled on a DB with delayed durability set als u transactionele replicatie of Change Data Capture wilt inschakelen voor een database met uitgestelde duurzaamheid.

  • Error 22892: Could not enable delayed durability on DB. Delayed durability cannot be enabled on a DB while '_FeatureName_' is enabled als u probeert vertraagde duurzaamheid in te schakelen voor een database die is geconfigureerd met transactionele replicatie of Change Data Capture.

crashherstel
Consistentie is gegarandeerd, maar sommige wijzigingen van uitgestelde duurzame transacties die zijn doorgevoerd, kunnen mogelijk verloren gaan.

database-overschrijdend en DTC-
Als een transactie cross-database of gedistribueerd is, is deze volledig betrouwbaar, ongeacht welke instelling dan ook voor database- of transactieverwerking.

AlwaysOn-beschikbaarheidsgroepen en spiegeling
Vertraagde duurzame transacties garanderen geen duurzaamheid op de primaire of op enig van de secundaire systemen. Daarnaast garanderen ze geen kennis van de transactie bij de secundaire partij. Na het doorvoeren wordt de controle teruggegeven aan de client voordat een bevestiging wordt ontvangen van een synchrone secundaire instantie. Replicatie naar secundaire replica's blijft plaatsvinden, omdat de schijf wordt leeggemaakt op de primaire replica.

failover-clustering
Sommige vertraagde schrijfbewerkingen voor duurzame transacties kunnen mogelijk verloren gaan.

Azure Synapse Link voor SQL
Vertraagde duurzame transacties worden niet ondersteund met Azure Synapse Link voor SQL.

logboekverzending
Alleen transacties die duurzaam zijn vastgelegd, worden opgenomen in het logbestand dat wordt verzonden.

back-up van transactielogboeken
Alleen transacties die duurzaam zijn gemaakt, worden opgenomen in de back-up.

Wanneer kan ik gegevens kwijtraken?

Als u vertraagde duurzaamheid implementeert voor een van uw tabellen, moet u begrijpen dat bepaalde omstandigheden tot gegevensverlies kunnen leiden. Als u geen gegevensverlies tolereert, moet u geen vertraagde duurzaamheid voor uw tabellen gebruiken.

Onherstelbare gebeurtenissen

In het geval van een catastrofale gebeurtenis, zoals een servercrash, verliest u de gegevens voor alle vastgelegde transacties die niet op schijf zijn opgeslagen. Vertraagde duurzame transacties worden opgeslagen op schijf wanneer een volledig duurzame transactie wordt uitgevoerd op een tabel (duurzaam geoptimaliseerd voor geheugen of schijf) in de database of sp_flush_log wordt aangeroepen. Als u vertraagde duurzame transacties gebruikt, kunt u een kleine tabel maken in de database die u periodiek kunt bijwerken of periodiek sp_flush_log kunt aanroepen om alle openstaande vastgelegde transacties op te slaan. Het transactielogboek wordt ook leeggemaakt wanneer het vol wordt, maar dat is moeilijk te voorspellen en onmogelijk te beheren.

SQL Server afsluiten en opnieuw opstarten

Voor vertraagde duurzaamheid is er geen verschil tussen een onverwacht afsluiten en een verwachte afsluiting/herstart van SQL Server. Net als onherstelbare gebeurtenissen moet u gegevensverlies plannen. Bij een gepland afsluiten/opnieuw opstarten kunnen sommige transacties die niet naar de schijf zijn geschreven, worden opgeslagen op de schijf voordat u deze afsluit, maar moet u er niet op plannen. Plan alsof bij een afsluiting of opnieuw opstarten, of het nu gepland of ongepland is, de gegevens verloren gaan net als bij een catastrofale gebeurtenis.

Volgende stappen