Dela via

Versionsguide för transaktionslåsning och rad

  • Artikel

gäller för:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)SQL-databas i Microsoft Fabric

I alla databaser leder felaktig hantering av transaktioner ofta till konkurrens- och prestandaproblem i system som har många användare. När antalet användare som får åtkomst till data ökar blir det viktigt att ha program som använder transaktioner effektivt. Den här guiden beskriver låsnings- och radversionsmekanismer som databasmotorn använder för att säkerställa varje transaktions integritet och ger information om hur program kan kontrollera transaktioner effektivt.

Obs

Optimerad låsning är en databasmotorfunktion som introducerades 2023 och som drastiskt minskar låsminnet och antalet lås som krävs för samtidiga skrivningar. Den här artikeln uppdateras för att beskriva database engine-beteendet med och utan optimerad låsning.

Optimerad låsning medför betydande ändringar i vissa avsnitt i den här artikeln, inklusive:

Grunderna för transaktioner

En transaktion är en sekvens med åtgärder som utförs som en enda logisk arbetsenhet. En logisk arbetsenhet måste ha fyra egenskaper, som kallas egenskaperna atomicitet, konsekvens, isolering och hållbarhet (ACID), för att kvalificeras som en transaktion.

Atomicitet
En transaktion måste vara en atomisk arbetsenhet. antingen utförs alla dataändringar eller så utförs ingen av dem.

Konsekvens
När den är klar måste en transaktion lämna alla data i ett konsekvent tillstånd. I en relationsdatabas måste alla regler tillämpas på transaktionens ändringar för att upprätthålla all dataintegritet. Alla interna datastrukturer, till exempel B-trädindex eller dubbelt länkade listor, måste vara korrekta i slutet av transaktionen.

Obs

I dokumentationen används termen B-träd vanligtvis som referens till index. I radlagringsindex implementerar databasmotorn ett B+-träd. Detta gäller inte för kolumnlagringsindex eller index i minnesoptimerade tabeller. Mer information finns i arkitekturen och designguiden för SQL Server och Azure SQL-index.

isolering
Ändringar som görs av samtidiga transaktioner måste isoleras från de ändringar som görs av andra samtidiga transaktioner. En transaktion identifierar antingen data i det tillstånd den befann sig i innan en annan samtidig transaktion ändrade den, eller så känner den igen data när den andra transaktionen har slutförts, men den känner inte igen ett mellanliggande tillstånd. Detta kallas serialisering eftersom det resulterar i möjligheten att läsa in startdata igen och spela upp en serie transaktioner för att sluta med data i samma tillstånd som de var i efter att de ursprungliga transaktionerna utfördes.

Hållbarhet
När en helt varaktig transaktion har slutförts är dess effekter permanent på plats i systemet. Ändringarna kvarstår även i händelse av ett systemfel. SQL Server 2014 (12.x) och senare aktivera fördröjda varaktiga transaktioner. Fördröjda varaktiga transaktioner bekräftas innan transaktionsloggposten sparas på disken. Mer information om fördröjd transaktionshållbarhet finns i artikeln Control Transaction Durability.

Applikationer är ansvariga för att starta och avsluta transaktioner vid punkter som framtvingar logisk konsekvens av data. Programmet måste definiera sekvensen med dataändringar som lämnar data i ett konsekvent tillstånd i förhållande till organisationens affärsregler. Programmet utför dessa ändringar i en enda transaktion så att databasmotorn kan framtvinga transaktionens integritet.

Det är ett företags databassystems ansvar, till exempel en instans av databasmotorn, att tillhandahålla mekanismer som säkerställer integriteten för varje transaktion. Databasmotorn tillhandahåller:

  • Låsningsanläggningar som bevarar transaktionsisolering.

  • Loggningsanläggningar för att säkerställa transaktionshållbarhet. För fullständigt varaktiga transaktioner skrivs loggposten till disk innan transaktionen bekräftas. Även om servermaskinvaran, operativsystemet eller instansen av själva databasmotorn misslyckas använder instansen transaktionsloggarna vid omstart för att automatiskt återställa eventuella ofullständiga transaktioner till systemfelets tidpunkt. Fördröjda bekräftelser av beständiga transaktioner genomförs innan transaktionsloggposten skrivs till disk. Sådana transaktioner kan gå förlorade om det uppstår ett systemfel innan loggposten härdas till disken. Mer information om fördröjd transaktionshållbarhet finns i artikeln Control Transaction Durability.

  • Transaktionshanteringsfunktioner som säkerställer transaktions atomitet och konsekvens. När en transaktion har startats måste den slutföras (bekräftas) eller så ångrar databasmotorn alla dataändringar som gjorts av transaktionen sedan transaktionen startades. Den här åtgärden kallas för att återställa en transaktion eftersom den returnerar data till det tillstånd som den var före dessa ändringar.

Styr transaktioner

Program styr transaktioner främst genom att ange när en transaktion startar och slutar. Detta kan anges med hjälp av antingen Transact-SQL-instruktioner eller API-funktioner (Database Application Programming Interface). Systemet måste också kunna hantera fel som avslutar en transaktion korrekt innan den slutförs. Mer information finns i Transactions, Performing Transactions in ODBCoch Transactions in SQL Server Native Client.

Som standard hanteras transaktioner på anslutningsnivå. När en transaktion startas på en anslutning är alla Transact-SQL-instruktioner som körs på den anslutningen en del av transaktionen tills transaktionen upphör. Under en MARS-session (Multiple Active Result Set) blir en Transact-SQL explicit eller implicit transaktion en transaktion med batchomfattning som hanteras på batchnivå. När batchen är klar, om den batchomfattande transaktionen inte har genomförts eller återställts, återställs den automatiskt av databasmotorn. Mer information finns i Använda flera aktiva resultatuppsättningar (MARS).

Starta transaktioner

Med hjälp av API-funktioner och Transact-SQL-instruktioner kan du starta transaktioner som explicita, automatiska eller implicita transaktioner.

explicita transaktioner

En explicit transaktion är en transaktion där du uttryckligen definierar både början och slutet av transaktionen via en API-funktion eller genom att utfärda Transact-SQL BEGIN TRANSACTION, COMMIT TRANSACTION, COMMIT WORK, ROLLBACK TRANSACTIONeller ROLLBACK WORK Transact-SQL-instruktioner. När transaktionen avslutas återgår anslutningen till det transaktionsläge som den befann sig i innan den explicita transaktionen startades, vilket kan vara implicit läge eller automatiskt återtagandeläge.

Du kan använda alla Transact-SQL-instruktioner i en explicit transaktion, förutom följande instruktioner:

  • CREATE DATABASE
  • ALTER DATABASE
  • DROP DATABASE
  • CREATE FULLTEXT CATALOG
  • ALTER FULLTEXT CATALOG
  • DROP FULLTEXT CATALOG
  • DROP FULLTEXT INDEX
  • ALTER FULLTEXT INDEX
  • CREATE FULLTEXT INDEX
  • BACKUP
  • RESTORE
  • RECONFIGURE
  • Lagrade procedurer för fulltextsystem
  • sp_dboption för att ange databasalternativ eller systemprocedurer som ändrar master databas i explicita eller implicita transaktioner.

Obs

UPDATE STATISTICS kan användas i en explicit transaktion. Emellertid åtar sig UPDATE STATISTICS oberoende av den omslutande transaktionen och kan inte rullas tillbaka.

Automatiskt genomförande av transaktioner

Autocommit-läge är standardläget för transaktionshantering i databasmotorn. Varje Transact-SQL-instruktion förpliktas eller ångras när den är klar. Om en transaktion slutförs framgångsrikt bekräftas den; om den stöter på något fel återställs den. En anslutning till en instans av databasmotorn fungerar i autokommittläge när detta standardläge inte har åsidosatts av uttalade eller underförstådda transaktioner. Autocommit-läge är också standardläget för SqlClient, ADO, OLE DB och ODBC.

implicita transaktioner

När en anslutning körs i implicit transaktionsläge startar databasmotorinstansen automatiskt en ny transaktion när den aktuella transaktionen har bekräftats eller återförts. Du gör ingenting för att markera starten av en transaktion; du bara begår eller återställer varje transaktion. Implicit transaktionsläge genererar en kontinuerlig transaktionskedja. Ange implicit transaktionsläge på via antingen en API-funktion eller Transact-SQL SET IMPLICIT_TRANSACTIONS ON-instruktionen. Det här läget är även känt som "Autocommit OFF", se setAutoCommit-metoden (SQLServerConnection).

När implicit transaktionsläge har aktiverats för en anslutning startar instansen av databasmotorn automatiskt en transaktion när den först kör någon av dessa instruktioner:

  • ALTER TABLE
  • CREATE
  • DELETE
  • DENY
  • DROP
  • FETCH
  • GRANT
  • INSERT
  • OPEN
  • REVOKE
  • SELECT
  • TRUNCATE
  • UPDATE

Batch-specifika transaktioner

Gäller endast för flera aktiva resultatuppsättningar (MARS) blir en Transact-SQL explicit eller implicit transaktion som startar under en MARS-session en batchomfattande transaktion. En batchomfattande transaktion som inte bekräftas eller rullas tillbaka när en batch slutförs rullas automatiskt tillbaka av databasmotorn.

distribuerade transaktioner

Distribuerade transaktioner omfattar två eller flera servrar som kallas resurshanterare. Hanteringen av transaktionen måste samordnas mellan resurshanterarna av en serverkomponent som kallas transaktionshanterare. Varje instans av databasmotorn kan fungera som resurshanterare i distribuerade transaktioner som samordnas av transaktionshanterare, till exempel Microsoft Distributed Transaction Coordinator (MS DTC) eller andra transaktionshanterare som stöder Open Group XA-specifikationen för distribuerad transaktionsbearbetning. Mer information finns i MS DTC-dokumentationen.

En transaktion inom en enda instans av databasmotorn som sträcker sig över två eller flera databaser är en distribuerad transaktion. Instansen hanterar den distribuerade transaktionen internt. för användaren fungerar den som en lokal transaktion.

I programmet hanteras en distribuerad transaktion ungefär på samma sätt som en lokal transaktion. I slutet av transaktionen begär programmet att transaktionen antingen bekräftas eller återställs. En distribuerad commit måste hanteras på ett annat sätt av transaktionshanteraren för att minimera risken att ett nätverksfel kan resultera i att vissa resurshanterare lyckas genomföra ändringarna medan andra återställer transaktionen. Detta uppnås genom att hantera incheckningsprocessen i två faser (förberedelsefasen och incheckningsfasen), som kallas för en tvåfasincheckning.

  • Förberedelsefas

    När transaktionshanteraren tar emot en begäran om godkännande skickar den ett förberedande kommando till alla resurshanterarna som är inblandade i transaktionen. Varje resurshanterare gör sedan allt som krävs för att göra transaktionen beständig och alla transaktionsloggbuffertar för transaktionen rensas till disk. När varje resurshanterare slutför förberedelsefasen returnerar den resultatet av fasen, framgång eller misslyckande, till transaktionshanteraren. SQL Server 2014 (12.x) medförde fördröjd transaktionshållbarhet. Fördröjda varaktiga transaktioner slutförs innan transaktionsloggbuffertarna på varje resurshanterare skrivs till disk. Mer information om fördröjd transaktionshållbarhet finns i artikeln Control Transaction Durability.

  • incheckningsfasen

    Om transaktionshanteraren får lyckade förberedelser från alla resurshanterare, skickar den bekräftelsekommandon till varje resurshanterare. Resurshanterna kan sedan slutföra åtagandet. Om alla resurshanterare rapporterar ett lyckat åtagande skickar transaktionshanteraren sedan en bekräftelse av framgång till programmet. Om någon resurshanterare rapporterade att det inte gick att förbereda skickar transaktionshanteraren ett återställningskommando till varje resurshanterare och anger att incheckningen till programmet misslyckades.

    Databasmotorprogram kan hantera distribuerade transaktioner antingen via Transact-SQL eller via databas-API:et. Mer information finns i BEGIN DISTRIBUTED TRANSACTION (Transact-SQL).

Avsluta transaktioner

Du kan avsluta transaktioner med antingen en COMMIT- eller ROLLBACK-instruktion eller via en motsvarande API-funktion.

  • Commit

    Om en transaktion lyckas, bekräfta den. En COMMIT-instruktion garanterar att alla ändringar av transaktionen görs som en permanent del av databasen. En transaktionsbekräftelse frigör även resurser, till exempel lås, som används av transaktionen.

  • Rulla tillbaka

    Om ett fel uppstår i en transaktion, eller om användaren bestämmer sig för att avbryta transaktionen, återställer du transaktionen. En ROLLBACK-instruktion säkerhetskopierar alla ändringar som gjorts i transaktionen genom att returnera data till det tillstånd som den befann sig i i början av transaktionen. Återställning frigör även resurser som innehas av transaktionen.

Obs

På sessioner med flera aktiva resultatuppsättningar (MARS) kan en explicit transaktion som startats via en API-funktion inte avslutas när det finns väntande exekveringsförfrågningar. Varje försök att utföra den här typen av transaktion medan det finns körande begäranden resulterar i ett fel.

Fel vid transaktionsbearbetning

Om ett fel förhindrar att en transaktion slutförs återställer databasmotorn automatiskt transaktionen och frigör alla resurser som innehas av transaktionen. Om klientnätverksanslutningen till en instans av databasmotorn är bruten återställs alla utestående transaktioner för anslutningen när nätverket meddelar instansen av anslutningsbrottet. Om klientprogrammet misslyckas eller om klientdatorn stängs av eller startas om bryts även anslutningen, och instansen av databasmotorn återställer eventuella utestående transaktioner när nätverket meddelar det om anslutningsavbrottet. Om klienten kopplas från databasmotorn återställs alla utestående transaktioner.

Om ett körningsfel (till exempel en begränsningsöverträdelse) inträffar i en batch är standardbeteendet i databasmotorn att endast rulla tillbaka instruktionen som genererade felet. Du kan ändra det här beteendet med hjälp av instruktionen SET XACT_ABORT ON. När SET XACT_ABORT ON har körts orsakar alla körningsinstruktorfel en automatisk återställning av den aktuella transaktionen. Kompileringsfel, till exempel syntaxfel, påverkas inte av SET XACT_ABORT. Mer information finns i SET XACT_ABORT (Transact-SQL).

När fel inträffar bör lämplig åtgärd (COMMIT eller ROLLBACK) ingå i programkoden. Ett effektivt verktyg för att hantera fel, inklusive de i transaktioner, är den Transact-SQL TRY...CATCH konstruktionen. Mer information om exempel som innehåller transaktioner finns i TRY... CATCH (Transact-SQL). Från och med SQL Server 2012 (11.x) kan du använda instruktionen THROW för att generera ett undantag och överföra körningen till ett CATCH block i en TRY...CATCH konstruktion. Mer information finns i THROW (Transact-SQL).

Kompilerings- och körfel i autocommit-läge

I autocommit-läge ser det ibland ut som om en instans av databasmotorn har återställt en hel batch i stället för bara en SQL-instruktion. Detta inträffar om felet som påträffades är ett kompileringsfel, inte ett körningsfel. Ett kompileringsfel hindrar databasmotorn från att skapa en körningsplan. Därför kan inget i batchen köras. Även om det verkar som om alla instruktioner innan den som genererade felet återställdes, så förhindrade felet att något i batchen kördes. I följande exempel körs ingen av de INSERT-uttrycken i den tredje batchen på grund av ett kompileringsfel. Det verkar som om de två första INSERT-uttrycken återställs utan att de någonsin körs.

CREATE TABLE TestBatch (ColA INT PRIMARY KEY, ColB CHAR(3));
GO
INSERT INTO TestBatch VALUES (1, 'aaa');
INSERT INTO TestBatch VALUES (2, 'bbb');
INSERT INTO TestBatch VALUSE (3, 'ccc');  -- Syntax error.
GO
SELECT * FROM TestBatch;  -- Returns no rows.
GO

I följande exempel genererar den tredje INSERT-instruktionen ett fel på grund av dubbla primärnycklar vid körtid. De två första INSERT-instruktionerna är lyckade och bekräftade, så de kvarstår efter körningsfelet.

CREATE TABLE TestBatch (ColA INT PRIMARY KEY, ColB CHAR(3));
GO
INSERT INTO TestBatch VALUES (1, 'aaa');
INSERT INTO TestBatch VALUES (2, 'bbb');
INSERT INTO TestBatch VALUES (1, 'ccc');  -- Duplicate key error.
GO
SELECT * FROM TestBatch;  -- Returns rows 1 and 2.
GO

Databasmotorn använder uppskjuten namnmatchning, där objektnamn matchas vid körning, inte vid kompileringstid. I följande exempel utförs och sparas de två första INSERT-instruktionerna, och de två raderna finns kvar i tabellen TestBatch efter att den tredje INSERT-instruktionen genererar ett körningsfel genom att referera till en tabell som inte finns.

CREATE TABLE TestBatch (ColA INT PRIMARY KEY, ColB CHAR(3));
GO
INSERT INTO TestBatch VALUES (1, 'aaa');
INSERT INTO TestBatch VALUES (2, 'bbb');
INSERT INTO TestBch VALUES (3, 'ccc');  -- Table name error.
GO
SELECT * FROM TestBatch;  -- Returns rows 1 and 2.
GO

Grunderna i låsning och radversionering

Databasmotorn använder följande mekanismer för att säkerställa transaktionernas integritet och upprätthålla konsekvensen i databaser när flera användare har åtkomst till data samtidigt:

  • Lås

    Varje transaktion begär lås av olika typer på resurserna, till exempel rader, sidor eller tabeller, som transaktionen är beroende av. Låsen blockerar andra transaktioner från att ändra resurserna på ett sätt som skulle orsaka problem för transaktionen som begär låset. Varje transaktion frigör sina lås när den inte längre har ett beroende av de låsta resurserna.

  • Radversioner

    När en radversionsbaserad isoleringsnivå används behåller databasmotorn versioner av varje rad som ändras. Applikationer kan ange att en transaktion använder radversionerna för att visa data som de var i början av transaktionen eller instruktionen, i stället för att skydda alla läsningar med lås. Genom att använda radversionshantering minskar risken för att en läsåtgärd blockerar andra transaktioner avsevärt.

Låsning och radversioner hindrar användare från att läsa oanvända data och förhindrar att flera användare försöker ändra samma data samtidigt. Utan låsning eller radversioner kan frågor som körs mot dessa data ge oväntade resultat genom att returnera data som ännu inte har checkats in i databasen.

Program kan välja transaktionsisoleringsnivåer, som definierar skyddsnivån för transaktionen från ändringar som görs av andra transaktioner. Tips på tabellnivå kan anges för enskilda Transact-SQL-instruktioner för att ytterligare skräddarsy beteendet så att det passar programmets krav.

Hantera samtidig dataåtkomst

Användare som har åtkomst till en resurs samtidigt sägs ha åtkomst till resursen samtidigt. Samtidig dataåtkomst kräver mekanismer för att förhindra negativa effekter när flera användare försöker ändra resurser som andra användare aktivt använder.

Samtidighetseffekter

Användare som ändrar data kan påverka andra användare som läser eller ändrar samma data samtidigt. Dessa användare sägs ha åtkomst till data samtidigt. Om en databas inte har någon samtidighetskontroll kan användarna se följande biverkningar:

  • Förlorade uppdateringar

    Förlorade uppdateringar inträffar när två eller flera transaktioner väljer samma rad och sedan uppdaterar raden baserat på det värde som ursprungligen valdes. Varje transaktion känner inte till de andra transaktionerna. Den senaste uppdateringen skriver över uppdateringar som gjorts av de andra transaktionerna, vilket resulterar i förlorade data.

    Två redigerare gör till exempel en elektronisk kopia av samma dokument. Varje redigerare ändrar kopian oberoende av varandra och sparar sedan den ändrade kopian och skriver över det ursprungliga dokumentet. Redigeraren som sparar den ändrade kopian skriver över de ändringar som gjorts av den andra redigeraren. Det här problemet skulle kunna undvikas om en redigerare inte kunde komma åt filen förrän den andra redigeraren hade slutfört och checkat in transaktionen.

  • Ocommitterade beroende (smutsig läsning)

    Ett icke-kommitterat beroende inträffar när den andra transaktionen läser en rad som uppdateras av en annan transaktion. Den andra transaktionen läser data som ännu inte har bekräftats och som kan ändras av transaktionen som uppdaterar raden.

    En redigerare gör till exempel ändringar i ett elektroniskt dokument. Under ändringarna tar en andra redigerare en kopia av dokumentet som innehåller alla ändringar som gjorts hittills och distribuerar dokumentet till den avsedda målgruppen. Den första redigeraren bestämmer sedan att de ändringar som gjorts hittills är felaktiga och tar bort redigeringarna och sparar dokumentet. Det distribuerade dokumentet innehåller redigeringar som inte längre finns och bör behandlas som om de aldrig fanns. Det här problemet kan undvikas om ingen kunde läsa det ändrade dokumentet förrän den första redigeraren sparar ändringarna och genomför transaktionen.

  • inkonsekvent analys (icke-upprepbar läsning)

    Inkonsekvent analys inträffar när en andra transaktion kommer åt samma rad flera gånger och läser olika data varje gång. Inkonsekvent analys liknar ett oregistrerat beroende eftersom en annan transaktion ändrar data som en andra transaktion läser. I en inkonsekvent analys var data som lästes av den andra transaktionen godkända av transaktionen som gjorde ändringen. Inkonsekvent analys omfattar dessutom flera läsningar (två eller fler) av samma rad, och varje gång ändras informationen av en annan transaktion; därav termen orepeterbar läsning.

    En redigerare läser till exempel samma dokument två gånger, men mellan varje läsning skriver författaren om dokumentet. När redigeraren läser dokumentet för andra gången har det ändrats. Den ursprungliga läsningen kunde inte upprepas. Det här problemet kan undvikas om skrivaren inte kunde ändra dokumentet förrän redigeraren har läst det för sista gången.

  • Phantom läser

    En fiktiv läsning är en situation som inträffar när två identiska sökningar körs och uppsättningen rader som returneras av den andra sökningen skiljer sig åt. I följande exempel visas hur detta kan inträffa. Anta att de två transaktionerna körs samtidigt. De två SELECT-uttrycken i den första transaktionen kan returnera olika resultat eftersom INSERT-instruktionen i den andra transaktionen ändrar de data som används av båda.

    --Transaction 1
BEGIN TRAN;

SELECT ID
FROM dbo.employee
WHERE ID > 5 AND ID < 10;

--The INSERT statement from the second transaction occurs here.

SELECT ID
FROM dbo.employee
WHERE ID > 5 and ID < 10;

COMMIT;

    --Transaction 2
BEGIN TRAN;
INSERT INTO dbo.employee (Id, Name)
VALUES(6 ,'New');

COMMIT;

  • Saknade och dubbla läsningar som orsakas av raduppdateringar

    • Saknar en uppdaterad rad eller ser en uppdaterad rad flera gånger

      Transaktioner som körs på READ UNCOMMITTED-nivå (eller som använder NOLOCK-tabelltips) utfärdar inte delade lås för att förhindra att andra transaktioner ändrar data som läses av den aktuella transaktionen. Transaktioner som körs på READ COMMITTED nivå utfärdar delade lås, men rad- eller sidlåsen släpps när raden har lästs. Om en annan användare ändrar indexnyckelkolumnen på raden under din läsning, kan raden visas igen ifall nyckeländringen flyttar raden till en position som kommer före din genomsökning. På samma sätt kanske raden inte läsas alls om ändring av nyckeln flyttade raden till en position i indexet som du redan har läst. Undvik detta genom att använda SERIALIZABLE eller HOLDLOCK tips eller radversioner. Mer information finns i Tabelltips (Transact-SQL).

    • En eller flera rader som inte var målet för uppdateringen saknas

      När du använder READ UNCOMMITTED, om din fråga läser rader med hjälp av en allokeringsordningsgenomsökning (med hjälp av IAM-sidor), kan du missa rader om en annan transaktion orsakar en siddelning. Detta inträffar inte när du använder READ COMMITTED isoleringsnivå.

Typer av samtidighet

När flera transaktioner försöker ändra data i en databas samtidigt måste ett kontrollsystem implementeras så att ändringar som görs av en transaktion inte påverkar en annan transaktion negativt. Detta kallas samtidighetskontroll.

Samtidighetskontrollteorin har två klassificeringar för metoderna för att införa samtidighetskontroll:

  • Pessimistisk samtidighetskontroll

    Ett låssystem förhindrar att transaktioner ändrar data på ett sätt som påverkar andra transaktioner. När en transaktion har genomfört en åtgärd som gör att ett lås tillämpas kan andra transaktioner inte utföra åtgärder som skulle stå i konflikt med låset förrän ägaren släpper det. Detta kallas pessimistisk kontroll eftersom det vanligtvis används i system där det finns hög konkurrens om data, där kostnaden för att skydda data med lås är mindre än kostnaden för att återställa transaktioner om samtidighetskonflikter uppstår.

  • Optimistisk samtidighetskontroll

    I optimistisk samtidighetskontroll låser transaktioner inte data när de läser dem. Men när en transaktion uppdaterar data kontrollerar systemet om en annan transaktion har ändrat data efter att den har lästs. Om en annan transaktion uppdaterade data utlöses ett fel. Vanligtvis återställs transaktionen som tar emot felet och börjar om. Detta kallas optimistiskt eftersom det vanligtvis används i system där det finns låg konkurrens om data och där kostnaden för att ibland återställa en transaktion är lägre än kostnaden för att låsa data när de läses.

Databasmotorn stöder båda metoderna för samtidighetskontroll. Användare anger typen av samtidighetskontroll genom att välja transaktionsisoleringsnivåer för anslutningar eller samtidighetsalternativ på markörer. Dessa attribut kan definieras med hjälp av Transact-SQL-instruktioner, eller genom egenskaperna och attributen för programmeringsgränssnitt för databasprogram (API:er) som ADO, ADO.NET, OLE DB och ODBC.

Isoleringsnivåer i databasmotorn

Transaktioner anger en isoleringsnivå som definierar i vilken grad en transaktion måste isoleras från resursen eller dataändringar som görs av andra transaktioner. Isoleringsnivåer beskrivs i termer av vilka samtidiga bieffekter, till exempel smutsiga läsningar eller fantomläsningar, som tillåts.

Kontroll av transaktionsisoleringsnivåer:

  • Om lås hämtas när data läss och vilken typ av lås som begärs.
  • Hur länge läslåsen hålls.
  • Om en läsåtgärd refererar till rader som ändrats av en annan transaktion:
    • Blockerar tills det exklusiva låset på raden frigörs.
    • Hämtar den bekräftade versionen av raden vid den tidpunkt då uttalandet eller transaktionen påbörjades.
    • Läser den ogenomförda dataändringen.

Viktig

Att välja en transaktionsisoleringsnivå påverkar inte de lås som hämtas för att skydda dataändringar. En transaktion har alltid ett exklusivt lås för att utföra dataändringar och håller låset tills transaktionen har slutförts, oavsett vilken isoleringsnivå som angetts för transaktionen. För läsåtgärder definierar transaktionsisoleringsnivåer främst skyddsnivån från effekterna av ändringar som görs av andra transaktioner.

En lägre isoleringsnivå ökar möjligheten för många transaktioner att komma åt data samtidigt, men ökar också antalet samtidighetseffekter (till exempel felaktiga läsningar eller förlorade uppdateringar) som kan uppstå. Omvänt minskar en högre isoleringsnivå de typer av samtidighetseffekter som transaktioner kan stöta på, men kräver fler systemresurser och ökar risken för att en transaktion blockerar en annan. Om du väljer lämplig isoleringsnivå beror det på att du balanserar programmets dataintegritetskrav mot omkostnaderna för varje isoleringsnivå. Den högsta isoleringsnivån, SERIALIZABLE, garanterar att en transaktion hämtar exakt samma data varje gång den upprepar en läsåtgärd, men den gör detta genom att utföra en låsningsnivå som sannolikt kommer att påverka andra transaktioner i system med flera användare. Den lägsta isoleringsnivån, READ UNCOMMITTED, kan hämta data som har ändrats men inte utförts av andra transaktioner. Alla samtidighetseffekter kan inträffa i READ UNCOMMITTED, men det finns ingen läslåsning eller versionshantering, så omkostnaderna minimeras.

Isoleringsnivåer för databasmotor

ISO-standarden definierar följande isoleringsnivåer, som alla stöds av databasmotorn:

Isoleringsnivå Definition
READ UNCOMMITTED Den lägsta isoleringsnivån där transaktioner är tillräckligt isolerade för att säkerställa att fysiskt inkonsekventa data inte läses. På den här nivån tillåts felaktiga läsningar, så en transaktion kan se ännu inte genomförda ändringar som gjorts av andra transaktioner.
READ COMMITTED Tillåter att en transaktion läser data som tidigare lästs (inte ändrats) av en annan transaktion utan att vänta på att den första transaktionen ska slutföras. Databasmotorn behåller skrivlås (hämtas på valda data) till slutet av transaktionen, men läslås släpps så snart läsåtgärden utförs. Det här är standardnivån för databasmotorn.
REPEATABLE READ Databasmotorn behåller läs- och skrivlås som hämtas på valda data till slutet av transaktionen. Men eftersom intervalllås inte hanteras kan fiktiva läsningar inträffa.
SERIALIZABLE Den högsta nivån där transaktioner är helt isolerade från varandra. Databasmotorn behåller läs- och skrivlås som hämtats på valda data till slutet av transaktionen. Intervalllås hämtas när en SELECT-åtgärd använder en RANGE WHERE-sats för att undvika fiktiva läsningar.

Obs! DDL-åtgärder och transaktioner på replikerade tabeller kan misslyckas när SERIALIZABLE isoleringsnivå begärs. Det beror på att replikeringsfrågor använder tips som kan vara inkompatibla med SERIALIZABLE isoleringsnivå.

Databasmotorn stöder även två ytterligare transaktionsisoleringsnivåer som använder radversioner. Den ena är en implementering av READ COMMITTED isoleringsnivå och en är SNAPSHOT transaktionsisoleringsnivå.

Isoleringsnivå för radversion Definition
Read Committed Snapshot (RCSI) När alternativet READ_COMMITTED_SNAPSHOT i databasen anges ON, vilket är standardinställningen i Azure SQL Database, använder READ COMMITTED isoleringsnivå radversioner för att ge läskonsistens på satsnivå. Läsåtgärder kräver endast schemastabilitet (Sch-S) lås på tabellnivå och inga sid- eller radlås. Databasmotorn använder alltså radversioner för att presentera varje -instruktion med en transaktionsmässigt konsekvent ögonblicksbild av data som den fanns i början av -instruktionen. Lås används inte för att skydda data från uppdateringar av andra transaktioner. En användardefinierad funktion kan returnera data vars ändringar har godkänts efter att instruktionen som innehåller den användardefinierade funktionen (UDF) började.

När databasalternativet READ_COMMITTED_SNAPSHOT är inställt på OFF, vilket är standardinställningen i SQL Server och Azure SQL Managed Instance, använder isoleringsnivån READ COMMITTED delade lås för att förhindra att andra transaktioner ändrar rader medan den aktuella transaktionen utför en läsåtgärd. De delade låsen blockerar också instruktionen från att läsa rader som ändrats av andra transaktioner tills den andra transaktionen har slutförts. Båda implementeringarna uppfyller ISO-definitionen av READ COMMITTED isolering.
SNAPSHOT Isoleringsnivån för ögonblicksbilder använder radversioner för att ge läskonsekvens på transaktionsnivå. Läsåtgärder hämtar inga sid- eller radlås. Endast lås för schemastabilitet (Sch-S) på tabellnivå hämtas. När du läser rader som ändrats av en annan transaktion hämtar läsåtgärder den version av raden som fanns när transaktionen startade. Du kan bara använda SNAPSHOT isolering när ALLOW_SNAPSHOT_ISOLATION-databasalternativet är inställt på ON. Som standard är det här alternativet inställt på OFF för användardatabaser i SQL Server och Azure SQL Managed Instance och inställt på ON för databaser i Azure SQL Database.

Obs! Databasmotorn stöder inte versionshantering av metadata. Därför finns det begränsningar för vilka DDL-åtgärder som kan utföras i en explicit transaktion som körs under ögonblicksbildisolering. Följande DDL-instruktioner är inte tillåtna under ögonblicksbildisolering efter en BEGIN TRANSACTION-instruktion: ALTER TABLE, CREATE INDEX, CREATE XML INDEX, ALTER INDEX, DROP INDEX, DBCC REINDEX, ALTER PARTITION FUNCTION, ALTER PARTITION SCHEMEeller någon CLR-DDL-instruktion (Common Language Runtime). Dessa uttalanden är tillåtna när du använder snapshot-isolering inom implicita transaktioner. En implicit transaktion är per definition en enda instruktion som gör det möjligt att framtvinga semantiken för ögonblicksbildisolering, även med DDL-instruktioner. Överträdelser av den här principen kan orsaka fel 3961: Snapshot isolation transaction failed in database '%.*ls' because the object accessed by the statement has been modified by a DDL statement in another concurrent transaction since the start of this transaction. It is not allowed because the metadata is not versioned. A concurrent update to metadata could lead to inconsistency if mixed with snapshot isolation.

I följande tabell visas samtidighetseffekterna som aktiveras av de olika isoleringsnivåerna.

Isoleringsnivå Smutsig läsning Icke-repeterbar läsning Fantom
READ UNCOMMITTED Ja Ja Ja
READ COMMITTED Nej Ja Ja
REPEATABLE READ Nej Nej Ja
SNAPSHOT Nej Nej Nej
SERIALIZABLE Nej Nej Nej

Mer information om de specifika typerna av låsning eller radversioner som styrs av varje transaktionsisoleringsnivå finns i SET TRANSACTION ISOLATION LEVEL (Transact-SQL).

Transaktionsisoleringsnivåer kan anges med hjälp av Transact-SQL eller via ett databas-API.

Transact-SQL
Transact-SQL skript använder instruktionen SET TRANSACTION ISOLATION LEVEL.

ADO
ADO-program anger egenskapen IsolationLevel för Connection-objektet till adXactReadUncommitted, adXactReadCommitted, adXactRepeatableReadeller adXactReadSerializable.

ADO.NET
ADO.NET program som använder det System.Data.SqlClient hanterade namnområdet kan anropa metoden SqlConnection.BeginTransaction och ange alternativet IsolationLevel till Unspecified, Chaos, ReadUncommitted, ReadCommitted, RepeatableRead, Serializableeller Snapshot.

OLE DB-
När du startar en transaktion anropar program som använder OLE DB ITransactionLocal::StartTransaction med isoLevel inställt på ISOLATIONLEVEL_READUNCOMMITTED, ISOLATIONLEVEL_READCOMMITTED, ISOLATIONLEVEL_REPEATABLEREAD, ISOLATIONLEVEL_SNAPSHOTeller ISOLATIONLEVEL_SERIALIZABLE.

När du anger transaktionsisoleringsnivån i autocommit-läge kan OLE DB-applikationer ange egenskapen DBPROPSET_SESSIONDBPROP_SESS_AUTOCOMMITISOLEVELS till DBPROPVAL_TI_CHAOS, DBPROPVAL_TI_READUNCOMMITTED, DBPROPVAL_TI_BROWSE, DBPROPVAL_TI_CURSORSTABILITY, DBPROPVAL_TI_READCOMMITTED, DBPROPVAL_TI_REPEATABLEREAD, DBPROPVAL_TI_SERIALIZABLE, DBPROPVAL_TI_ISOLATEDeller DBPROPVAL_TI_SNAPSHOT.

ODBC
ODBC-program anropar SQLSetConnectAttr med Attribute inställt på SQL_ATTR_TXN_ISOLATION och ValuePtr inställt på SQL_TXN_READ_UNCOMMITTED, SQL_TXN_READ_COMMITTED, SQL_TXN_REPEATABLE_READeller SQL_TXN_SERIALIZABLE.

För transaktioner med ögonblicksbilder anropar program SQLSetConnectAttr med attributet inställt på SQL_COPT_SS_TXN_ISOLATION och ValuePtr inställt på SQL_TXN_SS_SNAPSHOT. En ögonblicksbildtransaktion kan hämtas med antingen SQL_COPT_SS_TXN_ISOLATION eller SQL_ATTR_TXN_ISOLATION.

Låsning i databasmotorn

Låsning är en mekanism som används av databasmotorn för att synkronisera åtkomst av flera användare till samma datamängd samtidigt.

Innan en transaktion får ett beroende av det aktuella tillståndet för en databit, till exempel genom att läsa eller ändra data, måste den skydda sig mot effekterna av att en annan transaktion ändrar samma data. Transaktionen gör detta genom att begära ett lås på databiten. Lås har olika lägen, till exempel delade (S) eller exklusiva (X). Låsläget definierar den beroendenivå som transaktionen har på data. Ingen transaktion kan beviljas ett lås som skulle stå i konflikt med läget för ett lås som redan har beviljats för dessa data till en annan transaktion. Om en transaktion begär ett låsläge som står i konflikt med ett lås som redan har beviljats på samma data pausar databasmotorn den begärande transaktionen tills det första låset släpps.

När en transaktion ändrar en del data innehåller den vissa lås som skyddar ändringen till slutet av transaktionen. Hur länge en transaktion behåller de lås som tagits för att skydda läsåtgärder beror på inställningen för transaktionsisoleringsnivå och huruvida optimerad låsning är aktiverad eller inte.

  • När optimerad låsning inte är aktiverad hålls rad- och sidlås som krävs för skrivningar till slutet av transaktionen.

  • När optimerad låsning är aktiverad hålls endast ett TID-lås (Transaction ID) kvar till slutet av transaktionen. Under standardnivån READ COMMITTED isolering kommer transaktioner inte att innehålla rad- och sidlås som krävs för skrivningar förrän transaktionen har slutförts. Detta minskar låsminnet som krävs och minskar behovet av låseskalering. När optimerad låsning är aktiverad utvärderar lås efter kvalificering (LAQ) optimering predikat för en fråga på den senaste bekräftade versionen av raden utan att hämta ett lås, vilket förbättrar samtidigheten.

Alla lås som innehas av en transaktion släpps när transaktionen slutförs (antingen bekräftas eller återkallas).

Program begär vanligtvis inte lås direkt. Lås hanteras internt av en del av databasmotorn som kallas låshanteraren. När en instans av databasmotorn bearbetar en Transact-SQL-instruktion avgör databasmotorns frågeprocessor vilka resurser som ska nås. Frågeprocessorn avgör vilka typer av lås som krävs för att skydda varje resurs baserat på typen av åtkomst och inställningen för transaktionsisoleringsnivå. Frågeprocessorn begär sedan lämpliga lås från låshanteraren. Låshanteraren beviljar låsen om det inte finns några motstridiga lås som innehas av andra transaktioner.

Låsa granularitet och hierarkier

Databasmotorn har flersidig låsning som gör att olika typer av resurser kan låsas av en transaktion. För att minimera kostnaden för låsning låser databasmotorn resurser automatiskt på en nivå som är lämplig för uppgiften. Att låsa på en mindre granularitetsnivå, såsom rader, ökar samtidigheten men har en högre överbelastning eftersom fler lås måste behållas om många rader är låsta. Det är kostsamt att låsa på en högre detaljnivå, till exempel hela tabeller, när det gäller samtidighet eftersom låsning av en hel tabell begränsar åtkomsten till någon del av tabellen för andra transaktioner. Den har dock lägre omkostnader eftersom färre lås underhålls.

Databasmotorn måste ofta hämta lås på flera detaljnivåer för att helt skydda en resurs. Den här gruppen med lås på flera nivåer av kornighet kallas för en låshierarki. För att till exempel helt skydda en läsning av ett index kan en instans av Databasmotorn behöva hämta delade lås på rader och avsiktsdelade lås på sidorna och tabellen.

I följande tabell visas de resurser som databasmotorn kan låsa.

Resurs Beskrivning
RID En radidentifierare som används för att låsa en enskild rad inom en hög.
KEY Ett radlås för att låsa en enskild rad i ett B-trädindex.
PAGE En sida på 8 kilobyte (KB) i en databas, till exempel data- eller indexsidor.
EXTENT En sammanhängande grupp på åtta sidor, till exempel data- eller indexsidor.
HoBT 1 En hög eller ett B-träd. Ett lås som skyddar ett B-träd (index) eller heap-datasidorna i en tabell som inte har ett klusterindex.
TABLE 1 Hela tabellen, inklusive alla data och index.
FILE En databasfil.
APPLICATION En programspecifik resurs.
METADATA Metadata lås.
ALLOCATION_UNIT En allokeringsenhet.
DATABASE Hela databasen.
XACT 2 I Optimerad låsninganvänds Transaktions-ID-lås (TID). För mer information, se Låsning av Transaktions-ID (TID).

1HoBT och TABLE lås kan påverkas av alternativet LOCK_ESCALATIONALTER TABLE.

2 Ytterligare låsningsresurser är tillgängliga för XACT låsresurser, se Diagnostiska tillägg för optimerad låsning.

Låslägen

Databasmotorn låser resurser med olika låslägen som avgör hur resurserna kan nås av samtidiga transaktioner.

I följande tabell visas de resurslåslägen som databasmotorn använder.

Låsläge Beskrivning
Delat (S) Används för läsåtgärder som inte ändrar eller uppdaterar data, till exempel en SELECT-instruktion.
Uppdatering (U) Används på resurser som är uppdaterbara. Förhindrar en vanlig form av dödläge som inträffar när flera sessioner läser, låser och eventuellt uppdaterar resurser senare.
Exclusive (X) Används för datamodifieringsåtgärder, till exempel INSERT, UPDATEeller DELETE. Säkerställer att flera uppdateringar inte kan göras till samma resurs samtidigt.
avsikt Används för att upprätta en låshierarki. Typerna av avsiktslås är: intent shared (IS), intent exclusive (IX) och shared with intent exclusive (SIX).
Schema Används när en åtgärd som är beroende av schemat för en tabell körs. Typerna av schemalås är: schemaändring (Sch-M) och schemastabilitet (Sch-S).
massuppdatering (BU) Används vid masskopiering av data till en tabell med TABLOCK-anvisning.
Tangentbordområde Skyddar radintervallet som läses av en fråga när du använder transaktionsisoleringsnivån SERIALIZABLE. Säkerställer att andra transaktioner inte kan infoga rader som skulle uppfylla villkoren för frågorna i transaktion SERIALIZABLE om frågorna kördes igen.

Delade lås

Delade (S) lås tillåter samtidiga transaktioner att läsa en resurs under pessimistisk samtidighetskontroll. Inga andra transaktioner kan ändra data när delade (S) lås finns på resursen. Delade (S) lås på en resurs släpps så snart läsåtgärden har slutförts, såvida inte transaktionsisoleringsnivån är inställd på REPEATABLE READ eller högre, eller om ett låstips används för att behålla de delade låsen (S) under transaktionens varaktighet.

Uppdatera lås

Databasmotorn placerar uppdateringslås (U) som en del av förberedelserna för att exekvera en uppdatering. U lås är kompatibla med S lås, men endast en transaktion kan hålla ett U lås åt gången på en viss resurs. Det här är nyckeln – många samtidiga transaktioner kan innehålla S lås, men endast en transaktion kan innehålla ett U lås på en resurs. Uppdateringslås (U) uppgraderas slutligen till exklusiva lås (X) för att uppdatera en rad.

Uppdateringslås (U) kan också tas av andra satser än UPDATE, när tabelltipset UPDLOCK anges i satsen.

  • Vissa program använder mönstret "välj en rad och uppdaterar sedan raden", där läsning och skrivning uttryckligen avgränsas i transaktionen. Om isoleringsnivån i det här fallet är REPEATABLE READ eller SERIALIZABLEkan samtidiga uppdateringar orsaka ett dödläge enligt följande:

    En transaktion läser data, hämtar ett delat (S) lås på resursen och ändrar sedan data, vilket kräver låskonvertering till ett exklusivt (X) lås. Om två transaktioner skaffar delade (S) lås på en resurs och sedan försöker uppdatera data samtidigt, försöker en av transaktionerna utföra lås-konverteringen till ett exklusivt lås (X). Konverteringen av delat till exklusivt lås måste vänta eftersom det exklusiva låset (X) för en transaktion inte är kompatibelt med det delade låset (S) för den andra transaktionen. en låsvänte inträffar. Den andra transaktionen försöker hämta ett exklusivt (X) lås för uppdateringen. Eftersom båda transaktionerna konverteras till exklusiva lås (X) och de väntar på att den andra transaktionen ska frigöra sitt delade lås (S) uppstår ett dödläge.

    I standardnivån READ COMMITTED i isoleringsnivån är S-låsen kortsiktiga och frigörs så snart de används. Även om det dödläge som beskrivs ovan fortfarande är möjligt, är det mycket mindre troligt med kortvariga lås.

    För att undvika den här typen av dödläge kan program följa mönstret "välj en rad med UPDLOCK tips och sedan uppdatera raden".

  • Om UPDLOCK hint används vid en skrivoperation när SNAPSHOT isolering används måste transaktionen få åtkomst till den senaste versionen av raden. Om den senaste versionen inte längre visas kan du ta emot Msg 3960, Level 16, State 2 Snapshot isolation transaction aborted due to update conflict. Ett exempel finns i Arbeta med ögonblicksbildisolering.

Exklusiva lås

Exklusiva lås (X) förhindrar åtkomst till en resurs genom samtidiga transaktioner. Med ett exklusivt lås (X) kan inga andra transaktioner ändra de data som skyddas av låset. läsåtgärder kan endast utföras med hjälp av NOLOCK tips eller READ UNCOMMITTED isoleringsnivå.

Datamodifieringsinstruktioner, till exempel INSERT, UPDATEoch DELETE kombinera både läs- och ändringsåtgärder. Instruktionen utför först läsåtgärder för att hämta data innan de nödvändiga ändringsåtgärderna utförs. Datamodifieringsinstruktioner begär därför vanligtvis både delade lås och exklusiva lås. En UPDATE-instruktion kan till exempel ändra rader i en tabell baserat på en koppling till en annan tabell. I det här fallet begär UPDATE-instruktionen delade lås på raderna som läser i sammanslagningstabellen utöver att begära exklusiva lås på de uppdaterade raderna.

Låsmekanismer för avsikt

Databasmotorn använder avsiktslås för att skydda att placera ett delat (S) lås eller exklusivt (X) lås på en resurs längre ned i låshierarkin. Avsiktslås kallas "avsiktslås" eftersom de förvärvas före ett lås på den lägre nivån och därför signalerar en avsikt att placera lås på den lägre nivån.

Avsiktslås har två syften:

  • För att förhindra att andra transaktioner ändrar resursen på högre nivå på ett sätt som skulle ogiltigförklara låset på den lägre nivån.
  • För att förbättra effektiviteten i databasmotorn vid identifiering av låskonflikter på den högre detaljnivån.

Till exempel begärs ett lås för delad avsikt på tabellnivå innan delade (S) lås begärs på sidor eller rader i tabellen. Om du anger ett avsiktslås på tabellnivå förhindrar du att en annan transaktion senare hämtar ett exklusivt lås (X) på tabellen som innehåller den sidan. Avsiktslås förbättrar prestanda eftersom databasmotorn endast undersöker avsiktslås på tabellnivå för att avgöra om en transaktion på ett säkert sätt kan hämta ett lås i tabellen. Detta tar bort kravet på att undersöka varje rad- eller sidlås i tabellen för att avgöra om en transaktion kan låsa hela tabellen.

Avsiktslås inkluderar avsikt delat (IS), avsikt exklusivt (IX) och delat med avsikt exklusivt (SIX).

Låsläge Beskrivning
avsikt delad (IS) Skyddar begärda eller förvärvade delade lås på vissa (men inte alla) resurser lägre i hierarkin.
Avsikt exklusiv (IX) Skyddar begärda eller förvärvade exklusiva lås på vissa (men inte alla) resurser lägre i hierarkin. IX är en superuppsättning av ISoch skyddar även resurser på lägre nivå genom att begära delade lås.
Delat med avsikt – exklusivt (SIX) Skyddar begärda eller förvärvade delade lås på alla resurser som är lägre i hierarkin och har exklusiv avsikt att låsa vissa (men inte alla) resurser på lägre nivå. Samtidiga IS-lås i den översta resursen tillåts. Om du till exempel hämtar ett SIX lås på en tabell hämtas även exklusiva avsiktslås på de sidor som ändras och exklusiva lås på de ändrade raderna. Det kan bara finnas ett SIX-lås per resurs åt gången, vilket förhindrar uppdateringar av resursen som görs av andra transaktioner. Andra transaktioner kan dock läsa resurser på lägre nivåer i hierarkin genom att skaffa IS-lås på tabellnivå.
Avsiktsuppdatering (IU) Skyddar begärda eller förvärvade uppdateringslås på alla resurser som finns lägre i hierarkin. IU-lås används endast på sidans resurser. IU lås konverteras till IX lås om en uppdateringsåtgärd utförs.
Uppdatering av delad avsikt (SIU) En kombination av S och IU lås, som ett resultat av att förvärva dessa lås separat och samtidigt hålla båda låsen. En transaktion kör till exempel en fråga med tipset PAGLOCK och kör sedan en uppdateringsåtgärd. Frågan med tipset PAGLOCK hämtar S låset och uppdateringsåtgärden hämtar IU låset.
Uppdatera avsikt exklusivt (UIX) En kombination av U och IX lås, som ett resultat av att förvärva dessa lås separat och samtidigt hålla båda låsen.

Schemalås

Databasmotorn använder schemaändring (Sch-M) lås under en DDL-åtgärd (Table Data Definition Language), till exempel att lägga till en kolumn eller släppa en tabell. Under tiden som det hålls förhindrar Sch-M lås samtidig åtkomst till tabellen. Det innebär att Sch-M lås blockerar alla externa åtgärder tills låset släpps.

Vissa åtgärder för datamanipuleringsspråk (DML), till exempel t.ex. tabelltrunkering, använder Sch-M lås för att förhindra åtkomst till berörda tabeller genom samtidiga åtgärder.

Databasmotorn använder schemastabilitet (Sch-S) lås vid kompilering och körning av frågor. Sch-S lås blockerar inte några transaktionslås, inklusive exklusiva (X) lås. Därför fortsätter andra transaktioner, inklusive de med X lås på en tabell, att köras medan en fråga kompileras. Parallella DDL-åtgärder och parallella DML-åtgärder som förvärvar Sch-M-lås blockeras dock av Sch-S-lås.

Massuppdateringslås

Massuppdateringslås (BU) gör att flera trådar kan massinläsa data samtidigt i samma tabell, samtidigt som andra processer som inte massinläser data inte kommer åt tabellen. Databasmotorn använder massuppdateringslås (BU) när båda följande villkor är uppfyllda.

  • Du använder Transact-SQL BULK INSERT-instruktionen eller funktionen OPENROWSET(BULK), eller använder du något av massinfognings-API:erna, till exempel .NET SqlBulkCopy, OLEDB Snabbinläsnings-API:er eller ODBC Masskopierings-API:er för att masskopiera data till en tabell.
  • Hinten TABLOCK anges eller alternativet table lock on bulk load för tabellen ställs in med hjälp av sp_tableoption.

Tips

Till skillnad från BULK INSERT-instruktionen, som innehåller ett mindre restriktivt massuppdateringslås (BU), har INSERT INTO...SELECT med TABLOCK hint ett exklusivt avsiktslås (IX) på tabellen. Det innebär att du inte kan infoga rader med hjälp av parallella infogningsåtgärder.

Nyckelomfångslås

Nyckelintervallås skyddar ett intervall med rader som implicit ingår i en postuppsättning som läses av en Transact-SQL-instruktion med SERIALIZABLE-transaktionsisoleringsnivå. Nyckelomfångslåsning förhindrar fantomläsningar. Genom att skydda nyckelintervall mellan rader förhindras även skeninfogningar eller -raderingar i en datamängd som nås av en transaktion.

Låskompatibilitet

Låskompatibilitet styr om flera transaktioner kan hämta lås på samma resurs samtidigt. Om en resurs redan är låst av en annan transaktion kan en ny låsbegäran endast beviljas om läget för det begärda låset är kompatibelt med läget för det befintliga låset. Om läget för det begärda låset inte är kompatibelt med det befintliga låset väntar transaktionen som begär det nya låset på att det befintliga låset släpps eller att tidsgränsintervallet för låset upphör att gälla. Till exempel är inga låslägen kompatibla med exklusiva lås. Även om ett exklusivt (X) lås hålls, kan ingen annan transaktion hämta ett lås av något slag (delat, uppdatera eller exklusivt) på resursen tills det exklusiva låset (X) släpps. Om ett delat lås (S) har tillämpats på en resurs kan andra transaktioner också hämta ett delat lås eller ett uppdateringslås (U) på resursen även om den första transaktionen inte har slutförts. Andra transaktioner kan dock inte skaffa ett exklusivt lås förrän det delade låset har släppts.

Följande tabell visar kompatibiliteten för de vanligaste låslägena.

Befintligt beviljat läge IS S U IX SIX X
Begärt läge
avsikt delad (IS) Ja Ja Ja Ja Ja Nej
Delat (S) Ja Ja Ja Nej Nej Nej
Uppdatering (U) Ja Ja Nej Nej Nej Nej
Avsikt exklusiv (IX) Ja Nej Nej Ja Nej Nej
Delat med avsikt – exklusivt (SIX) Ja Nej Nej Nej Nej Nej
Exclusive (X) Nej Nej Nej Nej Nej Nej

Obs

Ett exklusivt avsiktslås (IX) är kompatibelt med ett IX låsläge eftersom IX innebär att avsikten är att endast uppdatera några av raderna i stället för alla. Andra transaktioner som försöker läsa eller uppdatera vissa rader tillåts också så länge de inte är samma rader som uppdateras av andra transaktioner. Om två transaktioner försöker uppdatera samma rad beviljas dessutom båda transaktionerna ett IX lås på tabell- och sidnivå. En transaktion beviljas dock ett X-lås på radnivå. Den andra transaktionen måste vänta tills låset på radnivå har tagits bort.

Använd följande tabell för att fastställa kompatibiliteten för alla låslägen som är tillgängliga i databasmotorn.

diagram som visar en matris med låskonflikter och kompatibilitet.

Nyckel Beskrivning
N Ingen konflikt
Jag Olaglig
C Konflikt
NL Inget lås
SCH-S Schemastabilitetslås
SCH-M Schemaändringslås
S Delad
U Uppdatera
X Exklusiv
VARA Avsikt delad
IU Avsiktsuppdatering
IX Uteslutande avsikt
SIU Dela med avsiktsuppdatering
SEX Dela med exklusiv avsikt
UIX Uppdatera med exklusiv avsikt
BU Massuppdatering
RS-S Delat intervall-gemensamt
RS-U Uppdatering av delat intervall
RI-N Infoga intervall-null
RI-S Infoga intervall-samdelad
RI-U Infoga områdeuppdatering
RI-X Infoga intervallexkluderande
RX-S Exklusivt delat spektrum
RX-U Exklusiv intervalluppdatering
RX-X Exklusivt intervallexkluderande

Nyckelintervallåsning

Nyckelintervallås skyddar ett intervall med rader som implicit ingår i en postuppsättning som läses av en Transact-SQL-instruktion med SERIALIZABLE-transaktionsisoleringsnivå. Den SERIALIZABLE isoleringsnivån kräver att alla frågor som körs under en transaktion måste hämta samma uppsättning rader varje gång den körs under transaktionen. Ett nyckelintervalllås uppfyller det här kravet genom att förhindra att andra transaktioner infogar nya rader vars nycklar skulle falla i intervallet för nycklar som läses av den SERIALIZABLE transaktionen.

Nyckelomfångslåsning förhindrar fantomläsningar. Genom att skydda intervallen med nycklar mellan rader förhindrar det också spökinfogningar i en uppsättning poster som används av en transaktion.

Ett nyckelintervalllås placeras på ett index och anger ett värde för början och slutnyckeln. Det här låset blockerar alla försök att infoga, uppdatera eller ta bort en rad med ett nyckelvärde som faller inom intervallet eftersom dessa åtgärder först måste hämta ett lås på indexet. Till exempel kan en SERIALIZABLE transaktion utfärda en SELECT-instruktion som läser alla rader vars nyckelvärden matchar villkoret BETWEEN 'AAA' AND 'CZZ'. Ett nyckelintervalllås på nyckelvärdena i intervallet från AAA till CZZ förhindrar att andra transaktioner infogar rader med nyckelvärden var som helst i det intervallet. som ADG, BBDeller CAL.

Låslägen för nyckelintervall

Nyckelintervalllås omfattar både ett intervall och en radkomponent som anges i intervallradsformat:

  • Intervallet representerar låsläget som skyddar intervallet mellan två på varandra följande indexposter.
  • Rad representerar låsläget som skyddar indexposten.
  • Läget representerar det kombinerade låsläge som används. Låslägen för nyckelintervall består av två delar. Den första representerar den typ av lås som används för att låsa indexintervallet (intervallT) och den andra representerar låstypen som används för att låsa en specifik nyckel (K). De två delarna är anslutna med ett bindestreck (-), till exempel RangeT-K.
Omfång Rad Läge Beskrivning
RangeS S RangeS-S Delat intervall, delat resurslås; SERIALIZABLE områdessökning.
RangeS U RangeS-U Delat intervall, uppdatera resurslås; SERIALIZABLE uppdatera genomsökning.
RangeI Null RangeI-N Infoga intervall, null-resurslås; används för att testa intervall innan du infogar en ny nyckel i ett index.
RangeX X RangeX-X Exklusivt intervall, exklusivt resurslås; används vid uppdatering av en nyckel i ett intervall.

Obs

Det interna Null låsläget är kompatibelt med alla andra låslägen.

Låslägen för nyckelintervall har en kompatibilitetsmatris som visar vilka lås som är kompatibla med andra lås som erhålls på överlappande nycklar och intervall.

Befintligt beviljat läge S U X RangeS-S RangeS-U RangeI-N RangeX-X
Begärt läge
Delat (S) Ja Ja Nej Ja Ja Ja Nej
Uppdatering (U) Ja Nej Nej Ja Nej Ja Nej
Exclusive (X) Nej Nej Nej Nej Nej Ja Nej
RangeS-S Ja Ja Nej Ja Ja Nej Nej
RangeS-U Ja Nej Nej Ja Nej Nej Nej
RangeI-N Ja Ja Ja Nej Nej Ja Nej
RangeX-X Nej Nej Nej Nej Nej Nej Nej

Konverteringslås

Konverteringslås skapas när ett nyckelintervalllås överlappar ett annat lås.

Lås 1 Lås 2 Konverteringslås
S RangeI-N RangeI-S
U RangeI-N RangeI-U
X RangeI-N RangeI-X
RangeI-N RangeS-S RangeX-S
RangeI-N RangeS-U RangeX-U

Konverteringslås kan observeras under en kort tidsperiod under olika komplexa omständigheter, ibland när samtidiga processer körs.

Seriell intervallavläsning, ensamt hämta, ta bort och infoga

Nyckelomfångslåsning säkerställer att följande åtgärder är serialiserbara:

  • Sökfråga för intervallskanning
  • Singleton-hämtning av obefintlig rad
  • Ta bort åtgärd
  • Infoga åtgärd

Innan nyckelomfångslåsning kan inträffa måste följande villkor vara uppfyllda:

  • Transaktionsisoleringsnivån måste anges till SERIALIZABLE.
  • Frågeprocessorn måste använda ett index för att implementera intervallfilterpredikatet. Till exempel kan WHERE-satsen i en SELECT-instruktion upprätta ett intervallvillkor med det här predikatet: ColumnX BETWEEN N'AAA' AND N'CZZ'. Ett nyckelintervallås kan bara skaffas om ColumnX omfattas av en indexnyckel.

Exempel

Följande tabell och index används som grund för de nyckelintervallslåsningsexempel som följer.

Ett diagram över ett urval av ett Btree.

Sökfråga för intervallskanning

För att säkerställa att en intervallgenomsökningsfråga är serialiserbar bör samma fråga returnera samma resultat varje gång den körs inom samma transaktion. Nya rader får inte infogas i intervallgenomsökningsfrågan av andra transaktioner. annars blir dessa fantominfogningar. Följande fråga använder till exempel tabellen och indexet i föregående bild:

SELECT name
FROM mytable
WHERE name BETWEEN 'A' AND 'C';

Nyckelintervalllås placeras på de indexposter som motsvarar radintervallet där namnet ligger mellan värdena Adam och Dale, vilket förhindrar att nya rader som kvalificerar i föregående fråga läggs till eller tas bort. Även om förnamnet i det här intervallet är Adamser nyckelintervalllåset för RangeS-S läge på den här indexposten till att inga nya namn som börjar med bokstaven A kan läggas till innan Adam, till exempel Abigail. På samma sätt säkerställer RangeS-S nyckelintervalllås på indexposten för Dale att inga nya namn som börjar med bokstaven C kan läggas till efter Carlos, till exempel Clive.

Obs

Antalet RangeS-S lås som lagras är n+1, där n är antalet rader som uppfyller frågan.

Singleton-hämtning av icke-existerande data

Om en fråga inom en transaktion försöker välja en rad som inte finns, måste frågan utfärdas vid en senare tidpunkt inom samma transaktion för att returnera samma resultat. Ingen annan transaktion kan tillåtas infoga den obefintliga raden. Till exempel med den här frågan:

SELECT name
FROM mytable
WHERE name = 'Bill';

Ett nyckelintervalllås placeras på indexposten som motsvarar namnintervallet från Ben till Bing eftersom namnet Bill skulle infogas mellan dessa två intilliggande indexposter. Nyckelintervalllåset för RangeS-S läge placeras på indexposten Bing. Detta förhindrar att andra transaktioner infogar värden, till exempel Bill, mellan indexposterna Ben och Bing.

Ta bort åtgärd utan optimerad låsning

När du tar bort en rad i en transaktion behöver inte intervallet som raden hamnar i låsas under hela transaktionen som utför borttagningen. Att låsa det borttagna nyckelvärdet till slutet av transaktionen räcker för att upprätthålla serialisering. Till exempel givet detta DELETE-uttalande:

DELETE mytable
WHERE name = 'Bob';

Ett exklusivt lås (X) placeras på indexposten som motsvarar namnet Bob. Andra transaktioner kan infoga eller ta bort värden före eller efter raden med värdet Bob som tas bort. Emellertid blockeras alla transaktioner som försöker läsa, infoga eller ta bort rader som matchar värdet Bob tills den borttagande transaktionen antingen validerar eller återställs. (Databasalternativet READ_COMMITTED_SNAPSHOT och isoleringsnivå SNAPSHOT möjliggör även läsningar från en radversion av den tidigare bekräftade tillståndet.)

Intervallborttagning kan köras med tre grundläggande låslägen: rad-, sid- eller tabelllås. Låsningsstrategin för rad, sida eller tabell bestäms av Frågeoptimeraren eller kan anges av användaren via Frågeoptimertips som ROWLOCK, PAGLOCKeller TABLOCK. När PAGLOCK eller TABLOCK används frigör databasmotorn omedelbart en indexsida om alla rader tas bort från den här sidan. När ROWLOCK används markeras däremot alla borttagna rader endast som borttagna. de tas bort från indexsidan senare med hjälp av en bakgrundsaktivitet.

Ta bort åtgärd med optimerad låsning

När du tar bort en rad i en transaktion hämtas rad- och sidlåsen inkrementellt och släpps inte under transaktionens varaktighet. Till exempel, givet den här DELETE-satsen:

DELETE mytable
WHERE name = 'Bob';

Ett TID-lås placeras på alla ändrade rader under transaktionens varaktighet. Ett lås hämtas på TID för de indexrader som motsvarar värdet Bob. Med optimerad låsning fortsätter sid- och radlås att hämtas för uppdateringar, men varje sida och radlås släpps så fort varje rad uppdateras. TID-låset skyddar raderna från att uppdateras tills transaktionen har slutförts. En transaktion som försöker läsa, inför eller ta bort rader med värdet Bob blockeras tills borttagningstransaktionen antingen bekräftar eller rullar tillbaka. (Databasalternativet READ_COMMITTED_SNAPSHOT och isoleringsnivå SNAPSHOT möjliggör även läsningar från en radversion av den tidigare bekräftade tillståndet.)

Annars är låsningsmekaniken för en borttagningsåtgärd densamma som utan optimerad låsning.

Infoga åtgärd utan optimerad låsning

När du infogar en rad i en transaktion behöver inte intervallet som raden hamnar i låsas under hela transaktionen som utför infogningsåtgärden. Att låsa det infogade nyckelvärdet till slutet av transaktionen räcker för att upprätthålla serialisering. Till exempel med den här INSERT-instruktionen:

INSERT mytable VALUES ('Dan');

Nyckelintervallslåset för RangeI-N läge placeras på indexraden som motsvarar namnet David för att testa intervallet. Om låset beviljas infogas en rad med värdet Dan och ett exklusivt lås (X) placeras på den infogade raden. Nyckelintervalllåset för RangeI-N läge är bara nödvändigt för att testa intervallet och hålls inte kvar under hela transaktionen som utför infogningsåtgärden. Andra transaktioner kan infoga eller ta bort värden före eller efter den infogade raden med värdet Dan. Alla transaktioner som försöker läsa, infoga eller ta bort raden med värdet Dan blockeras dock tills den infogande transaktionen antingen kommittar eller återställs.

Infoga åtgärd med optimerad låsning

När du infogar en rad i en transaktion behöver inte intervallet som raden hamnar i låsas under hela transaktionen som utför infogningsåtgärden. Rad- och sidlås hämtas sällan, bara när det pågår ett onlineindexåterskapande eller när det förekommer samtidiga SERIALIZABLE-transaktioner. Om rad- och sidlås hämtas frigörs de snabbt och lagras inte under transaktionens varaktighet. Att placera ett exklusivt TID-lås på det infogade nyckelvärdet till slutet av transaktionen räcker för att upprätthålla serialisering. Till exempel givet detta INSERT-uttalande:

INSERT mytable VALUES ('Dan');

Med optimerad låsning hämtas ett RangeI-N-lås endast om det i instansen finns minst en transaktion som använder SERIALIZABLE-isoleringsnivå. Nyckelintervallslåset för RangeI-N läge placeras på indexraden som motsvarar namnet David för att testa intervallet. Om låset beviljas infogas en rad med värdet Dan och ett exklusivt lås (X) placeras på den infogade raden. Nyckelintervalllåset för RangeI-N läge är bara nödvändigt för att testa intervallet och hålls inte kvar under hela transaktionen som utför infogningsåtgärden. Andra transaktioner kan infoga eller ta bort värden före eller efter den infogade raden med värdet Dan. Alla transaktioner som försöker läsa, infoga eller ta bort raden med värdet Dan blockeras dock tills den infogande transaktionen antingen kommittar eller återställs.

Låseskalering

Låseskalering är processen att konvertera många detaljerade lås till färre grova lås, vilket minskar systemets omkostnader samtidigt som sannolikheten för konflikter vid samtidighet ökar.

Låseskalering fungerar annorlunda beroende på om optimerad låsning är aktiverad.

Låseskalering utan optimerad låsning

När databasmotorn hämtar lås på låg nivå placerar den även avsiktslås på de objekt som innehåller objekt på lägre nivå:

  • När du låser rader eller indexnyckelintervall placerar databasmotorn ett avsiktslås på de sidor som innehåller raderna eller nycklarna.
  • När du låser sidor placerar databasmotorn ett avsiktslås på de objekt på högre nivå som innehåller sidorna. Förutom avsiktslåset på objektet begärs avsiktssidans lås på följande objekt:
    • Sidor på lövnivå med icke-grupperade index
    • Datasidor med klustrade index
    • Heap-datasidor

Databasmotorn kan göra både rad- och sidlåsning för samma instruktion för att minimera antalet lås och minska sannolikheten för att låseskalering krävs. Databasmotorn kan till exempel placera sidlås på ett icke-klustrat index (om tillräckligt många sammanhängande nycklar i indexnoden valts för att uppfylla frågeställningen) och radlås på det klustrade indexet eller heapen.

För att eskalera lås försöker databasmotorn ändra avsiktslåset i tabellen till motsvarande fullständiga lås, till exempel genom att ändra ett avsiktligt exklusivt (IX) lås till ett exklusivt (X) lås, eller ett avsiktligt delat (IS) lås till ett delat (S) lås. Om låseskaleringsförsöket lyckas och det fullständiga tabelllåset hämtas, frigörs alla HoBT-lås, sidelås (PAGE) eller radnivålås (RID, KEY) som innehas av transaktionen på heapen eller indexet. Om det inte går att hämta det fullständiga låset sker ingen låseskalering vid den tidpunkten och databasmotorn fortsätter att hämta rad-, nyckel- eller sidlås.

Databasmotorn eskalerar inte rad- eller nyckelintervalllås till sidlås, men eskalerar dem direkt till tabelllås. På samma sätt eskaleras alltid sidlås till tabelllås. Låsning av partitionerade tabeller kan eskalera till HoBT-nivån för den associerade partitionen i stället för till tabelllåset. Ett lås på HoBT-nivå låser inte nödvändigtvis de justerade hoBT:erna för partitionen.

Obs

Lås på HoBT-nivå ökar vanligtvis samtidigheten, men introducerar risken för dödlägen när transaktioner som låser olika partitioner vill utöka sina exklusiva lås till de andra partitionerna. I sällsynta fall kan TABLE låsningsgranularitet fungera bättre.

Om ett låseskaleringsförsök misslyckas på grund av motstridiga lås som innehas av samtidiga transaktioner, försöker databasmotorn upprepa låseskaleringen för varje ytterligare 1 250 lås som förvärvas av transaktionen.

Varje eskaleringshändelse fungerar främst på samma nivå som en enda Transact-SQL-instruktion. När händelsen startar försöker databasmotorn eskalera alla lås som ägs av den aktuella transaktionen i någon av de tabeller som har refererats av den aktiva instruktionen förutsatt att den uppfyller tröskelvärdet för eskalering. Om eskaleringshändelsen startar innan uttrycket har åtkomst till en tabell görs inget försök att eskalera låsen på den tabellen. Om låseskaleringen lyckas eskaleras alla lås som har förvärvats av transaktionen i en tidigare instruktion och som fortfarande hålls när händelsen startar om tabellen refereras av den aktuella instruktionen och ingår i eskaleringshändelsen.

Anta till exempel att en session utför följande åtgärder:

  • Påbörjar en transaktion.
  • Uppdateringar TableA. Detta genererar exklusiva radlås i TableA som hålls kvar tills transaktionen har slutförts.
  • Uppdateringar TableB. Detta genererar exklusiva radlås i TableB som hålls kvar tills transaktionen har slutförts.
  • Utför en SELECT som kopplar ihop TableA med TableC. Frågekörningsplanen kräver att raderna hämtas från TableA innan raderna hämtas från TableC.
  • SELECT-instruktionen utlöser låseskalering när den hämtar rader från TableA och innan den har fått tillgång till TableC.

Om låseskaleringen lyckas eskaleras endast de lås som hålls av sessionen på TableA. Detta omfattar både delade lås från SELECT-instruktionen och de exklusiva låsen från föregående UPDATE-instruktion. Även om endast de lås som sessionen har förvärvat i TableA för SELECT-instruktionen räknas för att avgöra om låseskalering ska utföras, så eskaleras alla lås som hålls av sessionen i TableA till ett exklusivt lås på tabellen när eskaleringen har slutförts. Dessutom släpps alla andra lås med lägre granularitet, inklusive avsiktslås, på TableA.

Inga försök görs att eskalera lås på TableB eftersom det inte fanns någon aktiv referens till TableB i SELECT-instruktionen. På samma sätt görs inga försök att eskalera låsen på TableC, eftersom de inte hade eskalerats då de ännu inte hade använts när eskaleringen inträffade.

Låseskalering med optimerad låshantering

Optimerad låsning hjälper till att minska låsminnet eftersom mycket få lås hålls under hela transaktionen. När databasmotorn hämtar rad- och sidlås kan låseskalering ske på samma sätt, men mycket mindre ofta. Optimerad låsning lyckas vanligtvis undvika låseskaleringar, vilket minskar antalet lås och mängden låsminne som krävs.

När optimerad låsning är aktiverad och på standardnivå READ COMMITTED isolering släpper databasmotorn rad- och sidlås så snart raden ändras. Inga rad- och sidlås hålls under transaktionens varaktighet, förutom ett enda TID-lås (Transaction ID). Detta minskar sannolikheten för låseskalering.

Låsa tröskelvärden för eskalering

Låseskalering utlöses när låseskalering inte inaktiveras i tabellen med hjälp av alternativet ALTER TABLE SET LOCK_ESCALATION och när något av följande villkor finns:

  • En enda Transact-SQL-uttalande hämtar minst 5 000 lås på en enda icke-partitionerad tabell eller ett index.
  • En enda Transact-SQL-instruktion hämtar minst 5 000 lås på en enda partition i en partitionerad tabell och alternativet ALTER TABLE SET LOCK_ESCALATION är inställt på AUTO.
  • Antalet lås i en instans av databasmotorn överskrider tröskelvärdena för minne eller konfiguration.

Om lås inte kan eskaleras på grund av låskonflikter utlöser databasmotorn regelbundet låseskalering vid varje 1 250 nya lås som hämtas.

Eskaleringströskel för ett Transact-SQL-uttalande

När databasmotorn söker efter möjliga eskaleringar vid varje 1 250 nyförvärvade lås sker en låseskalering om och endast om en Transact-SQL-instruktion har fått minst 5 000 lås på en enda referens för en tabell. Låseskalering utlöses när en Transact-SQL-instruktion hämtar minst 5 000 lås på en enda referens för en tabell. Låseskalering utlöses till exempel inte om en instruktion hämtar 3 000 lås i ett index och 3 000 lås i ett annat index i samma tabell. På samma sätt utlöses inte låseskalering om en -instruktion har en självkoppling i en tabell, och varje referens till tabellen hämtar bara 3 000 lås i tabellen.

Låseskalering sker endast för tabeller som har använts när eskaleringen utlöses. Anta att en enda SELECT-instruktion är en koppling som har åtkomst till tre tabeller i den här sekvensen: TableA, TableBoch TableC. Instruktionen hämtar 3 000 radlås i det klustrade indexet för TableA och minst 5 000 radlås i det klustrade indexet för TableB, men har ännu inte använt TableC. När databasmotorn upptäcker att uttrycket har förvärvat minst 5 000 radlås i TableB, försöker den eskalera alla lås som innehas av den aktuella transaktionen på TableB. Den försöker också eskalera alla lås som innehas av den aktuella transaktionen på TableA, men eftersom antalet lås på TableA är mindre än 5 000, skulle inte eskaleringen lyckas. Ingen låseskalering görs för TableC eftersom den ännu inte hade använts när eskalering inträffade.

Eskaleringströskel för en instans av databasmotorn

När antalet lås är större än minneströskeln för låseskalering utlöser databasmotorn låseskalering. Tröskelvärdet för minne beror på inställningen för låser konfigurationsalternativet:

  • Om alternativet locks är inställt på standardinställningen 0 nås tröskelvärdet för låseskalering när minnet som används av låsobjekt är 24 procent av det minne som används av databasmotorn, exklusive AWE-minne. Datastrukturen som används för att representera ett lås är cirka 100 byte lång. Det här tröskelvärdet är dynamiskt eftersom databasmotorn dynamiskt hämtar och frigör minne för att justera för olika arbetsbelastningar.

  • Om alternativet locks är ett annat värde än 0 är tröskelvärdet för låseskalering 40 procent (eller mindre om det finns ett minnestryck) för låsalternativets värde.

Databasmotorn kan välja valfri aktiv instruktion från valfri session för eskalering, och för varje 1 250 nya lås väljer den instruktioner för eskalering så länge låsminnet som används i instansen ligger kvar över tröskelvärdet.

Låseskalering med blandade låstyper

När låseskalering inträffar är låset som valts för heapen eller indexet tillräckligt starkt för att uppfylla kraven för det mest restriktiva låset på lägre nivå.

Anta till exempel en session:

  • Påbörjar en transaktion.
  • Uppdaterar en tabell som innehåller ett grupperat index.
  • Utfärdar en SELECT-instruktion som refererar till samma tabell.

Kommandot UPDATE hämtar dessa lås:

  • Exklusivt (X) låser på de uppdaterade dataraderna.
  • Avsiktsexklusiv (IX) låser på de grupperade indexsidorna som innehåller dessa rader.
  • Ett IX lås på det klustrade indexet och ett annat på tabellen.

Kommandot SELECT hämtar dessa lås:

  • Delade (S) låses på alla datarader som den läser, såvida inte raden redan skyddas av ett X lås från UPDATE-instruktionen.
  • Intent Shared (IS) låses på alla grupperade indexsidor som innehåller dessa rader, såvida inte sidan redan skyddas av ett IX lås.
  • Inget lås på det klustrade indexet eller tabellen eftersom de redan skyddas av IX-lås.

Om SELECT-instruktionen hämtar tillräckligt med lås för att utlösa låseskalering, och om eskaleringen lyckas, konverteras IX-låset på tabellen till ett X-lås, och alla rad-, sid- och indexlås frigörs. Både uppdateringarna och läsningarna skyddas av X-låset på tabellen.

Minska låsning och låseskalering

I de flesta fall ger databasmotorn bästa prestanda vid drift med standardinställningarna för låsning och låseskalering.

  • Dra nytta av den optimerade låsningen .

    • Optimerad låsning erbjuder en förbättrad mekanism för transaktionslåsning som minskar förbrukningen av låsminne och blockering för samtidiga transaktioner. Låseskalering är mycket mindre sannolikt att inträffa när optimerad låsning är aktiverad.
    • Undvik att använda tabelltips med optimerad låsning. Tabelltips kan minska effektiviteten för optimerad låsning.
    • Aktivera alternativet READ_COMMITTED_SNAPSHOT för databasen så att du får störst nytta av optimerad låsning. Det här är standardinställningen i Azure SQL Database.
    • Optimerad låsning kräver accelererad databasåterställning (ADR) aktiveras i databasen.

Om en instans av databasmotorn genererar många lås och ser frekventa låseskaleringar bör du överväga att minska mängden låsning med följande strategier:

  • Använd en isoleringsnivå som inte genererar delade lås för läsåtgärder:

    • READ COMMITTED isoleringsnivå när READ_COMMITTED_SNAPSHOT databasalternativet är ON.
    • SNAPSHOT isoleringsnivå.
    • READ UNCOMMITTED isoleringsnivå. Detta kan endast användas för system som hanterar smutsiga läsningar.

  • Använd tabelltipsen PAGLOCK eller TABLOCK för att använda databasmotorns sida, heap eller indexlås i stället för lås på låg nivå. Med det här alternativet ökar dock problemen för användare som blockerar andra användare som försöker komma åt samma data och bör inte användas i system med fler än några samtidiga användare.

  • Om optimerad låsning inte är tillgänglig använder du alternativet LOCK_ESCALATION för ALTER TABLE för att eskalera lås till partitionen i stället för tabellen, eller för att inaktivera låseskalering för en tabell.

  • Dela upp stora batchåtgärder i flera mindre åtgärder. Anta till exempel att du körde följande fråga för att ta bort flera hundra tusen gamla rader från en granskningstabell, och sedan upptäckte du att det orsakade en låseskalering som blockerade andra användare:

    DELETE FROM LogMessages
WHERE LogDate < '2024-09-26'

    Genom att ta bort dessa rader några hundra åt gången kan du avsevärt minska antalet lås som ackumuleras per transaktion och förhindra låseskalering. Till exempel:

    DECLARE @DeletedRows int;

WHILE @DeletedRows IS NULL OR @DeletedRows > 0
BEGIN
    DELETE TOP (500)
    FROM LogMessages
    WHERE LogDate < '2024-09-26'

    SELECT @DeletedRows = @@ROWCOUNT;
END;

  • Minska fotavtrycket för frågelåset genom att göra frågan så effektiv som möjligt. Stora genomsökningar eller ett stort antal viktiga sökningar kan öka risken för låseskalering. Dessutom ökar risken för dödlägen och påverkar i allmänhet samtidighet och prestanda negativt. När du har hittat frågan som orsakar låseskalering letar du efter möjligheter att skapa nya index eller lägga till kolumner i ett befintligt index för att ta bort fullständiga index- eller tabellgenomsökningar och maximera effektiviteten för indexsökningar. Överväg att använda Database Engine Tuning Advisor för att utföra en automatisk indexanalys på frågan. Mer information finns i Tutorial: Database Engine Tuning Advisor. Ett mål med den här optimeringen är att få indexsökningar att returnera så få rader som möjligt för att minimera kostnaden för nyckelsökningar (maximera indexets selektivitet för den specifika frågan). Om databasmotorn uppskattar att en logisk nyckelsökningsoperator kan returnera många rader, kan den använda en förhämtande optimering för att utföra sökningen. Om databasmotorn använder prefetch för ett uppslag måste den öka transaktionsisoleringsnivån för en del av sökfrågan till REPEATABLE READ. Det innebär att det som kan likna en SELECT-instruktion på en READ COMMITTED-isoleringsnivå kan hämta tusentals nyckellås (på både klustrat index och ett icke-klustrat index), vilket kan leda till att en sådan frågeställning överskrider tröskelvärdena för låseskalering. Detta är särskilt viktigt om du upptäcker att det eskalerade låset är ett delat tabelllås, vilket dock inte ofta förekommer vid standardisolationsnivån READ COMMITTED.

    Om en nyckelsökning med prefetchoptimering orsakar låseskalering kan du överväga att lägga till ytterligare kolumner i det icke-klustrade indexet som visas i den logiska operatorn Indexsök eller indexgenomsökning nedanför nyckelsökningens logiska operator i exekveringsplanen. Det kan vara möjligt att skapa ett täckande index (ett index som innehåller alla kolumner i en tabell som användes i frågan), eller åtminstone ett index som täcker de kolumner som användes för kopplingsvillkor eller i WHERE-satsen om det är opraktiskt att inkludera allt i SELECT kolumnlistan. En kapslad loopkoppling kan också använda prefetch-optimeringen och detta orsakar samma låsningsbeteende.

  • Låseskalering kan inte inträffa om en annan SPID för närvarande har ett inkompatibelt tabelllås. Låseskalering leder alltid till ett tabellås och aldrig till sidlås. Dessutom, om ett försök till låseskalering misslyckas eftersom en annan SPID har ett inkompatibelt tabelllås, blockeras inte frågan som försökte eskalera medan den väntar på ett tabelllås. I stället fortsätter den att hämta lås på den ursprungliga, mer detaljerade nivån (rad, nyckel eller sida) och gör regelbundet ytterligare eskaleringsförsök. Därför är en metod för att förhindra låseskalering i en viss tabell att hämta och hålla ett lås på en annan anslutning som inte är kompatibel med den eskalerade låstypen. Ett exklusivt avsiktslås (IX) på tabellnivå låser inte några rader eller sidor, men det är fortfarande inte kompatibelt med ett eskalerat delat (S) eller exklusivt (X) tabelllås. Anta till exempel att du måste köra ett batchjobb som ändrar ett stort antal rader i tabellen mytable och som har orsakat blockering på grund av låsupptrappning. Om det här jobbet alltid slutförs på mindre än en timme kan du skapa ett Transact-SQL jobb som innehåller följande kod och schemalägga det nya jobbet att starta flera minuter före batchjobbets starttid:

    BEGIN TRAN;

SELECT *
FROM mytable WITH (UPDLOCK, HOLDLOCK)
WHERE 1 = 0;

WAITFOR DELAY '1:00:00';

COMMIT TRAN;

    Den här frågan hämtar och håller ett IX-lås på mytable i en timme, vilket förhindrar låseskalering på tabellen under den tiden. Den här batchen ändrar inga data eller blockerar andra frågor (såvida inte den andra frågan tvingar fram ett tabelllås med TABLOCK tips eller om en administratör har inaktiverat sida eller radlås på ett index på mytable).

  • Du kan också använda spårningsflaggorna 1211 och 1224 för att inaktivera alla eller vissa låseskaleringar. Dessa spårningsflaggor inaktiverar all låseskalering globalt för hela databasmotorinstansen. Låseskalering tjänar ett användbart syfte i databasmotorn genom att maximera effektiviteten för frågor som annars saktas ned av omkostnaderna för att hämta och frigöra flera tusentals lås. Låseskalering hjälper också till att minimera det minne som krävs för att hålla reda på lås. Det minne som databasmotorn dynamiskt kan allokera för låsstrukturer är begränsat, så om du inaktiverar låseskalering och låsminnet växer tillräckligt stort kan försök att allokera ytterligare lås för en fråga misslyckas och följande fel inträffar: Error: 1204, Severity: 19, State: 1 The SQL Server cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users or ask the system administrator to check the SQL Server lock and memory configuration.

    Obs

    När felet MSSQLSERVER_1204 inträffar stoppar det bearbetningen av den aktuella instruktionen och orsakar en återställning av den aktiva transaktionen. Själva återställningen kan blockera användare eller leda till en lång återställningstid för databasen om du startar om databastjänsten.

    Obs

    Om du använder ett låstips som ROWLOCK ändras bara det första låsförvärvet. Låstips förhindrar inte låseskalering.

Från och med SQL Server 2008 (10.0.x) har beteendet för låseskalering ändrats i och med införandet av alternativet LOCK_ESCALATION tabell. För mer information, se alternativ LOCK_ESCALATION för ALTER TABLE.

Övervaka låseskalering

Övervaka låseskalering med hjälp av den lock_escalation utökade händelsen, till exempel i följande exempel:

-- Session creates a histogram of the number of lock escalations per database
CREATE EVENT SESSION [Track_lock_escalation] ON SERVER
ADD EVENT sqlserver.lock_escalation
    (
    SET collect_database_name=1,collect_statement=1
    ACTION(sqlserver.database_id,sqlserver.database_name,sqlserver.query_hash_signed,sqlserver.query_plan_hash_signed,sqlserver.sql_text,sqlserver.username)
    )
ADD TARGET package0.histogram
    (
    SET source=N'sqlserver.database_id'
    )
GO

Dynamisk låsning

Om du använder lås på låg nivå, till exempel radlås, ökar samtidigheten genom att minska sannolikheten för att två transaktioner begär lås på samma datahona samtidigt. Om du använder lås på låg nivå ökar också antalet lås och de resurser som behövs för att hantera dem. Att använda tabell- eller sidlås på hög nivå sänker kostnaderna, men på bekostnad av lägre samtidighet.

Ett diagram över låsningskostnader jämfört med samtidighetskostnader.

Databasmotorn använder en strategi för dynamisk låsning för att fastställa de mest effektiva låsen. Databasmotorn avgör automatiskt vilka lås som är lämpligast när frågan körs, baserat på egenskaperna för schemat och frågan. Om du till exempel vill minska låsningskostnaderna kan optimeraren välja sidlås i ett index när du utför en indexgenomsökning.

Låsning av partitionering

För stora datorsystem kan lås på objekt som ofta refereras bli en flaskhals för prestanda eftersom anskaffning och frigörande av lås sätter konkurrens på interna låsresurser. Låspartitionering förbättrar låsningsprestanda genom att dela upp en enskild låsresurs i flera låsresurser. Den här funktionen är endast tillgänglig för system med 16 eller fler logiska processorer och aktiveras automatiskt och kan inte inaktiveras. Endast objektlås kan partitioneras. Objektlås som har en undertyp partitioneras inte. Mer information finns i sys.dm_tran_locks (Transact-SQL).

Förstå låspartitionering

Låsning av uppgifter har åtkomst till flera delade resurser, varav två optimeras genom låspartitionering:

  • Spinlock

    Detta styr åtkomsten till en låsresurs, till exempel en rad eller en tabell.

    Utan låspartitionering hanterar en spinlock alla låsbegäranden för en enskild låsresurs. På system som upplever en stor mängd aktivitet kan konkurrens uppstå när låsbegäranden väntar på att spinlocket ska bli tillgängligt. I den här situationen kan det bli en flaskhals att skaffa lås och påverka prestanda negativt.

    För att minska konkurrensen på en enskild låsresurs delar låspartitionering upp en enda låsresurs i flera låsresurser för att distribuera belastningen över flera spinlocks.

  • Minnes

    Detta används för att lagra låsresursstrukturerna.

    När spinlocket har förvärvats lagras låsstrukturer i minnet och kan därefter användas och eventuellt ändras. Genom att distribuera låsåtkomst över flera resurser kan du eliminera behovet av att överföra minnesblock mellan processorer, vilket hjälper till att förbättra prestandan.

Implementera och övervaka låspartitionering

Låspartitionering är aktiverat som standard för system med 16 eller fler processorer. När låspartitionering är aktiverat registreras ett informationsmeddelande i SQL Server-felloggen.

När du hämtar lås på en partitionerad resurs:

  • Endast NL, Sch-S, IS, IUoch IX låslägen hämtas på en enda partition.

  • Delade (S), exklusiva (X) och andra lås i andra lägen än NL, Sch-S, IS, IUoch IX måste hämtas på alla partitioner som börjar med partitions-ID 0 och följande i partitions-ID-ordning. Dessa lås på en partitionerad resurs använder mer minne än lås i samma läge på en icke-partitionerad resurs eftersom varje partition i praktiken är ett separat lås. Ökningen av minne bestäms av antalet partitioner. Prestandaräknare för SQL Server-lås visar information om minne som används av partitionerade och icke-partitionerade lås.

En transaktion tilldelas till en partition när transaktionen startar. För transaktionen använder alla låsbegäranden som kan partitioneras den partition som tilldelats till transaktionen. Med den här metoden fördelas åtkomsten till låsresurser för samma objekt av olika transaktioner över olika partitioner.

Kolumnen resource_lock_partition i vyn sys.dm_tran_locks dynamisk hantering innehåller låspartitions-ID:t för en låspartitionerad resurs. Mer information finns i sys.dm_tran_locks (Transact-SQL).

Arbeta med låspartitionering

Följande kodexempel illustrerar låspartitionering. I exemplen körs två transaktioner i två olika sessioner för att visa beteendet för låspartitionering på ett datorsystem med 16 processorer.

Dessa Transact-SQL-instruktioner skapar testobjekt som används i exemplen som följer.

-- Create a test table.
CREATE TABLE TestTable
(
col1 int
);
GO

-- Create a clustered index on the table.
CREATE CLUSTERED INDEX ci_TestTable ON TestTable (col1);
GO

-- Populate the table.
INSERT INTO TestTable
VALUES (1);
GO

Exempel A

Session 1:

En SELECT-instruktion körs under en transaktion. På grund av det HOLDLOCK-låstipset hämtar och behåller den här satsen ett intentionsdelat (IS) lås på tabellen (för det här exemplet ignoreras rad- och sidlås). Låset IS hämtas endast på den partition som tilldelats transaktionen. I det här exemplet förutsätts det att IS-låset tas på partitions-ID 7.

-- Start a transaction.
BEGIN TRANSACTION;

-- This SELECT statement will acquire an IS lock on the table.
SELECT col1
FROM TestTable
WITH (HOLDLOCK);

Session 2:

En transaktion startas och SELECT-instruktionen som körs under den här transaktionen hämtar och behåller ett delat (S) lås på tabellen. Låset S hämtas på alla partitioner, vilket resulterar i flera tabelllås, ett för varje partition. I ett 16-CPU-system utfärdas till exempel 16 S lås över låspartitions-ID:t 0–15. Eftersom låset S är kompatibelt med låset IS, som hålls på partitions-ID 7 av transaktionen i session 1, finns det ingen blockering mellan transaktionerna.

BEGIN TRANSACTION;

SELECT col1
FROM TestTable
WITH (TABLOCK, HOLDLOCK);

Session 1:

Följande SELECT-instruktion körs under transaktionen som fortfarande är aktiv under session 1. På grund av det exklusiva tabelllåstipset (X) försöker transaktionen hämta ett X lås på tabellen. Men det S-lås som hålls av transaktionen i session 2 blockerar ett X-lås vid partitions-ID 0.

SELECT col1
FROM TestTable
WITH (TABLOCKX);

Exempel B

Session 1:

En SELECT-instruktion körs under en transaktion. På grund av det HOLDLOCK-låstipset hämtar och behåller den här satsen ett intentionsdelat (IS) lås på tabellen (för det här exemplet ignoreras rad- och sidlås). Låset IS hämtas endast på den partition som tilldelats transaktionen. I det här exemplet antas det att lås IS förvärvas på partitions-ID 6.

-- Start a transaction.
BEGIN TRANSACTION;

-- This SELECT statement will acquire an IS lock on the table.
SELECT col1
FROM TestTable
WITH (HOLDLOCK);

Session 2:

En SELECT-instruktion körs under en transaktion. På grund av TABLOCKX låshint försöker transaktionen få ett exklusivt lås (X) på tabellen. Kom ihåg att låset X måste hämtas på alla partitioner som börjar med partitions-ID 0. Låset X hämtas på alla partitions-ID:t 0–5 men blockeras av det IS lås som hämtas på partitions-ID 6.

På partitions-ID:n 7–15 där X-låset ännu inte har nått kan andra transaktioner fortsätta att skaffa lås.

BEGIN TRANSACTION;

SELECT col1
FROM TestTable
WITH (TABLOCKX, HOLDLOCK);

Radversionsbaserad isoleringsnivå i databasmotorn

Från och med SQL Server 2005 (9.x) erbjuder databasmotorn en implementering av en befintlig transaktionsisoleringsnivå, READ COMMITTED, som ger en ögonblicksbild på instruktionsnivå med hjälp av radversioner. Database Engine erbjuder också en transaktionsisoleringsnivå, SNAPSHOT, som ger en ögonblicksbild på transaktionsnivå även med hjälp av radversionshantering.

Radversioner är ett allmänt ramverk i SQL Server som anropar en mekanism för kopiering vid skrivning när en rad ändras eller tas bort. Detta kräver att medan transaktionen körs måste den gamla versionen av raden vara tillgänglig för transaktioner som kräver ett tidigare transaktionsmässigt konsekvent tillstånd. Radversioner används för att implementera följande funktioner:

  • Skapa tabellerna inserted och deleted i utlösare. Alla rader som ändras av utlösaren är versionshanterade. Detta inkluderar de rader som ändrats av instruktionen som startade utlösaren samt eventuella dataändringar som gjorts av utlösaren.
  • Stöd för flera aktiva resultatuppsättningar (MARS). Om en MARS-session utfärdar en instruktion för datamodifiering (till exempel INSERT, UPDATEeller DELETE) samtidigt som det finns en aktiv resultatuppsättning, är raderna som påverkas av ändringssatsen versionshanterade.
  • Stöd för indexåtgärder som anger alternativet ONLINE.
  • Stöd för radversionsbaserade transaktionsisoleringsnivåer:
    • En ny implementering av READ COMMITTED isoleringsnivå som använder radversioner för att tillhandahålla läskonsekvens på satsnivå.
    • En ny isolationsnivå, SNAPSHOT(för att ge läsningskonsistens på transaktionsnivå).

Radversioner lagras i ett versionslager. Om accelererad databasåterställning (ADR) är aktiverad på en databas skapas versionsarkivet i databasen. Annars skapas versionsarkivet i tempdb-databasen.

Databasen måste ha tillräckligt med utrymme för versionsarkivet. När versionsarkivet finns i tempdb, och tempdb databasen är full, slutar uppdateringsåtgärderna att generera versioner men fortsätter att lyckas, men läsåtgärder kan misslyckas eftersom en viss radversion som behövs inte finns. Detta påverkar åtgärder som utlösare, MARS och onlineindexering.

När ADR används och versionsarkivet är fullt fortsätter läsåtgärderna att lyckas, men skrivåtgärder som genererar versioner, till exempel UPDATE och DELETE misslyckas. INSERT åtgärder fortsätter att lyckas om databasen har tillräckligt med utrymme.

Att använda radversioner för READ COMMITTED och SNAPSHOT transaktioner är en tvåstegsprocess:

  1. Ange antingen READ_COMMITTED_SNAPSHOT- eller både READ_COMMITTED_SNAPSHOT- och ON-databasalternativen till .

  2. Ange lämplig transaktionsisoleringsnivå i ett program:

    • När READ_COMMITTED_SNAPSHOT databasalternativet är ONanvänder transaktioner som anger READ COMMITTED isoleringsnivå radversioner.
    • När ALLOW_SNAPSHOT_ISOLATION databasalternativet är ONkan transaktioner ange SNAPSHOT isoleringsnivå.

När antingen READ_COMMITTED_SNAPSHOT eller ALLOW_SNAPSHOT_ISOLATION databasalternativet är inställt på ONtilldelar databasmotorn ett transaktionssekvensnummer (XSN) till varje transaktion som manipulerar data med hjälp av radversioner. Transaktioner startar när en BEGIN TRANSACTION-instruktion körs. Transaktionssekvensnumret börjar dock med den första läs- eller skrivåtgärden efter BEGIN TRANSACTION-instruktionen. Transaktionssekvensnumret ökas med en varje gång det tilldelas.

När databasalternativen READ_COMMITTED_SNAPSHOT eller ALLOW_SNAPSHOT_ISOLATION anges till ONunderhålls logiska kopior (versioner) för alla dataändringar som utförs i databasen. Varje gång en rad ändras av en specifik transaktion lagrar instansen av databasmotorn en version av den tidigare bekräftade avbildningen av raden i versionsarkivet. Varje version markeras med transaktionssekvensnumret för den transaktion som gjorde ändringen. Versionerna av ändrade rader är länkade med hjälp av en länklista. Det senaste radvärdet lagras alltid i den aktuella databasen och länkas till de versionshanterade raderna i versionsarkivet.

Obs

För ändring av stora objekt (LOB) kopieras endast det ändrade fragmentet till versionsarkivet.

Radversioner bevaras tillräckligt länge för att uppfylla behoven hos transaktioner som körs under radversionsbaserade isoleringsnivåer. Databasmotorn spårar det tidigaste användbara transaktionssekvensnumret och tar regelbundet bort alla radversioner som stämplats med transaktionssekvensnummer som är lägre än det tidigaste användbara sekvensnumret.

När båda databasalternativen är inställda på OFFär endast rader som ändras av utlösare eller MARS-sessioner, eller läses av onlineindexåtgärder, versionshanterade. Dessa radversioner släpps när de inte längre behövs. En bakgrundsprocess tar bort inaktuella radversioner.

Obs

För kortvariga transaktioner kan en version av en ändrad rad cachelagras i buffertpoolen utan att skrivas till versionsarkivet. Om behovet av den versionsbaserade raden är kortlivat tas raden bort från buffertpoolen och medför inte I/O-omkostnader.

Beteende vid läsning av data

När transaktioner som körs under radversionsbaserad isolering läser data, skaffar läsåtgärderna inte några delade (S) lås på de data som läses och blockerar därför inte transaktioner som ändrar data. Dessutom minimeras omkostnaderna för att låsa resurser eftersom antalet förvärvade lås minskar. READ COMMITTED-isolering med hjälp av radversioner och SNAPSHOT-isolering är utformade för att ge läskonsekvens på instruktionsnivå eller transaktionsnivå för versionsbaserade data.

Alla frågeoperationer, inklusive transaktioner som körs under isolering med radversioner, förvärvar schemastabilitetslås (Sch-S) under kompilering och exekvering. Därför blockeras frågor när en samtidig transaktion innehåller ett schemaändringslås (Sch-M) i tabellen. En DDL-åtgärd (datadefinitionsspråk) hämtar till exempel ett Sch-M lås innan schemainformationen i tabellen ändras. Transaktioner, inklusive de som körs under en radversionsbaserad isoleringsnivå, blockeras när de försöker att få ett Sch-S-lås. Omvänt blockerar en sökfråga med ett Sch-S-lås en samtidig transaktion som försöker hämta ett Sch-M-lås.

När en transaktion med SNAPSHOT isoleringsnivå startar registrerar instansen av databasmotorn alla aktiva transaktioner. När SNAPSHOT transaktion läser en rad som har en versionskedja följer databasmotorn kedjan och hämtar raden där transaktionssekvensnumret är:

  • Närmast men lägre än sekvensnumret för ögonblicksbildtransaktionen som läser raden.

  • Inte i listan över de transaktioner som är aktiva när ögonblicksbildstransaktionen startade.

Läsåtgärder som utförs av SNAPSHOT transaktion hämtar den senaste versionen av varje rad som hade slutförts vid starten av SNAPSHOT transaktionen. Detta ger en transaktionsmässigt konsekvent ögonblicksbild av data som fanns i början av transaktionen.

READ COMMITTED transaktioner som använder radversioner fungerar på ungefär samma sätt. Skillnaden är att transaktionen READ COMMITTED inte använder sitt eget transaktionssekvensnummer när man väljer radversioner. Varje gång en utsaga startas läser READ COMMITTED-transaktionen det senaste tilldelade transaktionssekvensnumret som utfärdats för denna instans av databasmotorn. Det här är transaktionssekvensnumret som används för att välja radversionerna för instruktionen. På så sätt kan READ COMMITTED transaktioner se en ögonblicksbild av data eftersom de finns i början av varje instruktion.

Obs

Även om READ COMMITTED transaktioner som använder radversionshantering ger en transaktionsmässigt konsekvent vy över data på instruktionsnivå, underhålls radversioner som genereras eller används av den här typen av transaktioner tills transaktionen har slutförts.

Beteende vid ändring av data

Beteendet för dataskrivningar skiljer sig med och utan optimerad låsning aktiverad.

Ändra data utan optimerad låsning

I en READ COMMITTED-transaktion som använder radversionering görs valet av rader som ska uppdateras med hjälp av en blockerande skanning där ett uppdateringslås (U) hämtas på dataraden i det att datavärden läses. Detta är samma som en READ COMMITTED transaktion som inte använder radversionshantering. Om dataraden inte uppfyller uppdateringsvillkoren släpps uppdateringslåset på den raden och nästa rad låses och genomsöks.

Transaktioner som körs under SNAPSHOT-isolering antar en optimistisk inställning till dataändringar genom att låsa data innan ändringar görs, huvudsakligen för att upprätthålla begränsningar. Annars sätts inte lås på data förrän data ska ändras. När en datarad uppfyller uppdateringsvillkoren verifierar SNAPSHOT-transaktionen att dataraden inte har ändrats av en samtidig transaktion som har checkats in efter att SNAPSHOT transaktionen påbörjades. Om dataraden har ändrats utanför den SNAPSHOT transaktionen uppstår en uppdateringskonflikt och SNAPSHOT transaktionen avslutas. Uppdateringskonflikten hanteras av databasmotorn och det går inte att inaktivera uppdateringskonfliktidentifieringen.

Obs

Uppdateringsåtgärder som körs under SNAPSHOT isolering internt körs under READ COMMITTED isolering när SNAPSHOT-transaktionen får åtkomst till något av följande:

En tabell med en främmande nyckelbegränsning.

En tabell som refereras till i referensnyckelsbegränsningen i en annan tabell.

En indexerad vy som refererar till mer än en tabell.

Men även under dessa förhållanden fortsätter uppdateringsåtgärden att verifiera att data inte har ändrats av en annan transaktion. Om data har ändrats av en annan transaktion uppstår en uppdateringskonflikt för den SNAPSHOT transaktionen och avslutas. Uppdateringskonflikter måste hanteras och göras om av programmet.

Ändra data med optimerad låsning

Med optimerad låsning aktiverad och med databasalternativet READ_COMMITTED_SNAPSHOT (RCSI) aktiverat, och med standardvärdet READ COMMITTED isoleringsnivå, hämtar läsarna inga lås och skrivarna skaffar kort varaktighetslås på låg nivå, i stället för lås som upphör att gälla i slutet av transaktionen.

Aktivering av RCSI rekommenderas för bästa effektivitet med optimerad låsning. När du använder striktare isoleringsnivåer, till exempel REPEATABLE READ eller SERIALIZABLE, rymmer databasmotorn rad- och sidlås till slutet av transaktionen, för både läsare och skrivare, vilket resulterar i ökad blockering och låsminne.

Med RCSI aktiverat, och när standardnivån READ COMMITTED isolering används, kvalificerar skrivare rader enligt predikatet baserat på den senaste bekräftade versionen av raden, utan att använda U lås. En fråga väntar bara om raden kvalificerar sig och det finns en annan aktiv skrivtransaktion på den raden eller sidan. Om du kvalificerar baserat på den senaste bekräftade versionen och endast låser de kvalificerade raderna minskar blockeringen och ökar samtidigheten.

Om uppdateringskonflikter identifieras med RCSI och på standardnivån READ COMMITTED isolering hanteras de och görs om automatiskt utan att det påverkar kundens arbetsbelastningar.

Med optimerad låsning aktiverad och när du använder SNAPSHOT isoleringsnivå är beteendet för uppdateringskonflikter detsamma som utan optimerad låsning. Uppdateringskonflikter måste hanteras och göras om av programmet.

Obs

Mer information om beteendeändringar med funktionen lås efter kvalificering (LAQ) för optimerad låsning finns i Query behavior changes with optimized locking and RCSI.

Beteende i sammanfattning

I följande tabell sammanfattas skillnaderna mellan SNAPSHOT-isolering och READ COMMITTED-isolering med hjälp av användningen av radversionering.

Egenskap READ COMMITTED isoleringsnivå med hjälp av radversionshantering SNAPSHOT isoleringsnivå
Databasalternativet som måste anges till ON för att aktivera det stöd som krävs. READ_COMMITTED_SNAPSHOT ALLOW_SNAPSHOT_ISOLATION
Hur en session begär den specifika typen av radversionering. Använd standardnivån READ COMMITTED isolering eller kör SET TRANSACTION ISOLATION LEVEL-instruktionen för att ange READ COMMITTED isoleringsnivå. Detta kan göras när transaktionen startar. Kräver körning av SET TRANSACTION ISOLATION LEVEL för att ange SNAPSHOT isoleringsnivå innan transaktionen startas.
Versionen av data som läses av uttryck. All data som sparades före början av varje påstående. Alla data som har checkats in före starten av varje transaktion.
Hur uppdateringar hanteras. Utan optimerad låsning: återställer från radversioner till faktiska data för att välja rader som ska uppdateras och använder uppdateringslås på de valda dataraderna. Hämtar exklusiva lås på faktiska datarader som ska ändras. Ingen uppdatering av konfliktidentifiering.

Med optimerad låsning: Rader väljs baserat på den senaste bekräftade versionen utan att några lås hämtas. Om rader kvalificerar sig för uppdateringen hämtas exklusiva rad- eller sidlås. Om uppdateringskonflikter identifieras hanteras de och görs om automatiskt.		 Använder radversioner för att välja rader som ska uppdateras. Försöker hämta ett exklusivt lås på den faktiska dataraden som ska ändras, och om data har ändrats av en annan transaktion uppstår en uppdateringskonflikt och snapshot-transaktionen avslutas.
Uppdatera konfliktdetektering Utan optimerad låsning: Ingen.

Med optimerad låsning: Om uppdateringskonflikter identifieras hanteras de och görs om automatiskt.		 Integrerat stöd. Det går inte att inaktivera.

Resursanvändning för radversionshantering

Ramverket för radversion stöder följande funktioner för databasmotorn:

  • Utlösare
  • Flera aktiva resultatuppsättningar (MARS)
  • Onlineindexering

Ramverket för radversion stöder också följande radversionsbaserade transaktionsisoleringsnivåer:

  • När databasalternativet READ_COMMITTED_SNAPSHOT är inställt på ONsäkerställer READ_COMMITTED-transaktioner läskonsekvens på samma nivå som uttalanden med hjälp av versionshantering av rader.
  • När databasalternativet ALLOW_SNAPSHOT_ISOLATION är inställt på ONger SNAPSHOT transaktioner konsekvent läsning på transaktionsnivå med hjälp av radversionshantering.

Radversionsbaserade isoleringsnivåer minskar antalet lås som förvärvas av transaktioner genom att eliminera användningen av delade lås på läsoperationer. Detta ökar systemprestandan genom att minska de resurser som används för att hantera lås. Prestandan ökar också genom att minska antalet gånger en transaktion blockeras av lås som förvärvas av andra transaktioner.

Radversionsbaserade isoleringsnivåer ökar de resurser som krävs för dataändringar. Om du aktiverar de här alternativen kan alla dataändringar för databasen versionshanteras. En kopia av data före ändring lagras i versionsarkivet även om det inte finns några aktiva transaktioner med hjälp av radversionsbaserad isolering. Data efter ändringen innehåller en pekare till versionsdata i versionsarkivet. För stora objekt lagras endast en del av objektet som ändrades i versionsarkivet.

Utrymme som används i tempdb

För varje instans av databasmotorn måste versionsarkivet ha tillräckligt med utrymme för radversionerna. Databasadministratören måste se till att tempdb och andra databaser (om ADR är aktiverat) har gott om utrymme för versionsarkivet. Det finns två typer av versionslager:

  • Versionslagret för byggande av onlineindex används för onlineindexbyggande.
  • Det gemensamma versionsarkivet används för alla andra åtgärder för dataändring.

Radversioner måste lagras så länge en aktiv transaktion behöver komma åt dem. Med jämna mellanrum tar en bakgrundstråd bort radversioner som inte längre behövs och frigör utrymme i versionsarkivet. En långvarig transaktion förhindrar att utrymme i versionsarkivet släpps om det uppfyller något av följande villkor:

  • Den använder radversionsbaserad isolering.
  • Den använder utlösare, MARS eller online index build-åtgärder.
  • Den genererar radversioner.

Obs

När en utlösare anropas i en transaktion underhålls radversionerna som skapats av utlösaren till slutet av transaktionen, även om radversionerna inte längre behövs när utlösaren har slutförts. Detta gäller även för READ COMMITTED transaktioner som använder radversioner. Med den här typen av transaktion behövs en transaktionsmässigt konsekvent vy av databasen endast för varje instruktion i transaktionen. Det innebär att radversionerna som skapats för en instruktion i transaktionen inte längre behövs efter att instruktionen har slutförts. Radversioner som skapas av varje instruktion i transaktionen underhålls dock tills transaktionen har slutförts.

Om versionsarkivet finns i tempdboch tempdb får slut på utrymme tvingar databasmotorn versionsarkivet att krympa. Under krympningsprocessen betraktas de längst pågående transaktionerna som ännu inte har genererat radversioner som drabbade. Ett meddelande 3967 genereras i felloggen för varje offertransaktion. Om en transaktion har markerats som ett offer kan den inte längre läsa radversionerna i versionsarkivet. När den försöker läsa radversioner genereras meddelande 3966 och transaktionen återställs. Om krympningsprocessen lyckas blir utrymme tillgängligt i tempdb. Annars tar tempdb slut på utrymme och följande inträffar:

  • Skrivåtgärder fortsätter att köras men genererar inte versioner. Ett informationsmeddelande (3959) visas i felloggen, men transaktionen som skriver data påverkas inte.

  • Transaktioner som försöker komma åt radversioner som inte genererades på grund av en fullständig återställning av tempdb och avslutar med ett fel 3958.

Utrymme som används i datarader

Varje databasrad kan använda upp till 14 byte i slutet av raden för information om radversion. Informationen om radversion innehåller transaktionssekvensnumret för den transaktion som bekräftade versionen och pekaren till den versionerade raden. Dessa 14 byte läggs till första gången raden ändras, eller när en ny rad infogas, under något av följande villkor:

  • READ_COMMITTED_SNAPSHOT eller ALLOW_SNAPSHOT_ISOLATION alternativen är inställda på ON.
  • Tabellen har en utlösare.
  • Flera aktiva resultatuppsättningar (MARS) används.
  • Online index byggoperationer körs för närvarande på tabellen.

Om versionsarkivet finns i tempdbtas dessa 14 byte bort från databasraden första gången raden ändras under alla dessa villkor:

  • READ_COMMITTED_SNAPSHOT och ALLOW_SNAPSHOT_ISOLATION alternativ är inställda på OFF.
  • Utlösaren finns inte längre i tabellen.
  • MARS används inte.
  • Byggoperationer för online-index pågår inte just nu.

De 14 byteen tas också bort när en rad ändras om ADR inte längre är aktiverat och villkoren ovan är uppfyllda.

Om du använder någon av radversionsfunktionerna kan du behöva allokera ytterligare diskutrymme för databasen för att rymma 14 byte per databasrad. Om du lägger till information om radversioner kan det leda till index siduppdelningar eller allokering av en ny datasida om det inte finns tillräckligt med utrymme på den aktuella sidan. Om den genomsnittliga radlängden till exempel är 100 byte leder de ytterligare 14 byteen till att en befintlig tabell växer upp till 14 procent.

Om du minskar fyllningsfaktorn kan förhindra eller minska fragmenteringen av indexsidor. Om du vill visa aktuell information om siddensitet för data och index i en tabell eller vy kan du använda sys.dm_db_index_physical_stats.

Utrymme som används av det beständiga versionsarkivet (PVS)

När ADR är aktiverat kan radversioner lagras i beständigt versionsarkiv (PVS) på något av följande sätt, beroende på radens storlek före ändringen:

  • Om storleken är liten lagras hela den gamla radversionen som en del av den ändrade raden.
  • Om storleken är mellanliggande lagras skillnaden mellan den gamla radversionen och den ändrade raden som en del av den ändrade raden. Skillnaden konstrueras på ett sätt som gör att databasmotorn kan rekonstruera hela den gamla radversionen om det behövs.
  • Om storleken är stor lagras hela den gamla radversionen i en separat intern tabell.

De första två metoderna kallas inom rad versionslagring. Den sista metoden kallas versionslagring utanför rad. När radversioner inte längre behövs tas de bort för att frigöra utrymme på sidor. På samma sätt tas sidor i den interna tabellen som inte längre behövs för off-row-versioner bort av versionsrensaren.

Om du lagrar radversioner som en del av raden optimeras datahämtningen av transaktioner som behöver läsa radversioner. Om en version lagras på rad krävs ingen separat läsning av en PVS-sida utanför rad.

sys.dm_db_index_physical_stats DMV ger typen och antalet versioner som lagras i rad och utanför rad för en indexpartition. Den totala storleken på versionsdata som lagras på rad rapporteras i kolumnen total_inrow_version_payload_size_in_bytes.

Storleken på versionslagringen utanför rad rapporteras i kolumnen persistent_version_store_size_kb i sys.dm_tran_persistent_version_store_stats DMV.

Utrymme som används i stora objekt

Databasmotorn har stöd för flera datatyper som kan innehålla stora strängar på upp till 2 GIGABYTE (GB), till exempel: nvarchar(max), varchar(max), varbinary(max), ntext, textoch image. Stora data som lagras med dessa datatyper lagras i en serie datafragment som är länkade till dataraden. Information om radversioner lagras i varje fragment som används för att lagra dessa stora strängar. Datafragment lagras på en uppsättning sidor som är dedikerade till stora objekt i en tabell.

När nya stora värden läggs till i en databas allokeras de med högst 8 040 byte data per fragment. Tidigare versioner av databasmotorn lagrade upp till 8 080 byte av ntext, texteller image data per fragment.

Befintliga ntext, textoch image stora objektdata (LOB) uppdateras inte för att ge utrymme för radversionsinformationen när en databas uppgraderas till SQL Server från en tidigare version av SQL Server. Men första gången LOB-data ändras uppgraderas de dynamiskt för att aktivera lagring av versionsinformation. Detta händer även om radversioner inte genereras. När LOB-data har uppgraderats minskas det maximala antalet byte som lagras per fragment från 8 080 byte till 8 040 byte. Uppgraderingsprocessen motsvarar att ta bort LOB-värdet och återställa samma värde. LOB-data uppgraderas även om endast 1 byte ändras. Det här är en engångsåtgärd för varje ntext, texteller image kolumn, men varje åtgärd kan generera en stor mängd sidallokeringar och I/O-aktivitet beroende på storleken på LOB-data. Det kan också generera en stor mängd loggningsaktivitet om ändringen är helt loggad. WRITETEXT och UPDATETEXT åtgärder loggas minimalt om databasåterställningsmodellen inte är inställd på FULL.

Tillräckligt med diskutrymme ska allokeras för att tillgodose detta krav.

Övervaka radversioner och versionsarkivet

Databasmotorn tillhandahåller verktyg i form av dynamiska hanteringsvyer (DMV: er) och prestandaräknare för övervakning av radversioner, versionslager och ögonblicksbildisoleringsprocesser för prestanda och problem.

DMV:er

Följande DMV:er innehåller information om det aktuella systemtillståndet för tempdb och versionsarkivet samt transaktioner med radversionshantering.

  • sys.dm_db_file_space_usage. Returnerar information om utrymmesanvändning för varje fil i databasen. Mer information finns i sys.dm_db_file_space_usage (Transact-SQL).

  • sys.dm_db_session_space_usage. Returnerar sidallokering och avallokeringsaktivitet per session för databasen. Mer information finns i sys.dm_db_session_space_usage (Transact-SQL).

  • sys.dm_db_task_space_usage. Returnerar allokering och frigöring av sidor per uppgift i databasen. Mer information finns i sys.dm_db_task_space_usage (Transact-SQL).

  • sys.dm_tran_top_version_generators. Returnerar en virtuell tabell för de objekt som producerar flest versioner i versionsarkivet. Den grupperar de översta 256 aggregerade postlängderna efter database_id och rowset_id. Använd den här funktionen för att hitta de största konsumenterna i versionslagret. Gäller endast för versionslagret i tempdb. Mer information finns i sys.dm_tran_top_version_generators (Transact-SQL).

  • sys.dm_tran_version_store. Returnerar en virtuell tabell som visar alla versionsposter i det gemensamma versionsarkivet. Gäller endast för versionslagret i tempdb. Mer information finns i sys.dm_tran_version_store (Transact-SQL).

  • sys.dm_tran_version_store_space_usage. Returnerar en virtuell tabell som visar det totala utrymmet i tempdb som används av versionsarkivposter för varje databas. Gäller endast för versionslagret i tempdb. Mer information finns i sys.dm_tran_version_store_space_usage (Transact-SQL).

    Obs

    Det kan vara dyrt att fråga sys.dm_tran_top_version_generators och sys.dm_tran_version_store eftersom båda genomsöker hela versionsarkivet, som kan vara stort. sys.dm_tran_version_store_space_usage är effektivt och inte dyrt att köra eftersom det inte navigerar genom enskilda versionsarkivposter och i stället returnerar aggregerat versionslagerutrymme som förbrukas i tempdb per databas.

  • sys.dm_tran_active_snapshot_database_transactions. Returnerar en virtuell tabell för alla aktiva transaktioner i alla databaser i SQL Server-instansen som använder radversionshantering. Systemtransaktioner visas inte i denna DMV. Mer information finns i sys.dm_tran_active_snapshot_database_transactions (Transact-SQL).

  • sys.dm_tran_transactions_snapshot. Returnerar en virtuell tabell som visar ögonblicksbilder som tas av varje transaktion. Ögonblicksbilden innehåller sekvensnumret för de aktiva transaktioner som använder radversionshantering. Mer information finns i sys.dm_tran_transactions_snapshot (Transact-SQL).

  • sys.dm_tran_current_transaction. Returnerar en enskild rad som visar information om radversionsrelaterade tillstånd för transaktionen i den aktuella sessionen. Mer information finns i sys.dm_tran_current_transaction (Transact-SQL).

  • sys.dm_tran_current_snapshot. Returnerar en virtuell tabell som visar alla aktiva transaktioner när den aktuella ögonblicksbildisoleringstransaktionen startar. Om den aktuella transaktionen använder ögonblicksbildisolering returnerar den här funktionen inga rader. DMV-sys.dm_tran_current_snapshot liknar sys.dm_tran_transactions_snapshot, förutom att den endast returnerar de aktiva transaktionerna för den aktuella ögonblicksbilden. Mer information finns i sys.dm_tran_current_snapshot (Transact-SQL).

  • sys.dm_tran_persistent_version_store_stats. Returnerar statistik för det beständiga versionsarkivet i varje databas som används när accelererad databasåterställning är aktiverad. Mer information finns i sys.dm_tran_persistent_version_store_stats (Transact-SQL).

Prestandaräknare

Följande prestandaräknare övervakar versionsarkivet i tempdbsamt transaktioner med hjälp av radversionshantering. Prestandaräknarna finns i prestandaobjektet SQLServer:Transactions.

  • Ledigt utrymme i tempdb (KB). Övervakar mängden ledigt utrymme i kilobyte (KB) i den tempdb databasen. Det måste finnas tillräckligt med ledigt utrymme i tempdb för att hantera versionsarkivet som stöder ögonblicksbildisolering.

    Följande formel ger en ungefärlig uppskattning av storleken på versionsarkivet. För långvariga transaktioner kan det vara användbart att övervaka genererings- och rensningsfrekvensen för att uppskatta den maximala storleken på versionsarkivet.

    [Storleken på det gemensamma versionsarkivet] = 2 * [versionslagringsdata som genereras per minut] * [den längsta körningstiden (minuter) för transaktionen]

    Den längsta körtiden för transaktioner bör inte inkludera onlineindexeringar. Eftersom dessa åtgärder kan ta lång tid på mycket stora tabeller använder onlineindexversioner ett separat versionslager. Den ungefärliga storleken på versionsarkivet för onlineindexet är lika med mängden data som ändrats i tabellen, inklusive alla index, medan onlineindexversionen är aktiv.

  • Version Store Size (KB). Övervakar storleken i KB för alla versionslagringar i tempdb. Den här informationen hjälper dig att fastställa hur mycket utrymme som behövs i tempdb databas för versionsarkivet. Övervakning av den här räknaren under en viss tidsperiod ger en användbar uppskattning av ytterligare utrymme som behövs för tempdb.

  • versionsgenereringshastighet (KB/s). Övervakar versionsgenereringshastigheten i KB per sekund i alla versionslager i tempdb.

  • version rensningshastighet (KB/s). Övervakar versionsrensningshastigheten i KB per sekund i alla versionslager i tempdb.

    Obs

    Information från versionsgenereringshastighet (KB/s) och versionsrensningshastighet (KB/s) kan användas för att förutsäga tempdb utrymmeskrav.

  • versionslagerenhetsantalet. Övervakar antalet versionslagerenheter.

  • Skapandet av Version Store-enheten. Övervakar det totala antalet versionslagerenheter som skapats för att lagra radversioner sedan instansen startades.

  • enhetstrunkering i Version Store. Övervakar det totala antalet versionslagerenheter som trunkerats sedan instansen startades. En versionslagerenhet trunkeras när SQL Server fastställer att ingen av de versionsrader som lagras i versionslagringsenheten behövs för att köra aktiva transaktioner.

  • Uppdatera konfliktförhållandet. Övervakar förhållandet mellan transaktioner med ögonblicksbilder av uppdateringar som har uppdateringskonflikter med det totala antalet transaktioner med ögonblicksbilder av uppdateringar.

  • längsta körtid för transaktion. Övervakar den längsta körningstiden i sekunder för en transaktion med hjälp av radversioner. Detta kan användas för att avgöra om en transaktion körs under en oväntad tid.

  • Transaktioner. Övervakar det totala antalet aktiva transaktioner. Detta inkluderar inte systemtransaktioner.

  • Ögonblicksbildtransaktioner. Övervakar det totala antalet aktiva ögonblicksbildtransaktioner.

  • Uppdatera ögonblicksbildtransaktioner. Övervakar det totala antalet aktiva ögonblicksbildtransaktioner som utför uppdateringsåtgärder.

  • NonSnapshot-Versionstransaktioner. Övervakar det totala antalet aktiva icke-ögonblicksbildstransaktioner som genererar versionsposter.

    Obs

    Summan av uppdateringstransaktioner för ögonblicksbilder och icke-bildversionstransaktioner representerar det totala antalet transaktioner som deltar i versionsgenereringen. Skillnaden mellan transaktioner med ögonblicksbilder och uppdaterade ögonblicksbildtransaktioner representerar antalet skrivskyddade ögonblicksbildstransaktioner.

Exempel på radversionsbaserad isoleringsnivå

I följande exempel visas skillnader i beteende mellan SNAPSHOT isoleringstransaktioner och READ COMMITTED transaktioner som använder radversionshantering.

A. Arbeta med ögonblicksbildisolering

I det här exemplet läser en transaktion, som körs under SNAPSHOT-isolering, data som sedan ändras av en annan transaktion. Den SNAPSHOT-transaktionen blockerar inte uppdateringsåtgärden som utförs av den andra transaktionen, och den fortsätter att läsa data från den versionerade raden utan att bry sig om ändringen av data. Men när den SNAPSHOT transaktionen försöker ändra de data som redan har ändrats av den andra transaktionen genererar den SNAPSHOT transaktionen ett fel och avslutas.

Vid session 1:

USE AdventureWorks2022;
GO

-- Enable snapshot isolation on the database.
ALTER DATABASE AdventureWorks2022 SET ALLOW_SNAPSHOT_ISOLATION ON;
GO

-- Start a snapshot transaction
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
GO

BEGIN TRANSACTION;

-- This SELECT statement will return
-- 48 vacation hours for the employee.
SELECT BusinessEntityID, VacationHours
FROM HumanResources.Employee
WHERE BusinessEntityID = 4;

Vid session 2:

USE AdventureWorks2022;
GO

-- Start a transaction.
BEGIN TRANSACTION;

-- Subtract a vacation day from employee 4.
-- Update is not blocked by session 1 since
-- under snapshot isolation shared locks are
-- not requested.
UPDATE HumanResources.Employee
SET VacationHours = VacationHours - 8
WHERE BusinessEntityID = 4;

-- Verify that the employee now has 40 vacation hours.
SELECT VacationHours
FROM HumanResources.Employee
WHERE BusinessEntityID = 4;

Vid session 1:

-- Reissue the SELECT statement - this shows
-- the employee having 48 vacation hours. The
-- snapshot transaction is still reading data from
-- the older, versioned row.
SELECT BusinessEntityID, VacationHours
FROM HumanResources.Employee
WHERE BusinessEntityID = 4;

Vid session 2:

-- Commit the transaction; this commits the data
-- modification.
COMMIT TRANSACTION;
GO

Vid session 1:

-- Reissue the SELECT statement - this still
-- shows the employee having 48 vacation hours
-- even after the other transaction has committed
-- the data modification.
SELECT BusinessEntityID, VacationHours
FROM HumanResources.Employee
WHERE BusinessEntityID = 4;

-- Because the data has been modified outside of the
-- snapshot transaction, any further data changes to
-- that data by the snapshot transaction will cause
-- the snapshot transaction to fail. This statement
-- will generate a 3960 error and the transaction will
-- terminate.
UPDATE HumanResources.Employee
SET SickLeaveHours = SickLeaveHours - 8
WHERE BusinessEntityID = 4;

-- Undo the changes to the database from session 1.
-- This will not undo the change from session 2.
ROLLBACK TRANSACTION;
GO

B. Arbeta med READ COMMITTED-isolering med hjälp av radversionshantering

I det här exemplet körs en READ COMMITTED transaktion med radversion samtidigt med en annan transaktion. Den READ COMMITTED transaktionen fungerar annorlunda än en SNAPSHOT transaktion. Precis som en SNAPSHOT transaktion läser READ COMMITTED transaktion versionsrader även efter att den andra transaktionen har ändrat data. Men till skillnad från en SNAPSHOT-transaktion, den READ COMMITTED-transaktionen:

  • Läser de modifierade data efter att den andra transaktionen bekräftar dataändringarna.
  • Kan uppdatera data som ändrats av den andra transaktionen där SNAPSHOT-transaktionen inte kunde.

Vid session 1:

USE AdventureWorks2022;
GO

-- Enable READ_COMMITTED_SNAPSHOT on the database.
-- For this statement to succeed, this session
-- must be the only connection to the AdventureWorks2022
-- database.
ALTER DATABASE AdventureWorks2022 SET READ_COMMITTED_SNAPSHOT ON;
GO

-- Start a read-committed transaction
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
GO

BEGIN TRANSACTION;

-- This SELECT statement will return
-- 48 vacation hours for the employee.
SELECT BusinessEntityID, VacationHours
FROM HumanResources.Employee
WHERE BusinessEntityID = 4;

Vid session 2:

USE AdventureWorks2022;
GO

-- Start a transaction.
BEGIN TRANSACTION;

-- Subtract a vacation day from employee 4.
-- Update is not blocked by session 1 since
-- under read-committed using row versioning shared locks are
-- not requested.
UPDATE HumanResources.Employee
SET VacationHours = VacationHours - 8
WHERE BusinessEntityID = 4;

-- Verify that the employee now has 40 vacation hours.
SELECT VacationHours
FROM HumanResources.Employee
WHERE BusinessEntityID = 4;

Vid session 1:

-- Reissue the SELECT statement - this still shows
-- the employee having 48 vacation hours. The
-- read-committed transaction is still reading data
-- from the versioned row and the other transaction
-- has not committed the data changes yet.
SELECT BusinessEntityID, VacationHours
FROM HumanResources.Employee
WHERE BusinessEntityID = 4;

Vid session 2:

-- Commit the transaction.
COMMIT TRANSACTION;
GO

Vid session 1:

-- Reissue the SELECT statement which now shows the
-- employee having 40 vacation hours. Being
-- read-committed, this transaction is reading the
-- committed data. This is different from snapshot
-- isolation which reads from the versioned row.
SELECT BusinessEntityID, VacationHours
FROM HumanResources.Employee
WHERE BusinessEntityID = 4;

-- This statement, which caused the snapshot transaction
-- to fail, will succeed with read-committed using row versioning.
UPDATE HumanResources.Employee
SET SickLeaveHours = SickLeaveHours - 8
WHERE BusinessEntityID = 4;

-- Undo the changes to the database from session 1.
-- This will not undo the change from session 2.
ROLLBACK TRANSACTION;
GO

Aktivera radversionsbaserade isoleringsnivåer

Databasadministratörer styr inställningarna på databasnivå för radversionshantering med hjälp av databasalternativen READ_COMMITTED_SNAPSHOT och ALLOW_SNAPSHOT_ISOLATION i ALTER DATABASE-instruktionen.

När READ_COMMITTED_SNAPSHOT databasalternativet är inställt på ONaktiveras de mekanismer som används för att stödja alternativet omedelbart. När du anger alternativet READ_COMMITTED_SNAPSHOT tillåts endast anslutningen som kör kommandot ALTER DATABASE i databasen. Det får inte finnas någon annan öppen anslutning i databasen förrän ALTER DATABASE har slutförts. Databasen behöver inte vara i enanvändarläge.

Följande Transact-SQL-instruktion aktiverar READ_COMMITTED_SNAPSHOT:

ALTER DATABASE AdventureWorks2022 SET READ_COMMITTED_SNAPSHOT ON;

När alternativet ALLOW_SNAPSHOT_ISOLATION databas är inställt på ONbörjar inte instansen av databasmotorn generera radversioner för ändrade data förrän alla aktiva transaktioner som har ändrat data i databasen har slutförts. Om det finns aktiva ändringstransaktioner anger databasmotorn tillståndet för alternativet till PENDING_ON. När alla ändringstransaktioner har slutförts ändras tillståndet för alternativet till ON. Användare kan inte starta en SNAPSHOT transaktion i databasen förrän alternativet är ON. På samma sätt passerar databasen ett PENDING_OFF tillstånd när databasadministratören anger ALLOW_SNAPSHOT_ISOLATION alternativet till OFF.

Följande Transact-SQL-instruktion aktiverar ALLOW_SNAPSHOT_ISOLATION:

ALTER DATABASE AdventureWorks2022 SET ALLOW_SNAPSHOT_ISOLATION ON;

I följande tabell visas och beskrivs tillståndet för alternativet ALLOW_SNAPSHOT_ISOLATION. Att använda ALTER DATABASE med alternativet ALLOW_SNAPSHOT_ISOLATION blockerar inte användare som för närvarande har åtkomst till databasdata.

Tillståndet för isolering av SNAPSHOT för den aktuella databasen Beskrivning
OFF Stödet för SNAPSHOT isoleringstransaktioner är inte aktiverat. Inga SNAPSHOT isoleringstransaktioner tillåts.
PENDING_ON Stödet för SNAPSHOT isoleringstransaktioner är i övergångsfas (från OFF till ON). Öppna transaktioner måste slutföras.

Inga SNAPSHOT isoleringstransaktioner tillåts.
ON Stöd för SNAPSHOT-isoleringstransaktioner har aktiverats.

SNAPSHOT transaktioner tillåts.
PENDING_OFF Stödet för SNAPSHOT isoleringstransaktioner är i övergångsfas (från ON till OFF).

SNAPSHOT transaktioner som startats efter den här tiden kan inte komma åt den här databasen. Befintliga SNAPSHOT transaktioner kan fortfarande komma åt den här databasen. Befintliga skrivtransaktioner använder fortfarande versionshantering i den här databasen. Tillståndet PENDING_OFF blir inte OFF förrän alla SNAPSHOT transaktioner som påbörjades när databasen SNAPSHOT isoleringstillstånd var ON har slutförts.

Använd sys.databases katalogvyn för att fastställa tillståndet för databasalternativen för båda radversionerna.

Alla uppdateringar av användartabeller och vissa systemtabeller som lagras i master och msdb generera radversioner.

Alternativet ALLOW_SNAPSHOT_ISOLATION anges automatiskt till ON i master- och msdb-databaserna och kan inte inaktiveras.

Användarna kan inte ange alternativet READ_COMMITTED_SNAPSHOT till ON i master, tempdbeller msdb.

Använd radversionsbaserade isoleringsnivåer

Ramverket för radversion är alltid aktiverat och används av flera funktioner. Förutom att tillhandahålla radversionsbaserade isoleringsnivåer används det för att stödja ändringar som görs i utlösare och i sessioner med Multiple Active Result Sets (MARS) samt för att stödja dataläsningar för online indexoperationer.

Radversionsbaserade isoleringsnivåer aktiveras på databasnivå. Alla program som kommer åt objekt från aktiverade databaser kan köra frågor med hjälp av följande isoleringsnivåer:

  • READ COMMITTED som använder radversionshantering genom att ange databasalternativet READ_COMMITTED_SNAPSHOT till ON enligt följande kodexempel:

    ALTER DATABASE AdventureWorks2022 SET READ_COMMITTED_SNAPSHOT ON;

    När databasen är konfigurerad för READ_COMMITTED_SNAPSHOTanvänder alla frågor som körs under READ COMMITTED isoleringsnivå sig av radversionering, vilket innebär att läsåtgärder inte blockerar uppdateringsåtgärder.

  • SNAPSHOT isolering genom att ställa in databasalternativet ALLOW_SNAPSHOT_ISOLATION till ON enligt följande kodexempel:

    ALTER DATABASE AdventureWorks2022 SET ALLOW_SNAPSHOT_ISOLATION ON;

    När du använder frågor mellan databaser kan en transaktion som körs under SNAPSHOT isolering komma åt tabeller i de databaser som har ALLOW_SNAPSHOT_ISOLATION databasalternativet inställt på ON. Om du vill komma åt tabeller i databaser som inte har ALLOW_SNAPSHOT_ISOLATION databasalternativet inställt på ONmåste isoleringsnivån ändras. I följande kodexempel visas till exempel en SELECT-instruktion som kopplar två tabeller medan den körs under en SNAPSHOT transaktion. En tabell tillhör en databas där SNAPSHOT isolering inte är aktiverad. När SELECT-instruktionen körs under SNAPSHOT isolering kan den inte köras korrekt.

    SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

BEGIN TRANSACTION;

SELECT t1.col5, t2.col5
FROM Table1 as t1
INNER JOIN SecondDB.dbo.Table2 as t2
ON t1.col1 = t2.col2;

    I följande kodexempel visas samma SELECT-instruktion som har ändrats för att ändra transaktionsisoleringsnivån till READ COMMITTED vid åtkomst till en specifik tabell. På grund av den här ändringen körs SELECT-instruktionen framgångsrikt.

    SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

BEGIN TRANSACTION;

SELECT t1.col5, t2.col5
FROM Table1 as t1 WITH (READCOMMITTED)
INNER JOIN SecondDB.dbo.Table2 as t2
ON t1.col1 = t2.col2;

Begränsningar för transaktioner som använder radversionsbaserade isoleringsnivåer

Tänk på följande begränsningar när du arbetar med radversionsbaserade isoleringsnivåer:

  • READ_COMMITTED_SNAPSHOT kan inte aktiveras i tempdb, msdbeller master.

  • Globala temporära tabeller lagras i tempdb. När du kommer åt globala temporära tabeller i en SNAPSHOT transaktion måste något av följande inträffa:

    • Ange databasalternativet ALLOW_SNAPSHOT_ISOLATION till ON i tempdb.
    • Använd ett isoleringstips för att ändra isoleringsnivån för -instruktionen.

  • SNAPSHOT transaktioner misslyckas när:

    • En databas görs endast läsbar efter att SNAPSHOT-transaktionen startar, men innan SNAPSHOT-transaktionen kommer åt databasen.
    • Om du kommer åt objekt från flera databaser har ett databastillstånd ändrats på ett sådant sätt att databasåterställning inträffade efter att en SNAPSHOT transaktion startar, men innan SNAPSHOT transaktionen kommer åt databasen. Till exempel: databasen har angetts till OFFLINE och sedan till ONLINE, databasen stängdes automatiskt och öppnades igen på grund av att alternativet AUTO_CLOSE var inställt på ON, eller databasen kopplades från och kopplades tillbaka.

  • Distribuerade transaktioner, inklusive frågor i distribuerade partitionerade databaser, stöds inte under SNAPSHOT isolering.

  • Databasmotorn behåller inte flera versioner av systemmetadata. DDL-instruktioner (Data Definition Language) för tabeller och andra databasobjekt (index, vyer, datatyper, lagrade procedurer och vanliga språkkörningsfunktioner) ändrar metadata. Om en DDL-instruktion ändrar ett objekt, gör alla samtidiga referenser till objektet under SNAPSHOT isolering att SNAPSHOT transaktionen misslyckas. READ COMMITTED transaktioner har inte den här begränsningen när READ_COMMITTED_SNAPSHOT databasalternativet är inställt på ON.

    En databasadministratör kör till exempel följande ALTER INDEX-instruktion.

    USE AdventureWorks2022;
GO
ALTER INDEX AK_Employee_LoginID ON HumanResources.Employee REBUILD;
GO

    Alla ögonblickstransaktioner som är aktiva när ALTER INDEX-instruktionen körs får ett fel om de försöker referera till tabellen HumanResources.Employee efter att ALTER INDEX-instruktionen har körts. READ COMMITTED transaktioner som använder radversionshantering påverkas inte.

    Obs

    BULK INSERT åtgärder kan orsaka ändringar i måltabellmetadata (till exempel vid inaktivering av begränsningskontroller). När detta inträffar misslyckas samtidiga SNAPSHOT isoleringstransaktioner med åtkomst till massinsatta tabeller.

Anpassa låsning och radversioner

Anpassa tidsgränsen för låset

När en instans av databasmotorn inte kan bevilja ett lås till en transaktion eftersom en annan transaktion redan äger ett konfliktlås på resursen blockeras den första transaktionen i väntan på att det befintliga låset släpps. Som standard finns det ingen tidsgräns för låsväntetid, därför kan en transaktion eventuellt blockeras på obestämd tid.

Obs

Använd vyn sys.dm_os_waiting_tasks dynamisk hantering för att avgöra om en uppgift blockeras och vad som blockerar den. Mer information och exempel finns i Förstå och lösa problem med SQL Server-blockering.

Med inställningen LOCK_TIMEOUT kan ett program ange en maximal tid som en instruktion väntar på en blockerad resurs. När en instruktion har väntat längre än inställningen LOCK_TIMEOUT avbryts den blockerade instruktionen automatiskt och felmeddelandet 1222 (Lock request time-out period exceeded) returneras. Ingen transaktion som innehåller uttalandet återställs dock. Därför måste programmet ha en felhanterare som kan fånga felmeddelandet 1222. Om ett program inte fångar upp felet kan programmet fortsätta ovetande om att en enskild instruktion i en transaktion har annullerats men att transaktionen förblir aktiv. Fel kan inträffa eftersom instruktioner senare i transaktionen kan bero på den instruktion som aldrig kördes.

Om du implementerar en felhanterare som hämtar felmeddelandet 1222 kan ett program hantera timeout-situationen och vidta åtgärdsåtgärder, till exempel: skicka automatiskt om instruktionen som blockerades eller återställa hela transaktionen.

Viktig

Program som använder explicita transaktioner och kräver att transaktionen avslutas när fel 1222 tas emot måste uttryckligen återställa transaktionen som en del av felhanteringen. Utan detta kan andra påståenden oavsiktligt köras på samma session medan transaktionen förblir aktiv, vilket leder till obunden tillväxt av transaktionsloggen och dataförlust om transaktionen återställs senare.

Kör funktionen LOCK_TIMEOUT för att fastställa den aktuella inställningen för @@LOCK_TIMEOUT:

SELECT @@LOCK_TIMEOUT;
GO

Anpassa transaktionsisoleringsnivå

READ COMMITTED är standardisoleringsnivån för databasmotorn. Om ett program måste fungera på en annan isoleringsnivå kan det använda följande metoder för att ange isoleringsnivån:

  • Kör instruktionen SET TRANSACTION ISOLATION LEVEL.
  • ADO.NET program som använder System.Data.SqlClient-namnområdet kan ange ett IsolationLevel alternativ med hjälp av metoden SqlConnection.BeginTransaction.
  • Program som använder ADO kan ange egenskapen Autocommit Isolation Levels.
  • När du startar en transaktion kan program som använder OLE DB anropa ITransactionLocal::StartTransaction med isoLevel inställt på önskad transaktionsisoleringsnivå. När du anger isoleringsnivån i automatiskt återtagandeläge kan program som använder OLE DB ange den DBPROPSET_SESSION egenskapen DBPROP_SESS_AUTOCOMMITISOLEVELS till önskad transaktionsisoleringsnivå.
  • Program som använder ODBC kan ange attributet SQL_COPT_SS_TXN_ISOLATION med hjälp av SQLSetConnectAttr.

När isoleringsnivån har angetts fungerar låsningsbeteendet för alla frågor och DML-instruktioner (datamanipuleringsspråk) i sessionen på den isoleringsnivån. Isoleringsnivån gäller tills sessionen avslutas eller tills isoleringsnivån har angetts till en annan nivå.

I följande exempel ställs isoleringsnivån SERIALIZABLE in:

USE AdventureWorks2022;
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
GO
BEGIN TRANSACTION;

SELECT BusinessEntityID
FROM HumanResources.Employee;

COMMIT;
GO

Isoleringsnivån kan åsidosättas för enskilda frågor eller DML-instruktioner, om det behövs, genom att ange ett tips på tabellnivå. Att ange en vägledning på tabellnivå påverkar inte andra satser i sessionen.

Om du vill fastställa vilken transaktionsisoleringsnivå som för närvarande har angetts använder du instruktionen DBCC USEROPTIONS enligt följande exempel. Resultatuppsättningen kan variera från resultatuppsättningen i systemet.

USE AdventureWorks2022;
GO
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
GO
DBCC USEROPTIONS;
GO

Här är resultatet.

Set Option                   Value
---------------------------- -------------------------------------------
textsize                     2147483647
language                     us_english
dateformat                   mdy
datefirst                    7
...                          ...
Isolation level              repeatable read

(14 row(s) affected)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Låsningstips

Låsningsindikationer kan anges för enskilda tabellreferenser i SELECT, INSERT, UPDATE, DELETE- och MERGE-instruktioner. Tipsen anger vilken typ av låsning eller radversion som instansen av databasmotorn använder för tabelldata. Låsningstips på tabellnivå kan användas när en finare kontroll av de typer av lås som hämtas på ett objekt krävs. Dessa låstips åsidosätter den aktuella transaktionsisoleringsnivån för sessionen.

Obs

Låstips rekommenderas inte för användning när optimerad låsning är aktiverad. Även om tabell- och frågetips respekteras minskar de fördelen med den optimerade låsmekanismen. Mer information finns i Undvik låsningsindikatorer med optimerad låsning.

Mer information om de specifika låsningsindikationerna och deras beteenden finns i Tabelltips (Transact-SQL).

Obs

Vi rekommenderar att låsningstips på tabellnivå används för att ändra standardlåsningsbeteendet endast när det behövs. Att tvinga fram en låsnivå kan påverka samtidigheten negativt.

Databasmotorn kan behöva hämta lås när metadata läses, även när en instruktion bearbetas med ett låstips som förhindrar begäranden om delade lås vid läsning av data. En SELECT-instruktion som körs under READ UNCOMMITTED-isoleringsnivå eller med hjälp av NOLOCK-hint hämtar till exempel inte delningslås när data läses, men kan ibland begära lås när en systemkatalogvy läses. Det innebär att en sådan SELECT-instruktion kan blockeras när en samtidig transaktion ändrar tabellens metadata.

Som du ser i följande exempel, om transaktionsisoleringsnivån är inställd på SERIALIZABLE, och låsningstipset på tabellnivå NOLOCK används med instruktionen SELECT, hämtas inte nyckelintervalllås som vanligtvis används för att underhålla SERIALIZABLE transaktioner.

USE AdventureWorks2022;
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
GO
BEGIN TRANSACTION;
GO
SELECT JobTitle
FROM HumanResources.Employee WITH (NOLOCK);
GO

-- Get information about the locks held by
-- the transaction.
SELECT resource_type,
       resource_subtype,
       request_mode
FROM sys.dm_tran_locks
WHERE request_session_id = @@SPID;

-- End the transaction.
ROLLBACK;
GO

Det enda lås som hämtas som refererar till HumanResources.Employee är ett schemastabilitetslås (Sch-S). I det här fallet garanteras inte längre serialisering.

Alternativet LOCK_ESCALATION för ALTER TABLE undviker tabellås under låseskalering och aktiverar lås på HoBT-partitioner för partitionerade tabeller. Det här alternativet är inte ett låstips och kan användas för att minska låseskalering. Mer information finns i ALTER TABLE (Transact-SQL).

Anpassa låsning för ett index

Databasmotorn använder en strategi för dynamisk låsning som automatiskt väljer den bästa låsningskornigheten för frågor i de flesta fall. Vi rekommenderar att du inte åsidosätter standardlåsningsnivåerna, såvida inte tabell- eller indexåtkomstmönster är väl förstådda och konsekventa, och det finns ett problem med resurskonkurrensen att lösa. Att åsidosätta en låsnivå kan avsevärt hindra samtidig åtkomst till en tabell eller ett index. Om du till exempel bara anger lås på tabellnivå på en stor tabell som användarna har åtkomst till kraftigt kan det orsaka flaskhalsar eftersom användarna måste vänta tills låset på tabellnivå släpps innan de kommer åt tabellen.

Det finns några fall där det kan vara fördelaktigt att inte tillåta låsning av sidor eller rader, om åtkomstmönstren är väl förstådda och konsekventa. Ett databasprogram använder till exempel en uppslagstabell som uppdateras varje vecka i en batchprocess. Samtidiga läsare kommer åt tabellen med ett delat (S) lås och den veckovisa batchuppdateringen kommer åt tabellen med ett exklusivt (X) lås. Om du inaktiverar låsning av sidor och rader i tabellen minskar låsningskostnaderna under hela veckan genom att läsare samtidigt kan komma åt tabellen via delade tabelllås. När batchjobbet körs kan det slutföra uppdateringen effektivt eftersom det hämtar ett exklusivt tabelllås.

Att stänga av sid- och radlåsning kanske eller kanske inte är acceptabelt eftersom den veckovisa batchuppdateringen blockerar samtidiga läsare från att komma åt tabellen medan uppdateringen körs. Om batchjobbet bara ändrar några rader eller sidor kan du ändra låsnivån så att rad- eller sidnivålås tillåts, vilket gör att andra sessioner kan läsa från tabellen utan att blockera. Om batchjobbet har ett stort antal uppdateringar kan det bästa sättet att se till att batchjobbet körs effektivt vara att skaffa ett exklusivt lås på tabellen.

I vissa arbetsbelastningar kan en typ av dödläge uppstå när två samtidiga åtgärder hämtar radlås i samma tabell och sedan blockerar varandra eftersom de båda måste låsa sidan. Genom att inte tillåta radlås tvingas en av åtgärderna att vänta, vilket undviker dödläge. Mer information om dödlägen finns i guiden Dödlägen.

Kornigheten för låsning som används i ett index kan anges med hjälp av CREATE INDEX- och ALTER INDEX-instruktioner. Dessutom kan CREATE TABLE- och ALTER TABLE-instruktionerna användas för att ange låsningskornighet för PRIMARY KEY och UNIQUE begränsningar. För bakåtkompatibilitet kan den lagrade systemproceduren sp_indexoption också ange detaljnivån. Om du vill visa det aktuella låsalternativet för ett visst index använder du funktionen INDEXPROPERTY. Lås på sidnivå, lås på radnivå eller både lås på sidnivå och radnivå kan inte tillåtas för ett visst index.

Otillåtna lås Index som nås av
Sidnivå Lås på radnivå och tabellnivå
Radnivå Lås på sidnivå och tabellnivå
Sidnivå och radnivå Lås på tabellnivå

Avancerad transaktionsinformation

Kapslade transaktioner

Explicita transaktioner kan nästlas. Detta är främst avsett att stödja transaktioner i lagrade procedurer som kan anropas antingen från en process som redan finns i en transaktion eller från processer som inte har någon aktiv transaktion.

I följande exempel visas användningen av kapslade transaktioner. Om TransProc anropas när en transaktion är aktiv, styrs resultatet av den kapslade transaktionen i TransProc av den yttre transaktionen, och dess INSERT-instruktioner begås eller rullas tillbaka baserat på att transaktionen begås eller rullas tillbaka av den yttre transaktionen. Om TransProc körs av en process som inte har en utestående transaktion genomför COMMIT TRANSACTION i slutet av proceduren INSERT-instruktionerna.

SET QUOTED_IDENTIFIER OFF;
GO
SET NOCOUNT OFF;
GO

CREATE TABLE TestTrans
(
ColA INT PRIMARY KEY,
ColB CHAR(3) NOT NULL
);
GO

CREATE PROCEDURE TransProc
  @PriKey INT,
  @CharCol CHAR(3)
AS

BEGIN TRANSACTION InProc;

INSERT INTO TestTrans VALUES (@PriKey, @CharCol);
INSERT INTO TestTrans VALUES (@PriKey + 1, @CharCol);

COMMIT TRANSACTION InProc;
GO

/* Start a transaction and execute TransProc. */
BEGIN TRANSACTION OutOfProc;
GO
EXEC TransProc 1, 'aaa';
GO

/* Roll back the outer transaction, this will
   roll back TransProc's nested transaction. */
ROLLBACK TRANSACTION OutOfProc;
GO

EXECUTE TransProc 3,'bbb';
GO

/*
The following SELECT statement shows only rows 3 and 4 are
still in the table. This indicates that the commit
of the inner transaction from the first EXECUTE statement of
TransProc was overridden by the subsequent roll back of the
outer transaction.
*/
SELECT *
FROM TestTrans;
GO

Genomförande av inre transaktioner ignoreras av databasmotorn när en yttre transaktion är aktiv. Transaktionen genomförs antingen eller återställs baserat på beslutet att genomföra eller återställa i slutet av den yttersta transaktionen. Om den yttre transaktionen bekräftas, bekräftas även de inre kapslade transaktionerna. Om den yttre transaktionen återställs, återställs även alla inre transaktioner, oavsett om de inre transaktionerna har bekräftats individuellt eller inte.

Varje anrop till COMMIT TRANSACTION eller COMMIT WORK gäller för den senast utförda BEGIN TRANSACTION. Om BEGIN TRANSACTION-uttrycken är kapslade gäller en COMMIT-instruktion endast för den senaste kapslade transaktionen, som är den innersta transaktionen. Även om en COMMIT TRANSACTION transaction_name-instruktion i en kapslad transaktion refererar till namnet på den yttre transaktionen, gäller commit endast för den innersta transaktionen.

Det är inte tillåtet att parametern transaction_name i en ROLLBACK TRANSACTION-instruktion hänvisar till den innersta transaktionen i en uppsättning namngivna kapslade transaktioner. transaction_name kan bara referera till transaktionsnamnet för den yttersta transaktionen. Om en ROLLBACK TRANSACTION transaction_name-instruktion med namnet på den yttre transaktionen körs på någon nivå av en uppsättning kapslade transaktioner, återställs alla kapslade transaktioner. Om en ROLLBACK WORK- eller ROLLBACK TRANSACTION-instruktion utan en transaction_name-parameter körs på någon nivå av en uppsättning kapslade transaktioner återställs alla kapslade transaktioner, inklusive den yttersta transaktionen.

Funktionen @@TRANCOUNT registrerar det nuvarande nivån av transaktionskapsling. Varje BEGIN TRANSACTION-instruktion ökar @@TRANCOUNT med en. Varje COMMIT TRANSACTION- eller COMMIT WORK-instruktion minskar @@TRANCOUNT med en. En ROLLBACK WORK- eller ROLLBACK TRANSACTION-instruktion som saknar ett transaktionsnamn återställer alla kapslade transaktioner och minskar @@TRANCOUNT till 0. En ROLLBACK TRANSACTION som använder transaktionsnamnet för den yttersta transaktionen i en uppsättning kapslade transaktioner återställer alla kapslade transaktioner och minskningar @@TRANCOUNT till 0. För att avgöra om du redan är i en transaktion, använd SELECT @@TRANCOUNT för att kontrollera om det är 1 eller mer. Om @@TRANCOUNT är 0 är du inte i en transaktion.

Använda bundna sessioner

Bundna sessioner underlättar samordningen av åtgärder mellan flera sessioner på samma server. Med bundna sessioner kan två eller flera sessioner dela samma transaktion och lås och kan arbeta med samma data utan låskonflikter. Bundna sessioner kan skapas från flera sessioner i samma program eller från flera program med separata sessioner.

För att delta i en bunden session anropar en session sp_getbindtoken eller srv_getbindtoken (via Open Data Services) för att hämta en bindningstoken. En bindningstoken är en teckensträng som unikt identifierar varje bunden transaktion. Bindningstoken skickas sedan till de andra sessionerna för att bindas till den aktuella sessionen. De andra sessionerna binder till transaktionen genom att anropa sp_bindsessionmed hjälp av bindtoken som togs emot från den första sessionen.

Obs

En session måste ha en aktiv användartransaktion för att sp_getbindtoken eller srv_getbindtoken ska lyckas.

Bindningstoken måste överföras från den programkod som skapar den första sessionen till den programkod som sedan binder sina sessioner till den ursprungliga sessionen. Det finns ingen Transact-SQL-instruktion eller API-funktion som ett program kan använda för att hämta bindningstoken för en transaktion som startas av en annan process. Några av de metoder som kan användas för att överföra en bindningstoken är följande:

  • Om alla sessioner initieras från samma programprocess kan bindningstoken lagras i globalt minne eller skickas till funktioner som en parameter.

  • Om sessionerna görs från separata programprocesser kan bindningstoken överföras med hjälp av interprocesskommunikation (IPC), till exempel ett fjärrproceduranrop (RPC) eller dynamiskt datautbyte (DDE).

  • Bindningstoken kan lagras i en tabell i en instans av databasmotorn som kan läsas av processer som vill binda till den första sessionen.

Endast en session i en uppsättning bundna sessioner kan vara aktiv när som helst. Om en session kör en -instruktion på instansen eller har väntande resultat från instansen kan ingen annan session som är bunden till samma token komma åt instansen förrän den aktuella sessionen har slutfört bearbetningen eller avbrutit den aktuella instruktionen. Om instansen är upptagen med att bearbeta en instruktion från en annan av de bundna sessionerna uppstår ett fel som anger att transaktionsutrymmet används och sessionen bör försöka igen senare.

När du binder sessioner behåller varje session sin inställning för isoleringsnivå. Att använda SET TRANSACTION ISOLATION LEVEL för att ändra inställningen för isoleringsnivå för en session påverkar inte inställningen för någon annan session som är bunden till samma token.

Typer av bundna sessioner

De två typerna av bundna sessioner är lokala och distribuerade.

  • Lokal bunden session Tillåter att bundna sessioner delar transaktionsutrymmet för en enskild transaktion i en enda instans av databasmotorn.

  • distribuerad bunden session Tillåter att bundna sessioner delar samma transaktion över två eller flera instanser tills hela transaktionen antingen checkas in eller återställs med hjälp av Microsoft Distributed Transaction Coordinator (MS DTC).

Distribuerade bundna sessioner identifieras inte av en teckensträngsbindningstoken. de identifieras med distribuerade transaktionsidentifieringsnummer. Om en bunden session är involverad i en lokal transaktion och kör en RPC på en fjärrserver med SET REMOTE_PROC_TRANSACTIONS ON, befordras den lokala bundna transaktionen automatiskt till en distribuerad bunden transaktion av MS DTC och en MS DTC-session startas.

När du ska använda bundna sessioner

I tidigare versioner av SQL Server användes bundna sessioner främst för att utveckla utökade lagrade procedurer som måste köra Transact-SQL-instruktioner för den process som anropar dem. Genom att låta anropsprocessen överföra en bindningstoken som en parameter i den utökade lagrade proceduren, kan proceduren ansluta sig till transaktionsutrymmet för anropsprocessen, vilket integrerar den utökade lagrade proceduren med anropsprocessen.

I databasmotorn är lagrade procedurer som skrivs med CLR säkrare, skalbara och stabila än utökade lagrade procedurer. CLR-lagrade procedurer använder SqlContext-objektet för att ansluta till kontexten för den anropande sessionen, inte sp_bindsession.

Bundna sessioner kan användas för att utveckla program med tre nivåer där affärslogik införlivas i separata program som samarbetar med en enda affärstransaktion. Dessa program måste kodas för att noggrant samordna deras åtkomst till en databas. Eftersom de två sessionerna delar samma lås får de två programmen inte försöka ändra samma data samtidigt. Vid varje given tidpunkt kan bara en session utföra arbete som en del av transaktionen, och det kan inte finnas någon parallell körning. Transaktionen kan bara växlas mellan sessioner vid väldefinierade avkastningspunkter, till exempel när alla DML-instruktioner har slutförts och deras resultat har hämtats.

Effektivisera transaktioner

Det är viktigt att hålla transaktionerna så korta som möjligt. När en transaktion startas måste ett databashanteringssystem (DBMS) innehålla många resurser till slutet av transaktionen för att skydda egenskaperna atomicitet, konsekvens, isolering och hållbarhet (ACID). Om data ändras måste de ändrade raderna skyddas med exklusiva lås som hindrar andra transaktioner från att läsa raderna, och exklusiva lås måste hållas kvar tills transaktionen har checkats in eller återställts. Beroende på inställningarna för transaktionsisoleringsnivå kan SELECT-instruktioner erhålla lås som måste hållas kvar tills transaktionen har genomförts eller återställts. Särskilt i system med många användare måste transaktionerna hållas så korta som möjligt för att minska låsningskonkurrationen för resurser mellan samtidiga anslutningar. Långvariga, ineffektiva transaktioner kanske inte är ett problem med ett litet antal användare, men de är mycket problematiska i ett system med tusentals användare. Från och med SQL Server 2014 (12.x) stöder databasmotorn fördröjda varaktiga transaktioner. Fördröjda varaktiga transaktioner kan förbättra skalbarheten och prestandan, men de garanterar inte hållbarhet. Mer information finns i Kontrollera transaktionshållbarhet.

Riktlinjer för kod

Det här är riktlinjerna för att koda effektiva transaktioner:

  • Kräva inte indata från användare under en transaktion. Hämta alla nödvändiga indata från användare innan en transaktion startas. Om ytterligare användarindata krävs under en transaktion återställer du den aktuella transaktionen och startar om transaktionen efter att användarens indata har angetts. Även om användarna svarar omedelbart är de mänskliga reaktionstiderna mycket långsammare än datorhastigheterna. Alla resurser som innehas av transaktionen hålls under extremt lång tid, vilket kan orsaka blockeringsproblem. Om användarna inte svarar förblir transaktionen aktiv och låser kritiska resurser tills de svarar, vilket kanske inte sker på flera minuter eller till och med timmar.

  • Öppna inte en transaktion när du bläddrar igenom data, om det är möjligt. Transaktioner bör inte startas förrän all preliminär dataanalys har slutförts.

  • Håll transaktionen så kort som möjligt. När du vet vilka ändringar som måste göras startar du en transaktion, utför ändringsåtgärderna och genomför eller återställer direkt. Öppna inte transaktionen innan den krävs.

  • För att minska blockeringen bör du överväga att använda en radversionsbaserad isoleringsnivå för frågor som enbart läser data.

  • Använd lägre transaktionsisoleringsnivåer på ett intelligent sätt. Många program kan kodas för att använda READ COMMITTED transaktionsisoleringsnivå. Få transaktioner kräver SERIALIZABLE transaktionsisoleringsnivå.

  • Använd optimistiska samtidighetsalternativ på ett intelligent sätt. I ett system med låg sannolikhet för samtidiga uppdateringar kan kostnaden för att hantera enstaka "någon annan har ändrat dina data efter att du har läst det" vara mycket lägre än omkostnaderna för att alltid låsa rader när de läses.

  • Få åtkomst till minsta möjliga mängd data i en transaktion. Detta minskar antalet låsta rader, vilket minskar konkurrensen mellan transaktioner.

  • Undvik pessimistiska låstips som HOLDLOCK när det är möjligt. Ledtrådar som HOLDLOCK eller SERIALIZABLE isoleringsnivå kan leda till att processer väntar även på delade lås och minska samtidigheten.

  • Undvik att använda implicita transaktioner när det är möjligt. Implicita transaktioner kan introducera oförutsägbart beteende på grund av deras natur. Se implicita transaktioner och samtidighetsproblem.

Implicita transaktioner och undvika samtidighets- och resursproblem

För att förhindra samtidighets- och resursproblem kan du hantera implicita transaktioner noggrant. När du använder implicita transaktioner startar nästa Transact-SQL-instruktion efter COMMIT eller ROLLBACK automatiskt en ny transaktion. Detta kan leda till att en ny transaktion öppnas när programmet bläddrar igenom data, eller till och med när det kräver indata från användaren. När du har slutfört den senaste transaktionen som krävs för att skydda dataändringar inaktiverar du implicita transaktioner tills en transaktion återigen krävs för att skydda dataändringar. Med den här processen kan databasmotorn använda automatiskt återtagandeläge medan programmet bläddrar i data och hämtar indata från användaren.

Dessutom, när SNAPSHOT isoleringsnivån är aktiverad, kommer en ny transaktion inte att hålla lås, men en långvarig transaktion kommer att förhindra att de gamla versionerna tas bort från versionsarkivet.

Hantera långvariga transaktioner

En långvarig transaktion är en aktiv transaktion som inte har slutförts eller återkallats i tid. Om till exempel början och slutet av en transaktion styrs av användaren är en vanlig orsak till en tidskrävande transaktion en användare som startar en transaktion och sedan lämnar medan transaktionen väntar på ett svar från användaren.

En tidskrävande transaktion kan orsaka allvarliga problem för en databas enligt följande:

  • Om en serverinstans stängs av efter att en aktiv transaktion har utfört många icke-samtidiga ändringar kan återställningsfasen för den efterföljande omstarten ta mycket längre tid än den tid som anges av konfigurationsalternativet recovery interval server eller med alternativet ALTER DATABASE ... SET TARGET_RECOVERY_TIME. De här alternativen styr aktiva respektive indirekta kontrollpunkter. Mer information om typer av kontrollpunkter finns i Database checkpoints (SQL Server).

  • Ännu viktigare är att även om en väntande transaktion kan generera väldigt lite logg, förhindrar den loggtrunkering på obestämd tid, vilket gör att transaktionsloggen växer och eventuellt fylls upp. Om transaktionsloggen fylls i kan databasen inte utföra fler skrivningar. Mer information finns i arkitektur och hanteringsguide för SQL Server-transaktionsloggar, Felsöka en fullständig transaktionslogg (SQL Server-fel 9002)och Transaktionsloggen.

Viktig

I Azure SQL Database avslutas inaktiva transaktioner (transaktioner som inte har skrivits till transaktionsloggen på sex timmar) automatiskt för att frigöra resurser.

Identifiera långvariga transaktioner

Om du vill söka efter långvariga transaktioner använder du något av följande:

  • sys.dm_tran_database_transactions

    Den här dynamiska hanteringsvyn returnerar information om transaktioner på databasnivå. För en långvarig transaktion inkluderar kolumner av särskilt intresse tiden för den första loggposten (database_transaction_begin_time), det aktuella tillståndet för transaktionen (database_transaction_state) och loggsekvensnumret (LSN) för start posten i transaktionsloggen (database_transaction_begin_lsn).

    Mer information finns i sys.dm_tran_database_transactions (Transact-SQL).

  • DBCC OPENTRAN

    Med den här instruktionen kan du identifiera användar-ID:t för transaktionens ägare, så att du potentiellt kan spåra källan till transaktionen för lämplig avslutning (kommittera eller rulla tillbaka). För mer information, se DBCC OPENTRAN (Transact-SQL).

Avsluta en transaktion

Om du vill avsluta en transaktion på en viss session använder du instruktionen KILL. Använd dock den här instruktionen mycket noggrant, särskilt när kritiska processer körs. Mer information finns i KILL (Transact-SQL).

Dödlägen

Dödlägen är ett komplext ämne som rör låsning, men skiljer sig från blockering.

Relaterat innehåll