|
|
Veröffentlicht am 20.07.2016 12:37:53
|
|
|
Überblick über Schleusen
1. Warum Schlösser einführen
Wenn mehrere Benutzer gleichzeitig Operationen auf der Datenbank durchführen, treten folgende Dateninkonsistenzen auf:
Fehlende Updates
Zwei Nutzer, A und B, lesen dieselben Daten und ändern sie, und das Ergebnis der Änderung eines Benutzers zerstört das Ergebnis der anderen Änderung, wie zum Beispiel das Ticketbuchungssystem,
Schmutzige Lektüre
Benutzer A verändert die Daten, und dann liest Benutzer B die Daten aus, aber Benutzer A hebt die Änderung der Daten aus irgendeinem Grund auf, und die Daten kehren zu ihrem ursprünglichen Wert zurück
Nicht wiederholt lesen
Benutzer A liest die Daten, und dann liest Benutzer B die Daten und verändert sie
Die Hauptmethode der Nebenläufigkeitskontrolle ist das Blockieren, das bedeutet, dass Benutzer bestimmte Operationen für einen bestimmten Zeitraum durchführen, um Dateninkonsistenzen zu vermeiden
2. Klassifizierung der Schleusen
Es gibt zwei Unterteilungen in Kategorien von Schleusen:
1 . Aus Sicht des Datenbanksystems: Es ist unterteilt in exklusive Sperren (d. h. exklusive Sperren), geteilte Sperren und Aktualisierungssperren
MS - SQL Server verwendet die folgenden Ressourcensperrmuster.
Beschreibung des Sperrmodus
Share(s) wird für Operationen verwendet, die Daten nicht ändern oder aktualisieren (Nur-Lese-Operationen), wie z. B. SELECT-Anweisungen.
Update (U) wird in aktualisierten Ressourcen verwendet. Verhindert häufige Formen von Deadlocks, wenn mehrere Sitzungen gelesen, gesperrt und möglicherweise ein Ressourcen-Update auftreten kann.
Exclusive (X) wird für Datenänderungsoperationen wie INSERT, UPDATE oder DELETE verwendet. Stellen Sie sicher, dass nicht mehrere Updates gleichzeitig auf derselben Ressource durchgeführt werden.
Intent Locks werden verwendet, um eine Hierarchie von Locks zu etablieren. Die Arten von Intent-Sperren sind: Intent Shared (IS), Intent Exclusive (IX) und Intent Exclusive (SIX).
Schema-Sperren werden verwendet, wenn Operationen ausgeführt werden, die vom Tabellenschema abhängen. Die Arten von Schema-Sperren sind: Schema-Modifikation (Sch-M) und Schema-Stabilität (Sch-S).
Bulk-Updates (BUs) werden verwendet, wenn große Datenmengen in eine Tabelle kopiert und ein TABLOCK-Hinweis angegeben wird.
Gemeinsame Schleusen
Eine Shared(s)-Sperre ermöglicht es gleichzeitigen Transaktionen, eine Ressource zu lesen (SELECT). Wenn eine geteilte (S)-Sperre auf einer Ressource existiert, kann keine andere Transaktion die Daten ändern. Lösen Sie die geteilte (S)-Sperre auf der Ressource, sobald die Daten gelesen wurden, es sei denn, die Transaktionsisolationsstufe ist auf wiederholbar oder höher gesetzt oder die geteilte (S)-Sperre wird mit einem Lock-Hinweis für die gesamte Lebensdauer der Transaktion behalten.
Update-Sperre
Update-(U)-Sperren verhindern Deadlocks in ihrer üblichen Form. Ein typisches Aktualisierungsmuster besteht aus einer Transaktion, die einen Datensatz liest, ein gemeinsames (S) Schloss für eine Ressource (Seite oder Zeile) erhält und dann eine Zeile modifiziert, was erfordert, dass das Schloss in ein exklusives (X) Schloss umgewandelt wird. Wenn zwei Transaktionen eine Shared-Mode-Sperre auf einer Ressource erhalten und dann gleichzeitig versuchen, die Daten zu aktualisieren, versucht eine Transaktion, die Sperre in eine exklusive (X) Sperre umzuwandeln. Der Übergang vom Shared-Modus zum exklusiven Lock muss eine Weile warten, da die exklusive Sperre einer Transaktion nicht mit der Shared-Mode-Sperre einer anderen Transaktion kompatibel ist; Es kommt eine Wartezeit auf die Sperre. Die zweite Transaktion versucht, eine exklusive (X) Sperre für ein Update zu erhalten. Ein Deadlock tritt auf, weil beide Transaktionen in exklusive (X) Sperren umgewandelt werden und jede Transaktion darauf wartet, dass die andere Transaktion die Shared-Mode-Sperre aufhebt.
Um dieses potenzielle Deadlock-Problem zu vermeiden, verwenden Sie eine aktualisierte (U)-Sperre. Nur eine Transaktion gleichzeitig kann eine aktualisierte (U)-Sperre für eine Ressource erhalten. Wenn die Transaktion die Ressource verändert, wird das Update-(U)-Schloss in ein exklusives (X)-Lock umgewandelt. Andernfalls wird das Schloss in ein gemeinsames Schloss umgewandelt.
Exklusive Schlösser
Exklusive (X) Sperren verhindern, dass gleichzeitige Transaktionen auf Ressourcen zugreifen. Andere Transaktionen können die durch das exklusive (X)-Schloss gesperrten Daten nicht lesen oder verändern.
Absichtssperre
Eine Intent-Sperre zeigt an, dass SQL Server eine gemeinsame (S) Sperre oder eine exklusive (X) Sperre für einige der zugrundeliegenden Ressourcen in der Hierarchie erwerben muss. Zum Beispiel zeigt eine auf Tabellenebene platzierte Share-Intent-Sperre an, dass die Transaktion beabsichtigt, eine Share(s)-Sperre auf einer Seite oder Zeile in der Tabelle zu platzieren. Das Setzen einer Intent-Sperre auf Tabellenebene verhindert, dass eine andere Transaktion anschließend eine exklusive (X)-Sperre auf der Tabelle erhält, die diese Seite enthält. Intent Locks können die Leistung verbessern, da SQL Server die Intent Lock nur auf Tabellenebene prüft, um zu bestimmen, ob eine Transaktion sicher eine Sperre in dieser Tabelle erhalten kann. Anstatt die Sperren auf jeder Zeile oder Seite in der Tabelle zu überprüfen, um festzustellen, ob eine Transaktion die gesamte Tabelle sperren kann.
Intent-Sperren umfassen Intent Sharing (IS), Intent Exclusive (IX) und Intent Exclusive Sharing (SIX).
Beschreibung des Sperrmodus
Intent Sharing (IS) zeigt an, dass die Absicht der Transaktion einige, aber nicht alle zugrundeliegenden Ressourcen in der Lesehierarchie ist, indem S-Locks auf jede Ressource gesetzt werden.
Intent Exclusive (IX) zeigt an, dass die Absicht der Transaktion darin besteht, einige, aber nicht alle zugrundeliegenden Ressourcen in der Hierarchie zu verändern, indem jede Ressource ein X-Lock angebracht wird. IX ist eine Übermenge von IS.
Exklusives Teilen mit Absicht (SIX) zeigt an, dass die Absicht der Transaktion darin besteht, alle zugrundeliegenden Ressourcen in der Hierarchie zu lesen und einige, aber nicht alle, der zugrundeliegenden Ressourcen durch das Anbringen von IX-Sperren auf jede Ressource zu verändern. Gleichzeitig IS-Sperren auf obersten Ressourcen zulassen. Zum Beispiel platziert das SIX-Lock einer Tabelle ein SIX-Lock auf die Tabelle (was gleichzeitig IS-Locks erlaubt) und ein IX-Lock auf der aktuell modifizierten Seite (ein X-Lock auf der modifizierten Reihe). Während jede Ressource nur eine SIX-Sperre für einen bestimmten Zeitraum haben kann, um zu verhindern, dass andere Transaktionen die Ressource aktualisieren, können andere Transaktionen die zugrunde liegenden Ressourcen in der Hierarchie lesen, indem sie Tabellen-IS-Sperren erwerben.
Exklusive Sperre: Nur das Programm, das die Sperroperation durchführt, darf sie verwenden, und andere Operationen darauf werden nicht akzeptiert. Wenn Sie einen Daten-Update-Befehl ausführen, verwendet SQL Server automatisch ein exklusives Lock. Wenn andere Sperren auf einem Objekt existieren, kann man kein exklusives Schloss hinzufügen.
Gemeinsames Schloss: Die vom geteilte Schloss gesperrte Ressource kann von anderen Benutzern gelesen werden, aber andere Benutzer können sie nicht ändern.
Update-Sperre: Wenn SQL Server bereit ist, Daten zu aktualisieren, sperrt es zunächst das Datenobjekt, sodass die Daten nicht verändert, sondern gelesen werden können. Wenn SQL Server feststellt, dass es Daten aktualisieren möchte, ersetzt es das Update-Lock automatisch durch ein exklusives Schloss und kann kein Update-Lock hinzufügen, wenn andere Sperren auf dem Objekt vorhanden sind.
2 . Aus Sicht des Programmierers: Sie ist in optimistische und pessimistische Bindung unterteilt.
Optimism Lock: Ist vollständig auf die Datenbank angewiesen, um die Arbeit des Locks zu verwalten.
Pessimistische Sperren: Programmierer verwalten die Lock-Handhabung von Daten oder Objekten selbst.
MS - SQLSERVER verwendet Sperren, um eine pessimistische Nebenläufigkeitskontrolle zwischen mehreren Benutzern zu implementieren, die gleichzeitig Änderungen in der Datenbank vornehmen.
3. Die Partikelgröße des Schlosses
Die Sperrgranularität ist die Größe des blockierten Ziels, die kleine Blockgranularität ist eine hohe Parallelität, aber der Overhead ist groß, und die große Blockierungsgranularität ist niedrig nebenläufig, aber der Overhead ist gering
SQL Server unterstützt Sperrgranularität für Zeilen, Seiten, Schlüssel, Schlüsselbereiche, Indizes, Tabellen oder Datenbanken
Ressourcenbeschreibung
RID-Zeilenkennung. Früher hat man eine Reihe einzeln in einem Tisch gesperrt.
Schlüsselreihenschloss im Index. Verwendet zum Schutz des Schlüsselbereichs bei serialisierbaren Transaktionen.
8 Kilobyte (KB) Datenseiten oder Indexseiten.
Erweiterte Festplatte Ein Satz von acht benachbarten Datenseiten oder Indexseiten.
Tabelle Die gesamte Tabelle einschließlich aller Daten und Indizes.
Datenbank.
4. Die Länge der Sperrzeit
Die Dauer, die eine Sperre gehalten wird, entspricht der Zeit, die benötigt wird, um die Ressource auf der angeforderten Ebene zu schützen.
Die Wartezeit des gemeinsamen Schlosses, das zum Schutz von Leseoperationen verwendet wird, hängt vom Transaktionsisolationsniveau ab. Mit dem Standard-Transaktionsisolationslevel READ COMCOMMITTED wird das gemeinsame Schloss nur für die Dauer der Leseseite gesteuert. Bei einem Scan wird das Schloss erst freigegeben, wenn es auf der nächsten Seite im Scan erfasst wird. Wenn Sie einen HOLDLOCK-Prompt angeben oder das Transaktionsisolationsniveau auf REPEATABLE READ oder SERIALIZABLE setzen, wird das Schloss erst aufgehoben, wenn die Transaktion endet.
Je nach Nebenläufigkeitsoption für den Cursor kann der Cursor im Shared-Modus eine Scroll-Lock erhalten, um den Extrakt zu schützen. Wenn ein Scroll-Lock erforderlich ist, wird dieser erst aufgehoben, wenn der Cursor das nächste Mal extrahiert oder geschlossen wird, je nachdem, was zuerst eintritt. Wenn Sie jedoch einen HOLDLOCK angeben, wird das Scroll-Lock erst am Ende der Transaktion aufgehoben.
Die exklusive Sperre, die zum Schutz des Updates verwendet wird, wird erst am Ende der Transaktion freigegeben.
Wenn eine Verbindung versucht, ein Schloss zu erlangen, das mit einem von einer anderen Verbindung kontrollierten Schloss kollidiert, wird die Verbindung, die versucht, das Schloss zu erlangen, blockiert, bis:
Die widersprüchliche Sperre wird freigegeben und die Verbindung erhält die angeforderte Sperre.
Die Verbindungszeitzeit ist abgelaufen. Standardmäßig gibt es kein Timeout-Intervall, aber einige Apps setzen Timeout-Intervalle, um unbegrenztes Warten zu verhindern
Fünf Anpassungen von Sperren in SQL Server
1 Deadlocks handhaben und Prioritäten für Deadlocks festlegen
Deadlock ist das endlose Warten, das durch mehrere Nutzer verursacht wird, die unterschiedliche Blockierungen beantragen, weil der Antragsteller einen Teil des Blockierungsrechts hat und auf die teilweise Sperre wartet, die anderen Nutzern gehören
Sie können die SET-DEADLOCK_PRIORITY verwenden, um zu steuern, wie die Sitzung im Falle einer Deadlock-Bedingung reagiert. Wenn beide Prozesse die Daten sperren und jeder Prozess seine eigene Sperre nicht öffnen kann, bis der andere Prozess seine eigene Sperre freigibt, entsteht eine Deadlock-Situation.
2 Timeouts verwalten und Lock-Timeout-Dauern festlegen.
@@LOCK_TIMEOUT Gibt die aktuelle Sperrzeit-Einstellung für die aktuelle Sitzung in Millisekunden zurück
Die SET LOCK_TIMEOUT-Einstellung erlaubt es der Anwendung, die maximale Zeit festzulegen, in der die Anweisung wartet, um die Ressource zu blockieren. Wenn die Wartezeit der Anweisung länger ist als die LOCK_TIMEOUT-Einstellung, hebt das System die Blockierungsanweisung automatisch ab und erhält der Anwendung die Fehlermeldung 1222, dass die Zeitabgrenzung der Sperranforderung überschritten wurde
Beispiel
Im folgenden Beispiel ist der Sperrzeitpunkt auf 1.800 Millisekunden gesetzt.
SET LOCK_TIMEOUT1800
3) Stellen Sie das Transaktionsisolationsniveau ein.
4) Verwenden Sie Tabellen-Lock-Hinweise für SELECT, INSERT, UPDATE und DELETE-Anweisungen.
5) Konfigurieren Sie die Sperrgranularität des Index
Sie können sp_indexoption systemgespeicherten Prozeduren verwenden, um die Sperrgranularität für die Indexierung einzustellen
6. Sehen Sie sich die Informationen zur Schleuse an
1 EXEC ausführen SP_LOCK Informationen über das Schloss melden
2 Drücken Sie Strg + 2 im Abfrageanalysator, um die Informationen des Schlosses zu sehen
7. Vorsichtsmaßnahmen bei der Anwendung
Wie man Blockaden vermeidet
1. Versuchen Sie bei der Verwendung von Transaktionen, den logischen Verarbeitungsprozess zu verkürzen und Transaktionen frühzeitig einzureichen oder zurückzurollen.
2 Setzen Sie den Deadlock-Timeout-Parameter auf einen angemessenen Bereich, z. B. 3 Minuten – 10 Minuten; Nach dieser Zeit wird der Vorgang automatisch abgebrochen, um ein Stocken des Prozesses zu vermeiden;
3. Optimierung des Programms, Überprüfung und Vermeidung des Phänomens des Deadlocks;
4. Alle Skripte und SPs sorgfältig vor der exakten Version testen.
5 Alle SPs müssen eine Fehlerbehandlung (über @error) haben.
6 Ändern Sie nicht die Standardstufe der SQL-SERVER-Transaktionen. Zwangsverriegelung wird nicht empfohlen
Lösen Sie das Problem: Wie man eine Zeilentabellendatenbank sperrt.
8. Mehrere Fragen zu Schlössern
1 Wie man eine Reihe eines Tisches abschließt
TRANSACTIONISOLATION LEVEL READUNCOMMITTED SETZEN
AUSWÄHLEN *AUS der Tabelle ROWLOCKWHERE id = 1
2 Sperre eine Tabelle in der Datenbank
AUSWÄHLEN *AUS der Tabelle MIT( HOLDLOCK )
Lock-Aussage:
sybase:
Aktualisieren Sie die Tabelle Col1 = Col1 wobei 1= 0 ;
MSSQL:
Wähle kol1 aus der Tabelle (tablockx), wobei 1= 0 ;
oracle:
TABELLENSPERRE IM EXKLUSIVEN MODUS ;
Nachdem das Schloss verschlossen ist, kann es niemand mehr bedienen, bis der gesperrte Benutzer es entsperrt, und es wird mit Commit oder Rollback entriegelt
Einige Beispiele helfen Ihnen, Ihren Eindruck zu vertiefen
Set table1(A,B,C)
A B C
a1 b1 c1
a2 b2 c2
a3 b3 c3
1) Exklusives Schloss
Zwei neue Verbindungen schaffen
Führe die folgende Anweisung in der ersten Verbindung aus
Start Tran
Aktualisierungstabelle1
Set A= ' aa '
wobei B = ' b2 '
Warte auf Verzögerung' 00:00:30' --Warte 30 Sekunden
Commit tran
Führe die folgende Anweisung in der zweiten Verbindung aus
Start Tran
*aus Tabelle1 auswählen
wobei B = ' b2 '
Commit tran
Wenn die beiden oben genannten Anweisungen gleichzeitig ausgeführt werden, muss die Select-Abfrage warten, bis das Update ausgeführt wird, also 30 Sekunden warten
2) Gemeinsames Schloss
Führe die folgende Anweisung in der ersten Verbindung aus
Start Tran
auswählen *aus Tabelle1 holdlock – Der holdlock wird künstlich zum Lock hinzugefügt
wobei B = ' b2 '
Warte auf Verzögerung' 00:00:30' --Warte 30 Sekunden
Commit tran
Führe die folgende Anweisung in der zweiten Verbindung aus
Start Tran
A,C aus Tabelle1 auswählen
wobei B = ' b2 '
Aktualisierungstabelle1
Set A= ' aa '
wobei B = ' b2 '
Commit tran
Wenn die beiden oben genannten Anweisungen gleichzeitig ausgeführt werden, kann die Select-Abfrage in der zweiten Verbindung ausgeführt werden
Das Update muss warten, bis die erste Transaktion das geteilte Schloss freigibt und es in ein exklusives Schloss umwandelt, bevor es ausgeführt werden kann, also 30 Sekunden warten
3) Pattsituation
Tabelle2(D,E) hinzugefügt
D E
d1 e1
d2 e2
Führe die folgende Anweisung in der ersten Verbindung aus
Start Tran
Aktualisierungstabelle1
Set A= ' aa '
wobei B = ' b2 '
Warte auf Verzögerung' 00:00:30'
Aktualisierungstabelle2
Menge D= ' d5 '
wobei E = ' e1 '
Commit tran
Führe die folgende Anweisung in der zweiten Verbindung aus
Start Tran
Aktualisierungstabelle2
Menge D= ' d5 '
wobei E = ' e1 '
Warten auf Verzögerung' 00:00:10'
Aktualisierungstabelle1
Set A= ' aa '
wobei B = ' b2 '
Commit tran
Gleichzeitig erkennt das System die Blockade und bricht den Prozess ab
Zur Ergänzung:
Tabellenebene-Sperrhinweise, die von SQL Server 2000 unterstützt werden
HOLDLOCK hält das geteilte Schloss, bis die gesamte Transaktion abgeschlossen ist, und sollte freigegeben werden, sobald das gesperrte Objekt nicht mehr benötigt wird, entsprechend dem SERIALIZABLE-Transaktionsisolationsniveau
Die NOLOCK-Anweisung wird ausgeführt, ohne eine gemeinsame Sperre auszustellen, was Dirty Reads erlaubt, was dem READ UNCOMMITTED Transaktionsisolationslevel entspricht
PAGLOCK verwendet mehrere Seitensperren, bei denen eine Tabellensperre verwendet wird
READPAST erlaubt es dem SQL-Server, alle gesperrten Zeilen zu überspringen und Transaktionen auszuführen, und bei READ UNCOMMITTED Transaktionsisolationsstufen nur RID-Sperren zu überspringen, nicht Seiten-, Zonen- und Tabellensperren
ROWLOCK erzwingt die Verwendung von Rowlocks
TABLOCKX erzwingt die Verwendung einer exklusiven Tabellensperre, die verhindert, dass andere Transaktionen die Tabelle während der Transaktion verwenden
UPLOCK erzwingt die Verwendung von Updates beim Lesen einer Tabelle ohne geteilte Sperre
App-Sperre:
Eine Anwendungssperre ist eine Sperre, die durch Client-Code generiert wird, nicht eine Sperre, die von SQL Server selbst erzeugt wird
Zwei Prozesse zur Behandlung von Anwendungssperren
sp_getapplock Anwendungsressourcen sperren
sp_releaseapplock Schalten Sie die Anwendungsressourcen frei
Hinweis: Der Unterschied zwischen dem Sperren einer Tabelle in einer Datenbank
AUSWÄHLEN *AUS der Tabelle WITH( HOLDLOCK ) Andere Transaktionen können die Tabelle lesen, können aber nicht aktualisieren und löschen
AUSWÄHLEN *AUS der Tabelle WITH(TABLOCKX) Andere Transaktionen können die Tabelle nicht lesen, aktualisieren und löschen
|
Vorhergehend:Es gab keinen Endpunkt, der zuhörte, http://localhost:111/xxx.svc das k...Nächster:SQL locks NOLOCK, HOLDLOCK, UPDLOCK, TABLOCK, TABLOCKX
|
