Blokkeringsproblemen oplossen die worden veroorzaakt door vergrendelingsescalatie in SQL Server

Artikel
01/10/2025

Samenvatting

Escalatie van vergrendelingen is het proces van het converteren van veel fijnmazige vergrendelingen (zoals rij- of paginavergrendelingen) naar tabelvergrendelingen. Microsoft SQL Server bepaalt dynamisch wanneer escalatie moet worden vergrendeld. Wanneer deze beslissing wordt genomen, houdt SQL Server rekening met het aantal vergrendelingen dat op een bepaalde scan wordt bewaard, het aantal vergrendelingen dat wordt bewaard door de hele transactie en het geheugen dat wordt gebruikt voor vergrendelingen in het systeem als geheel. Normaal gesproken veroorzaakt het standaardgedrag van SQL Server escalatie van vergrendelingen alleen op die momenten waarop de prestaties zouden worden verbeterd of wanneer u overmatig geheugen voor systeemvergrendeling moet verminderen tot een redelijker niveau. Sommige toepassings- of queryontwerpen kunnen echter escalatie van vergrendelingen activeren op een moment dat deze actie niet wenselijk is, en de geëscaleerde tabelvergrendeling kan andere gebruikers blokkeren. In dit artikel wordt beschreven hoe u kunt bepalen of escalatie van vergrendelingen blokkeren veroorzaakt en hoe u ongewenste escalatie van vergrendelingen kunt afhandelen.

Oorspronkelijke productversie: SQL Server
Oorspronkelijk KB-nummer: 323630

Bepalen of escalatie van vergrendelingen blokkeert

Escalatie van vergrendeling veroorzaakt niet de meeste blokkeringsproblemen. Als u wilt bepalen of escalatie van vergrendeling plaatsvindt op of bijna het moment waarop u problemen ondervindt, start u een sessie met uitgebreide gebeurtenissen die de lock_escalation gebeurtenis bevat. Als u geen gebeurtenissen ziet lock_escalation , vindt escalatie van vergrendelingen niet plaats op uw server en is de informatie in dit artikel niet van toepassing op uw situatie.

Als escalatie van vergrendeling plaatsvindt, controleert u of de geëscaleerde tabelvergrendeling andere gebruikers blokkeert.

Voor meer informatie over het identificeren van de hoofdblokkering en de vergrendelingsresource die wordt bewaard door de head blocker en die andere serverproces-id's (SPID's) blokkeert, raadpleegt u INF: Informatie over en het oplossen van problemen met blokkerende SQL Server.

Als de vergrendeling die andere gebruikers blokkeert iets anders is dan een TAB-vergrendeling (tabelniveau) met een vergrendelingsmodus van S (gedeeld) of X (exclusief), is escalatie van vergrendeling niet het probleem. Met name als de TAB-vergrendeling een intentievergrendeling is (zoals een vergrendelingsmodus van IS, IU of IX), wordt dit niet veroorzaakt door escalatie van vergrendelingen. Als uw blokkeringsproblemen niet worden veroorzaakt door escalatie van vergrendelingen, raadpleegt u de INF: Informatie over en het oplossen van problemen met het blokkeren van SQL Server- en probleemoplossingsstappen.

Escalatie van vergrendeling voorkomen

De eenvoudigste en veiligste methode om escalatie van vergrendelingen te voorkomen, is door transacties kort te houden en de vergrendelingsvoetafdruk van dure query's te verminderen, zodat de drempels voor vergrendelings escalatie niet worden overschreden. Er zijn verschillende methoden om dit doel te bereiken, waaronder de volgende strategieën:

Grote batchbewerkingen opsplitsen in verschillende kleinere bewerkingen. U voert bijvoorbeeld de volgende query uit om 100.000 oude records uit een audittabel te verwijderen en vervolgens bepaalt u dat de query een escalatie van vergrendeling heeft veroorzaakt die andere gebruikers heeft geblokkeerd:
```
DELETE FROM LogMessages WHERE LogDate < '20020102';
```
Door deze records een paar honderd per keer te verwijderen, kunt u het aantal vergrendelingen dat per transactie wordt verzameld aanzienlijk verminderen. Dit voorkomt escalatie van vergrendelingen. U voert bijvoorbeeld de volgende query uit:
```
DECLARE @done bit = 0;
WHILE (@done = 0)
BEGIN
    DELETE TOP(1000) FROM LogMessages WHERE LogDate < '20020102';
    IF @@rowcount < 1000 SET @done = 1;
END;
```
Verminder de vergrendelingsvoetafdruk van de query door de query zo efficiënt mogelijk te maken. Grote scans of veel opzoekacties voor bladwijzers kunnen de kans op escalatie van vergrendeling vergroten. Bovendien vergroten deze de kans op impasses en beïnvloeden deze gelijktijdigheid en prestaties nadelig. Nadat u hebt vastgesteld dat de query die escalatie van vergrendeling veroorzaakt, zoekt u naar mogelijkheden om nieuwe indexen te maken of kolommen toe te voegen aan een bestaande index om index- of tabelscans te verwijderen en om de efficiëntie van indexzoekopdrachten te maximaliseren. Bekijk het uitvoeringsplan en maak mogelijk nieuwe niet-geclusterde indexen om de queryprestaties te verbeteren. Zie de sql Server-indexarchitectuur en ontwerphandleiding voor meer informatie.

Eén doel van deze optimalisatie is om indexzoekopdrachten zo weinig mogelijk rijen te laten retourneren om de kosten van bladwijzerzoekacties te minimaliseren (maximaliseer de selectiviteit van de index voor de query). Als sql Server schat dat een logische operator Bladwijzerzoeken veel rijen retourneert, kan er een PREFETCH component worden gebruikt voor het opzoeken van bladwijzers. Als SQL Server wel wordt gebruikt PREFETCH voor het opzoeken van bladwijzers, moet het isolatieniveau van de transactie van een deel van de query worden verhoogd naar 'herhaalbare leesbewerking' voor een deel van de query. Dit betekent dat wat eruit kan zien als een SELECT instructie op een isolatieniveau met leesbewerkingen vele duizenden sleutelvergrendelingen kan verkrijgen (op zowel de geclusterde index als één niet-geclusterde index). Dit kan ertoe leiden dat een dergelijke query de drempelwaarden voor escalatie van vergrendeling overschrijdt. Dit is met name belangrijk als u merkt dat de geëscaleerde vergrendeling een gedeelde tabelvergrendeling is, hoewel deze niet vaak worden gezien op het standaard isolatieniveau 'read-commit'. Als een component Bladwijzerzoeken MET PREFETCH de escalatie de escalatie veroorzaakt, kunt u kolommen toevoegen aan de niet-geclusterde index die wordt weergegeven in indexzoeken, of de logische operator Indexscan onder de logische operator Bladwijzerzoeken in het queryplan. Het is mogelijk om een dekkingsindex te maken (een index die alle kolommen in een tabel bevat die in de query zijn gebruikt), of ten minste een index die de kolommen omvat die zijn gebruikt voor joincriteria of in de WHERE-component als het niet praktisch is om alles op te nemen in de lijst 'kolom selecteren'.

Een Geneste Lus-join kan ook worden gebruikt PREFETCHen dit veroorzaakt hetzelfde vergrendelingsgedrag.
Escalatie van vergrendeling kan niet optreden als een andere SPID momenteel een incompatibele tabelvergrendeling bevat. Escalatie van vergrendeling escaleert altijd naar een tabelvergrendeling en nooit naar een paginavergrendeling. Als een escalatiepoging voor vergrendeling mislukt omdat een andere SPID een niet-compatibele TAB-vergrendeling bevat, wordt de query die de escalatie heeft geprobeerd, niet geblokkeerd terwijl wordt gewacht op een TAB-vergrendeling. In plaats daarvan blijft het vergrendelingen verkrijgen op het oorspronkelijke, gedetailleerdere niveau (rij, sleutel of pagina), waardoor er regelmatig extra escalatiepogingen worden uitgevoerd. Een methode om escalatie van vergrendelingen in een bepaalde tabel te voorkomen, is door een vergrendeling op een andere verbinding te verkrijgen en vast te houden die niet compatibel is met het geëscaleerde vergrendelingstype. Een IX-vergrendeling (intentie exclusief) op tabelniveau vergrendelt geen rijen of pagina's, maar is nog steeds niet compatibel met een geëscaleerde S (gedeeld) of X (exclusief) TAB-vergrendeling. Stel dat u een batchtaak moet uitvoeren waarmee veel rijen in de tabel mytable worden gewijzigd en dat de blokkering vanwege escalatie van de vergrendeling is veroorzaakt. Als deze taak altijd binnen een uur is voltooid, kunt u een Transact-SQL-taak met de volgende code maken en de nieuwe taak plannen om enkele minuten vóór de begintijd van de batchtaak te beginnen:
```
BEGIN TRAN;
SELECT * FROM mytable WITH (UPDLOCK, HOLDLOCK) WHERE 1 = 0;
WAITFOR DELAY '1:00:00';
COMMIT TRAN;
```
Deze query verkrijgt en bewaart een IX-vergrendeling voor mijn tabel gedurende één uur. Dit voorkomt escalatie van de vergrendeling van de tabel gedurende die tijd. In deze batch worden geen gegevens gewijzigd of andere query's geblokkeerd (tenzij de andere query een tabelvergrendeling dwingt met behulp van de TABLOCK-hint of als een beheerder pagina- of rijvergrendelingen heeft uitgeschakeld met ALTER INDEX).
Blokkeer escalatie veroorzaakt door gebrek aan SARGability, een relationele databaseterm die wordt gebruikt om te beschrijven of een query indexen voor predicaten en joinkolommen kan gebruiken. Zie Overwegingen voor query's in ontwerphandleidingen voor meer informatie over SARGability. Een vrij eenvoudige query die niet veel rijen aanvraagt , of misschien één rij, kan bijvoorbeeld nog steeds een hele tabel/index scannen. Dit kan gebeuren als er een functie of berekening aan de linkerkant van een WHERE-component staat. Dergelijke voorbeelden die geen SARGability hebben, zijn impliciete of expliciete conversies van gegevenstypen, de systeemfunctie ISNULL(), een door de gebruiker gedefinieerde functie met de kolom die is doorgegeven als een parameter of een berekening op de kolom, zoals WHERE CONVERT(INT, column1) = @a of WHERE Column1*Column2 = 5. In dergelijke gevallen kan de query de bestaande index niet ZOEKEN, zelfs niet als deze de juiste kolommen bevat, omdat alle kolomwaarden eerst moeten worden opgehaald en aan de functie moeten worden doorgegeven. Dit leidt tot een scan van de hele tabel of index en resulteert in het verkrijgen van een groot aantal vergrendelingen. In dergelijke gevallen kan SQL Server de escalatiedrempel voor het aantal vergrendelingen bereiken. De oplossing is om te voorkomen dat functies worden gebruikt op basis van kolommen in de WHERE-component, waardoor SARGable voorwaarden worden gegarandeerd.

Escalatie van vergrendeling uitschakelen

Hoewel het mogelijk is om escalatie van vergrendelingen in SQL Server uit te schakelen, raden we dit niet aan. Gebruik in plaats daarvan de preventiestrategieën die worden beschreven in de sectie Escalatie van vergrendeling voorkomen.

Tabelniveau: U kunt escalatie van vergrendeling op tabelniveau uitschakelen. Zie ALTER TABLE ... SET (LOCK_ESCALATION = DISABLE). Bekijk de T-SQL-query's om te bepalen welke tabel u wilt targeten. Als dat niet mogelijk is, gebruikt u Uitgebreide gebeurtenissen, schakelt u de lock_escalation gebeurtenis in en bekijkt u de kolom object_id . U kunt ook de gebeurtenis Lock:Escalatie gebruiken en de ObjectID2 kolom onderzoeken met behulp van SQL Profiler.
Instantieniveau: u kunt escalatie van vergrendeling uitschakelen door de traceringsvlagmen 1211 of 1224 of beide voor het exemplaar in te schakelen. Deze traceringsvlagmen schakelen echter alle escalatie van vergrendelingen wereldwijd uit in het exemplaar van SQL Server. Escalatie van vergrendeling is een nuttig doel in SQL Server door de efficiëntie van query's te maximaliseren die anders worden vertraagd door de overhead van het verkrijgen en vrijgeven van enkele duizenden vergrendelingen. Vergrendelings escalatie helpt ook om het vereiste geheugen te minimaliseren om vergrendelingen bij te houden. Het geheugen dat SQL Server dynamisch kan toewijzen voor vergrendelingsstructuren is eindig. Als u escalatie van vergrendelingen uitschakelt en het vergrendelingsgeheugen groot genoeg wordt, kan elke poging om extra vergrendelingen toe te wijzen voor een query mislukken en genereert u de volgende foutvermelding:

Fout: 1204, Ernst: 19, Status: 1
De SQL Server kan op dit moment geen LOCK-resource verkrijgen. Voer uw instructie opnieuw uit wanneer er minder actieve gebruikers zijn of vraag de systeembeheerder om de SQL Server-vergrendeling en -geheugenconfiguratie te controleren.

Notitie

Wanneer er een 1204-fout optreedt, wordt de verwerking van de huidige instructie gestopt en wordt de actieve transactie teruggedraaid. Het terugdraaien zelf kan gebruikers blokkeren of een lange hersteltijd van de database veroorzaken als u de SQL Server-service opnieuw start.

U kunt deze traceringsvlagmen (-T1211 of -T1224) toevoegen met behulp van SQL Server Configuration Manager. U moet de SQL Server-service opnieuw starten om een nieuwe opstartparameter van kracht te laten worden. Als u de DBCC TRACEON (1211, -1) of DBCC TRACEON (1224, -1) query uitvoert, wordt de traceringsvlag onmiddellijk van kracht.
Als u de -T1211 of -T1224 echter niet toevoegt als opstartparameter, gaat het effect van een DBCC TRACEON opdracht verloren wanneer de SQL Server-service opnieuw wordt opgestart. Als u de traceringsvlag inschakelt, voorkomt u toekomstige escalaties van vergrendelingen, maar er worden geen vergrendelings escalaties omgekeerd die al zijn opgetreden in een actieve transactie.

Als u een vergrendelingshint gebruikt, zoals ROWLOCK, verandert dit alleen het eerste vergrendelingsplan. Vergrendelingshints verhinderen geen escalatie van vergrendelingen.

Escalatiedrempels vergrendelen

Escalatie van vergrendelingen kan zich voordoen onder een van de volgende omstandigheden:

Geheugendrempel wordt bereikt : er wordt een geheugendrempel van 40 procent van het vergrendelingsgeheugen bereikt. Wanneer het vergrendelingsgeheugen groter is dan 24 procent van de buffergroep, kan een escalatie van vergrendelingen worden geactiveerd. Geheugen vergrendelen is beperkt tot 60 procent van de zichtbare buffergroep. De drempelwaarde voor escalatie van vergrendelingen wordt ingesteld op 40 procent van het vergrendelingsgeheugen. Dit is 40 procent van 60 procent van de buffergroep of 24 procent. Als het vergrendelingsgeheugen de limiet van 60 procent overschrijdt (dit is veel waarschijnlijker als escalatie van vergrendelingen is uitgeschakeld), mislukken alle pogingen om extra vergrendelingen toe te wijzen en 1204 worden er fouten gegenereerd.
Er wordt een vergrendelingsdrempel bereikt : nadat de geheugendrempel is gecontroleerd, wordt het aantal vergrendelingen dat is verkregen in de huidige tabel of index beoordeeld. Als het aantal groter is dan 5000, wordt een escalatie van vergrendeling geactiveerd.

Als u wilt weten welke drempelwaarde is bereikt, gebruikt u Uitgebreide gebeurtenissen, schakelt u de lock_escalation gebeurtenis in en bekijkt u de kolommen escalated_lock_count en escalation_cause . U kunt ook de gebeurtenis Lock:Escalatie gebruiken en de EventSubClass waarde onderzoeken, waarbij '0 - LOCK_THRESHOLD' aangeeft dat de instructie de drempelwaarde voor vergrendeling heeft overschreden en '1 - MEMORY_THRESHOLD' aangeeft dat de instructie de geheugendrempel heeft overschreden. Bekijk ook de IntegerData en IntegerData2 kolommen.

Aanbevelingen

De methoden die worden besproken in de sectie Escalatie van vergrendeling voorkomen, zijn betere opties dan het uitschakelen van escalatie op tabel- of exemplaarniveau. Bovendien produceren de preventieve methoden over het algemeen betere prestaties voor de query dan het uitschakelen van escalatie van vergrendelingen. Microsoft raadt u aan deze traceringsvlag alleen in te schakelen om ernstige blokkeringen te beperken die worden veroorzaakt door escalatie van vergrendeling, terwijl andere opties, zoals die in dit artikel worden besproken, worden onderzocht.

Delen via