Beschreiben von Blockierung und Sperren
Das Sperren ist ein wichtiges Merkmal relationaler Datenbanken, das für die Aufrechterhaltung der Atomität, Konsistenz und Isolationseigenschaften des ACID-Modells unerlässlich ist. Alle RDBMSs blockieren Aktionen, die gegen die Konsistenz und Isolierung von Datenbankschreibvorgängen verstoßen würden. SQL-Programmierer müssen Transaktionen an den richtigen Stellen starten und beenden, um die Datenkonsistenz sicherzustellen. Das Datenbankmodul bietet Sperrmechanismen zum Schutz der logischen Konsistenz der betroffenen Tabellen, die für das relationale Modell von grundlegender Grundlage sind.
In SQL Server tritt das Blockieren auf, wenn ein Prozess eine Sperre für eine bestimmte Ressource (Zeile, Seite, Tabelle, Datenbank) hält und ein zweiter Prozess versucht, eine Sperre mit einem inkompatiblen Sperrtyp für dieselbe Ressource zu erhalten. In der Regel werden Sperren für einen kurzen Zeitraum gehalten, und sobald der Prozess die Sperre loslässt, kann der blockierte Prozess die Sperre abrufen und seine Transaktion abschließen.
SQL Server sperrt die kleinste Menge an Daten, die zum Abschließen einer Transaktion erforderlich sind, was eine maximale Parallelität ermöglicht. Wenn SQL Server beispielsweise eine einzelne Zeile sperrt, bleiben alle anderen Zeilen in der Tabelle für andere Prozesse verfügbar und ermöglichen gleichzeitige Arbeit. Jede Sperre erfordert jedoch Speicherressourcen, sodass es für einen Prozess nicht kosteneffizient ist, Tausende einzelner Sperren auf einer einzelnen Tabelle zu speichern. Um Parallelität und Kosten in Einklang zu bringen, verwendet SQL Server eine Technik, die als Sperreskalation bezeichnet wird. Wenn mehr als 5.000 Zeilen für ein einzelnes Objekt in einer einzelnen Anweisung gesperrt werden müssen, eskaliert SQL Server die mehreren Zeilensperren an eine einzelne Tabellensperre.
Das Sperren ist ein normales Verhalten und tritt häufig im Laufe des Tages auf. Es wird erst problematisch, wenn eine Blockierung auftritt, die nicht schnell beseitigt wird. Es gibt zwei Arten von Leistungsproblemen, die durch Blockieren verursacht werden:
- Ein Prozess enthält Sperren für eine Reihe von Ressourcen für einen längeren Zeitraum, bevor er sie freigeben kann, was dazu führt, dass andere Prozesse die Abfrageleistung und Parallelität blockieren und beeinträchtigen.
- Ein Prozess erwirbt Sperren für eine Reihe von Ressourcen und gibt sie nie frei, sodass ein Administratoreingriff erforderlich ist, um das Problem zu lösen.
Deadlocking ist ein weiteres Blockierungsszenario, das auftritt, wenn eine Transaktion eine Sperre für eine Ressource enthält und eine andere Transaktion eine Sperre für eine andere Ressource enthält. Jede Transaktion versucht dann, eine Sperre für die Ressource abzurufen, die derzeit durch die andere Transaktion gesperrt ist, was zu einer unendlichen Wartezeit führt, da keine Transaktion abgeschlossen werden kann. Das SQL Server-Modul erkennt diese Szenarien und löst den Deadlock auf, indem eine der Transaktionen beendet wird, basierend darauf, welche Transaktion die geringste Arbeit ausgeführt hat, die zurückgesetzt werden muss. Die Transaktion, die getötet wird, wird als Deadlock-Opfer bezeichnet. Deadlocks werden in der erweiterten Ereignissitzung system_health erfasst, die standardmäßig aktiviert ist.
Es ist wichtig, das Konzept einer Transaktion zu verstehen. AutoCommit ist der Standardmodus von SQL Server und Azure SQL-Datenbank. Dies bedeutet, dass die von der folgenden Anweisung vorgenommenen Änderungen automatisch im Transaktionsprotokoll der Datenbank aufgezeichnet werden.
INSERT INTO DemoTable (A) VALUES (1);
Um Entwicklern die genauere Kontrolle über ihren Anwendungscode zu ermöglichen, ermöglicht SQL Server auch die explizite Steuerung Ihrer Transaktionen. Die folgende Abfrage würde eine Sperre für eine Zeile in der DemoTable-Tabelle ausführen, die erst freigegeben werden würde, wenn ein nachfolgender Befehl zum Commit der Transaktion hinzugefügt wurde.
BEGIN TRANSACTION
INSERT INTO DemoTable (A) VALUES (1);
Die richtige Methode zum Schreiben der folgenden Abfrage lautet wie folgt:
BEGIN TRANSACTION
INSERT INTO DemoTable (A) VALUES (1);
COMMIT TRANSACTION
Der Befehl COMMIT TRANSACTION committet einen Datensatz der Änderungen explizit an das Transaktionsprotokoll. Die geänderten Daten werden schließlich asynchron in die Datendatei gelangen. Diese Transaktionen stellen eine Arbeitseinheit für das Datenbankmodul dar. Wenn der Entwickler vergisst, den Befehl COMMIT TRANSACTION auszugeben, bleibt die Transaktion offen, und die Sperren werden nicht aufgehoben. Dies ist einer der Hauptgründe für lang andauernde Transaktionen.
Der andere Mechanismus, den das Datenbankmodul verwendet, um die Parallelität der Datenbank zu unterstützen, ist die Zeilenversionsverwaltung. Wenn für die Datenbank eine Isolationsstufe für die Zeilenversionsverwaltung aktiviert ist, verwaltet das Modul Versionen jeder geänderten Zeile in TempDB. Dies wird in der Regel in gemischten Arbeitslasten verwendet, um zu verhindern, dass Leseabfragen Abfragen blockieren, die in die Datenbank schreiben.
Führen Sie die folgende Abfrage aus, um geöffnete Transaktionen zu überwachen, die auf Commit oder Rollback warten:
SELECT tst.session_id, [database_name] = db_name(s.database_id)
, tat.transaction_begin_time
, transaction_duration_s = datediff(s, tat.transaction_begin_time, sysdatetime())
, transaction_type = CASE tat.transaction_type WHEN 1 THEN 'Read/write transaction'
WHEN 2 THEN 'Read-only transaction'
WHEN 3 THEN 'System transaction'
WHEN 4 THEN 'Distributed transaction' END
, input_buffer = ib.event_info, tat.transaction_uow
, transaction_state = CASE tat.transaction_state
WHEN 0 THEN 'The transaction has not been completely initialized yet.'
WHEN 1 THEN 'The transaction has been initialized but has not started.'
WHEN 2 THEN 'The transaction is active - has not been committed or rolled back.'
WHEN 3 THEN 'The transaction has ended. This is used for read-only transactions.'
WHEN 4 THEN 'The commit process has been initiated on the distributed transaction.'
WHEN 5 THEN 'The transaction is in a prepared state and waiting resolution.'
WHEN 6 THEN 'The transaction has been committed.'
WHEN 7 THEN 'The transaction is being rolled back.'
WHEN 8 THEN 'The transaction has been rolled back.' END
, transaction_name = tat.name, request_status = r.status
, tst.is_user_transaction, tst.is_local
, session_open_transaction_count = tst.open_transaction_count
, s.host_name, s.program_name, s.client_interface_name, s.login_name, s.is_user_process
FROM sys.dm_tran_active_transactions tat
INNER JOIN sys.dm_tran_session_transactions tst on tat.transaction_id = tst.transaction_id
INNER JOIN Sys.dm_exec_sessions s on s.session_id = tst.session_id
LEFT OUTER JOIN sys.dm_exec_requests r on r.session_id = s.session_id
CROSS APPLY sys.dm_exec_input_buffer(s.session_id, null) AS ib
ORDER BY tat.transaction_begin_time DESC;
Isolationsgrad
SQL Server bietet mehrere Isolationsstufen, mit denen Sie den Grad der Konsistenz und Korrektheit festlegen können, den Sie für Ihre Daten sicherstellen möchten. Isolationsstufen ermöglichen es Ihnen, ein Gleichgewicht zwischen Parallelität und Konsistenz zu finden. Die Isolationsstufe wirkt sich nicht auf die Sperren aus, die ergriffen werden, um Die Datenänderung zu verhindern. Eine Transaktion erhält immer eine exklusive Sperre für die Daten, die geändert werden. Die Isolationsstufe kann sich jedoch auf die Dauer der Sperren auswirken. Niedrigere Isolationsstufen erhöhen die Fähigkeit mehrerer Benutzerprozesse, gleichzeitig auf Daten zuzugreifen, erhöhen jedoch die Risiken der Datenkonsistenz, die auftreten können. Die Isolationsstufen in SQL Server sind wie folgt:
Lesen ohne Commit: Die niedrigste verfügbare Isolationsstufe. Dirty Reads sind zulässig, was bedeutet, dass für eine Transaktion mehrere Änderungen angezeigt werden, die von eine anderen Transaktion durchgeführt wurde, die noch nicht committet wurden.
Lesen mit Commit: Dies ermöglicht es, dass eine Transaktion Daten liest, die zuvor bereits gelesen wurden, von einer anderen Transaktion jedoch nicht geändert wurden, ohne dass auf den Abschluss der ersten Transaktion gewartet werden muss. Auf dieser Ebene werden auch Lesesperren freigegeben, sobald der Auswahlvorgang ausgeführt wird. Dies ist die Standardmäßige SQL Server-Ebene.
Wiederholbare Leseoperation – Auf dieser Ebene werden Lese- und Schreibsperren, die für ausgewählte Daten erworben werden, bis zum Ende der Transaktion beibehalten.
Serializable – Dies ist die höchste Isolationsebene, bei der Transaktionen voneinander abgeschottet sind. Lese- und Schreibsperren werden für ausgewählte Daten abgerufen und erst am Ende der Transaktion freigegeben.
SQL Server enthält auch zwei Isolationsebenen, die Zeilenversionsverwaltung enthalten.
READ COMMITTED-Momentaufnahme: Auf dieser Stufe werden für Lesevorgänge keine Sperren für Zeilen oder Seiten verhängt, und die Engine bietet für die einzelnen Vorgänge eine konsistente Momentaufnahme der Daten, wie sie zu Beginn der Abfrage verfügbar waren. Diese Ebene wird in der Regel verwendet, wenn Benutzer häufige Berichtsabfragen für eine OLTP-Datenbank ausführen, um zu verhindern, dass die Lesevorgänge die Schreibvorgänge blockieren.
Momentaufnahme: Diese Stufe bietet Lesekonsistenz auf Transaktionsebene mithilfe einer Zeilenversionsverwaltung. Diese Stufe ist anfällig für Aktualisierungskonflikte. Wenn eine Transaktion, die unter dieser Ebene ausgeführt wird, Daten liest, die von einer anderen Transaktion geändert wurden, wird eine von der Schnappschuss-Transaktion durchgeführte Aktualisierung abgebrochen und es wird ein Rollback durchgeführt. Dies ist bei der Momentaufnahmenisolation mit „Lesen mit Commit“ kein Problem.
Wie gezeigt, werden Isolationsstufen für jede Sitzung mit dem T-SQL SET Befehl festgelegt.
SET TRANSACTION ISOLATION LEVEL
{ READ UNCOMMITTED
| READ COMMITTED
| REPEATABLE READ
| SNAPSHOT
| SERIALIZABLE
}
Es gibt keine Möglichkeit, eine globale Isolationsstufe für alle Abfragen festzulegen, die in einer Datenbank ausgeführt werden, oder für alle Abfragen, die von einem bestimmten Benutzer ausgeführt werden. Es handelt sich um eine Einstellung auf Sitzungsebene.
Überwachung auf Blockierungsprobleme
Die Identifizierung von Blockierungsproblemen kann aufgrund ihrer sporadischen Natur schwierig sein. Der DMV sys.dm_tran_locks, wenn er beigetreten ist sys.dm_exec_requests, stellt Informationen zu den Sperren bereit, die von jeder Sitzung gehalten werden. Eine effektivere Möglichkeit zum Überwachen von Blockierungsproblemen besteht darin, das Modul "Erweiterte Ereignisse" fortlaufend zu verwenden.
Blockierungsprobleme fallen in der Regel in zwei Kategorien:
- Ein schlechter Transaktionsentwurf: Beispielsweise wird eine Transaktion ohne
COMMIT TRANSACTIONnie beendet. Der Versuch, zu viel Arbeit in einer einzelnen Transaktion durchzuführen oder eine verteilte Transaktion mit einer verknüpften Serververbindung zu verwenden, kann zu unvorhersehbarer Leistung führen. - Lange ausgeführte Transaktionen, die durch den Schemaentwurf verursacht werden: Dies umfasst häufig eine Aktualisierung einer Spalte mit einem fehlenden Index oder einer schlecht gestalteten Aktualisierungsabfrage.
Mithilfe der Überwachung auf Sperrungsprobleme können Sie leistungsbeeinträchtigungen im Zusammenhang mit der Sperrung schnell erkennen.
Weitere Informationen zur Überwachung von Blockierungen finden Sie unter SQL Server Blockierungsprobleme verstehen und beheben.