Transacties met geheugen-geoptimaliseerde Tabellen

Van toepassing op:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

In dit artikel worden alle aspecten van transacties beschreven die specifiek zijn voor tabellen die zijn geoptimaliseerd voor geheugen en systeemeigen opgeslagen procedures.

De transactieisolatieniveaus in SQL Server zijn verschillend van toepassing op tabellen die zijn geoptimaliseerd voor geheugen versus tabellen op basis van schijven, en de onderliggende mechanismen verschillen. Een goed begrip van de verschillen helpt de programmeur bij het ontwerpen van een systeem met hoge doorvoer. Het doel van transactie-integriteit wordt in alle gevallen gedeeld.

Voor foutvoorwaarden die specifiek zijn voor transacties in tabellen die zijn geoptimaliseerd voor geheugen, gaat u naar de sectie Conflictdetectie en Logica voor opnieuw proberen.

Zie SET TRANSACTION ISOLATION LEVEL (Transact-SQL)voor algemene informatie.

Pessimistisch versus optimistisch

De functionele verschillen worden veroorzaakt door pessimistische versus optimistische benaderingen voor transactieintegriteit. Voor geheugen geoptimaliseerde tabellen wordt gebruikgemaakt van de optimistische benadering:

• Pessimistische benadering maakt gebruik van vergrendelingen om potentiële conflicten te blokkeren voordat ze optreden. Vergrendelingen worden genomen wanneer de instructie wordt uitgevoerd en vrijgegeven wanneer de transactie wordt bevestigd.

• Optimistische benadering detecteert conflicten wanneer ze optreden en voert validatiecontroles uit tijdens het doorvoeren.

  • Fout 1205, een impasse, kan niet optreden voor een tabel die is geoptimaliseerd voor geheugen.

De optimistische benadering is minder overhead en is meestal efficiënter, deels omdat transactieconflicten ongebruikelijk zijn in de meeste toepassingen. Het belangrijkste functionele verschil tussen de pessimistische en optimistische benaderingen is dat als er een conflict optreedt, u in de pessimistische benadering wacht, terwijl in de optimistische benadering een van de transacties mislukt en opnieuw moet worden geprobeerd door de klant. De functionele verschillen zijn groter wanneer het herhaalbare leesisolatieniveau van kracht is en het grootste is voor het SERIALIZABLE-niveau.

Transactieinitiatiemodi

SQL Server heeft de volgende modi voor het initiëren van transacties:

• Autocommit : het begin van een eenvoudige query of DML-instructie opent impliciet een transactie en het einde van de instructie voert de transactie impliciet door. Autocommit is de standaardinstelling.

  • In de autocommit-modus hoeft u meestal geen tabelhint te coderen over het transactie-isolatieniveau op de geheugen-geoptimaliseerde tabel in de FROM-clausule.

• Expliciet - uw Transact-SQL bevat de code BEGIN TRANSACTION, samen met een uiteindelijke COMMIT TRANSACTION. Twee of meer uitspraken kunnen in dezelfde transactie worden gegroepeerd.

  • In de expliciete modus moet u de database-optie MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT gebruiken, of een tabelhint definiëren die het isolatieniveau van de transactie aangeeft op de geheugen-geoptimaliseerde tabel in de FROM-clausule.

• Impliciet : wanneer SET IMPLICIT_TRANSACTION ON van kracht is. Misschien zou een betere naam zijn IMPLICIT_BEGIN_TRANSACTION, omdat al deze optie impliciet het equivalent van een expliciete BEGIN-TRANSACTIE voor elke UPDATE-instructie uitvoert als 0 = @@trancount. Daarom is het aan uw T-SQL-code om uiteindelijk een expliciete DOORVOERTRANSACTIE uit te geven.

• ATOMIC BLOCK : alle instructies in ATOMIC-blokken worden altijd uitgevoerd als onderdeel van één transactie. De acties van het atomische blok als geheel worden doorgevoerd bij succes, of de acties worden allemaal teruggedraaid in geval van een fout. Elke systeemeigen gecompileerde opgeslagen procedure vereist een ATOMIC-blok.

Codevoorbeeld met expliciete modus

In het volgende geïnterpreteerde Transact-SQL script wordt gebruikt:

• Een expliciete transactie.
• Een tabel die is geoptimaliseerd voor geheugen, met de naam dbo. Order_mo.
• De isolatieniveau-context van de READ COMMITTED-transactie.

Daarom is het nodig om een tabelhint te hebben voor de tabel die is geoptimaliseerd voor geheugen. De hint moet zijn voor MOMENTOPNAME of een nog meer isolerend niveau. Voor het codevoorbeeld is de hint WITH (SNAPSHOT). Als deze hint wordt verwijderd, heeft het script een fout 41368, waarvoor een automatische nieuwe poging ongepast zou zijn:

Fout 41368

Toegang tot tabellen die zijn geoptimaliseerd voor geheugen met behulp van het isolatieniveau READ COMMITTED wordt alleen ondersteund voor automatisch toewijzen van transacties. Het wordt niet ondersteund voor expliciete of impliciete transacties. Geef een ondersteund isolatieniveau op voor de tabel die is geoptimaliseerd voor geheugen met behulp van een tabelhint, zoals WITH (SNAPSHOT).

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;  
GO  

BEGIN TRANSACTION;  -- Explicit transaction.  

-- Order_mo  is a memory-optimized table.  
SELECT * FROM  
           dbo.Order_mo  as o  WITH (SNAPSHOT)  -- Table hint.  
      JOIN dbo.Customer  as c  on c.CustomerId = o.CustomerId;  
COMMIT TRANSACTION;

De noodzaak van de WITH (SNAPSHOT) hint kan worden vermeden door het gebruik van de databaseoptie MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT. Wanneer deze optie is ingesteld op ON, wordt de toegang tot een geheugen-geoptimaliseerde tabel onder een lager isolatieniveau automatisch verhoogd naar momentopname-isolatie.

ALTER DATABASE CURRENT
    SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = ON;

Rijversiebeheer van gegevensrijen

Tabellen die zijn geoptimaliseerd voor geheugen maken gebruik van een zeer geavanceerd systeem voor rijversiebeheer dat de optimistische benadering efficiënt maakt, zelfs op het meest strikte isolatieniveau van SERIALIZABLE. Zie Inleiding tot Memory-Optimized Tabellen voor meer details.

Schijftabellen hebben indirect een systeem voor rijversiebeheer wanneer READ_COMMITTED_SNAPSHOT of het SNAPSHOT-isolatieniveau van kracht is. Dit systeem is gebaseerd op tempdb, terwijl voor geheugen geoptimaliseerde gegevensstructuren een ingebouwde rijversiering hebben, voor maximale efficiëntie.

Isolatieniveaus

De volgende tabel bevat de mogelijke niveaus van transactieisolatie, in volgorde van minimale isolatie tot de meeste. Zie Conflictdetectie en Logica voor opnieuw proberen voor meer informatie over conflicten die kunnen optreden en logica voor opnieuw proberen om deze conflicten aan te pakken.

Transactiefasen en levensduur

Wanneer een voor geheugen geoptimaliseerde tabel betrokken is, doorloopt de levensduur van een transactie de fasen zoals weergegeven in de volgende afbeelding:

Beschrijvingen van de fasen volgen.

Reguliere verwerking: fase 1 (van 3)

• Deze fase bestaat uit de uitvoering van alle query's en DML-instructies in de query.
• Tijdens deze fase zien de verklaringen de versie van de geheugen-geoptimaliseerde tabellen vanaf de logische begintijd van de transactie.

Validatie: fase 2 (van 3)

• De validatiefase begint met het toewijzen van de eindtijd, waardoor de transactie als logisch is gemarkeerd. Met deze voltooiing worden alle wijzigingen van de transactie zichtbaar voor andere transacties die afhankelijk zijn van deze transactie. De afhankelijke transacties mogen niet worden gecommitteerd totdat deze transactie succesvol is gecommitteerd. Bovendien mogen transacties die dergelijke afhankelijkheden bevatten, geen resultatensets retourneren aan de client, om ervoor te zorgen dat de client alleen gegevens ziet die zijn doorgevoerd in de database.
• Deze fase bestaat uit de herhaalbare lees- en serialiseerbare validatie. Bij herhaalbare leesvalidatie wordt gecontroleerd of een van de rijen die door de transactie zijn gelezen, is bijgewerkt. Voor serialiseerbare validatie wordt gecontroleerd of een rij is ingevoegd in een gegevensbereik dat door deze transactie is gescand. Volgens de tabel in isolatieniveaus en conflicten kunnen zowel herhaalbare lees- als serialiseerbare validatie plaatsvinden bij het gebruik van momentopname-isolatie, om consistentie van unieke en refererende sleutelbeperkingen te valideren.

Commitverwerking: fase 3 (van 3)

• Tijdens de doorvoerfase worden de wijzigingen in duurzame tabellen naar het logboek geschreven en wordt het logboek naar de schijf geschreven. Vervolgens wordt het besturingselement teruggezet naar de client.
• Nadat de doorvoerverwerking is voltooid, krijgen alle afhankelijke transacties een melding dat ze kunnen doorvoeren.

Zoals altijd moet u proberen om uw transactionele werkeenheden zo minimaal en kort mogelijk te houden als geldig is voor uw gegevensbehoeften.

Conflictdetectie en logica voor opnieuw proberen

Er zijn twee soorten transactiegerelateerde foutvoorwaarden die ertoe leiden dat een transactie mislukt en terugdraait. Wanneer een dergelijke fout zich voordoet, moet de transactie in de meeste gevallen opnieuw worden geprobeerd, vergelijkbaar met wanneer er een impasse optreedt.

• Conflicten tussen gelijktijdige transacties. Dit zijn updateconflicten en validatiefouten en kunnen worden veroorzaakt door schendingen van transactieisolatieniveau of schendingen van beperkingen.
• Afhankelijkheidsstoringen. Deze problemen ontstaan door transacties waarvan u afhankelijk bent maar die niet worden uitgevoerd, of door een te groot aantal afhankelijkheden.

Hier volgen de foutvoorwaarden die ertoe kunnen leiden dat transacties mislukken wanneer ze toegang hebben tot tabellen die zijn geoptimaliseerd voor geheugen.

Logica voor opnieuw proberen

Wanneer een transactie mislukt vanwege een van de bovenstaande voorwaarden, moet de transactie opnieuw worden geprobeerd.

Logica voor opnieuw proberen kan worden geïmplementeerd aan de client- of serverzijde. De algemene aanbeveling is het implementeren van logica voor opnieuw proberen aan de clientzijde, omdat het efficiënter is en u kunt omgaan met resultatensets die door de transactie worden geretourneerd voordat de fout optreedt.

Voorbeeld van T-SQL-code opnieuw proberen

Logica voor opnieuw proberen aan de serverzijde met behulp van T-SQL mag alleen worden gebruikt voor transacties die geen resultatensets retourneren aan de client. Anders kunnen nieuwe pogingen leiden tot extra resultatensets dan de verwachte resultatensets die naar de client worden geretourneerd.

Het volgende geïnterpreteerde T-SQL-script illustreert hoe logica voor opnieuw proberen eruit kan zien voor de fouten die zijn gekoppeld aan transactieconflicten met behulp van tabellen die zijn geoptimaliseerd voor geheugen.

-- Retry logic, in Transact-SQL.
DROP PROCEDURE If Exists usp_update_salesorder_dates;
GO

CREATE PROCEDURE usp_update_salesorder_dates
AS
BEGIN
    DECLARE @retry INT = 10;

    WHILE (@retry > 0)
    BEGIN
        BEGIN TRY
            BEGIN TRANSACTION;

            UPDATE dbo.SalesOrder_mo WITH (SNAPSHOT)
                set OrderDate = GetUtcDate()
                where CustomerId = 42;

            UPDATE dbo.SalesOrder_mo WITH (SNAPSHOT)
                set OrderDate = GetUtcDate()
                where CustomerId = 43;

            COMMIT TRANSACTION;

            SET @retry = 0;  -- //Stops the loop.
        END TRY

        BEGIN CATCH
            SET @retry -= 1;

            IF (@retry > 0 AND
                ERROR_NUMBER() in (41302, 41305, 41325, 41301, 41823, 41840, 41839, 1205)
                )
            BEGIN
                IF XACT_STATE() = -1
                    ROLLBACK TRANSACTION;

                WAITFOR DELAY '00:00:00.001';
            END
            ELSE
            BEGIN
                PRINT 'Suffered an error for which Retry is inappropriate.';
                THROW;
            END
        END CATCH

    END -- //While loop
END;
GO

--  EXECUTE usp_update_salesorder_dates;

Transactie tussen containers

Een transactie wordt een transactie tussen containers genoemd als deze het volgende doet:

• Opent een tabel die is geoptimaliseerd voor geheugen vanuit geïnterpreteerde Transact-SQL; of
• Voert een systeemeigen proc uit wanneer een transactie al is geopend (XACT_STATE() = 1).

De term 'cross-container' is afgeleid van het feit dat de transactie wordt uitgevoerd in de twee containers voor transactiebeheer, één voor schijftabellen en één voor tabellen die zijn geoptimaliseerd voor geheugen.

Binnen een enkele transactie tussen containers kunnen verschillende isolatieniveaus worden gebruikt voor toegang tot schijf-gebaseerde en geheugen-geoptimaliseerde tabellen. Dit verschil wordt uitgedrukt via expliciete tabelhints zoals WITH (SERIALIZABLE) of via de databaseoptie MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT, waarmee impliciet het isolatieniveau voor de geheugen-geoptimaliseerde tabel wordt verhoogd naar momentopname wanneer het TRANSACTION ISOLATION LEVEL is geconfigureerd als READ COMMITTED of READ UNCOMMITTED.

In het volgende Transact-SQL codevoorbeeld:

• De schijftabel, Table_D1, wordt geopend met behulp van het isolatieniveau READ COMMITTED.
• De tabel die is geoptimaliseerd voor geheugen Table_MO7 wordt geopend met behulp van het SERIALIZABLE-isolatieniveau. Table_MO6 heeft geen specifiek isolatieniveau, omdat invoegingen altijd consistent zijn en in wezen worden uitgevoerd onder serialiseerbare isolatie.

-- Different isolation levels for
-- disk-based tables versus memory-optimized tables,
-- within one explicit transaction.

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
go

BEGIN TRANSACTION;

    -- Table_D1 is a traditional disk-based table, accessed using READ COMMITTED isolation.

    SELECT * FROM Table_D1;


    -- Table_MO6 and Table_MO7 are memory-optimized tables.
    -- Table_MO7 is accessed using SERIALIZABLE isolation,
    --   while Table_MO6 does not have a specific isolation level.

    INSERT Table_MO6
        SELECT * FROM Table_MO7 WITH (SERIALIZABLE);

COMMIT TRANSACTION;
go

Beperkingen

• Transacties tussen databases worden niet ondersteund voor tabellen die zijn geoptimaliseerd voor geheugen. Als een transactie toegang heeft tot een tabel die is geoptimaliseerd voor geheugen, heeft de transactie geen toegang tot een andere database, met uitzondering van:

  • tempdb-database.
  • Alleen-lezen uit de hoofddatabase.

• Gedistribueerde transacties worden niet ondersteund: wanneer BEGIN DISTRIBUTED TRANSACTION wordt gebruikt, heeft de transactie geen toegang tot een tabel die is geoptimaliseerd voor geheugen.

Systeemeigen gecompileerde opgeslagen procedures

• In een systeemeigen proc moet het ATOMIC-blok het niveau van transactieisolatie voor het hele blok declareren, zoals:

  • ... BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, ...) ...

• Er zijn geen expliciete instructies voor transactiebeheer toegestaan binnen de hoofdtekst van een systeemeigen proc. BEGIN TRANSACTION, ROLLBACK TRANSACTION, enzovoort, zijn allemaal niet toegestaan.

• Zie Atomische blokken voor meer informatie over transactiebeheer met ATOMIC-blokken

Andere transactiekoppelingen

• SET IMPLICIT_TRANSACTIONS

• sp_getapplock (Transact-SQL)

• Rijversiegebaseerde isolatieniveaus in de databasemotor

• Duurzaamheid van transacties beheren