Låsning är en förutsättning för att SQL Server-transaktioner ska kunna bearbetas på ett framgångsrikt sätt och är utformad för att SQL Server ska kunna fungera smidigt i en miljö med flera användare. Låsning är det sätt på vilket SQL Server hanterar transaktionskonkurrens. I huvudsak är låsningar strukturer i minnet som har ägare, typer och hash för den resurs som den ska skydda. En låsning som en struktur i minnet är 96 bytes stor.
För att bättre förstå låsningen i SQL Server är det viktigt att förstå att låsningen är utformad för att säkerställa integriteten hos data i databasen, eftersom den tvingar varje SQL Server-transaktion att klara ACID-testet.
ACID-testet består av fyra krav som varje transaktion måste klara med framgång:
- Atomicity – kräver att en transaktion som involverar två eller flera diskreta delar av information måste bekräfta alla delar eller ingen
- Consistency – kräver att en transaktion måste skapa ett giltigt tillstånd av nya data, eller så måste den återställa alla data till det tillstånd som fanns innan transaktionen utfördes
- Isolation – kräver att en transaktion som fortfarande körs och inte har bekräftat alla data ännu, måste förbli isolerad från alla andra transaktioner
- Hållbarhet – kräver att bekräftade data måste lagras med hjälp av en metod som kommer att bevara alla data i korrekt tillstånd och tillgängliga för en användare, även i händelse av fel
SQL Server- låsning är en viktig del av kravet på isolering och används för att låsa de objekt som påverkas av en transaktion. Medan objekten är låsta förhindrar SQL Server andra transaktioner från att ändra data som är lagrade i objekt som berörs av den införda låsningen. När låset frigörs genom att ändringarna bekräftas eller genom att ändringarna rullas tillbaka till det ursprungliga tillståndet tillåts andra transaktioner att göra nödvändiga dataförändringar.
Översatt till SQL Server-språket betyder detta att när en transaktion inför en låsning på ett objekt, kommer alla andra transaktioner som kräver åtkomst till det objektet att tvingas vänta tills låset frigörs och den väntan kommer att registreras med den lämpliga väntetypen
SQL Server-lås kan specificeras via låsningslägena och låsningsgranulariteten
Låsningslägen
Låsningsläget tar hänsyn till olika låstyper som kan appliceras på en resurs som måste låsas:
- Exklusiv (X)
- Delad (S)
- Uppdatering (U)
- Avsikt (I)
- Schema (Sch)
- Bulkuppdatering (BU)
Exklusiv låsning (X) – Denna låstyp, när den används säkerställer att en sida eller rad reserveras exklusivt för den transaktion som använde den exklusiva spärren, så länge som transaktionen innehar spärren.
Den exklusiva låsningen kommer att införas av transaktionen när den vill ändra sid- eller raddata, vilket är fallet med DML-anvisningar DELETE, INSERT och UPDATE. En exklusiv låsning kan endast införas för en sida eller rad om det inte finns någon annan delad eller exklusiv låsning som redan har införts för målet. Detta innebär praktiskt taget att endast en exklusiv låsning kan införas för en sida eller rad, och när den väl har införts kan ingen annan låsning införas på låsta resurser
Delad låsning (S) – denna låstyp, när den införs, kommer att reservera en sida eller rad så att den endast är tillgänglig för läsning, vilket innebär att alla andra transaktioner kommer att hindras från att ändra den låsta posten så länge som låset är aktivt. En delad spärr kan dock införas av flera transaktioner samtidigt på samma sida eller rad och på så sätt kan flera transaktioner dela på möjligheten att läsa data, eftersom själva läsprocessen inte på något sätt påverkar de faktiska sid- eller raddata. Dessutom kommer en delad spärr att tillåta skrivoperationer, men inga DDL-ändringar kommer att tillåtas
Uppdateringslås (U) – denna spärr liknar en exklusiv spärr men är utformad för att vara mer flexibel på ett sätt. En uppdateringslås kan införas på en post som redan har en delad låsning. I ett sådant fall kommer uppdateringslåset att införa ett annat delat lås på målraden. När den transaktion som har uppdateringslåset är redo att ändra uppgifterna omvandlas uppdateringslåset (U) till ett exklusivt lås (X). Det är viktigt att förstå att uppdateringsspärren är asymmetrisk i förhållande till delade spärrar. Medan uppdateringsspärren kan åläggas en post som har en delad spärr, kan den delade spärren inte åläggas en post som redan har uppdateringsspärren
Intentspärrar (I) – denna spärr används av en transaktion för att informera en annan transaktion om att den har för avsikt att förvärva en spärr. Syftet med en sådan spärr är att säkerställa att datamodifiering utförs korrekt genom att förhindra att en annan transaktion förvärvar en spärr på nästa objekt i hierarkin. I praktiken kommer en transaktion som vill förvärva en låsning på en rad att förvärva en avsiktslåsning på en tabell, som är ett högre hierarkiskt objekt. Genom att förvärva avsiktslåset kommer transaktionen inte att tillåta andra transaktioner att förvärva den exklusiva låsningen på det bordet (annars skulle en exklusiv låsning som införts av en annan transaktion upphäva radlåset).
Detta är en viktig låstyp ur prestandasynpunkt eftersom SQL Server-databasmotorn endast inspekterar avsiktslås på tabellnivå för att kontrollera om det är möjligt för transaktionen att förvärva ett lås på ett säkert sätt i den tabellen, och därför eliminerar avsiktslåset behovet av att inspektera varje rad/sidelås i en tabell för att se till att transaktionen kan förvärva ett lås på hela tabellen
Det finns tre vanliga avsiktslås och tre s.k. konverteringsslussar:
Reguljära avsiktslås:
Intent exclusive (IX) – när en intent exclusive lock (IX) förvärvas indikerar det för SQL Server att transaktionen har för avsikt att ändra några av de lägre hierarkiska resurserna genom att förvärva exklusiva (X) lås individuellt på dessa lägre hierarkiska resurser
Intent shared (IS) – när en intent shared lock (IS) förvärvas indikerar det för SQL Server att transaktionen har för avsikt att ändra några av de lägre hierarkiska resurserna. avsikt att läsa vissa resurser i lägre hierarkier genom att förvärva delade lås (S) individuellt på dessa resurser lägre i hierarkin
Intent update (IU) – när en avsiktlig delad låsning (IS) förvärvas indikerar den för SQL Server att transaktionen har för avsikt att läsa vissa resurser i lägre hierarkier genom att förvärva delade lås (S) individuellt på dessa resurser lägre i hierarkin. Avsiktslåset för uppdatering (IU) kan endast förvärvas på sidnivå och så snart uppdateringsoperationen äger rum omvandlas det till ett avsiktligt exklusivt lås (IX)
Konverteringslås:
Delat med avsikt exklusivt (SIX) – när det förvärvas indikerar det här låset att transaktionen har för avsikt att läsa alla resurser på en lägre hierarkisk nivå och därmed förvärva det delade låset på alla resurser som är lägre i hierarkin och i sin tur ändra en del av dessa, men inte alla. Därmed förvärvar den en avsiktlig exklusiv (IX) låsning på de resurser i den lägre hierarkin som ska ändras. I praktiken innebär detta att när transaktionen väl har förvärvat en SIX-låsning på tabellen kommer den att förvärva en avsiktlig exklusiv låsning (IX) på de modifierade sidorna och en exklusiv låsning (X) på de modifierade raderna.
Endast en delad med avsiktlig exklusiv låsning (SIX) kan förvärvas på en tabell åt gången och den blockerar andra transaktioner från att göra uppdateringar, men den hindrar inte andra transaktioner från att läsa de lägre hierarkiresurserna de kan förvärva avsiktlig delad (IS) låsning på tabellen
Delad med avsiktlig uppdatering (SIU) – detta är en lite mer specifik låsning eftersom det är en kombination av delad (S) och avsiktlig uppdatering (IU) låsning. Ett typiskt exempel på denna låsning är när en transaktion använder en fråga som utförs med PAGELOCK-tipset och -frågan och sedan uppdateringsfrågan. Efter att transaktionen har förvärvat en SIU-låsning på tabellen kommer frågan med PAGELOCK-hänvisningen att förvärva den delade (S) låsningen medan uppdateringsfrågan kommer att förvärva en avsiktlig uppdatering (IU) låsning
Uppdatering med avsiktlig exklusivitet (UIX) – när uppdateringsspärrar (U) och avsiktliga exklusiva (IX) låsningar förvärvas på lägre hierarkieresurser i tabellen samtidigt, kommer uppdateringslås med exklusivt syfte att förvärvas på tabellnivå som en konsekvens
Schemalås (Sch) – SQL Server-databasmotorn känner igen två typer av schemalås: Schema modifieringslås (Sch-M) och Schema stabilitetslås (Sch-S)
- En Schema modifieringslås (Sch-M) förvärvas när ett DDL-uttalande exekveras och förhindrar åtkomst till de låsta objektdata när objektets struktur ändras. SQL Server tillåter en enda Sch-M-låsning (Schema Modification Lock) på ett låst objekt. För att kunna ändra en tabell måste en transaktion vänta på att få en Sch-M-lås på målobjektet. När transaktionen har förvärvat schemamodifieringslåset (Sch-M) kan den ändra objektet och när ändringen är klar frigörs låset. Ett typiskt exempel på Sch-M-lås är en indexombyggnad, eftersom en indexombyggnad är en process för ändring av ett bord. När indexombyggnads-ID utfärdas kommer en schemaändringslås (Sch-M) att förvärvas på det bordet och frigöras först när indexombyggnaden är avslutad (när indexombyggnaden används med ONLINE-alternativet kommer indexombyggnaden att förvärva Sch-M-låset strax efter processens slut)
- En Schema-stabilitetslås (Sch-S) kommer att förvärvas medan en schemaberoende fråga sammanställs och exekveras och exekveringsplanen genereras. Denna speciella låsning blockerar inte andra transaktioner för åtkomst till objektsdata och är kompatibel med alla låsningslägen utom schemamodifieringslåsningen (Sch-M). I huvudsak kommer Schema stability locks att förvärvas av varje DML- och select-fråga för att säkerställa integriteten hos tabellstrukturen (se till att tabellen inte ändras medan frågorna körs).
Bulk Update locks (BU) – denna spärr är utformad för att användas av massimportoperationer när den utfärdas med ett TABLOCK-argument/hänvisning. När en bulkuppdateringslås förvärvas kommer andra processer inte att kunna få tillgång till en tabell under utförandet av bulkinläsningen. En bulkuppdateringslås hindrar dock inte att en annan bulkladdning behandlas parallellt. Tänk dock på att användning av TABLOCK på en klustrad indextabell inte tillåter parallell bulkimport. Mer information om detta finns i Guidelines for Optimizing Bulk Import
Låshierarki
SQL Server har infört den låshierarki som tillämpas när läsning eller ändring av data utförs. Låshierarkin börjar med databasen på den högsta hierarkinivån och ner via tabell och sida till raden på den lägsta nivån
I princip finns det alltid en delad låsning på databasnivå som införs när en transaktion ansluts till en databas. Den delade låsningen på databasnivå införs för att förhindra att databasen släpps eller att en databasbackup återställs över den databas som används. När till exempel ett SELECT-meddelande utfärdas för att läsa data, kommer en delad låsning (S) att införas på databasnivå, en avsiktlig delad låsning (IS) kommer att införas på tabell- och sidnivå, och en delad låsning (S) på själva raden
När det gäller ett DML-meddelande (t.ex. infoga, uppdatera, ta bort) kommer en delad spärr (S) att införas på databasnivå, en avsiktlig exklusiv spärr (IX) eller avsiktlig uppdateringsspärr (IU) kommer att införas på tabell- och sidnivå, och en exklusiv eller uppdateringsspärr (X eller U) på raden
Spärrar kommer alltid att förvärvas från toppen till botten, eftersom SQL Server på så sätt förhindrar att ett så kallat Race-tillstånd uppstår.
Nu när låsningslägen och låsningshierarki har förklarats ska vi ytterligare utveckla låsningslägena och hur de översätts till en låsningshierarki.
Alla låsningslägen kan inte tillämpas på alla nivåer.
På radnivå kan följande tre låsningslägen tillämpas:
- Exklusivt (X)
- Delat (S)
- Uppdatering (U)
För att förstå kompatibiliteten mellan dessa lägen hänvisar vi till följande tabell:
| Exklusivt (X) | Delat (S) | Uppdatering (U) | ||
| Exklusivt (X) | ✗ | ✗ | ✗ | ✗ |
| Delat (S) | ✗ | ✓ | ✓ | |
| Uppdatering (U) | ✗ | ✓ | ✗ |
✓ – Kompatibel ✗ – Inkompatibel
På tabellnivå, finns det fem olika typer av lås:
- Exklusivt (X)
- Delat (S)
- Intendent exklusivt (IX)
- Intendent delat (IS)
- Delat med intendent exklusivt (SIX)
Kompatibiliteten mellan dessa lägen kan ses i tabellen nedan
| (X) | (S) | (IX) | (IS) | (SEX) | ||
| (X) | ✗ | ✗ | ✗ | ✗ | ✗ | ✗ |
| (S) | ✗ | ✓ | ✗ | ✓ | ✗ | |
| (IX) | ✗ | ✗ | ✗ | ✓ | ✓ | ✗ |
| (ÄR) | ✗ | ✓ | ✓ | ✓ | ✓ | ✓ |
| (SEX) | ✗ | ✗ | ✗ | ✗ | ✓ | ✗ |
✓ – Kompatibel ✗ – Inkompatibel
Ett schemalås (Sch) är också ett lås på bordsnivå, men det är inte en datarelaterad spärr
För att bättre förstå kompatibiliteten mellan dessa spärrtyper hänvisar vi till den här tabellen:
Låseskalering
För att förhindra en situation där låsningen använder för många resurser har SQL Server infört funktionen låseskalering.
Utan eskalering kan låsningar kräva en betydande mängd minnesresurser. Låt oss ta ett exempel där en låsning bör införas på 30 000 datarader, där varje rad är 500 byte stor, för att utföra raderingsoperationen. Utan eskalering kommer en delad spärr (S) att införas på databasen, 1 avsiktlig exklusiv spärr (IX) på tabellen, 1 875 avsiktliga exklusiva spärrar (IX) på sidorna (en 8KB-sida rymmer 16 rader om 500 byte, vilket ger 1 875 sidor som rymmer 30 000 rader) och 30 000 exklusiva spärrar (X) på själva raderna. Eftersom varje lås är 96 byte stort tar 31 877 lås cirka 3 MB minne i anspråk för en enda raderingsoperation. Att köra ett stort antal operationer parallellt kan kräva betydande resurser bara för att se till att låsningshanteraren kan utföra operationen smidigt
För att förhindra en sådan situation använder SQL Server låseskalering. Detta innebär att i en situation där mer än 5 000 lås förvärvas på en enda nivå, kommer SQL Server att eskalera dessa lås till ett enda lås på tabellnivå. Som standard eskalerar SQL Server alltid direkt till tabellnivån, vilket innebär att eskalering till sidnivån aldrig sker. Istället för att förvärva många rader och sidlås kommer SQL Server att eskalera till exklusiv låsning (X) på tabellnivå
Även om detta minskar behovet av resurser innebär exklusiva låsningar (X) i en tabell att ingen annan transaktion kommer att kunna få tillgång till den låsta tabellen och att alla frågor som försöker få tillgång till den tabellen kommer att blockeras. Därför kommer detta att minska systemets overhead men öka sannolikheten för konkurrenskonflikter
För att ge kontroll över upptrappningen, från och med SQL Server 2008 R2, införs alternativet LOCK_EXCALATION som en del av ALTER TABLE-angivelsen
Vart och ett av dessa alternativ är definierat för att möjliggöra specifik kontroll över låseskaleringsprocessen:
Table – Detta är standardalternativet för alla nyskapade tabeller, eftersom SQL Server som standard alltid utför låseskalering till tabellnivå, vilket även inkluderar partitionerade tabeller
Auto – Detta alternativ tillåter låseskalering till en partitionsnivå när en tabell är partitionerad. När 5 000 lås förvärvas i en enda partition kommer låseskalering att förvärva ett exklusivt lås (X) på den partitionen medan bordet kommer att förvärva ett avsiktligt exklusivt lås (IX). Om tabellen inte är partitionerad kommer låseskalering att förvärva låset på tabellnivå (motsvarande tabellalternativet).
Även om detta ser ut som ett mycket användbart alternativ måste det användas med stor försiktighet eftersom det lätt kan orsaka ett dödläge. I en situation där vi har två transaktioner på två partitioner där den exklusiva låsningen (X) är förvärvad, och transaktionerna försöker komma åt datumet från partitionen som används av den andra transaktionen, kommer ett dödläge att uppstå
Så, Det är mycket viktigt att noggrant kontrollera dataåtkomstmönstret om det här alternativet är aktiverat, vilket inte är lätt att uppnå, och det är därför det här alternativet inte är standardinställningen i SQL Server
Disable – Det här alternativet inaktiverar helt och hållet låseskalering för en tabell. Återigen måste det här alternativet användas med försiktighet för att undvika att SQL Servers låshanterare tvingas använda en överdriven mängd minne
Som det framgår kan låseskalering vara en utmaning för DBA:er. Om applikationsdesignen kräver att mer än 5 000 rader raderas eller uppdateras samtidigt är en lösning för att undvika låseskalering, och de resulterande effekterna, att dela upp den enda transaktionen i två eller flera transaktioner där var och en hanterar mindre än 5 000 rader, eftersom låseskalering på detta sätt kan undvikas
Få information om aktiva SQL Server-lås
SQL Server tillhandahåller Dynamics Management View (DMV) sys.dm_tran_locks som returnerar information om låshanteringsresurser som för närvarande används, vilket innebär att den visar alla ”levande” lås som förvärvats av transaktioner. Mer information om denna DMV finns i artikeln sys.dm_tran_locks (Transact-SQL).
De viktigaste kolumnerna som används för att identifiera spärren är resource_type, request_mode och resource_description. Vid behov kan fler kolumner som ytterligare resurs för informationsinfo inkluderas under felsökning
Här är exemplet på frågan
Where-klausulen i den här frågan används som ett filter på den resource_type som ska elimineras. från resultaten, de generellt delade lås som förvärvats på databasen eftersom dessa alltid finns på databasnivå
En kort förklaring av de tre kolumnerna som presenteras här:
resource_type – Visar en databasresurs där låsen förvärvas. Kolumnen kan visa ett av följande värden: ALLOCATION_UNIT, APPLICATION, DATABASE, EXTENT, FILE, HOBT, METADATA, OBJECT, PAGE, KEY, RID
request_mode – visar det låsningsläge som förvärvas på resursen
resource_description – visar en kort beskrivning av resursen och fylls inte i för alla låsningslägen. Oftast innehåller kolumnen id för raden, sidan, objektet eller filen, etc
- Author
- Recent Posts
Militär flygfantast och hårdnackad modellbyggare av flygplan i skala. Extremsportsfantast; fallskärmshoppare och bungy jump-instruktör. En gång seriös, nu bara en fritidsfotograf
Se alla inlägg av Nikola Dimitrijevic
- SQL Server trace flags guide; från -1 till 840 – 4 mars 2019
- Hur man hanterar SQL Server WRITELOG väntetyp – 13 juni 2018
- SQL Server prestanda räknare (Batch Requests/sec eller Transactions/sec): vad man ska övervaka och varför – 5 juni 2018