Die Frage lautete:
Auf Verbindung 1 wird in einer etwas längeren Transaktion (ja, macht man nicht, aber nur mal angenommen..) der Datensatz mit ID "0815″ geändert.
Auf Verbindung 2 wird jetzt ein UPDATE-Befehl auf den Datensatz mit der ID "4711″ abgesetzt (… WHERE ID = 4711).
Geht der UPDATE auf der Verbindung 2 glatt durch oder wird er von Verbindung 1 blockiert?
Das kommt darauf an….
Tabelle ohne Index
Gehen wir mal davon aus, die Tabelle ist ein Heap, also eine Tabelle ohne Clustered Index, außerdem sind gar keine Indexes darauf. Dann muss der SQL-Server wohl oder übel einen Table-Scan durchführen um die betroffenen Sätze zu finden. Dazu liest er jeden Satz durch. Auf den gerade untersuchten Satz wird eine Update-Sperre (U) gesetzt. Ist der Datensatz betroffen, dann wird sie ein eine Exklusiv-Sperre (X) umgewandelt, sonst sofort wieder freigegeben.
Wegen des Table-Scan versucht Verbindung 2 auch den Satz mit ID 0815 zu lesen (er liest alle Sätze der Tabelle). Der ist schon gesperrt, daher wird die Verbindung blockiert. Der zweite muss warten und versteht nicht warum… 😉
Der SQL-Server-2005 verwendet hier übrigens Satz-Sperren, während der 2000er generell beim Table-Scan Seitensperren verwendete. In den anderen unten genannten Fällen verhalten sich die Systeme gleich.
Tabelle mit Index
Hat die Tabelle einen Index auf dem Suchfeld (hier ID), dann sieht es anders aus. Sowohl bei einem Clustered-Index als auch einem normalen Index kann der SQL-Server den Datensatz direkt mittels Index-Seek anspringen.
Daher tritt hier keine Blockierung auf.
Tabelle mit Index, Suche über zwei Felder
Angenommen wir filtern nicht über die ID, sondern über zwei Felder, z.B. Kundennummer und laufende-Nummer (… WHERE KDNR=4711 AND LFDNR=10). Nur auf einem davon ist ein Index: Auf der Kundennummer liegt ein nicht-eindeutiger Index, pro Kundennummer kann es zig laufende Nummern geben.
Dann macht der SQL-Server zwar einen Index-Seek auf alle Sätze mit KDNR=4711, aber er muss für alle potentiell betroffenen Sätze nachsehen, welchen Wert LFDNR hat. Daher wird auch hier eine Blockierung auftreten, wenn zufällig ein anderer Satz mit der gleichen Kundennummer schon exklusiv gesperrt ist.
Resümee
Ob es bei zwei UPDATEs auf unterschiedlichen Datensätzen zu einer Blockierung kommt oder nicht, hängt ganz allein vom Zugriffsplan des "Zweiten" ab. Der Nutzen der richtigen Indexe kann gar nicht hoch genug bewertet werden.
Und wie schon Cato sagte: Transaktionen müssen immer so kurz wie möglich sein.
Ausschlussklauseln
- Das Geschriebene gilt auch für DELETEs.
- Man kann die Blockierung nicht wirklich umgehen. Man kann lediglich mittels READPAST angeben, dass gesperrte Datensätze übersprungen werden. Das macht aber wirklich nur dann Sinn, wenn es wirklich egal ist, ob alle vom UPDATE oder DELETE betroffenen Datensätze geändert werden.
- Wenn man den Isolation-Level "SNAPSHOT" verwendet, dann könnte es anders sein, das habe ich nicht ausprobiert.
- Wenn man SERIALIZABLE verwendet, dann ist es ähnlich nur schlimmer, weil dann jede Menge RangeS-U-Locks gesetzt werden. Aber wann macht man schon SERIALIZABLE bei UPDATEs oder DELETEs?
