Lösa blockeringsproblem som orsakas av låseskalering i SQL Server

Artikel
04/03/2023

Sammanfattning

Låseskalering är processen att konvertera många detaljerade lås (till exempel rad- eller sidlås) till tabelllås. Microsoft SQL Server avgör dynamiskt när eskalering ska utföras. När det här beslutet fattas tar SQL Server hänsyn till antalet lås som hålls kvar vid en viss genomsökning, antalet lås som innehas av hela transaktionen och det minne som används för lås i systemet som helhet. Vanligtvis sker SQL Server standardbeteende endast vid de tillfällen då det skulle förbättra prestandan eller när du måste minska överdrivet systemlåsminne till en mer rimlig nivå. Vissa program- eller frågedesigner kan dock utlösa låseskalering vid en tidpunkt då den här åtgärden inte är önskvärd, och det eskalerade tabelllåset kan blockera andra användare. Den här artikeln beskriver hur du avgör om låseskalering orsakar blockering och hur du hanterar oönskad låseskalering.

Ursprunglig produktversion: SQL Server
Ursprungligt KB-nummer: 323630

Avgöra om låseskalering orsakar blockering

Låseskalering orsakar inte de flesta blockerande problem. Om du vill ta reda på om låseskalering sker vid eller nära tidpunkten när du upplever blockeringsproblem startar du en Extended Events-session som innehåller lock_escalation händelsen. Om du inte ser några lock_escalation händelser sker inte låseskalering på servern och informationen i den här artikeln gäller inte för din situation.

Om låseskalering sker kontrollerar du att det eskalerade tabelllåset blockerar andra användare.

Mer information om hur du identifierar huvudblockeraren och låsresursen som innehas av huvudblockeraren och som blockerar andra serverprocess-ID:er (SPID:er) finns i INF: Förstå och lösa SQL Server blockerande problem.

Om låset som blockerar andra användare är något annat än ett TAB-lås (tabellnivå) som har låsläget S (delat) eller X (exklusivt) är låseskalering inte problemet. Särskilt om TAB-låset är ett avsiktslås (till exempel ett låsläge för IS, IU eller IX) orsakas det inte av låseskalering. Om dina blockeringsproblem inte orsakas av låseskalering läser du felsökningsstegen INF: Förstå och lösa SQL Server blockerande problem.

Förhindra låseskalering

Den enklaste och säkraste metoden för att förhindra låseskalering är att hålla transaktionerna korta och minska låsfotavtrycket för dyra frågor så att tröskelvärdena för låseskalering inte överskrids. Det finns flera metoder för att uppnå det här målet, inklusive följande strategier:

Dela upp stora batchåtgärder i flera mindre åtgärder. Du kan till exempel köra följande fråga för att ta bort över 100 000 gamla poster från en granskningstabell och sedan fastställa att frågan orsakade en låseskalering som blockerade andra användare:
```
DELETE FROM LogMessages WHERE LogDate < '20020102';
```
Genom att ta bort dessa poster några hundra åt gången kan du avsevärt minska antalet lås som ackumuleras per transaktion. Detta förhindrar låseskalering. Du kan till exempel köra följande fråga:
```
DECLARE @done bit = 0;
WHILE (@done = 0)
BEGIN
    DELETE TOP(1000) FROM LogMessages WHERE LogDate < '20020102';
    IF @@rowcount < 1000 SET @done = 1;
END;
```
Minska frågans låsfotavtryck genom att göra frågan så effektiv som möjligt. Stora genomsökningar eller många bokmärkessökningar kan öka risken för låseskalering. Dessutom ökar dessa risken för dödlägen och påverkar samtidighet och prestanda negativt. När du har identifierat att frågan som orsakar låseskalering letar du upp möjligheter att skapa nya index eller lägga till kolumner i ett befintligt index för att ta bort index- eller tabellgenomsökningar och maximera effektiviteten för indexsökningar. Granska körningsplanen och skapa eventuellt nya icke-klustrade index för att förbättra frågeprestanda. Mer information finns i arkitektur- och designguiden för SQL Server index.

Ett mål med den här optimeringen är att göra så att indexsökningar returnerar så få rader som möjligt för att minimera kostnaden för bokmärkessökningar (maximera indexets selektivitet för frågan). Om SQL Server uppskattar att en logisk operator för bokmärkessökning returnerar många rader kan den använda en PREFETCH -sats för att göra bokmärkessökningen. Om SQL Server använder PREFETCH för en bokmärkessökning måste den öka transaktionsisoleringsnivån för en del av frågan till "repeterbar läsning" för en del av frågan. Det innebär att vad som kan se ut som en SELECT instruktion på isoleringsnivån "read-committed" kan hämta tusentals nyckellås (både för det klustrade indexet och ett icke-grupperat index). Detta kan göra att en sådan fråga ö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, även om de vanligtvis inte visas på standardisoleringsnivån "read-committed". Om en Bookmark Lookup WITH-sats PREFETCH orsakar eskalering kan du överväga att lägga till kolumner i det icke-klustrade indexet som visas i indexsökningen eller den logiska operatorn Indexgenomsökning under den logiska operatorn Bokmärkessökning i frågeplanen. 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 listan "välj kolumn".

En kapslad loopkoppling kan också använda PREFETCH, 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 eskalerar alltid till ett tabelllås och aldrig till ett sidlås. Om ett försök till låseskalering misslyckas eftersom en annan SPID innehåller ett inkompatibelt TAB-lås blockeras inte frågan som försökte eskalering i väntan på ett TAB-lå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 på 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 IX-lås (exklusiv avsikt) på tabellnivå låser inte några rader eller sidor, men det är fortfarande inte kompatibelt med ett eskalerat S-lås (delat) eller X (exklusivt) TAB-lås. Anta till exempel att du måste köra ett batchjobb som ändrar många rader i tabellen mytable och som orsakade blockering på grund av låseskalering. Om det här jobbet alltid avslutas på mindre än en timme kan du skapa ett Transact-SQL-jobb som innehåller följande kod och schemalägga det nya jobbet så att det startar flera minuter innan batchjobbets starttid:
```
BEGIN TRAN;
SELECT * FROM mytable (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. Detta förhindrar låseskalering i 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 hjälp av TABLOCK-tipset eller om en administratör har inaktiverat sid- eller radlås med ALTER INDEX).
Eliminera låseskalering på grund av brist på SARGability, en relationsdatabasterm som används för att beskriva om en fråga kan använda index för predikat och kopplingskolumner. Mer information om SARGability finns i Frågeöverväganden i designguiden. Till exempel kan en ganska enkel fråga som inte verkar begära många rader – eller kanske en enda rad – fortfarande genomsöka en hel tabell/index. Detta kan inträffa om det finns en funktion eller beräkning på vänster sida av en WHERE-sats. Exempel som saknar SARGability är implicita eller explicita datatypskonverteringar, systemfunktionen ISNULL(), en användardefinierad funktion med kolumnen som skickas som en parameter eller en beräkning i kolumnen, till exempel WHERE CONVERT(INT, column1) = @a eller WHERE Column1*Column2 = 5. I sådana fall kan frågan inte SÖKA det befintliga indexet, även om den innehåller lämpliga kolumner, eftersom alla kolumnvärden måste hämtas först och skickas till funktionen. Detta leder till en genomsökning av hela tabellen eller indexet och resulterar i förvärv av ett stort antal lås. I sådana fall kan SQL Server nå eskaleringströskeln för antal lås. Lösningen är att undvika att använda funktioner mot kolumner i WHERE-satsen, vilket säkerställer SARGable-villkor.

Inaktivera låseskalering

Även om det går att inaktivera låseskalering i SQL Server rekommenderar vi inte det. Använd i stället de förebyggande strategier som beskrivs i avsnittet Förhindra låseskalering .

Tabellnivå: Du kan inaktivera låseskalering på tabellnivå. Se ALTER TABLE ... SET (LOCK_ESCALATION = DISABLE). För att avgöra vilken tabell som ska riktas undersöker du T-SQL-frågorna. Om det inte är möjligt använder du Utökade händelser, aktiverar händelsen lock_escalation och undersöker kolumnen object_id . Du kan också använda händelsen Lock:Escalation och granska ObjectID2 kolumnen med hjälp av SQL Profiler.
Instansnivå: Du kan inaktivera låseskalering genom att aktivera antingen spårningsflaggorna 1211 eller 1224 eller båda för instansen. Dessa spårningsflaggor inaktiverar dock all låseskalering globalt i instansen av SQL Server. Låseskalering har ett användbart syfte i SQL Server genom att maximera effektiviteten för frågor som annars saktas ned av kostnaderna 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. Minnet som SQL Server dynamiskt kan allokera för låsstrukturer är begränsat. Om du inaktiverar låseskalering och låsminnet blir tillräckligt stort kan därför alla försök att allokera ytterligare lås för en fråga misslyckas och generera följande felpost:

Fel: 1204, Allvarlighetsgrad: 19, Tillstånd: 1
SQL Server kan inte hämta en LOCK-resurs just nu. Kör instruktionen igen när det finns färre aktiva användare eller be systemadministratören att kontrollera SQL Server lås- och minneskonfiguration.

Obs!

När ett 1204-fel 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 orsaka en lång databasåterställningstid om du startar om SQL Server-tjänsten.

Du kan lägga till dessa spårningsflaggor (-T1211 eller -T1224) med hjälp av Konfigurationshanteraren för SQL Server. Du måste starta om SQL Server-tjänsten för att en ny startparameter ska börja gälla. Om du kör DBCC TRACEON (1211, -1) frågan eller DBCC TRACEON (1224, -1) börjar spårningsflaggan gälla omedelbart.
Men om du inte lägger till parametern -T1211 eller -T1224 som startparameter försvinner effekten av ett DBCC TRACEON kommando när SQL Server-tjänsten startas om. Om du aktiverar spårningsflaggan förhindras eventuella framtida låseskaleringar, men det återställer inte några låseskaleringar som redan har inträffat i en aktiv transaktion.

Om du använder ett låstips, till exempel ROWLOCK, ändrar detta bara den ursprungliga låsplanen. Låstips förhindrar inte låseskalering.

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

Låseskalering kan inträffa under något av följande villkor:

Tröskelvärdet för minne har uppnåtts – Ett minneströskelvärde på 40 procent av låsminnet nås. När låsminnet överskrider 24 procent av buffertpoolen kan en låseskalering utlösas. Låsminnet är begränsat till 60 procent av den synliga buffertpoolen. Tröskelvärdet för låseskalering anges till 40 procent av låsminnet. Det här är 40 procent av 60 procent av buffertpoolen, eller 24 procent. Om låsminnet överskrider gränsen på 60 procent (detta är mycket mer troligt om låseskalering inaktiveras), misslyckas alla försök att allokera ytterligare lås och 1204 fel genereras.
Ett låströskelvärde nås – När tröskelvärdet för minne har markerats utvärderas antalet lås som hämtas i den aktuella tabellen eller indexet. Om antalet överskrider 5 000 utlöses en låseskalering.

För att förstå vilket tröskelvärde som uppnåddes använder du Utökade händelser, aktiverar händelsen lock_escalation och undersöker kolumnerna escalated_lock_count och escalation_cause . Du kan också använda händelsen Lås:eskalering och undersöka EventSubClass värdet, där "0 – LOCK_THRESHOLD" anger att instruktionen överskred låströskeln och "1 – MEMORY_THRESHOLD" anger att instruktionen överskred minneströskeln. Granska även kolumnerna IntegerData och IntegerData2 .

Rekommendationer

De metoder som beskrivs i avsnittet Förhindra låseskalering är bättre alternativ än att inaktivera eskalering på tabell- eller instansnivå. Dessutom ger förebyggande metoder vanligtvis bättre prestanda för frågan än inaktivering av låseskalering. Microsoft rekommenderar att du aktiverar den här spårningsflaggan endast för att minimera allvarlig blockering som orsakas av låseskalering medan andra alternativ, till exempel de som beskrivs i den här artikeln, undersöks.