Transaktioner med minnesoptimerade tabeller

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

Den här artikeln beskriver alla aspekter av transaktioner som är specifika för minnesoptimerade tabeller och inbyggda kompilerade lagrade procedurer.

Transaktionsisoleringsnivåerna i SQL Server gäller på olika sätt för minnesoptimerade tabeller jämfört med diskbaserade tabeller, och de underliggande mekanismerna skiljer sig åt. En förståelse av skillnaderna hjälper programmeraren att utforma ett system med högt dataflöde. Målet med transaktionsintegritet delas i alla fall.

För felvillkor som är specifika för transaktioner i minnesoptimerade tabeller går du till avsnittet Konfliktidentifiering och Logik för återförsök.

Allmän information finns i ANGE TRANSAKTIONSISOLERINGSNIVÅ (Transact-SQL).

Pessimistisk kontra optimistisk

De funktionella skillnaderna beror på pessimistiska kontra optimistiska metoder för transaktionsintegritet. Minnesoptimerade tabeller använder den optimistiska metoden:

• Pessimistisk metod använder lås för att blockera potentiella konflikter innan de inträffar. Lås tas när uttalandet körs och frigörs när transaktionen har begåtts.

• Optimistisk ansats identifierar konflikter när de inträffar och utför valideringskontrollerna vid commit-tidpunkten.

  • Fel 1205, ett dödläge, kan inte inträffa för en minnesoptimerad tabell.

Den optimistiska metoden är mindre omkostnader och är vanligtvis mer effektiv, delvis på grund av att transaktionskonflikter är ovanliga i de flesta program. Den största funktionella skillnaden mellan pessimistiska och optimistiska metoder är att om en konflikt inträffar, i den pessimistiska metoden väntar du, medan en av transaktionerna misslyckas och måste försökas igen av klienten i den optimistiska metoden. De funktionella skillnaderna är större när repeatable READ-isoleringsnivån är i kraft och är störst för SERIALIZABLE-nivån.

Initieringslägen för transaktioner

SQL Server har följande lägen för transaktionsinitiering:

• Autocommit – Starten av en enkel fråga eller DML-instruktion öppnar implicit en transaktion och slutet av -instruktionen genomför implicit transaktionen. Autocommit är standardinställningen.

  • I autocommit-läget behöver man vanligtvis inte ange en tabellhint om transaktionsisoleringsnivån på den minnesoptimerade tabellen i FROM-klausul.

• Explicit – Din Transact-SQL innehåller koden BEGIN TRANSACTION, tillsammans med en eventuell COMMIT TRANSACTION. Två eller flera uttryck kan samlas i samma transaktion.

  • I explicit läge måste du antingen använda databasalternativet MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT eller koda en tabellhint om transaktionsisoleringsnivån på den minnesoptimerade tabellen i FROM-klausulen.

• Implicit – När SET IMPLICIT_TRANSACTION ON är i kraft. Kanske skulle ett bättre namn ha varit IMPLICIT_BEGIN_TRANSACTION, eftersom allt det här alternativet gör är implicit att utföra motsvarigheten till en explicit BEGIN TRANSACTION före varje UPDATE-instruktion om 0 = @@trancount. Därför är det upp till din T-SQL-kod att så småningom utfärda en explicit COMMIT TRANSACTION.

• ATOMIC BLOCK – Alla instruktioner i ATOMIC-block körs alltid som en del av en enda transaktion. Antingen utförs åtgärderna i atomblocket som helhet när det lyckas eller så återställs alla åtgärder när ett fel inträffar. Varje inbyggt kompilerad lagrad procedur kräver ett ATOMIC-block.

Kodexempel med explicit läge

Följande tolkade Transact-SQL skript använder:

• En tydlig transaktion.
• En minnesoptimerad tabell med namnet dbo. Order_mo.
• Kontexten READ COMMITTED transaction isolation level (READ COMMITTED-transaktionsisoleringsnivå).

Därför är det nödvändigt att ha en tabellhint för den minnesoptimerade tabellen. Tipset måste vara för ÖGONBLICKSBILD eller en ännu mer isolerande nivå. När det gäller kodexemplet är tipset WITH (SNAPSHOT). Om det här tipset tas bort skulle skriptet drabbas av ett fel 41368, för vilket ett automatiskt återförsök skulle vara olämpligt:

Fel 41368

Åtkomst till minnesoptimerade tabeller med "READ COMMITTED"-isoleringsnivån stöds endast för transaktioner med autokommitt. Det stöds inte för explicita eller implicita transaktioner. Ange en isoleringsnivå som stöds för den minnesoptimerade tabellen med hjälp av ett tabelltips, till exempel 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;

Behovet av tipset WITH (SNAPSHOT) kan undvikas med hjälp av databasalternativet MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT. När det här alternativet är inställt på ONutökas åtkomsten till en minnesoptimerad tabell under en lägre isoleringsnivå automatiskt till ögonblicksbildisolering.

ALTER DATABASE CURRENT
    SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = ON;

Radversion

Minnesoptimerade tabeller använder ett mycket avancerat system för radversionshantering som gör den optimistiska metoden effektiv, även på den mest strikta isoleringsnivån i SERIALIZABLE. För mer information, se Introduktion till Memory-Optimized Tabeller.

Diskbaserade tabeller har indirekt ett system för radversionering när READ_COMMITTED_SNAPSHOT eller SNAPSHOT-isoleringsnivån är i kraft. Det här systemet baseras på tempdb, medan minnesoptimerade datastrukturer har inbyggda radversioner för maximal effektivitet.

Isoleringsnivåer

I följande tabell visas möjliga nivåer av transaktionsisolering, i följd från minsta isolering till de flesta. Mer information om konflikter som kan uppstå och omprövningslogik för att hantera dessa konflikter finns i Konfliktidentifiering och Logik för återförsök.

Transaktionsfaser och livslängd

När en minnesoptimerad tabell ingår fortsätter livslängden för en transaktion genom faserna som visas i följande bild:

Beskrivningar av faserna följer.

Reguljär bearbetning: Fas 1 (av 3)

• Den här fasen består av körningen av alla frågor och DML-instruktioner i frågan.
• Under denna fas ser instruktionerna versionen av minnesoptimerade tabellerna vid den logiska starttiden för transaktionen.

Validering: Fas 2 (av 3)

• Valideringsfasen börjar med att tilldela sluttiden, vilket markerar transaktionen som logiskt slutförd. Detta slutförande gör alla ändringar av transaktionen synliga hos andra transaktioner som är beroende av denna transaktion. De beroende transaktionerna får inte slutföras förrän den här transaktionen framgångsrikt har slutförts. Dessutom tillåts inte transaktioner som har sådana beroenden att returnera resultatuppsättningar till klienten, för att säkerställa att klienten bara ser data som har checkats in till databasen.
• Den här fasen består av upprepningsbar läsning och serialiserbar validering. För upprepningsbar läsverifiering kontrollerar den om någon av de rader som lästs av transaktionen sedan dess har uppdaterats. För serialiserbar validering kontrollerar den om någon rad har infogats i något dataområde som genomsökts av den här transaktionen. Enligt tabellen i isoleringsnivåer och konflikter kan både upprepningsbar läs- och serialiserbar validering ske när ögonblicksbildisolering används för att verifiera konsekvensen för unika och främmande nyckelbegränsningar.

Bearbetning av åtaganden: Fas 3 (av 3)

• Under commitfasen skrivs ändringarna av beständiga tabeller till loggen och loggen skrivs till disken. Sedan returneras kontrollen till klienten.
• När incheckningen har slutförts meddelas alla beroende transaktioner att de kan checka in.

Som alltid bör du försöka hålla dina transaktionsenheter så minimala och korta som de är giltiga för dina databehov.

Logik för konfliktidentifiering och återförsök

Det finns två typer av transaktionsrelaterade feltillstånd som gör att en transaktion misslyckas och återställs. I de flesta fall, när ett sådant fel inträffar, måste transaktionen göras om, ungefär som när ett dödläge inträffar.

• Konflikter mellan samtidiga transaktioner. Det här är uppdateringskonflikter och valideringsfel och kan bero på överträdelser på transaktionsisoleringsnivå eller begränsningsöverträdelser.
• Beroendefel. Dessa problem uppstår när transaktioner som du är beroende av inte godkänns, eller när antalet beroenden blir för stort.

Följande är de felvillkor som kan orsaka att transaktioner misslyckas när de får åtkomst till minnesoptimerade tabeller.

Försök med logik igen

När en transaktion misslyckas på grund av något av ovanstående villkor bör transaktionen göras om.

Logik för återförsök kan implementeras på klient- eller serversidan. Den allmänna rekommendationen är att implementera omprövningslogik på klientsidan, eftersom det är mer effektivt, och gör att du kan hantera resultatuppsättningar som returneras av transaktionen innan felet inträffar.

Exempel på T-SQL-kod igen

Logik för återförsök på serversidan med T-SQL ska endast användas för transaktioner som inte returnerar resultatuppsättningar till klienten. Annars kan återförsök potentiellt resultera i ytterligare resultatuppsättningar utöver de som förväntas returneras till klienten.

Följande tolkade T-SQL-skript illustrerar hur logik för återförsök kan se ut för fel som är associerade med transaktionskonflikter som involverar minnesoptimerade tabeller.

-- 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;

Transaktion mellan containrar

En transaktion kallas för en transaktion mellan containrar om den:

• Får åtkomst till en minnesoptimerad tabell från tolkad Transact-SQL. eller
• Kör en intern process när en transaktion redan är öppen (XACT_STATE() = 1).

Termen "cross-container" härleds från det faktum att transaktionen körs över de två transaktionshanteringscontainrarna, en för diskbaserade tabeller och en för minnesoptimerade tabeller.

I en enda transaktion mellan containrar kan olika isoleringsnivåer användas för åtkomst till diskbaserade och minnesoptimerade tabeller. Den här skillnaden uttrycks genom explicita tabelltips som WITH (SERIALIZABLE) eller via databasalternativet MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT, som implicit höjer isoleringsnivån för minnesoptimerad tabell till ögonblicksbild om TRANSAKTIONSISOLERINGsnivån har konfigurerats som READ COMMITTED eller READ UNCOMMITTED.

I följande Transact-SQL kodexempel:

• Den diskbaserade tabellen, Table_D1, nås med hjälp av READ COMMITTED-isoleringsnivån.
• Den minnesoptimerade tabellen Table_MO7 nås med hjälp av isoleringsnivån SERIALIZABLE. Table_MO6 har ingen specifik associerad isoleringsnivå, eftersom infogningar alltid är konsekventa och körs i stort sett under serialiserbar isolering.

-- 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

Begränsningar

• Transaktioner mellan databaser stöds inte för minnesoptimerade tabeller. Om en transaktion kommer åt en minnesoptimerad tabell kan transaktionen inte komma åt någon annan databas, förutom:

  • tempdb-databas.
  • Skrivskyddad i huvuddatabasen.

• Distribuerade transaktioner stöds inte: När BEGIN DISTRIBUTED TRANSACTION används kan transaktionen inte komma åt en minnesoptimerad tabell.

Inhemskt kompilerade lagrade procedurer

• I en intern process måste ATOMIC-blocket deklarera transaktionsisoleringsnivån för hela blocket, till exempel:

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

• Inga explicita transaktionskontrollinstruktioner tillåts i brödtexten i en intern process. BEGIN TRANSACTION, ROLLBACK TRANSACTION, och med flera, är alla otillåtna.

• Mer information om transaktionskontroll med ATOMIC-block finns i Atomblock

Andra transaktionslänkar

• ANGE IMPLICIT_TRANSACTIONS

• sp_getapplock (Transact-SQL)

• Radversionsbaserade isoleringsnivåer i databasmotorn

• Kontrollera transaktionshållbarhet