Diagnose von Locks in Oracle-Datenbanken

1 Szenario

Während der Hauptlast des Tagesgeschäftes auf einer OLTP-Produktionsdatenbank kommt es zur Verlangsamung des Systems bis fast zum völligen Stillstand.
Bei der Suche nach der Ursache fällt auf, daß die Anzahl der Sessions auf der Datenbank von normalerweise 250 auf über 600 gestiegen war. Ein Großteil dieser Sessions ist im Zustand „aktiv“ und wartet auf andere Sessions, die diese mit Locks blockieren. Die Sorge der Produktionsbetreuuer ist groß: Wenn die Application Server noch deutlich mehr aktive Sessions eröffnen, wird der Datenbank-Server bald unter der Last der Anfragen zusammenbrechen. Also werden kurzerhand alle Sessions, die schon länger als fünf Minuten aktiv sind (ohnehin ein Time-out in der Anwendung), getrennt, was allerdings die spätere Ursachenforschung deutlich erschwert.

Was fehlte, war eine Methode zum nachhaltigen Umgang mit diesem Szenario. Eine kurzfristige Behebung des Problems ist in einer produktiven Umgebung zwar angenehm, aber die „Windows-Methode“: „Wir machen mal alles zu und schauen, ob sich das System stabilisiert“. Idealerweise sollte das Problem zunächst einmal klar diagnostiziert werden, um es dann auch dauerhaft beheben zu können.

Klare Informationen, die in kurzer Zeit und ohne wesentliche Behinderung des Betriebs gewonnen werden, können zur künftigen Vermeidung dieser Blockaden dienen. Dieser Artikel soll daher einen kurzen Leitfaden zur effektiven Diagnose von Blocking-Lock-Situationen liefern.

2 Messungen

In der Reihenfolge einer sinnvollen Vorgehensweise bei der Lock-Diagnose werden die Views und Scripts aufgelistet, die bei Diagnose und Beseitigung helfen können. Die wesentlichen Informationen zur Auflösung von Blocking Locks sind:

1. Wie viele Sessions müssen auf andere warten?
2. Welche Sessions blockieren?
3. Warum blockieren diese Sessions?

2.1 Voraussetzungen

Um die für die Messungen nötigen Views bereitzustellen, muß das Script catblock.sql einmalig unter dem User SYS ausgeführt worden sein. Dieses Script legt u.a. die Views dba_locks und dba_waiters an.
Das Script steht im Pfad $ORACLE_HOME/rdbms/admin/ und kann unter SQL*Plus wie folgt aufgerufen werden:

@?/rdbms/admin/catblock

2.2 DBA_WAITERS

Die Data Dictionary View DBA_WAITERS liefert einen schnellen Überblick aller Sessions, die auf eine andere Session warten. In einem OLTP-System sollten in dieser View nur sehr wenige Einträge zu sehen sein, die dann auch nach wenigen Sekunden nicht mehr zu sehen sein sollten. Sprich: Eine Session wartet nur kurz auf eine Ressource, die von einer anderen Session blockiert wird.

Wenn diese Liste wächst oder einige Einträge kontinuierlich bestehen bleiben, sollte die blockierende Session – z.B mit utllockt.sql – untersucht werden.

SELECT holding_session, mode_held, waiting_session
  FROM dba_waiters
 ORDER BY holding_session;

 Die Spalten bedeuten im Einzelnen:

• holding_session: Die Session, die das Lock besitzt und damit blockiert
• mode_held: Der Typ des Locks (Details s.u.)
• waiting_session: Die Session, die auf das Lock wartet

2.3 utllockt.sql

Hinweis: In meinem Artikel Lock-Diagnose im RAC zeige ich eine Variante, die nicht auf dem Server laufen muss und auch im RAC funktioniert.

Das von Oracle als „Bordmittel“ mitgelieferte Script utllockt.sql eignet sich sehr gut zur Suche nach Sessions, die andere Sessions blockieren. Mithilfe dieser Information kann ein Blockadeszenario relativ schnell aufgelöst werden, indem die ursächliche Session beendet wird.

Das Script steht im Pfad $ORACLE_HOME/rdbms/admin/ und kann unter SQL*Plus auf dem DB-Server wie folgt aufgerufen werden:

@?/rdbms/admin/utllockt

Das Script erzeugt eine temporäre Tabelle, aus der eine baumartige Liste aller blockierenden und wartenden Sessions generiert wird. Der aufrufende User benötigt daher das „create table“-Recht.

Eine beispielhafte Ausgabe des Scripts kann so aussehen:

WAITING_SESSION   TYPE MODE REQUESTED    MODE HELD         LOCK ID1 LOCK ID2
----------------- ---- ----------------- ----------------- -------- --------
8                 NONE None              None              0         0
   9              TX   Share (S)         Exclusive (X)     65547     16
      7           RW   Exclusive (X)     S/Row-X (SSX)     33554440  2
      10          RW   Exclusive (X)     S/Row-X (SSX)     33554440  2

Der Spalte „waiting_session“ lässt sich entnehmen, daß die Sessions 7 und 10 auf Session 9 warten. Session 9 wiederum wartet auf Session 8. Ein Commit, Rollback oder die Terminierung der Session 8 würde dieses gesamte Szenario auflösen.

2.4 DBA_LOCK

Die Data Dictionary View DBA_LOCK bietet einen Überblick über alle aktiven und angeforderten Locks und Latches in der Datenbank.

2.5 V$SESSION, V$SQLAREA und DBA_OBJECTS

Die Data Dictionary View V$SESSION enthält alle Informationen zu den auf der Datenbank geöffneten Sessions – auch die Info, worauf die Session gerade wartet.

In Verbindung mit der View DBA_OBJECTS kann das Objekt, auf das blockierte Sessions gerade warten, bis hin zur Rownumber oder dem betroffenen Datenblock angezeigt werden:

SELECT sid, serial#, username, osuser
       , o. owner, o.object_name
	   ,ROW_WAIT_BLOCK# , ROW_WAIT_FILE#, ROW_WAIT_ROW#
  FROM v$session
     , dba_objects o
 WHERE lockwait is not null  -- nur Locks anzeigen
   AND object_id = ROW_WAIT_OBJ#;

In Verbindung mit der View V$SQLAREA kann das aktuelle SQL angezeigt werden, das blockierte Sessions gerade ausführen.

SELECT sid, serial#, username, osuser,
       sql_text
  FROM v$session, v$sqlarea
 WHERE lockwait is not null  -- nur Locks anzeigen
   AND address = sql_address;

Vorsicht: Das aktuelle SQL einer Session muss nicht zwangsläufig ursächlich für die Blockade sein. Zwar wird dies sicher häufig der Fall sein, aber wie Jonathan Lewis in seinem Blog beschreibt, lässt sich ein für ein Lock ursächliches SQL nicht mit endgültiger Sicherheit ermitteln.

3 Maßnahmen

• Identifizieren blockierender Sessions, z.B. mit utllockt.sql
• Dokumentation der Ursache: Blockierte Objekte, ausgeführtes SQL. Dies ist für eine eventuelle Fehlersuche in der Anwendung nötig.
• Ordnungsgemäßes Beenden dieser Sessions durch Anwender
• ODER Terminieren dieser Sessions durch einen DBA, beginnend mit den Sessions, die ganz links in der Baumansicht von utllockt.sql stehen.
  Dies führt zu einem Rollback der terminierten Sessions, was für die Integrität von Anwendungen und Daten Folgen haben kann und den Anwendern kommuniziert werden muss.

4 Grundsätzliches zu Locks

4.1 Row-exclusive Locks

Bei jedem Update oder Delete werden die betroffenen Rows gesperrt, so daß weitere Sessions zwar lesend, aber nicht schreibend auf diese Rows zugreifen können. Diese exklusiven Sperren werden beim nächsten Commit oder Rollback aufgehoben. Dies gilt auch für Insert-Statements und ist der Standard für Sperrungen in Oracle-Datenbanken.

Row-exclusive Locks können zu Blockaden führen, wenn eine Session die Daten nicht durch Commit oder Rollback freigibt, z.B. wenn eine Transaktion innerhalb einer Anwendung über mehrere Schritte geht und der Anwender diese Schritte nicht zu Ende führt.

Auch Adhoc-Änderungen an einer ganzen Tabelle oder großen Teilen davon können zu langen Wartezeiten für andere Sessions führen, wenn das SQL lange genug läuft.

4.2 Blocking Locks

Blockierende Locks sind fast immer TX (transaction) oder TM (table) Locks. Wenn eine Session auf ein TX-Lock wartet, wartet sie darauf, daß die blockierende Session entweder ein Commit oder ein Rollback ausführt. Die blockierende Transaktion hat einen Datenblock modifiziert, den die wartende Transaktion ebenfalls verändern will.

TM Locks werden gesetzt, um eine Veränderung der Tabellenstruktur zu verhindern, während ein DML-Statement läuft (z.B. darf kein DROP TABLE stattfinden, während ein UPDATE auf dieser Tabelle läuft). TM Locks tauchen als Problem vor allem im Zusammenhang mit Foreign Keys auf, die nicht durch einen Index unterstützt werden.

Hier kann zusätzlich die View V$LOCKED_OBJECT herangezogen werden, um herauszufinden, welche Sessions ein DML-Lock auf dem betroffenen Objekt halten, da blockierende TX enqueue Locks stets auch mit einem DML-Lock verbunden sind.

4.3 Deadlocks

Ein Deadlock tritt dann auf, wenn zwei Sessions sich „über Kreuz“ blockieren und beide durch das Warten auf die Transaktion der jeweils anderen Session ihre eigene Transaktion nicht beenden können.

Oracle erkennt solche Situationen automatisch und löst ein Rollback von einer der betroffenen Sessions aus. Diese Session bekommt den Fehler „ORA-00060“ zurückgeliefert. Weiterhin wird der Fehler „ORA-00060“ im Alert Log vermerkt und ein Tracefile generiert.

Deadlocks sind i.d.R. auf fehlerhafte Implementation der Anwendung zurückzuführen.

Weblinks

• Näheres zu Locks findet sich in der Oracle-Dokumentation (Englisch).
• Master Note: Locks, Enqueues and Deadlocks (ORA-00060) (Doc ID 1392319.1)
• Locking and Referential Integrity (Doc ID 33453.1)