Blokkeringsproblemen oplossen die worden veroorzaakt door escalatie van vergrendelingen in SQL Server

Artikel
04/03/2023

Samenvatting

Escalatie van vergrendeling is het proces van het converteren van veel fijnmazige vergrendelingen (zoals rij- of paginavergrendelingen) naar tabelvergrendelingen. Microsoft SQL Server bepaalt dynamisch wanneer vergrendelingsescalatie moet worden uitgevoerd. Wanneer deze beslissing wordt genomen, houdt SQL Server rekening met het aantal vergrendelingen dat wordt vastgehouden op een bepaalde scan, het aantal vergrendelingen dat wordt vastgehouden door de hele transactie en het geheugen dat wordt gebruikt voor vergrendelingen in het systeem als geheel. Normaal gesproken zorgt het standaardgedrag van SQL Server ervoor dat escalatie van vergrendelingen alleen optreedt op momenten waarop dit de prestaties zou verbeteren 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 andere gebruikers kan blokkeren. In dit artikel wordt beschreven hoe u kunt bepalen of escalatie van vergrendelingen blokkering veroorzaakt en hoe u omgaat met ongewenste escalatie van vergrendelingen.

Originele productversie: SQL Server
Origineel KB-nummer: 323630

Bepalen of escalatie van vergrendeling blokkering veroorzaakt

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

Als de vergrendeling escaleert, controleert u of de geëscaleerde tabelvergrendeling andere gebruikers blokkeert.

Voor meer informatie over het identificeren van de hoofdblokkering en de vergrendelingsresource die wordt vastgehouden door de hoofdblokkering en die andere serverproces-id's (SPID's) blokkeert, raadpleegt u INF: Inzicht in SQL Server blokkeringsproblemen.

Als de vergrendeling die andere gebruikers blokkeert, iets anders is dan een TAB-vergrendeling (op tabelniveau) met de vergrendelingsmodus S (gedeeld) of X (exclusief), is escalatie van de 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 de vergrendeling. Als uw blokkeringsproblemen niet worden veroorzaakt door escalatie van vergrendelingen, raadpleegt u de stappen INF: Inzicht in SQL Server blokkeringsproblemen oplossen.

Escalatie van vergrendeling voorkomen

De eenvoudigste en veiligste methode om escalatie van vergrendelingen te voorkomen, is om transacties kort te houden en de vergrendelingsvoetafdruk van dure query's te verminderen, zodat de drempelwaarden voor escalatie van vergrendelingen 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 meer dan 100.000 oude records uit een audittabel te verwijderen en vervolgens bepaalt u dat de query een escalatie van de vergrendeling heeft veroorzaakt waardoor andere gebruikers zijn geblokkeerd:
```
DELETE FROM LogMessages WHERE LogDate < '20020102';
```
Door deze records een paar honderd tegelijk te verwijderen, kunt u het aantal vergrendelingen per transactie aanzienlijk verminderen. Dit voorkomt escalatie van de vergrendeling. 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 bladwijzerzoekacties kunnen de kans op escalatie van vergrendeling vergroten. Bovendien vergroten deze de kans op impasses en hebben ze een negatieve invloed op gelijktijdigheid en prestaties. Nadat u hebt vastgesteld dat de query die vergrendelingsescalatie 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 de efficiëntie van indexzoekopdrachten te maximaliseren. Bekijk het uitvoeringsplan en maak mogelijk nieuwe niet-geclusterde indexen om de queryprestaties te verbeteren. Zie SQL Server Index Architecture and Design Guide (Handleiding voor indexarchitectuur en -ontwerp) voor meer informatie.

Een doel van deze optimalisatie is om indexzoekopdrachten zo weinig mogelijk rijen te laten retourneren om de kosten van bladwijzerzoekacties te minimaliseren (de selectiviteit van de index voor de query maximaliseren). Als SQL Server schat dat een logische operator bladwijzerzoeker veel rijen retourneert, kan deze een PREFETCH component gebruiken om bladwijzers op te zoeken. Als SQL Server gebruikt PREFETCH voor een bladwijzerzoekactie, moet het transactie-isolatieniveau van een deel van de query worden verhoogd naar 'herhaalbaar lezen' voor een deel van de query. Dit betekent dat wat eruit kan zien als een SELECT instructie op een 'read-committed' isolatieniveau vele duizenden sleutelvergrendelingen kan krijgen (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 'read-commit'-isolatieniveau. Als een component Bladwijzer opzoeken MET PREFETCH de escalatie veroorzaakt, kunt u overwegen kolommen toe te voegen aan de niet-geclusterde index die wordt weergegeven in de logische operator Indexzoeken of de logische operator Indexscan onder de logische operator Bladwijzerzoekactie in het queryplan. Het is mogelijk om een dekindex (een index die alle kolommen in een tabel bevat die in de query zijn gebruikt) te maken, of ten minste een index die de kolommen dekt die zijn gebruikt voor joincriteria of in de WHERE-component als het onpraktisch is om alles op te nemen in de lijst 'kolom selecteren'.

Een geneste luskoppeling kan ook gebruikmaken van PREFETCH, en dit veroorzaakt hetzelfde vergrendelingsgedrag.
Escalatie van vergrendeling kan niet optreden als een andere SPID momenteel een incompatibele tabelvergrendeling vasthoudt. Escalatie van vergrendeling escaleert altijd naar een tabelvergrendeling en nooit naar een paginavergrendeling. Als een escalatiepoging voor vergrendelingen mislukt omdat een andere SPID een incompatibele TAB-vergrendeling bevat, wordt de query die de escalatie heeft geprobeerd, niet geblokkeerd tijdens het wachten op een TAB-vergrendeling. In plaats daarvan blijft het vergrendelingen verkrijgen op het oorspronkelijke, gedetailleerdere niveau (rij, sleutel of pagina), waarbij periodiek extra escalatiepogingen worden uitgevoerd. Daarom is een methode om escalatie van vergrendelingen voor een bepaalde tabel te voorkomen, het verkrijgen en vasthouden van een vergrendeling op een andere verbinding die niet compatibel is met het geëscaleerde vergrendelingstype. Een IX-vergrendeling (intent exclusive) op tabelniveau vergrendelt geen rijen of pagina's, maar is nog steeds niet compatibel met een geëscaleerde S-vergrendeling (gedeeld) of X (exclusief) TAB-vergrendeling. Stel dat u een batchtaak moet uitvoeren die veel rijen in de tabel mytable wijzigt en die blokkering veroorzaakt vanwege escalatie van vergrendelingen. Als deze taak altijd binnen een uur is voltooid, kunt u een Transact-SQL-taak maken die de volgende code bevat en plannen dat de nieuwe taak enkele minuten voor de begintijd van de batchtaak begint:
```
BEGIN TRAN;
SELECT * FROM mytable (UPDLOCK, HOLDLOCK) WHERE 1 = 0;
WAITFOR DELAY '1:00:00';
COMMIT TRAN;
```
Deze query verkrijgt en houdt een IX-vergrendeling op mytable gedurende één uur vast. Dit voorkomt escalatie van de vergrendeling van de tabel gedurende die tijd. Met deze batch worden geen gegevens gewijzigd of andere query's geblokkeerd (tenzij de andere query een tabelvergrendeling afded met behulp van de TABLOCK-hint of als een beheerder pagina- of rijvergrendelingen heeft uitgeschakeld met ALTER INDEX).
Voorkom escalatie van vergrendelingen die wordt veroorzaakt door een gebrek aan SARGability, een relationele databaseterm die wordt gebruikt om te beschrijven of een query indexen kan gebruiken voor predicaten en joinkolommen. Zie Queryoverwegingen voor binnenontwerphandleiding voor meer informatie over SARGability. Een vrij eenvoudige query die niet veel rijen lijkt te vereisen, of misschien één rij, kan nog steeds een hele tabel/index scannen. Dit kan gebeuren als er links van een WHERE-component een functie of berekening is. Voorbeelden waarbij SARG-functionaliteit ontbreekt, zijn impliciete of expliciete conversies van gegevenstypen, de systeemfunctie ISNULL(), een door de gebruiker gedefinieerde functie waarbij de kolom wordt 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 doorgegeven aan de functie. Dit leidt tot een scan van de hele tabel of index en resulteert in het verkrijgen van een groot aantal vergrendelingen. In dergelijke omstandigheden kan SQL Server de escalatiedrempel voor het aantal vergrendelingen bereiken. De oplossing is om te voorkomen dat functies worden gebruikt voor kolommen in de WHERE-component, waardoor SARGable-voorwaarden worden gegarandeerd.

Escalatie van vergrendeling uitschakelen

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

Tabelniveau: U kunt escalatie van vergrendeling uitschakelen op tabelniveau. 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 Vergrendelen:Escalatie gebruiken en de ObjectID2 kolom onderzoeken met behulp van SQL Profiler.
Instantieniveau: U kunt escalatie van vergrendeling uitschakelen door een van de traceringsvlagmen 1211 of 1224 of beide in te schakelen voor het exemplaar. Deze traceringsvlagken schakelen echter alle escalatie van vergrendelingen globaal uit in het geval van SQL Server. Escalatie van vergrendeling dient een nuttig doel in SQL Server door het maximaliseren van de efficiëntie van query's die anders worden vertraagd door de overhead van het verkrijgen en vrijgeven van enkele duizenden vergrendelingen. Escalatie van vergrendeling helpt ook om het vereiste geheugen te minimaliseren om vergrendelingen bij te houden. Het geheugen dat SQL Server dynamisch kunt toewijzen voor vergrendelingsstructuren is eindig. Als u escalatie van vergrendeling uitschakelt en het vergrendelingsgeheugen groot genoeg wordt, kan elke poging om extra vergrendelingen toe te wijzen voor een query mislukken en de volgende foutvermelding genereren:

Fout: 1204, Ernst: 19, Status: 1
De SQL Server kunt momenteel 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.

Opmerking

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 traceringsvlagken (-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-query DBCC TRACEON (1224, -1) uitvoert, wordt de traceringsvlag onmiddellijk van kracht.
Als u echter de -T1211 of -T1224 niet toevoegt als opstartparameter, gaat het effect van een DBCC TRACEON opdracht verloren wanneer de SQL Server-service opnieuw wordt gestart. Als u de traceringsvlag inschakelt, worden toekomstige escalaties van vergrendelingen voorkomen, maar worden vergrendelingsescalaties die al in een actieve transactie zijn opgetreden, niet ongedaan gemaakt.

Als u een vergrendelingshint gebruikt, zoals ROWLOCK, verandert dit alleen het initiële vergrendelingsplan. Vergrendelingshints voorkomen geen escalatie van vergrendelingen.

Drempelwaarden voor escalatie vergrendelen

Escalatie van vergrendeling kan optreden onder een van de volgende voorwaarden:

Geheugendrempel is bereikt : een geheugendrempelwaarde van 40 procent van het vergrendelingsgeheugen is bereikt. Wanneer het vergrendelingsgeheugen 24 procent van de buffergroep overschrijdt, kan een escalatie van de vergrendeling worden geactiveerd. Geheugen vergrendelen is beperkt tot 60 procent van de zichtbare buffergroep. De drempelwaarde voor escalatie van vergrendeling is 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 vergrendeling is uitgeschakeld), mislukken alle pogingen om extra vergrendelingen toe te wijzen en 1204 worden er fouten gegenereerd.
Een vergrendelingsdrempel is bereikt : nadat de geheugendrempelwaarde is gecontroleerd, wordt het aantal vergrendelingen in de huidige tabel of index beoordeeld. Als het aantal groter is dan 5000, wordt een escalatie van de 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 Vergrendelen: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 kolommen en IntegerData2 .

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 vergrendeling. Microsoft raadt u aan deze traceringsvlag alleen in te schakelen om ernstige blokkeringen te beperken die worden veroorzaakt door escalatie van vergrendelingen, terwijl andere opties, zoals de opties die in dit artikel worden besproken, worden onderzocht.

Share via