|
|
Geplaatst op 20-07-2016 12:37:53
|
|
|
Overzicht van sluizen
1. Waarom sloten introduceren
Wanneer meerdere gebruikers gelijktijdige bewerkingen op de database uitvoeren, treden de volgende gegevensinconsistenties op:
Ontbrekende updates
Twee gebruikers, A en B, lezen dezelfde gegevens en passen deze aan, en het resultaat van de wijziging van de ene gebruiker vernietigt het resultaat van de andere wijziging, zoals het ticketboekingssysteem,
Vuile leesstof
Gebruiker A wijzigt de gegevens, waarna gebruiker B de gegevens voorleest, maar gebruiker A annuleert om een of andere reden de wijziging van de gegevens, en de gegevens keren terug naar hun oorspronkelijke waarde
Lees niet herhaaldelijk
Gebruiker A leest de gegevens, en vervolgens leest gebruiker B de gegevens en wijzigt ze
De belangrijkste methode voor gelijktijdigheidscontrole is blokkeren, wat betekent dat gebruikers bepaalde bewerkingen gedurende een bepaalde periode niet kunnen uitvoeren om data-inconsistenties te voorkomen
2. Classificatie van sluizen
Er zijn twee categorieën van sluizen:
1 . Vanuit het perspectief van het databasesysteem: het is onderverdeeld in exclusieve locks (d.w.z. exclusieve locks), gedeelde locks en update-locks
MS - SQL Server gebruikt de volgende resource lock patronen.
Slot Mode Beschrijving
Share(s) wordt gebruikt voor bewerkingen die gegevens niet wijzigen of bijwerken (alleen-lezen bewerkingen), zoals SELECT-instructies.
Update (U) wordt gebruikt in updateerbare bronnen. Voorkomt veelvoorkomende vormen van deadlocks wanneer meerdere sessies worden gelezen, vergrendeld en mogelijk een resource-update die kan plaatsvinden.
Exclusief (X) wordt gebruikt voor gegevenswijzigingsoperaties, zoals INSERT, UPDATE of DELETE. Zorg ervoor dat er niet meerdere updates op dezelfde bron tegelijk worden uitgevoerd.
Intent Locks worden gebruikt om een hiërarchie van locks vast te stellen. De typen intentie-locks zijn: Intent Shared (IS), Intent Exclusive (IX) en Intent Exclusive (SIX).
Schema-locks worden gebruikt bij het uitvoeren van bewerkingen die afhankelijk zijn van het tabelschema. De typen schema-locks zijn: schema-modificatie (Sch -M) en schemastabiliteit (Sch -S).
Bulk updates (BU's) worden gebruikt wanneer grote hoeveelheden data naar een tabel worden gekopieerd en een TABLOCK-hint wordt gespecificeerd.
Gedeelde sluizen
Een shared(s) lock maakt het mogelijk dat gelijktijdige transacties een resource kunnen lezen (SELECT). Wanneer er een gedeelde (S) lock op een resource bestaat, kan geen andere transactie de data wijzigen. Laat de gedeelde (S) lock op de resource direct los zodra de data is gelezen, tenzij het transaction isolation level is ingesteld op herhaalbaar of hoger, of de gedeelde (S) lock wordt behouden met een lock hint gedurende de levensduur van de transactie.
Update lock
Update (U) locks voorkomen deadlocks in hun gebruikelijke vorm. Een typisch updatepatroon bestaat uit een transactie die een record leest, een gedeeld (S) slot voor een resource (pagina of rij) ophaalt, en vervolgens een rij wijzigt, waardoor de lock wordt omgezet naar een exclusief (X) slot. Als twee transacties een shared mode-lock op een resource verkrijgen en vervolgens tegelijkertijd proberen de data bij te werken, probeert één transactie de lock om te zetten in een exclusieve (X) lock. De overgang van gedeelde modus naar exclusieve vergrendeling moet even wachten omdat de exclusieve vergrendeling van de ene transactie niet compatibel is met de gedeelde modus vergrendeling van een andere transactie; Er volgt een wachttijd bij het slot. De tweede transactie probeert een exclusieve (X) lock te verkrijgen voor een update. Een deadlock treedt op omdat beide transacties worden omgezet in exclusieve (X) locks, en elke transactie wacht tot de andere transactie de shared mode-lock vrijgeeft.
Om dit potentiële deadlock-probleem te voorkomen, gebruik je een bijgewerkte (U)-lock. Slechts één transactie tegelijk kan een bijgewerkte (U) lock voor een resource krijgen. Als de transactie de resource aanpast, wordt de update (U) lock omgezet in een exclusieve (X) lock. Anders wordt de lock omgezet in een gedeeld slot.
Exclusieve sloten
Exclusieve (X) sloten voorkomen dat gelijktijdige transacties toegang krijgen tot bronnen. Andere transacties kunnen de gegevens die door de exclusieve (X) vergrendeling zijn niet lezen of wijzigen.
Intentievergrendeling
Een intentieslot geeft aan dat SQL Server een gedeelde (S) lock of een exclusieve (X) lock op een van de onderliggende resources in de hiërarchie moet verwerven. Bijvoorbeeld, een share-intentie-lock op tabelniveau geeft aan dat de transactie bedoeld is om een share(s)-lock op een pagina of rij in de tabel te plaatsen. Het instellen van een intentielock op tabelniveau voorkomt dat een andere transactie vervolgens een exclusieve (X) lock op de tabel krijgt die die pagina bevat. Intent locks kunnen de prestaties verbeteren omdat SQL Server alleen de intent lock op tabelniveau controleert om te bepalen of een transactie veilig een lock op die tabel kan verkrijgen. In plaats van de vergrendelingen op elke rij of pagina in de tabel te controleren om te bepalen of een transactie de hele tabel kan vergrendelen.
Intent locks omvatten Intent Sharing (IS), Intent Exclusive (IX) en Intent Exclusive Sharing (SIX).
Slot Mode Beschrijving
Intent Sharing (IS) geeft aan dat de intentie van de transactie een beetje, maar niet alle, van de onderliggende bronnen in de leeshiërarchie is door S-locks op elke bron te plaatsen.
Intent Exclusive (IX) geeft aan dat de intentie van de transactie is om sommige, maar niet alle, van de onderliggende middelen in de hiërarchie te wijzigen door een X-lock op elke resource te plaatsen. IX is een superset van IS.
Exclusieve delen met intentie (SIX) geeft aan dat de intentie van de transactie is om alle onderliggende bronnen in de hiërarchie te lezen en sommige, maar niet alle, onderliggende bronnen te wijzigen door IX-locks op elke bron te plaatsen. Sta gelijktijdige IS-locks toe op top-level resources. Bijvoorbeeld, de SIX-lock van een tabel plaatst een SIX-lock op de tabel (waardoor gelijktijdige IS-locks mogelijk zijn) en een IX-lock op de momenteel gewijzigde pagina (een X-lock op de gewijzigde rij). Hoewel elke resource slechts één SIX lock kan hebben voor een bepaalde periode om te voorkomen dat andere transacties de resource bijwerken, kunnen andere transacties de onderliggende resources in de hiërarchie lezen door tabelniveau IS-locks te verkrijgen.
Exclusieve vergrendeling: Alleen het programma dat de vergrendelingsoperatie uitvoert, mag deze gebruiken, en andere bewerkingen daarop worden niet geaccepteerd. Wanneer je een data-updatecommando uitvoert, gebruikt SQL Server automatisch een exclusieve lock. Wanneer er andere sloten op een object bestaan, kun je er geen exclusieve lock aan toevoegen.
Gedeeld slot: De resource die door het gedeelde slot is vergrendeld, kan door andere gebruikers worden gelezen, maar andere gebruikers kunnen deze niet wijzigen.
Update lock: Wanneer SQL Server klaar is om data bij te werken, vergrendelt het eerst het dataobject zodat de data niet kan worden gewijzigd maar wel gelezen kan worden. Wanneer SQL Server vaststelt dat het data wil bijwerken, vervangt het automatisch het update-slot door een exclusief slot en kan het geen update-slot toevoegen als er andere locks op het object bestaan.
2 . Vanuit het perspectief van de programmeur: het is verdeeld in optimistische en pessimistische vergrendeling.
Optimisme Lock: Vertrouwt volledig op de database om het werk van het slot te beheren.
Pessimistische locks: Programmeurs beheren de afhandeling van de lock op data of objecten zelf.
MS - SQLSERVER gebruikt sloten om pessimistische gelijktijdigheidscontrole te implementeren tussen meerdere gebruikers die tegelijkertijd wijzigingen in de database uitvoeren
3. De deeltjesgrootte van het slot
De lock-granulariteit is de grootte van het geblokkeerde doel, de kleine blokkeringsgranulariteit is een hoge gelijktijdigheid, maar de overhead is groot, en de grote blokkeringsgranulariteit is laag gelijktijdig maar de overhead is klein
SQL Server ondersteunt vergrendelingsgranulariteit voor rijen, pagina's, sleutels, sleutelbereiken, indexen, tabellen of databases
Bronbeschrijving
RID-rijidentificatie. Vroeger sloten ze een rij afzonderlijk in een tafel.
Keyrow-slot in de index. Gebruikt om het bereik van sleutels in serialiseerbare transacties te beschermen.
8 kilobyte (KB) aan datapagina's of indexpagina's.
Uitgebreide schijf Een set van acht aangrenzende datapagina's of indexpagina's.
Tabel De volledige tabel inclusief alle gegevens en indexen.
DB-database.
4. De duur van de lock-up tijd
De duur van een slot is de tijd die nodig is om de bron op het gevraagde niveau te beschermen.
De wachttijd van de gedeelde vergrendeling die wordt gebruikt om leesoperaties te beschermen, hangt af van het niveau van de transactieisolatie. Met het standaard transactie-isolatieniveau van READ COMMITTED wordt de gedeelde vergrendeling alleen gecontroleerd gedurende de duur van de leespagina. Bij een scan wordt het slot pas losgemaakt als het slot op de volgende pagina binnen de scan is verkregen. Als je een HOLDLOCK-prompt specificeert of het transactieisolatieniveau instelt op HERHAALBARE LEES of SERIALISEERBAAR, wordt de vergrendeling pas vrijgegeven nadat de transactie is beëindigd.
Afhankelijk van de gelijktijdige optie die voor de cursor is ingesteld, kan de cursor een scrolllock krijgen in gedeelde modus om de extractie te beschermen. Wanneer een scrolllock nodig is, wordt deze pas losgelaten wanneer de cursor wordt verwijderd of gesloten, afhankelijk van wat het eerst gebeurt. Als je echter een HOLDLOCK specificeert, wordt de scrolllock pas losgelaten aan het einde van de transactie.
De exclusieve vergrendeling die de update beschermt, wordt pas aan het einde van de transactie vrijgegeven.
Als een verbinding probeert een slot te verkrijgen dat conflicteert met een slot dat door een andere verbinding wordt bestuurd, wordt de verbinding die probeert het slot te verkrijgen geblokkeerd totdat:
De conflicterende lock wordt vrijgegeven en de verbinding krijgt de gevraagde lock.
De verbindingstijd is verstreken. Er is standaard geen time-out interval, maar sommige apps stellen time-out intervallen in om oneindig wachten te voorkomen
Vijf aanpassingen van locks in SQL Server
1 Omgaan met deadlocks en prioritaire deadlock-prioriteiten instellen
Deadlock is het eindeloze wachten veroorzaakt doordat meerdere gebruikers verschillende blokkades aanvragen, omdat de aanvrager een deel van het blokkeringsrecht heeft en wacht op de gedeeltelijke blokkering die eigendom is van andere gebruikers
Je kunt de SET DEADLOCK_PRIORITY gebruiken om te bepalen hoe de sessie reageert bij een deadlock-conditie. Als beide processen de data vergrendelen, en elk proces zijn eigen lock niet kan openen totdat het andere proces zijn eigen lock opgeeft, ontstaat er een deadlock-situatie.
2 Beheer time-outs en stel lock-time-outduur in.
@@LOCK_TIMEOUT Geeft de huidige locktime-outinstelling voor de huidige sessie terug in milliseconden
De SET LOCK_TIMEOUT-instelling stelt de applicatie in staat de maximale tijd in te stellen die de instructie wacht om de bron te blokkeren. Wanneer de wachttijd van de instructie langer is dan de LOCK_TIMEOUT-instelling, annuleert het systeem automatisch de blokkeringsinstructie en geeft het de applicatie een foutmelding van 1222 dat de time-out periode voor het lock-verzoek is overschreden
voorbeeld
In het volgende voorbeeld is de lock-timeoutperiode ingesteld op 1.800 milliseconden.
ZET LOCK_TIMEOUT1800
3) Stel het isolatieniveau voor transacties in.
4) Gebruik tabelniveau-lock hints voor SELECT, INSERT, UPDATE en DELETE instructies.
5) Configureer de vergrendelingsgranulariteit van de index
Je kunt sp_indexoption systeemstored procedures gebruiken om de vergrendelingsgranulariteit voor indexering in te stellen
6. Bekijk de informatie van het slot
1 Voer EXEC uit SP_LOCK rapporteer informatie over het slot
2 Druk op Ctrl + 2 in de query-analyzer om de informatie van het slot te zien
7. Voorzorgsmaatregelen voor gebruik
Hoe je impasses kunt vermijden
1. Bij het gebruik van transacties, probeer het logische verwerkingsproces van transacties te verkorten en dien transacties vroeg in of rol terug.
2 Stel de time-outparameter van de deadlock in op een redelijk bereik, zoals: 3 minuten - 10 minuten; Na die tijd wordt de operatie automatisch stopgezet om te voorkomen dat het proces vastloopt;
3. Het programma optimaliseren, het fenomeen deadlock controleren en vermijden;
4. Test alle scripts en SP's zorgvuldig voordat je de exacte versie uitvindt.
5 Alle SP's moeten foutafhandeling hebben (via @error)
6 Wijzig het standaardniveau van SQL SERVER-transacties niet. Geforceerde vergrendeling wordt niet aanbevolen
Los het probleem op: Hoe sluit je een rijtabeldatabase
8. Verschillende vragen over sloten
1 Hoe sluit je een rij van een tafel
SET TRANSACTIONISOLATION LEVEL READUNCOMMITTED
SELECTEER *UIT tabel ROWLOCKWHERE id = 1
2 Vergrendel een tabel in de database
SELECTEER *UIT tafel MET( HOLDLOCK )
Lock-verklaring:
sybase:
Bijwerk-tabel set Col1 = Col1 waarom1= 0 ;
MSSQL:
selecteer col1 uit de tabel (tablockx) waarbij 1= 0 ;
oracle:
TAFEL VERGRENDELEN IN EXCLUSIEVE MODUS;
Nadat het slot is vergrendeld, kan niemand anders het bedienen totdat de vergrendelde gebruiker het ontgrendelt, en het wordt ontgrendeld met commit of rollback
Een paar voorbeelden helpen je indruk te verdiepen
Settafel1(A,B,C)
A B C
a1 b1 c1
a2 b2 c2
A3 B3 C3
1) Exclusief slot
Maak twee nieuwe verbindingen
Voer de volgende instructie uit in de eerste verbinding
Start Tran
Updatetabel 1
set A= ' aa '
waarbij B= ' b2 '
wacht op vertraging' 00:00:30' --wacht 30 seconden
commit tran
Voer de volgende instructie uit in de tweede verbinding
Start Tran
Selecteer *uit tabel1
waarbij B= ' b2 '
commit tran
Als bovenstaande twee statements tegelijkertijd worden uitgevoerd, moet de select-query wachten tot de update wordt uitgevoerd, dat wil zeggen, 30 seconden wachten
2) Gedeelde vergrendeling
Voer de volgende instructie uit in de eerste verbinding
Start Tran
selecteer *uit tabel1 holdlock - De holdlock wordt kunstmatig aan de lock toegevoegd
waarbij B= ' b2 '
wacht op vertraging' 00:00:30' --wacht 30 seconden
commit tran
Voer de volgende instructie uit in de tweede verbinding
Start Tran
selecteer A,C uit tabel1
waarbij B= ' b2 '
Updatetabel 1
set A= ' aa '
waarbij B= ' b2 '
commit tran
Als bovenstaande twee statements tegelijkertijd worden uitgevoerd, kan de select-query in de tweede verbinding worden uitgevoerd
De update moet wachten tot de eerste transactie de gedeelde lock vrijgeeft en deze omzetten in een exclusieve lock voordat deze kan worden uitgevoerd, dat wil zeggen, 30 seconden wachten
3) Impasse
Toegevoegde tabel2(D,E)
D E
d1 e1
d2 e2
Voer de volgende instructie uit in de eerste verbinding
Start Tran
Updatetabel 1
set A= ' aa '
waarbij B= ' b2 '
wacht op vertraging' 00:00:30'
Updatetabel 2
set D= ' d5 '
waarbij E = ' e1 '
commit tran
Voer de volgende instructie uit in de tweede verbinding
Start Tran
Updatetabel 2
set D= ' d5 '
waarbij E = ' e1 '
wacht op vertraging' 00:00:10'
Updatetabel 1
set A= ' aa '
waarbij B= ' b2 '
commit tran
Tegelijkertijd detecteert het systeem de deadlock en stopt het proces
Om toe te voegen:
Tabelniveau-vergrendelingshints ondersteund door SQL Server 2000
HOLDLOCK houdt de gedeelde vergrendeling vast totdat de volledige transactie is voltooid en moet worden vrijgegeven zodra het vergrendelde object niet meer nodig is, gelijk aan het SERIALIZABLE transactie-isolatieniveau
De NOLOCK-instructie wordt uitgevoerd zonder een gedeelde vergrendeling uit te voeren, waardoor dirty reads mogelijk zijn, wat gelijk is aan het READ UNCOMMITTED transactieisolatieniveau
PAGLOCK gebruikt meerdere paginavergrendelingen waarbij één tabelslot wordt gebruikt
READPAST laat de SQL-server alle vergrendelde regels overslaan en transacties uitvoeren, en voor READ UNCOMMITTED transactieisolatieniveaus alleen RID-locks overslaan, niet pagina-, zone- en tabellocks
ROWLOCK handhaaft het gebruik van rowlocks
TABLOCKX handhaaft het gebruik van een exclusieve tabel-niveau lock, die voorkomt dat andere transacties de tabel tijdens de transactie gebruiken
UPLOCK dwingt het gebruik van updates af bij het lezen van een tabel zonder gedeelde vergrendeling
App Lock:
Een applicatielock is een lock die wordt gegenereerd door clientcode, niet een lock die door SQL Server zelf wordt gegenereerd
Twee processen voor het afhandelen van applicatievergrendelingen
sp_getapplock Lock-applicatiebronnen
sp_releaseapplock Ontgrendel de applicatiebronnen
Opmerking: Het verschil tussen het vergrendelen van een tabel in een database
SELECTEER *UIT tabel MET( HOLDLOCK ) Andere transacties kunnen de tabel lezen, maar kunnen niet bijwerken en verwijderen
SELECTEER *VAN de tabel MET(TABLOCKX) Andere transacties kunnen de tabel niet lezen, bijwerken en verwijderen
|
Vorig:Er was geen eindpunt dat luisterde op http://localhost:111/xxx.svc dat c...Volgend:SQL locks NOLOCK, HOLDLOCK, UPDLOCK, TABLOCK, TABLOCKX
|
