Deutsch

Einleitung

Beide Schlüsselwörter machen exakt dasselbe

Beispiel

Vorab, möglichst kurz zusammengefasst

Im Detail

Fazit

Abschließende Wertung anhand eines konkreten Anwendungsfalls: Sage 100-Datenbanken

Deutsch

Einleitung

WITH(Readuncommitted) bzw. WITH(Nolock)

Die einen verwenden es in möglichst jeder SQL-SELECT-Anweisung, die Daten liest und sehen es als "Segen" an - die anderen "verfluchen"diese Isolationsanweisungen!

Was ist nun die "Wahrheit" bzw. was ist bei der Verwendung dieser "Isolation Hints" beim MS-SQL-Server zu beachten und welche Auswirkung hat die Verwendung dieser Schlüsselwörter?

Beide Schlüsselwörter machen exakt dasselbe

Beide Schlüsselwörter haben im MS-SQL-Server exakt dieselbe Auswirkung!

D.h. In MS-SQL stehen sowohl "WITH (READUNCOMMITTED)" als auch "WITH(NOLOCK)" beide für dieselbe Isolationsebene, die verwendet wird, um Daten zu lesen, die noch nicht endgültig geschrieben (committed) wurden.

Dieser Isolationslevel wird auch oft als "Dirty Read" bezeichnet.

Beispiel

BEGIN TRANSACTION T1;
UPDATE TableName SET ColumnName = 'NewValue' WHERE Id = 1;
-- T1 commit nicht ausgeführt

-- In einer anderen Abfrage:
SELECT * FROM TableName WITH (READUNCOMMITTED) WHERE Id = 1;
-- Diese Abfrage sieht die 'NewValue', obwohl T1 noch nicht festgeschrieben hat

Vorab, möglichst kurz zusammengefasst

• Die Verwendung von "Dirty Reads" durch Einbau der o.g. "Isolation Hints" kann insbesondere in allen Szenarien bei Tabellen, in die mit Transaktionen geschrieben wird, zu erheblichen Performance-Steigerungen führen. In ERP-Systemen wie der Sage 100 sind dies durchaus einige Aktionen (Belege, Lager, Buchungen etc.).!

• Nachteil ist, dass eben auch Daten im Ergebnis erscheinen können, die eigentlich noch gar nicht final durch Transaktionen abgeschlossen wurden (noch nicht endgültig gespeicherte Datensätze). Dies kann zu inkonsistenten Daten führen!

  Im Übrigen warten auch alle DDL- und alle schreibenden DML-Aktionen immer auf den Abschluss eines "Dirty Reads", siehe nachfolgende Hinweise.

SELECT mit WITH(NOLOCK):

• Keine "Shared Locks" und "Exclusive Locks" für andere Operationen.

  Standardmäßig setzt der SQL-Server bei SELECT-Befehlen "Shared Locks".

• Transaktionen und andere Schreibaktionen müssen nicht warten, bis die Leseoperation beendet ist.

• Dirty Reads können aber entstehen (nicht nicht endgültig committete Daten in der Ergebnismenge).

SELECT ohne WITH(NOLOCK):

• Transaktionen und andere Schreibaktionen müssen warten, bis die Leseoperation vollständig beendet ist.

• Keine Dirty Reads möglich , d.h. nur Daten aus erfolgreichen Transaktionen in der Ergebnismenge!

Im Detail

Es wird oft behauptet, dass ein "zu inflationäres" Benutzen von "Dirty Reads" bezüglich Performance dann wieder kontraproduktiv sei und dass dadurch die Performance sogar schlechten werden könnte.

Dies kann man so pauschal nicht stehen lassen und man muss im Einzelfall prüfen, wie mit einer betroffenen Datenbank gearbeitet wird und ob z.B. Daten primär gelesen oder primär geschrieben werden oder ob es eine Mischung aus beidem ist.

Die Verwendung von WITH (READUNCOMMITTED) oder WITH (NOLOCK) im Microsoft SQL Server bezieht sich auf die Art der Sperren, die von der Abfrage verwendet werden.

Soweit sollte das bisher klar sein.

WITH (READUNCOMMITTED) oder WITH (NOLOCK) in SQL-SELECT-Anweisungen vermeiden auf jeden Fall "Shared Locks" und "Exclusive Locks" für die Leseaktionen, um die Performance zu verbessern und Blockierungen zu reduzieren.

Das ist schon ein erheblicher Vorteil, aus nachfolgendem Grund:

⚠️Im Standardverhalten von MS-SQL-Server - d.h. ohne die Verwendung von WITH (NOLOCK) oder WITH (READUNCOMMITTED) - werden bei einem SELECT-Befehl "Shared Locks" gesetzt!

Diese Sperren beeinflussen sowohl Lese- als auch Schreiboperationen wie folgt.

Shared Locks (S-Sperren)

• Leseoperationen:

  Wenn ein SELECT-Befehl ausgeführt wird, setzt der SQL Server "Shared Locks" auf die gelesenen Daten.

  Diese Sperren verhindern, dass andere Transaktionen die gelesenen Daten ändern können, solange die Lesetransaktion aktiv ist.

• Schreiboperationen:

  Schreiboperationen (wie INSERT, UPDATE, DELETE) erfordern "Exclusive Locks" (X-Sperren). Diese Sperren können nicht gesetzt werden, wenn "Shared Locks" aktiv sind, was bedeutet, dass Schreiboperationen blockiert werden, bis die "Shared Locks" freigegeben werden.

• Andere Leseoperationen:

  "Shared Locks" blockieren andere "Exclusive Locks", aber nicht weitere "Shared Locks". Das bedeutet, dass andere Leseoperationen weiterhin gleichzeitig ausgeführt werden können.

⚠️Jedoch gibt es nun noch sogenannte "Schemastabilitätssperren" (Sch-S) und in einigen Fällen "Schemamodifikationssperren" (Sch-M) , die in bestimmten Szenarien auftreten können und die liebendgerne von "Gegnern" von WITH(Readuncommitted) bzw. WITH(NoLock) als beliebtes Gegenargument angeführt werden.

Schemastabilitätssperren (Sch-S)

Sch-S-Sperren verhindern Änderungen an der Struktur der Tabelle, wenn z.B. Schema-Daten gelesen werden. Eine Sch-S-Sperre wird benötigt, um sicherzustellen, dass die Schemainformationen der Tabelle während der Abfrage stabil bleiben. Diese Sperre blockiert jedoch keine Lese- oder Schreibvorgänge, sondern nur Strukturänderungen.

Beispiel:

-- Transaktion T1
BEGIN TRANSACTION;
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Orders'; -- Sch-S Sperre wird gesetzt
-- Diese Abfrage liest die Metadaten der Tabelle Orders, verhindert jedoch keine DML-Operationen.

Schemamodifikationssperren (Sch-M)

Sch-M-Sperren werden benötigt, wenn tatsächlich eine Änderung am Schema vorgenommen wird, wie z.B. durch eine ALTER TABLE-Anweisung.

Diese Sperren blockieren alle(!) anderen Sperren, einschließlich Sch-S, und verhindern sowohl Lese- als auch Schreibzugriffe, um die Integrität der Schemaänderungen zu gewährleisten.

Beispiel:

-- Transaktion T2
BEGIN TRANSACTION;
ALTER TABLE Orders ADD NewColumn INT; -- Sch-M Sperre wird gesetzt
-- Diese Operation blockiert alle anderen Transaktionen, die versuchen, die Tabelle Orders zu lesen oder zu ändern.

Auswirkungen auf DDL- und DML-Operationen

Auch wenn READUNCOMMITTED oder NOLOCK verwendet wird, gilt Folgendes.

• DDL-Operationen wie ALTER TABLE, DROP TABLE, etc. erfordern Sch-M-Sperren, die von Sch-S-Sperren blockiert werden können, selbst wenn die Leseoperationen READUNCOMMITTED oder NOLOCK verwenden. Daher müssen tatsächlich DDL-Operationen immer warten, bis alle aktiven Lesetransaktionen abgeschlossen sind.

• DML-Operationen wie INSERT, UPDATE, und DELETE können weiterhin gleichzeitig mit READUNCOMMITTED oder NOLOCK Leseoperationen ausgeführt werden, da diese Leseoperationen keine exklusiven Sperren verursachen. Allerdings müssen DML-Operationen unter Umständen selbst exklusive Sperren setzen, was dazu führen kann, dass konkurrierende DML-Operationen blockiert werden.

Fazit

Die Wahrheit liegt "irgendwo dazwischen"😊.

Die Verwendung von WITH (READUNCOMMITTED) oder WITH (NOLOCK) verhindert zwar die Verwendung von "Shared Locks" für die Leseoperationen, jedoch werden Sch-S-Sperren immer noch gesetzt, um Schemaänderungen während der Lesevorgänge zu verhindern.

Das bedeutet, dass DDL-Operationen, die Sch-M-Sperren erfordern, natürlich warten müssen, bis alle aktiven Lesetransaktionen abgeschlossen sind, auch wenn diese Leseaktionen READUNCOMMITTED oder NOLOCK verwenden.

Möglichst einfach gesagt:

Eine "inflationäre" Verwendung von WITH (READUNCOMMITTED) oder WITH (NOLOCK) wird sicherlich dazu führen, dass gewisse DML-Operationen länger dauern, da diese warten müssen, bis die Lesevorgänge abgeschlossen sind.

DDL-Operationen müssen immer warten, bis die Lesevorgänge abgeschlossen sind.

Somit sind wir wieder bei der Ausgangsfrage nach der konkreten Situation in Bezug auf die konkrete Datenbank, die involviert ist.

Wenn in einer Datenbank primär neue Daten geschrieben oder sogar zur Laufzeit dynamisch viele DDL-Operationen ausgeführt werden, dann kann eine allzu "inflationäre" Verwendung der hier beschriebenen Isolation Hints durchaus problematisch sein!

Testen, Testen, Testen!

Spekulieren bzw. eine pauschale Aussage hilft in der Praxis nicht. Stattdessen sollten man die Performance mit modernen Hilfsmitteln wie dem MS-SQL Query Store exakt über einen längeren Zeitraum unter realistischen Arbeitsbedingungen analysieren und dann entsprechend konkreter Monitor-Ergebnisse ganz schauen, ob und wo in der betreffenden Datenbank überhaupt Probleme entstehen und was genau die Ursachen für die Probleme sind.

Tipp: Siehe auch folgender Artikel:

https://www.officium-inservio.com/ms-sql/executionplansbasics

Entsprechend dieser Ergebnisse ist dann zu agieren und ggf. auch die Anwendungsentwicklung neu auszurichten oder sogar bestehender Code und SQL-Abfragen sind anzupassen!

Sehr oft fehlt ein Clustered Index oder ein sonstiger Index oder es werden aus anderen gründen "schlechte" Abfragepläne für das Lesen von Daten verwendet.

Abschließende Wertung anhand eines konkreten Anwendungsfalls: Sage 100-Datenbanken

Man muss für den jeweiligen Anwendungsfall abwägen und genau prüfen, wie sich die Performance verhält.

Beispiel:

In der Praxis der Sage 100-ERP-Datenbanken und der damit verbundenen Anwendungsentwicklung hat sich die Verwendung von WITH (READUNCOMMITTED) oder WITH (NOLOCK) beim Lesen von Daten nach unserer jahrelangen Erfahrung eher positiv bewährt!

Dies sollte mindestens immer dann erfolgen, wenn Daten aus Beleg-, Lager- oder Buchungstabellen gelesen werden, da diese seitens des Software-Herstellers durch Transaktionen beschrieben werden.

Auch in übrigen Tabellen kann es von Vorteil sein, so abzufragen, da eben ansonsten auch durch den Lese-Vorgang o.g. "ungünstige" Locks entstehen können!

Sicherlich macht die Verwendung dieser Isolation Hints immer dann keinen Sinn, wenn z.B. im Rechnungswesen Buchungsdaten für Abschlüsse ausgewertet werden oder in der Warenwirtschaft Lagerbestände im Rahmen der wichtigen Inventur.

Ein Abfragen mit WITH (READUNCOMMITTED) oder WITH (NOLOCK) wäre in derartigen Szenarien fehlerbehaftet und somit nicht sinnvoll!

Wie so oft in der EDV gibt es somit keine einfache "Faustregel", sondern es muss fallbezogen entschieden werden.