Sperren trotz fehlender Berechtigungen
Der seltsame Fall des blockierten Index-Rebuild
Ein Kunde machte mich kürzlich auf ein Blockade-Problem aufmerksam, welches er sich nicht erklären konnte.
Nach einer kurzen Analyse ist das Szenario recht einfach beschreibbar:
Session 1) (SPID 69 in meinem Beispiel)
Ein User führt innerhalb einer expliziten Transaktion ein Update-Statement auf eine Tabelle aus, das aber fehlschlägt, da er die dafür notwendigen Berechtigungen nicht hat. Die Transaktion bleibt aber offen. (Das ist natürlich das eigentliche Problem, wie man am Ende resümieren wird. Eine grundlegende Fehlerbehandlung ist essentiell.)
Session 2) (SPID 67)
Zwischenzeitlich läuft über einen Wartungsjob ein Index-Rebuild. Und dieser hängt aufgrund einer Blockade, die durch die vorab beschriebene Session ausgelöst wird.
Teil 1: blockierter Index-Rebuild
Was für eine Blockade kann ein nicht-ausführbares Update-Statement aufrechterhalten? – fragt man sich da zu Recht.
Die gesetzten Sperren durch Session 1, ermittelt über die DMV sys.dm_tran_locks, sieht man hier:
Zunächst fällt sicher ins Auge, dass die Tabelle ein IX erhalten hat. – Und das obwohl weiter unten in der Hierarchie keine weitere Sperre gesetzt wurde, werden konnte! Es gibt keine weiteren Sperren auf Page- oder Key-Ebene, wie es bei einem erfolgreichen Update ansonsten der Fall wäre.
Parallel dazu fallen Metadaten-Sperren ins Auge: diese liegen auf dem Schema der Tabelle (5) und dem Datenbank-Prinzipal dbo. Dazu kommen wir noch im 2. Teil weiter unten.
Was davon verursacht die Blockade?
Wer sich mit Index-Rebuilds beschäftigt hat, weiß, dass diese eine Sch-M Lock (Schema-Modification Sperre) setzen. Und dieser spezielle Lock-Typ ist mit keinem anderen Lock-Modus kompatibel! (https://technet.microsoft.com/en-us/library/ms172925(v=sql.110).aspx )
Dementsprechend sehen wir in der Index-Rebuild-Session folgende Sperren und Wartezustände:
(Session 69 hat das Update versucht, session 67 versucht den Index-Rebuild)
Etwas leichter interpretierbar via SQLSentry Blockade-Monitor, für die, die sich ein professionelles Monitoring-Werkzeug gönnen:
Wie man sieht, wartet die Sch-M Sperre tatsächlich auf Freigabe und ist blockiert bis zum Ende der Sperre der Transaktion mit dem fehlgeschlagenen Update, die das IX-Lock auf Tabellenebene hält.
Das ist nebenbei auch ein entscheidender Unterschied zu Index Reorganize. (Tipp)
Teil 2: Die große Preisfrage: Könnte man die Berechtigungen jetzt einfach über ein GRANT UPDATE für die Tabelle direkt vergeben, dann die 1. Transaktion das Update-Kommando wiederholen lassen, und erwarten, das damit dann deren Sperren verschwinden und infolgedessen das Index-Rebuild weitermacht?
Das Kommando dafür wäre:
GRANT UPDATE
ON SQL_Demos.Transactions
TO SQLUserX
Berechtigungen werden laut Dokumentation ja „sofort“ aktiv und erfordern kein erneutes Login.
Die Antwort lautet trotzdem: Nein, das funktioniert so nicht in diesem genauen Fall.
Warum?
Das Problem ist die Sch-M-Sperre auf der Tabelle („OBJECT“).
Ein GRANT UPDATE auf Objektebene erfordert eine Sch-S-Sperre auf der Tabelle.
Folgende Blockade-Kette entsteht (die entscheidenden Zeilen sind gelb hinterlegt):
(#1 entsteht durch den Update-Versuch, #2 durch den Index-Rebuild, der nun warten muss und #3 durch das darauf wartende GRANT)
Hier lohnt es sich, den SQLSentry Blockade-Monitor nochmals anzuwerfen:
Aber wer sich die Best Practice angewöhnt hat, Rechte nur auf Schema-Ebene zu vergeben (siehe auch: Schema-Design für SQL Server: Empfehlungen für Schema-Design mit Sicherheit im Blick), vermeidet diese Sperren auf Objekt-Ebene!:
GRANT UPDATE
ON SCHEMA::SQL_Demos
TO SQLUserX
Die Sperren dazu sind dann auf Schema- und Database-Principal-Ebene, und stehen damit nicht im Weg.
Der Principal zum Testen hat die Principal-ID 5, und auch mein Testschema hat zufällig die ID 5.
Für Insider: Object-ID 29 gehört zu der internen Systemtabelle sysprivs, die die Datenbank-Berechtigungen enthält.
Noch besser wäre es, nur mit Rollen und Schemas zu arbeiten.
Ein Nachträgliches Hinzufügen zu einer Rolle sieht dann auf Sperr-Ebene so aus:
Sprich: Auch kein Problem für die Weiterarbeit an der Tabelle.
Die Principal-ID 16391 gehört, wie man an der hohen Nummer erkennen kann, zu den eingebauten Datenbank-Rollen, in diesem Fall der db_datawriter-Rolle.
Ich hoffe, die Analyse dieses eher seltenen Szenarios war interessant für Einige.
Andreas
(Noch-Inhaber von Sarpedon Quality Lab®
(https://andreas-wolter.com/de-1809_news_senior_program_manager_microsoft_sql_server_security_team/ ))
Hinterlasse einen Kommentar
An der Diskussion beteiligen?Hinterlasse uns deinen Kommentar!