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

Artikel
01/10/2025

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 vid en viss genomsökning, antalet lås som hålls av hela transaktionen och det minne som används för lås i systemet som helhet. Vanligtvis leder SQL Server:s standardbeteende till att låseskalering endast sker 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 den tidpunkt då 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) finns i INF: Förstå och lösa problem med SQL Server-blockering.

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 inte låseskalering problemet. Särskilt om TAB-låset är ett avsiktslås (till exempel ett låsläge för IS, IU eller IX) orsakas detta inte av låseskalering. Om blockeringsproblemen inte orsakas av låseskalering kan du läsa felsökningsstegen INF: Förstå och lösa sql server-blockeringsproblem .

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 efter 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-illustrerade index för att förbättra frågeprestanda. Mer information finns i ARKITEKTUR och designguide för SQL Server-index.

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 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å en "skrivskyddad" isoleringsnivå kan hämta tusentals nyckellås (både på klustrat index 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å standardnivå för "skrivskyddad" isolering. Om en BOOKMARK Lookup WITH-sats PREFETCH orsakar eskalering kan du överväga att lägga till kolumner i det icke-illustrerade 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 att låsa eskalering misslyckas eftersom en annan SPID har 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 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 IX-lås (exklusiv avsikt) på tabellnivå låser inte några rader eller sidor, men det är fortfarande inte kompatibelt med ett eskalerat fliklås för S (delad) eller X (exklusiv). 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 WITH (UPDLOCK, HOLDLOCK) WHERE 1 = 0;
WAITFOR DELAY '1:00:00';
COMMIT TRAN;
```
Den här frågan hämtar och har ett IX-lås på mytable i en timme. Detta 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 hjälp av TABLOCK-tipset eller om en administratör har inaktiverat sida eller radlås med ALTER INDEX).
Eliminera låseskalering på grund av bristande 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 efter det befintliga indexet, även om det 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å tröskelvärdet för eskalering av låsantal. 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 är möjligt att inaktivera låseskalering i SQL Server rekommenderar vi det inte. 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 tjänar ett användbart syfte i SQL Server 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 SQL Server dynamiskt kan allokera för låsstrukturer är begränsat. Om du inaktiverar låseskalering och låsminnet växer 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 för närvarande inte hämta en LOCK-resurs. Kör instruktionen igen när det finns färre aktiva användare eller be systemadministratören att kontrollera SQL Server-låset och minneskonfigurationen.

Kommentar

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 start går effekten av ett DBCC TRACEON kommando förlorad när SQL Server-tjänsten startas om. Om du aktiverar spårningsflaggan förhindras eventuella framtida låseskaleringar, men det omvänt inte några låseskaleringar som redan har inträffat i en aktiv transaktion.

Om du använder ett låstips, till exempel ROWLOCK, ändras 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 minneströskelvärdet har markerats utvärderas antalet lås som hämtats 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 de förebyggande metoderna i allmänhet bättre prestanda för frågan än att inaktivera låseskalering. Microsoft rekommenderar att du endast aktiverar den här spårningsflaggan 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.

Dela via