Vergrendelen is essentieel voor een succesvolle verwerking van SQL Server transacties en het is ontworpen om SQL Server naadloos te laten werken in een multi-user omgeving. Locking is de manier waarop SQL Server de transactie gelijktijdigheid beheert. In essentie zijn “locks” in-memory structuren die eigenaars, types en de hash van de bron hebben die het moet beschermen. Een lock als een in-memory structuur is 96 bytes groot.
Om het locken in SQL Server beter te begrijpen, is het belangrijk te begrijpen dat locking is ontworpen om de integriteit van de gegevens in de database te waarborgen, omdat het elke SQL Server transactie dwingt de ACID test te doorstaan.
De ACID test bestaat uit 4 eisen die elke transactie succesvol moet doorstaan:
- Atomiciteit – vereist dat een transactie die twee of meer discrete delen van informatie omvat, alle delen of geen enkel deel moet committen
- Consistentie – vereist dat een transactie een geldige staat van nieuwe gegevens moet creëren, of het moet alle gegevens terugrollen naar de staat die bestond voordat de transactie werd uitgevoerd
- Isolatie – vereist dat een transactie die nog steeds loopt en nog niet alle gegevens heeft gecommit, geïsoleerd moet blijven van alle andere transacties
- Duurzaamheid – vereist dat vastgelegde gegevens moeten worden opgeslagen met een methode die alle gegevens in de juiste staat bewaart en beschikbaar houdt voor een gebruiker, zelfs in geval van een storing
SQL Server locking is het essentiële onderdeel van de isolatie-eis en dient om de objecten die worden beïnvloed door een transactie te vergrendelen. Zolang objecten geblokkeerd zijn, voorkomt SQL Server dat andere transacties gegevens wijzigen die zijn opgeslagen in objecten die onder de opgelegde blokkade vallen. Zodra het slot wordt opgeheven door het vastleggen van de wijzigingen of door het terugdraaien van wijzigingen naar de beginstand, wordt het andere transacties toegestaan de vereiste gegevens te wijzigen.
Vertaald naar de SQL Server taal betekent dit dat wanneer een transactie het slot op een object oplegt, alle andere transacties die toegang tot dat object nodig hebben, gedwongen worden te wachten totdat het slot wordt vrijgegeven en dat wachten wordt geregistreerd met het adequate wachttype
SQL Server-sloten kunnen worden gespecificeerd via de slotmodi en slotgranulariteit
Slotmodi
De slotmodus beschouwt verschillende vergrendelingstypen die kunnen worden toegepast op een bron die moet worden vergrendeld:
- Exclusief (X)
- Gedeeld (S)
- Update (U)
- Intentie (I)
- Schema (Sch)
- Bulkupdate (BU)
Exclusief slot (X) – Dit vergrendelingstype, zorgt ervoor dat een pagina of rij uitsluitend wordt gereserveerd voor de transactie die het exclusieve slot heeft opgelegd, zolang de transactie het slot behoudt.
Het exclusieve slot wordt door de transactie opgelegd, wanneer deze de gegevens van de pagina of rij wil wijzigen, hetgeen het geval is bij DML verklaringen DELETE, INSERT en UPDATE. Een exclusief slot kan alleen op een pagina of rij worden geplaatst als er nog geen ander gedeeld of exclusief slot op het doel aanwezig is. Dit betekent in de praktijk dat slechts één exclusief slot op een bladzijde of rij kan worden opgelegd, en dat geen ander slot op de vergrendelde bronnen kan worden opgelegd
Gedeeld slot (S) – dit slottype reserveert, wanneer het wordt opgelegd, een bladzijde of rij alleen voor lezen, hetgeen betekent dat iedere andere transactie de vergrendelde record niet kan wijzigen zolang het slot actief is. Een gedeelde “lock” kan echter door verscheidene transacties tegelijkertijd op dezelfde bladzijde of rij worden toegepast en op die manier kunnen verscheidene transacties de mogelijkheid om gegevens te lezen delen, aangezien het leesproces zelf op geen enkele wijze de feitelijke gegevens van de bladzijde of rij zal beïnvloeden. Bovendien staat een gedeelde “lock” schrijfoperaties toe, maar geen DDL wijzigingen
Update lock (U) – deze “lock” lijkt op een exclusieve “lock”, maar is in zekere zin flexibeler ontworpen. Een update slot kan opgelegd worden op een record dat reeds een gedeeld slot heeft. In zo’n geval zal het update slot een ander gedeeld slot opleggen op de doel rij. Zodra de transactie die het update slot heeft, klaar is om de gegevens te wijzigen, zal het update slot (U) omgezet worden in een exclusief slot (X). Het is belangrijk om te begrijpen dat het update slot asymmetrisch is ten opzichte van gedeelde sloten. Terwijl de update-lock kan worden opgelegd aan een record dat de gedeelde lock heeft, kan de gedeelde lock niet worden opgelegd aan de record die reeds de update-lock heeft
Intent locks (I) – deze lock is een middel dat door een transactie wordt gebruikt om een andere transactie te informeren over haar voornemen om een lock te verwerven. Het doel van een dergelijk slot is ervoor te zorgen dat de wijziging van gegevens correct wordt uitgevoerd, door te voorkomen dat een andere transactie een slot op het volgende object in de hiërarchie verkrijgt. In de praktijk zal een transactie die een slot op een rij wil verkrijgen, een intent lock op een tabel verkrijgen, die een object in een hogere hiërarchie is. Door het verkrijgen van het intent slot, zal de transactie niet toestaan dat andere transacties het exclusieve slot op die tabel verwerven (anders zou een exclusief slot, opgelegd door een andere transactie, het slot op de rij opheffen).
Dit is een belangrijk type slot vanuit het oogpunt van prestaties, omdat de SQL Server database-engine intent locks alleen op tabelniveau inspecteert om na te gaan of het voor een transactie mogelijk is om op een veilige manier een slot in die tabel te verwerven, en daarom hoeven intent locks niet meer elke rij/pagina-lock in een tabel te inspecteren om er zeker van te zijn dat een transactie een slot op de hele tabel kan verwerven
Er zijn drie gewone intent locks en drie zogenaamde conversiesloten:
Reguliere intent locks:
Intent exclusive (IX) – Wanneer een intent exclusive lock (IX) wordt verkregen, geeft dit aan de SQL Server aan dat de transactie van plan is een aantal bronnen in de lagere hiërarchie te wijzigen door het verkrijgen van exclusieve (X) sloten op deze bronnen in de lagere hiërarchie
Intent shared (IS) – Wanneer een intent shared lock (IS) wordt verkregen, geeft dit aan de SQL Server aan dat de transactie van plan is de intentie heeft om een aantal bronnen lager in de hiërarchie te lezen door individueel gedeelde sloten (S) te verwerven op deze bronnen lager in de hiërarchie
Intent update (IU) – wanneer een intent shared lock (IS) wordt verworven, geeft dit aan SQL Server aan dat de transactie de intentie heeft om een aantal bronnen lager in de hiërarchie te lezen door individueel gedeelde sloten (S) te verwerven op deze bronnen lager in de hiërarchie. Het intent update slot (IU) kan alleen op paginaniveau worden verkregen en wordt, zodra de update plaatsvindt, omgezet in het intent exclusive slot (IX)
Conversie sloten:
Gedeeld met intent exclusive (SIX) – wanneer dit slot wordt verkregen, geeft het aan dat de transactie van plan is alle bronnen in een lagere hiërarchie te lezen en dus het gedeelde slot te verkrijgen op alle bronnen die lager in de hiërarchie staan, en op zijn beurt een deel van deze bronnen te wijzigen, maar niet alle. Daarbij zal het een intent exclusive (IX) lock verwerven op de lager in de hiërarchie gelegen bronnen die gewijzigd moeten worden. In de praktijk betekent dit, dat wanneer de transactie een SIX-lock op de tabel verkrijgt, zij een intent-exclusive-lock (IX) op de gewijzigde pagina’s en een exclusive-lock (X) op de gewijzigde rijen verkrijgt.
Er kan maar één “shared with intent exclusive lock” (SIX) tegelijk op een tabel worden verworven en het zal andere transacties blokkeren om updates te maken, maar het zal andere transacties niet verhinderen om de bronnen in de lagere hiërarchie te lezen, zij kunnen het “intent shared” (IS) slot op de tabel verwerven
Shared with intent update (SIU) – dit is een iets specifieker slot, omdat het een combinatie is van de “shared” (S) en “intent update” (IU) sloten. Een typisch voorbeeld van deze “lock” is wanneer een transactie een query gebruikt die is uitgevoerd met de PAGELOCK hint en query, en daarna de update query. Nadat de transactie een SIU-lock op de tabel heeft verkregen, verkrijgt de query met de PAGELOCK-hint de gedeelde (S)-lock, terwijl de update-query de intent update-lock (IU) verkrijgt
Update with intent exclusive (UIX) – wanneer een update-lock (U) en een intent exclusive (IX)-lock gelijktijdig op bronnen in de lagere hiërarchie in de tabel worden verkregen, zal de update met intent exclusive lock worden verkregen op tabelniveau als gevolg
Schema locks (Sch) – De SQL Server database engine herkent twee soorten schema locks: Schema modificatie slot (Sch-M) en Schema stabiliteit slot (Sch-S)
- Een Schema modificatie slot (Sch-M) zal worden verworven wanneer een DDL statement wordt uitgevoerd, en het zal de toegang tot de vergrendelde object gegevens voorkomen terwijl de structuur van het object wordt gewijzigd. SQL Server staat een enkel schema modificatie slot (Sch-M) slot toe op elk vergrendeld object. Om een tabel te wijzigen, moet een transactie wachten om een Sch-M slot op het doel object te verkrijgen. Zodra het schema modificatie slot (Sch-M) is verkregen, kan de transactie het object modificeren en nadat de modificatie is voltooid zal het slot worden vrijgegeven. Een typisch voorbeeld van een Sch-M slot is een index rebuild, aangezien een index rebuild een tabel modificatie proces is. Zodra de index rebuild ID is uitgegeven, zal een schema modificatie slot (Sch-M) worden verworven op die tabel en zal pas worden vrijgegeven nadat het index rebuild proces is voltooid (wanneer gebruikt met ONLINE optie, zal index rebuild een Sch-M slot verwerven kort aan het einde van het proces)
- Een Schema stabiliteit slot (Sch-S) zal worden verworven terwijl een schema-afhankelijke query wordt gecompileerd en uitgevoerd en uitvoeringsplan wordt gegenereerd. Deze specifieke “lock” blokkeert andere transacties niet voor toegang tot de objectgegevens en is compatibel met alle “lock”-modi, behalve met de “schema modification lock” (Sch-M). Schemastabiliteitssloten worden door elke DML- en select-query verkregen om de integriteit van de tabelstructuur te waarborgen (ervoor zorgen dat de tabel niet verandert terwijl de query’s worden uitgevoerd).
Bulk Update-lock (BU) – deze lock is bedoeld om te worden gebruikt door bulkimportbewerkingen wanneer deze worden uitgevoerd met een TABLOCK-argument/hint. Wanneer een bulkupdate-lock wordt verkregen, hebben andere processen geen toegang tot een tabel tijdens de uitvoering van de bulkload. Een bulk update lock voorkomt echter niet dat een andere bulk load parallel wordt verwerkt. Maar houd in gedachten dat het gebruik van TABLOCK op een geclusterde indextabel parallelle bulk import niet toestaat. Meer details hierover zijn beschikbaar in Richtlijnen voor het optimaliseren van bulkimport
Vergrendelingshiërarchie
SQL Server heeft de vergrendelingshiërarchie geïntroduceerd die wordt toegepast bij het lezen of wijzigen van gegevens. De vergrendelingshiërarchie begint met de database op het hoogste hiërarchieniveau en gaat omlaag via tabel en pagina naar de rij op het laagste niveau
In wezen is er altijd een gedeeld slot op databaseniveau dat wordt opgelegd wanneer een transactie met een database wordt verbonden. De gedeelde vergrendeling op databaseniveau wordt opgelegd om te voorkomen dat de database wordt gedropt of dat een back-up van de database wordt hersteld over de database die in gebruik is. Wanneer bijvoorbeeld een SELECT-opdracht wordt gegeven om bepaalde gegevens te lezen, wordt een gedeelde vergrendeling (S) opgelegd op databaseniveau, een intentie gedeelde vergrendeling (IS) op tabel- en paginaniveau, en een gedeelde vergrendeling (S) op de rij zelf
In het geval van een DML-opdracht (d.w.z. insert, update, delete) wordt een gedeeld slot (S) opgelegd op databaseniveau, een intent-exclusief slot (IX) of intent-updateslot (IU) op tabel- en paginaniveau, en een exclusief of updateslot (X of U) op de rij
Sloten worden altijd van boven naar beneden verworven, omdat SQL Server op die manier voorkomt dat een zogenaamde “Race”-conditie optreedt.
Nu lock modes en lock hiërarchie zijn uitgelegd, gaan we verder in op lock modes en hoe die zich vertalen naar een lock hiërarchie.
Niet alle lock-modes kunnen op alle niveaus worden toegepast.
Op rijniveau kunnen de volgende drie blokkeringsmodi worden toegepast:
- Exclusief (X)
- Gedeeld (S)
- Update (U)
Om de compatibiliteit van deze modi te begrijpen, raadpleegt u de volgende tabel:
| Exclusive (X) | Shared (S) | Update (U) | |
| Exclusive (X) | ✗ | ✗ | |
| Shared (S) | ✗ | ✓ | ✓ |
| Update (U) | ✗ | ✓ | ✗ |
✓ – Compatible ✗ – Incompatible
Op het tabelniveau, zijn er vijf verschillende soorten sloten:
- Exclusief (X)
- Gedeeld (S)
- Intentie exclusief (IX)
- Intentie gedeeld (IS)
- Gedeeld met intentie exclusief (SIX)
De compatibiliteit van deze modi is te zien in de onderstaande tabel
| (X) | (S) | (IX) | (IS) | (ZES) | |
| (X) | ✗ | ✗ | ✗ | ✗ | ✗ |
| (S) | ✗ | ✓ | ✗ | ✓ | ✗ |
| (IX) | ✗ | ✗ | ✓ | ✓ | ✗ |
| (IS) | ✗ | ✓ | ✓ | ✓ | ✓ |
| (ZES) | ✗ | ✗ | ✗ | ✓ | ✗ |
✓ – Compatible ✗ – Incompatible
Een Schema-lock (Sch) is ook een lock op tabelniveau, maar het is geen gegevens-gerelateerde vergrendeling
Om de compatibiliteit tussen deze vergrendelingstypes beter te begrijpen, verwijzen wij u naar deze tabel:
Lock escalation
Om een situatie te voorkomen waarin het locken te veel bronnen gebruikt, heeft SQL Server de lock escalation functie geïntroduceerd.
Zonder escalatie zouden vergrendelingen een aanzienlijke hoeveelheid geheugenmiddelen in beslag kunnen nemen. Laten we een voorbeeld nemen waarbij een slot moet worden opgelegd op de 30.000 rijen gegevens, waarbij elke rij 500 bytes groot is, om de delete operatie uit te voeren. Zonder escalatie wordt een gedeeld slot (S) op de database geplaatst, 1 intent-exclusief slot (IX) op de tabel, 1.875 intent-exclusieve sloten (IX) op de pagina’s (een pagina van 8 KB bevat 16 rijen van 500 bytes, wat neerkomt op 1.875 pagina’s die 30.000 rijen bevatten) en 30.000 exclusieve sloten (X) op de rijen zelf. Aangezien elk slot 96 bytes groot is, nemen 31.877 sloten ongeveer 3 MB geheugen in beslag voor een enkele verwijderoperatie. Het parallel uitvoeren van een groot aantal bewerkingen kan aanzienlijke middelen vergen om er alleen al voor te zorgen dat de locking manager de bewerking probleemloos kan uitvoeren
Om een dergelijke situatie te voorkomen, maakt SQL Server gebruik van “lock escalation”. Dit houdt in dat in een situatie waarin meer dan 5.000 sloten worden verkregen op een enkel niveau, SQL Server deze sloten escaleert naar een enkele tabel niveau slot. Standaard zal SQL Server altijd direct escaleren naar het tabel niveau, wat betekent dat escalatie naar het pagina niveau nooit voorkomt. In plaats van het verkrijgen van een groot aantal rijen en pagina slot, zal SQL Server escaleren naar het exclusieve slot (X) op tabel niveau
Hoewel dit de behoefte aan middelen zal verminderen, betekent het exclusieve slot (X) in een tabel dat geen enkele andere transactie toegang kan krijgen tot de gelockte tabel en dat alle queries die toegang proberen te krijgen tot die tabel zullen worden geblokkeerd. Daarom zal dit de systeem overhead verminderen maar de kans op concurrency contention
Om controle te geven over de escalatie, te beginnen met SQL Server 2008 R2, de LOCK_EXCALATION-optie geïntroduceerd als onderdeel van de ALTER TABLE-instructie
Elk van deze opties is gedefinieerd om specifieke controle over het lock-escalatieproces mogelijk te maken:
Tabel – Dit is de standaardoptie voor elke nieuw aangemaakte tabel, omdat SQL Server standaard altijd een escalatie van vergrendelingen naar het tabelniveau uitvoert, wat ook geldt voor gepartitioneerde tabellen
Auto – Deze optie staat de escalatie van vergrendelingen naar een partitieniveau toe wanneer een tabel is gepartitioneerd. Wanneer 5.000 sloten worden verworven in een enkele partitie, zal de slot escalatie een exclusief slot (X) op die partitie verwerven, terwijl de tabel een intentief exclusief slot (IX) zal verwerven. In het geval dat de tabel niet gepartitioneerd is, zal de slot escalatie de slot op tabel niveau verkrijgen (gelijk aan de Tabel optie).
Hoewel dit een zeer nuttige optie lijkt, moet deze zeer voorzichtig worden gebruikt, omdat deze gemakkelijk een deadlock kan veroorzaken. In een situatie waarin we twee transacties hebben op twee partities waar het exclusieve slot (X) is verkregen, en transacties probeert toegang te krijgen tot de datum van de partitie die door de andere transactie wordt gebruikt, zal er een impasse ontstaan
Dus, Als deze optie is ingeschakeld, is het van groot belang dat het gegevenstoegangspatroon zorgvuldig wordt gecontroleerd. Dit is niet eenvoudig te realiseren en daarom is deze optie niet de standaardinstelling in SQL Server
Disable – Met deze optie wordt lock-escalatie voor een tabel volledig uitgeschakeld. Ook deze optie moet zorgvuldig worden gebruikt om te voorkomen dat de SQL Server lock manager gedwongen wordt om een overmatige hoeveelheid geheugen te gebruiken
Zoals u ziet, kan slot escalatie een uitdaging zijn voor DBA’s. Als het ontwerp van de applicatie vereist dat meer dan 5.000 rijen tegelijk worden verwijderd of bijgewerkt, is een oplossing om lock escalatie, en de daaruit voortvloeiende effecten, te voorkomen het opsplitsen van de enkele transactie in twee of meer transacties waarbij elke transactie minder dan 5.000 rijen afhandelt, omdat op deze manier de lock escalatie kan worden omzeild
Informatie inwinnen over actieve SQL Server locks
SQL Server biedt de Dynamics Management View (DMV) sys.dm_tran_locks dat informatie retourneert over lock manager bronnen die momenteel in gebruik zijn, wat betekent dat het alle “live” locks weergeeft die door transacties zijn verkregen. Meer details over deze DMV zijn te vinden in het artikel sys.dm_tran_locks (Transact-SQL).
De belangrijkste kolommen die gebruikt worden voor de identificatie van de lock zijn resource_type, request_mode, en resource_description. Indien nodig kunnen meer kolommen als extra informatiebron worden opgenomen bij het oplossen van problemen
Hier volgt een voorbeeld van een query
De where-clausule in deze query wordt gebruikt als filter op het resource_type om te elimineren. uit de resultaten, die over het algemeen gedeelde sloten die op de database zijn verkregen, aangezien deze altijd op databaseniveau aanwezig zijn
Een korte uitleg van de drie kolommen die hier worden gepresenteerd:
resource_type – Geeft een databasebron weer waar de sloten worden verkregen. De kolom kan een van de volgende waarden weergeven: ALLOCATION_UNIT, APPLICATION, DATABASE, EXTENT, FILE, HOBT, METADATA, OBJECT, PAGE, KEY, RID
request_mode – geeft de lockmodus weer die op de resource wordt verworven
resource_description – geeft een korte beschrijving van de resource weer en is niet voor alle lockmodi gevuld. Meestal bevat de kolom het id van de rij, pagina, object, bestand, enz
- Auteur
- Recent Posts
Militair luchtvaartfanaat en hard core schaalmodelbouwer van vliegtuigen. Fan van extreme sporten; parachutist en bungee jump instructeur. Ooit serieus, nu gewoon een vrijetijdsfotograaf
Bekijk alle berichten van Nikola Dimitrijevic
- SQL Server trace flags guide; van -1 tot 840 – March 4, 2019
- Hoe om te gaan met het SQL Server WRITELOG wait type – June 13, 2018
- SQL Server performance counters (Batch Requests/sec of Transactions/sec): wat te monitoren en waarom – 5 juni 2018