exklusive sperre auf Tabelle

[bigpoint]

Hallo zusammen,

jeder kennt wahrscheinlich das Problem, dass bei update auf mehrere tausend Datensätze bei eine Tabellengröße von Million Datensätze (oder mehr) sperrt der SQL Server die Tabelle exklusive??
Ich habe neulich gelesen, dass einen select auf dummy DS in der Tabelle der mit einem Agent aufgerufen wird soll das Problem beheben, der Agent soll allerdings die Tabelle ständig befragen.
Was denkt ihr darüber bzw. wie geht ihr mit dem Problem um???

Gruß bigpoint

[Andreas Kosch]

Hallo,

in der Voreinstellung eskaliert der MS SQL Server die Sperren beim Erreichen des Schwellwertes zum nächst größeren Kontext (Page, Tabelle, Datenbank). Wenn bereits vorher absehbar ist, dass eine komplette Sperre die bessere Wahl ist, kann dies über die so genannten Table Hints gesteuert werden. Der MS SQL Server kennt die folgenden Anweisungen: HOLDLOCK, NOLOCK, NOWAIT, PAGLOCK, READCOMMITTED, READCOMMITTEDLOCK, READPAST, READUNCOMMITTED, REPEATABLEREAD, ROWLOCK, SERIALIZABLE, TABLOCK, TABLOCKX, UPDLOCK, XLOCK. Über TABLOCKX kann die komplette Tabelle für den exklusiven Zugriff gesperrt werden (Beispiel: WITH (TABLOCKX) ).

Das folgende Beispiel ist aus BOL (Hilfeseite zu den Table Hints):

UPDATE Production.Product
WITH (TABLOCK)
SET ListPrice = ListPrice * 1.10
WHERE ProductNumber LIKE 'BK-%'

[bigpoint]

Hallo Herr Kosch,

Wenn bereits vorher absehbar ist, dass eine komplette Sperre die bessere Wahl ist

darum geht es nicht.
Folgende konkrete Beobachtung kann gemacht werden, ein update auf mehr als 746 Datensätze in einer Tabelle mit Milionen Sätzen, verursacht eine exklusive Tabellensperre (für die restliche Dauer der Transaktion ) was wiederrum bedeutet dass andere Benutzer haben während dieser Zeit keine Chance die Daten auch nur zu lesen bekommen.
Es ist dabei füllig egal welchen Table Hints man benutzt (ich habe es unter anderem mit NOLOCK versucht)

Gruß

[Andreas Kosch]

Hallo,

der SQL Server muss für einen Schreibzugriff auf einen Datensatz eine exklusive Sperre (X-Lock) auf alle betroffenen Objekte (Tabelle, Index) setzen. Wenn für andere Transaktionen (Benutzer) keine Dirty Reads in Frage kommen, blockiert ein X-Lock jeden Lesezugriff, der ein S-Lock auf den Datensatz setzen muss (solange dies nicht abgeschaltet wird). Damit die Verwaltungsinformationen nicht ins Unendliche wachsen, eskaliert der SQL Server die Gültigkeit der Sperren einer Transaktion.

Um die unerwünschten Nebenwirkungen (Blockieren der Lesezugriffe anderen Datenbanksitzungen) zu vermeiden, gibt es 2 Strategien:

a) Im Fall des SQL Server 2005 wird die Datenbank im neuen Transaktions-Isolationsgrad Snapshot Isolation (SI) oder Read Committed with Snapshot Isolation (RCSI) betrieben. Der neue Isolationsgrad SNAPSHOT rüstet beim SQL Server 2005 mit dem Row Versioning eine Funktion nach, die bisher nur bei der Konkurrenz zur Verfügung stand. Für das Lesen von Daten wird kein S-Lock benötigt, so dass die folgenden Grundsätze gelten:

- Lesezugriffe blockieren keine Schreibzugriffe, da beim Lesen keine S-Locks gesetzt werden.

- Schreibzugriffe blockieren keine Lesezugriffe, da die Schreibzugriffe auf den originalen Datensatz erfolgen, aber die Lesezugriffe der eigenen Transaktion auf die Schnappschuss-Kopie umgeleitet werden.

- Mehrere Schreibzugriffe aus verschiedenen Transaktionen blockieren sich im Gegensatz dazu auch allerdings auch weiterhin, da für den Schreibzugriff ein Exclusive Lock (X-Lock) notwendig ist.


b) Die zweite Strategie unterteilt die Aktualisierung in mehrere kleinere Häppchen, die jeweils in separaten Transaktionen ausgeführt werden. In diesem Fall sind weniger Datensätze betroffen, so dass die Eskalation u.U. nicht bis auf die Tabellen-Ebene anwächst.

[bigpoint]

Hallo,

ich glaube, ich habe mich nicht eindeutlich ausgedruckt.
Der SQL Server kann/muss alle Datensätze die grade upgedetet sind exklusive sperren.
Was ist aber mit den restlichen Datensätzen die überhaupt nicht von der Aktualisierung betroffen sind?? Warum kann man die nicht lesen???
Diese Lösung die ich oben beschreibe, habe ich in SQL Server Magazin gelesen, der Autor beschreibt die Vorgehensweise als die beste Lösung und ein bekannter Fehler von MS?!, mir schein es jedoch nicht ganz klar wie man den Agent immer wieder starten soll (ganz schön ist es glaube ich auch nicht).

PS:
Es handelt sich um SQL Server 2000

[Andreas Kosch]

Hallo,

Was ist aber mit den restlichen Datensätzen die überhaupt nicht von der Aktualisierung betroffen sind?? Warum kann man die nicht lesen???

wenn das Update "..auf mehrere tausend Datensätze.." greift, eskaliert der MS SQL Server die Sperren automatisch vom Row (Datensatz) über die Page (Seite) und das Extent (8 Seiten) bis zur kompletten Tabelle. Das ist in der Regel eine sinnvolle Entscheidung, um den Verwaltungsaufwand für die Sperren in vernünftigen Grenzen zu halten (bei extrem vielen Row-Sperren würde die Sperrverwaltung mehr Ressourcen belegen als der eigentliche Update-Vorgang). Wenn die Sperre bis zur Tabellensperre eskaliert ist, kann keine andere Transaktion auf die Tabelle zugreifen, die zu einem späteren Zeitpunkt gestartet wird.

Was in einem Experiment zu überprüfen wäre, ist die Vermutung, dass ständige S-Lock-Anforderungen von anderen Datenbanksitzungen (Transaktionen) einen Einfluss auf die automatische Sperr-Eskalation haben. Es kann durchaus sein, dass der SQL Server immer dann auf Tabellensperren verzichtet, wenn ständig Anfragen aus anderen Datenbanksitzungen ausgeführt werden. Allerdings würde sich dann die Zeitdauer des Updatevorgangs entsprechend verlängern.

[bigpoint]

Es kann durchaus sein, dass der SQL Server immer dann auf Tabellensperren verzichtet, wenn ständig Anfragen aus anderen Datenbanksitzungen ausgeführt werden.

Hallo,

ja, das habe ich auch damit gemeint
Die Frage ist nur wie kann man so ein zustand automatisieren, spricht wenn ein update der die sperren verursacht dann eben eine anfrage aus der andren Datenbanksitzung.

Vielen Dank

bigpoint

[Andreas Kosch]

Hallo,

die andere Datenbanksitzung muss die SELECT-Anweisung auf die Tabelle ausführen, bevor die UPDATE-Transaktion startet. Um das schmale Zeitfenster zuverlässtig "zu treffen", müsste eine zweite Anwendung in der Tat mit der "Schrotflinte" Sperrfeuer schießen (d.h. ständig unsinnige SELECT's mit der READPAST-Option ausführen).

Ich glaube nicht, dass dies sinnvoll ist.