Låsning er afgørende for en vellykket behandling af SQL Server-transaktioner, og den er designet til at gøre det muligt for SQL Server at fungere problemfrit i et miljø med flere brugere. Låsning er den måde, hvorpå SQL Server håndterer transaktionskonkurrence. I bund og grund er låse in-memory-strukturer, som har ejere, typer og hash-koden for den ressource, som den skal beskytte. En lås som en in-memory-struktur er 96 bytes stor.
For bedre at forstå låsning i SQL Server er det vigtigt at forstå, at låsning er designet til at sikre integriteten af dataene i databasen, da den tvinger hver SQL Server-transaktion til at bestå ACID-testen.
ACID-testen består af 4 krav, som hver transaktion skal bestå med succes:
- Atomicity – kræver, at en transaktion, der involverer to eller flere diskrete dele af information, skal commit alle dele eller ingen
- Consistency – kræver, at en transaktion skal skabe en gyldig tilstand af nye data, eller den skal rulle alle data tilbage til den tilstand, der eksisterede, før transaktionen blev udført
- Isolation – kræver, at en transaktion, der stadig kører og ikke har commit alle data endnu, skal forblive isoleret fra alle andre transaktioner
- Holdbarhed – kræver, at data, der er blevet bekræftet, skal gemmes ved hjælp af en metode, der bevarer alle data i korrekt tilstand og er tilgængelige for en bruger, selv i tilfælde af en fejl
SQL Server-låsning er den væsentlige del af kravet om isolation og tjener til at låse de objekter, der er berørt af en transaktion. Mens objekter er låst, forhindrer SQL Server andre transaktioner i at foretage ændringer af data, der er gemt i objekter, som er berørt af den pålagte låsning. Når låsen er frigivet ved at overføre ændringerne eller ved at rulle ændringerne tilbage til den oprindelige tilstand, kan andre transaktioner foretage de nødvendige dataændringer.
Oversat til SQL Server-sprog betyder det, at når en transaktion pålægger en lås på et objekt, vil alle andre transaktioner, der kræver adgang til det pågældende objekt, blive tvunget til at vente, indtil låsen frigives, og denne ventetid vil blive registreret med den passende ventetype
SQL Server-låse kan specificeres via låsemåder og låsegranularitet
Låsemåder
Låsemåde overvejer forskellige låsetyper, der kan anvendes på en ressource, der skal låses:
- Eksklusiv (X)
- Delet (S)
- Opdatering (U)
- Intension (I)
- Skema (Sch)
- Bulk opdatering (BU)
Eksklusiv lås (X) – Denne låstype, sikrer, når den pålægges, at en side eller række vil være reserveret udelukkende til den transaktion, der pålagde den eksklusive lås, så længe transaktionen har låsen.
Den eksklusive lås vil blive pålagt af transaktionen, når den ønsker at ændre side- eller rækkedata, hvilket er tilfældet i forbindelse med DML-meddelelser DELETE, INSERT og UPDATE. En eksklusiv lås kan kun pålægges en side eller række, hvis der ikke allerede er pålagt en anden delt eller eksklusiv lås på målet. Dette betyder praktisk talt, at der kun kan pålægges én eksklusiv lås på en side eller række, og når den er pålagt, kan der ikke pålægges andre låse på de låste ressourcer
Delt lås (S) – denne type lås vil, når den pålægges, reservere en side eller række til kun at være tilgængelig for læsning, hvilket betyder, at enhver anden transaktion vil være forhindret i at ændre den låste post, så længe låsen er aktiv. En delt lås kan imidlertid pålægges af flere transaktioner på samme tid over den samme side eller række, og på den måde kan flere transaktioner dele muligheden for at læse data, da selve læseprocessen ikke på nogen måde vil påvirke de faktiske data på siden eller rækken. Desuden vil en delt lås tillade skriveoperationer, men ingen DDL-ændringer vil være tilladt
Update-lås (U) – denne lås svarer til en eksklusiv lås, men er designet til at være mere fleksibel på en måde. En opdateringslås kan pålægges en post, der allerede har en delt lås. I et sådant tilfælde vil opdateringslåsen pålægge en anden delt lås på målrækken. Når den transaktion, der har opdateringslåsen, er klar til at ændre dataene, vil opdateringslåsen (U) blive omdannet til en eksklusiv lås (X). Det er vigtigt at forstå, at opdateringslåse er asymmetriske i forhold til delte låse. Mens opdateringslåsen kan pålægges en post, der har den delte lås, kan den delte lås ikke pålægges en post, der allerede har opdateringslåsen
Intentslåse (I) – denne lås er et middel, der bruges af en transaktion til at informere en anden transaktion om, at den har til hensigt at erhverve en lås. Formålet med en sådan lås er at sikre, at datamodifikationer udføres korrekt ved at forhindre en anden transaktion i at erhverve en lås på det næste objekt i hierarkiet. I praksis vil en transaktion, der ønsker at erhverve en lås på en række, erhverve en intentionel lås på en tabel, som er et objekt i et højere hierarki. Ved at erhverve en intentionel lås vil transaktionen ikke tillade andre transaktioner at erhverve en eksklusiv lås på det pågældende bord (ellers vil en eksklusiv lås, der er pålagt af en anden transaktion, annullere rækkelåsen).
Dette er en vigtig låsetype set ud fra et ydelsesmæssigt synspunkt, da SQL Server-databasemaskinen kun inspicerer intentionelle låse på tabelniveau for at kontrollere, om det er muligt for transaktionen at erhverve en lås på en sikker måde i den pågældende tabel, og derfor eliminerer intentionelle låse behovet for at inspicere hver enkelt række-/sidelås i en tabel for at sikre, at transaktionen kan erhverve en lås på hele tabellen
Der findes tre almindelige intentionelle låse og tre såkaldte konverteringslåse:
Regulære intentionsslots:
Intent eksklusiv (IX) – når en intent eksklusiv lås (IX) er erhvervet, indikerer det til SQL Server, at transaktionen har til hensigt at ændre nogle af de lavere hierarkiressourcer ved at erhverve eksklusive (X) låse individuelt på disse lavere hierarkiressourcer
Intent delt (IS) – når en intent delt lås (IS) er erhvervet, indikerer det til SQL Server, at transaktionen har har til hensigt at læse nogle ressourcer i lavere hierarkieressourcer ved at erhverve delte låse (S) individuelt på disse ressourcer lavere i hierarkiet
Intent update (IU) – når en intent shared lock (IS) erhverves, indikerer det over for SQL Server, at transaktionen har til hensigt at læse nogle ressourcer i lavere hierarkieressourcer ved at erhverve delte låse (S) individuelt på disse ressourcer lavere i hierarkiet. En intentionel opdateringslås (IU) kan kun erhverves på sideniveau, og så snart opdateringsoperationen finder sted, konverteres den til en intentionel eksklusiv lås (IX)
Konverteringslåse:
Shared with intent exclusive (SIX) – når denne lås erhverves, angiver den, at transaktionen har til hensigt at læse alle ressourcer i et lavere hierarki og således erhverve den delte lås på alle ressourcer, der er lavere i hierarkiet, og til gengæld at ændre en del af disse, men ikke alle. Dermed vil den erhverve en eksklusiv (IX) lås på de ressourcer i det lavere hierarki, der skal ændres. I praksis betyder det, at når transaktionen opnår en SIX-lås på tabellen, vil den opnå en eksklusiv lås (IX) på de ændrede sider og en eksklusiv lås (X) på de ændrede rækker.
Der kan kun erhverves én shared with intent exclusive lock (SIX) på en tabel ad gangen, og den vil blokere andre transaktioner for at foretage opdateringer, men den vil ikke forhindre andre transaktioner i at læse de lavere hierarkiressourcer, de kan erhverve intent shared (IS) lock på tabellen
Shared with intent update (SIU) – dette er en lidt mere specifik lås, da det er en kombination af shared (S) og intent update (IU) locks. Et typisk eksempel på denne lås er, når en transaktion bruger en forespørgsel, der udføres med PAGELOCK-hinvisningen og forespørgslen, og derefter opdateringsforespørgslen. Efter at transaktionen har erhvervet en SIU-lås på tabellen, vil forespørgslen med PAGELOCK-hinvisningen erhverve den delte (S) lås, mens opdateringsforespørgslen vil erhverve intent update (IU)-låsen
Opdatering med intent exclusive (UIX) – når opdateringslås (U) og intent exclusive (IX)-låse erhverves på lavere hierarkiressourcer i tabellen samtidigt, vil opdateringslåsen med eksklusiv hensigt blive erhvervet på tabelniveau som følge heraf
Skemalåse (Sch) – SQL Server-databasemotoren genkender to typer af skemalåse: Schema modifikationslås (Sch-M) og Schema stabilitetslås (Sch-S)
- En Schema modifikationslås (Sch-M) vil blive erhvervet, når en DDL-anvisning udføres, og den vil forhindre adgang til de låste objektdata, da objektets struktur ændres. SQL Server tillader en enkelt Schema Modification Lock (Sch-M)-lås på et hvilket som helst låst objekt. For at ændre en tabel skal en transaktion vente på at få en Sch-M-lås på målobjektet for at kunne ændre det. Når transaktionen har opnået skemaændringslåsen (Sch-M), kan den ændre objektet, og når ændringen er afsluttet, bliver låsen frigivet. Et typisk eksempel på Sch-M-lås er en indeksgenopbygning, da en indeksgenopbygning er en tabelændringsproces. Når indeksgenopbygnings-ID’et er udstedt, vil der blive erhvervet en skemaændringslås (Sch-M) på det pågældende bord, og den vil først blive frigivet, når indeksgenopbygningsprocessen er afsluttet (når indeksgenopbygningen anvendes med ONLINE-indstillingen, vil indeksgenopbygningen erhverve Sch-M-låsen kort tid efter afslutningen af processen)
- Der vil blive erhvervet en skemastabilitetslås (Sch-S), mens en skemaafhængig forespørgsel kompileres og udføres, og der genereres en eksekveringsplan. Denne særlige lås vil ikke blokere andre transaktioner for adgang til objektdataene, og den er kompatibel med alle låsemåder undtagen med skemaændringslåsen (Sch-M). I det væsentlige erhverves skema-stabilitetslåse af alle DML- og select-forespørgsler for at sikre integriteten af tabelstrukturen (sikre, at tabellen ikke ændres, mens forespørgsler kører).
Bulk Update locks (BU) – denne lås er designet til at blive brugt af bulkimportoperationer, når den udstedes med et TABLOCK-argument/hint. Når en bulkopdateringslås er erhvervet, vil andre processer ikke kunne få adgang til en tabel under udførelsen af bulkindlæsningen. En bulkopdateringslås forhindrer dog ikke en anden bulkindlæsning i at blive behandlet parallelt. Men husk på, at brug af TABLOCK på et clusteret indekstabel ikke tillader parallel bulkimport. Flere detaljer om dette findes i Guidelines for Optimizing Bulk Import
Låsningshierarki
SQL Server har indført det låsningshierarki, der anvendes, når der læses eller ændres data. Låsehierarkiet starter med databasen på det højeste hierarkiniveau og ned via tabel og side til rækken på det laveste niveau
I bund og grund er der altid en delt lås på databaseniveau, som pålægges, når en transaktion er forbundet til en database. Den delte lås på databaseniveau pålægges for at forhindre, at databasen droppes eller at en databasebackup gendannes over den database, der er i brug. Når der f.eks. udstedes en SELECT-anvisning for at læse nogle data, vil der blive pålagt en delt lås (S) på databaseniveau, en intentionel delt lås (IS) på tabellen og på sideniveau og en delt lås (S) på selve rækken
I tilfælde af en DML-anvisning (f.eks. indsæt, opdatering, sletning) vil der blive pålagt en delt lås (S) på databaseniveau, en eksklusiv lås (IX) eller en opdateringslås (IU) på tabellen og på sideniveau og en eksklusiv eller opdateringslås (X eller U) på rækken
Låsene vil altid blive erhvervet fra toppen til bunden, da SQL Server på den måde forhindrer, at der opstår en såkaldt race-tilstand.
Nu hvor låsetilstande og låsehierarki er blevet forklaret, skal vi uddybe låsetilstande yderligere, og hvordan disse oversættes til et låsehierarki.
Det er ikke alle låsemåder, der kan anvendes på alle niveauer.
På række-niveau kan følgende tre låsemodes anvendes:
- Eksklusiv (X)
- Fælles (S)
- Opdatering (U)
For at forstå kompatibiliteten af disse modes henvises der til følgende tabel:
| Eksklusiv (X) | Delt (S) | Opdatering (U) | |||
| Eksklusiv (X) | ✗ | ✗ | ✗ | ||
| Delt (S) | ✗ | ✓ | ✓ | ||
| Opdatering (U) | ✗ | ✓ | ✗ | ✗ |
✓ – Kompatibel ✗ – Inkompatibel
På tabelniveau, er der fem forskellige typer af låse:
- Eksklusiv (X)
- Delt (S)
- Eksklusiv hensigt (IX)
- Delt hensigt (IS)
- Delt med hensigt eksklusiv (SIX)
Kompatibiliteten af disse tilstande kan ses i nedenstående tabel
| (X) | (S) | (IX) | (IS) | (SIX) | ||
| (X) | ✗ | ✗ | ✗ | ✗ | ✗ | ✗ |
| (S) | ✗ | ✓ | ✗ | ✓ | ✗ | |
| (IX) | ✗ | ✗ | ✗ | ✓ | ✓ | ✗ |
| (ER) | ✗ | ✓ | ✓ | ✓ | ✓ | ✓ |
| (SEKS) | ✗ | ✗ | ✗ | ✗ | ✓ | ✗ |
✓ – Kompatibel ✗ – Inkompatibel
En skemalås (Sch) er også en lås på tabelniveau, men det er ikke en datarelateret lås
For bedre at forstå kompatibiliteten mellem disse låsetyper henvises der til denne tabel:
Låseskalering
For at forhindre en situation, hvor låsning bruger for mange ressourcer, har SQL Server indført funktionen til låseskalering.
Uden eskalering kunne låse kræve en betydelig mængde hukommelsesressourcer. Lad os tage et eksempel, hvor der skal pålægges en lås på de 30.000 datarækker, hvor hver række er 500 bytes stor, for at udføre sletningsoperationen. Uden eskalering vil der blive pålagt en delt lås (S) på databasen, 1 intent eksklusiv lås (IX) på tabellen, 1.875 intent eksklusive låse (IX) på siderne (8 KB side indeholder 16 rækker på 500 byte, hvilket giver 1.875 sider, der indeholder 30.000 rækker) og 30.000 eksklusive låse (X) på selve rækkerne. Da hver lås er på 96 byte, vil 31 877 låse tage ca. 3 MB hukommelse i brug for en enkelt sletningsoperation. Hvis et stort antal operationer køres parallelt, kan det kræve betydelige ressourcer at sikre, at låsemanageren kan udføre operationen problemfrit
For at forhindre en sådan situation bruger SQL Server låseskalering. Det betyder, at i en situation, hvor der erhverves mere end 5.000 låse på et enkelt niveau, vil SQL Server optrappe disse låse til en enkelt lås på tabelniveau. Som standard vil SQL Server altid eskalere direkte til tabelniveauet, hvilket betyder, at eskalering til sideniveauet aldrig finder sted. I stedet for at erhverve mange rækker og sidelåse vil SQL Server eskalere til en eksklusiv lås (X) på tabelniveau
Selv om dette vil reducere behovet for ressourcer, betyder eksklusive låse (X) i en tabel, at ingen anden transaktion vil kunne få adgang til den låste tabel, og alle forespørgsler, der forsøger at få adgang til den pågældende tabel, vil blive blokeret. Derfor vil dette reducere systemoverhead, men vil øge sandsynligheden for konkurrencestridigheder
For at give kontrol over eskaleringen, der starter med SQL Server 2008 R2, LOCK_EXCALATION-indstillingen indført som en del af ALTER TABLE-erklæringen
Hver af disse indstillinger er defineret for at give specifik kontrol over låseskaleringsprocessen:
Table – Dette er standardindstillingen for alle nyoprettede tabeller, da SQL Server som standard altid vil udføre låseskalering til tabelniveauet, hvilket også omfatter partitionerede tabeller
Auto – Denne indstilling tillader låseskalering til et partitionsniveau, når et bord er partitioneret. Når der er erhvervet 5.000 låse i en enkelt partition, vil låseskalering erhverve en eksklusiv lås (X) på den partition, mens tabellen vil erhverve en eksklusiv lås med hensigt (IX). Hvis tabellen ikke er partitioneret, vil låseskalering erhverve låsen på tabelniveau (svarende til tabelindstillingen).
Selv om dette ser ud til at være en meget nyttig mulighed, skal den bruges meget forsigtigt, da den let kan forårsage en fastlåsning. I en situation, hvor vi har to transaktioner på to partitioner, hvor den eksklusive lås (X) er erhvervet, og transaktionerne forsøger at få adgang til datoen fra den partition, der bruges af den anden transaktion, vil der opstå en deadlock
Så, det er meget vigtigt at kontrollere dataadgangsmønstret omhyggeligt, hvis denne indstilling er aktiveret, hvilket ikke er let at opnå, og det er derfor, at denne indstilling ikke er standardindstillingerne i SQL Server
Disable – Denne indstilling deaktiverer helt låseskalering for en tabel. Igen skal denne indstilling bruges med omtanke for at undgå at SQL Server lock manager tvinges til at bruge en for stor mængde hukommelse
Som det kan ses, kan lock escalation være en udfordring for DBA’er. Hvis applikationsdesignet kræver sletning eller opdatering af mere end 5.000 rækker på én gang, er en løsning til at undgå låseskalering og de deraf følgende virkninger at opdele den enkelte transaktion i to eller flere transaktioner, hvor hver enkelt vil håndtere mindre end 5.000 rækker, da låseskalering på denne måde kan omgås
Hent information om aktive SQL Server-låse
SQL Server tilbyder Dynamics Management View (DMV) sys.dm_tran_locks, der returnerer oplysninger om låsemanagerressourcer, der er i brug i øjeblikket, hvilket betyder, at den viser alle “levende” låse, der er erhvervet af transaktioner. Du kan finde flere oplysninger om denne DMV i artiklen sys.dm_tran_locks (Transact-SQL).
De vigtigste kolonner, der bruges til identifikation af en lås, er resource_type, request_mode og resource_description. Hvis det er nødvendigt, kan flere kolonner som yderligere ressource til informationsinfo medtages under fejlfinding
Her er et eksempel på forespørgslen
Where-klausulen i denne forespørgsel bruges som filter på den resource_type, der skal elimineres. fra resultaterne, de generelt delte låse, der er erhvervet på databasen, da disse altid er til stede på databaseniveau
En kort forklaring på de tre kolonner, der præsenteres her:
resource_type – Viser en databaseresource, hvor låsene er erhvervet. Kolonnen kan vise en af følgende værdier: ALLOCATION_UNIT, APPLICATION, DATABASE, EXTENT, FILE, HOBT, METADATA, OBJECT, PAGE, KEY, RID
request_mode – viser den låsemodus, der er erhvervet på ressourcen
resource_description – viser en kort ressourcebeskrivelse og er ikke udfyldt for alle låsemåder. Oftest indeholder kolonnen id’et for rækken, siden, objektet eller filen, osv
- Author
- Recent Posts
Militær luftfartsfanatiker og hårdkogt modelbygger af skalafly. Ekstrem sportsfan; faldskærmsudspringer og bungee jump-instruktør. Engang seriøs, nu bare en fritidsfotograf
Se alle indlæg af Nikola Dimitrijevic
- SQL Server trace flags guide; fra -1 til 840 – 4. marts 2019
- Sådan håndteres SQL Server WRITELOG wait type – 13. juni 2018
- SQL Server performance counters (Batch Requests/sec eller Transactions/sec): hvad der skal overvåges og hvorfor – 5. juni 2018