Sessions Transaktionen und Sperren

05.
Februar
2006
Veröffentlicht von: Matthias Meyer

Ist es Ihnen schon häufiger passiert, dass Sie Änderungen an Ihren Daten vornehmen wollten und dabei in bestehende Tabellen- oder Zeilensperren gelaufen sind? Im unangenehmsten Fall „hängte“ sich Ihre Session solange auf, bis die gesperrten Zeilen wieder freigegeben wurden...

Zusammenspiel zwischen Sessions, Transaktionen und Sperren

Ist es Ihnen schon häufiger passiert, dass Sie Änderungen an Ihren Daten vornehmen wollten und dabei in bestehende Tabellen- oder Zeilensperren gelaufen sind? Im unangenehmsten Fall „hängte“ sich Ihre Session solange auf, bis die gesperrten Zeilen wieder freigegeben wurden. Und selbst wenn Sie vor dem Durchführen Ihrer Änderung festgestellt haben, dass eine bereits vorhandene Sperre die Manipulation verhindert, hatte es Sie sehr viel Aufwand gekostet, die „störende“ Session auszumachen.

Der folgende Beitrag soll Ihnen beim Auffinden von Sperren behilflich sein und die Zuordnung der Sperren zu den jeweiligen Transaktionen, den Benutzern und den gesperrten Objekten erleichtern. Alle SQL-Beispiele beziehen sich auf die Oracle Versionen 9i und 10g.

Ausgangssituation

Zu Demonstrationszwecken werden die Benutzer in diesem Beitrag mit TEST01, TEST02, ... bezeichnet. Die Benutzer greifen alle auf die selben Tabellen EMP und DEPT zu und versuchen diese zu manipulieren.

Fall 1 (Default-Einstellung bei Oracle): User TEST01 sperrt durch seine Transaktion einzelne Zeilen einer Tabelle und User TEST02 läuft mit seiner Änderung in die Sperre hinein. Die Session von TEST02 ist blockiert und der User muss warten, bis die Transaktion des Users TEST01 beendet ist.

Fall 2 (SELECT ... FOR UPDATE NOWAIT): User TEST01 startet erneut eine Transaktion:

UPDATE scott.dept SET loc='BERLIN' WHERE deptno=40;

TEST02 möchte parallel den Namen der Abteilung 40 ändern, prüft allerdings vorher, ob es bereits eine Sperre auf diesem Datensatz gibt:

SELECT * FROM scott.dept WHERE deptno=40 FOR UPDATE NOWAIT;

Und erhält folgende Fehlermeldung:

ORA-00054: Versuch, mit NOWAIT eine bereits belegte Ressource anzufordern.

Nun weiß TEST02 zwar, dass er momentan nicht in der Lage ist, diesen Datensatz zu verändern, er weiß jedoch nicht, welcher Benutzer ihn in seiner Tätigkeit behindert. Dies gilt es im weiteren Verlauf des Beitrags zu ermitteln.

Die wichtigsten Data Dictionary Views zu Benutzerprozessen, Transaktionen und Sperren

Zum Aufruf der folgenden Views benötigen Sie mindestens das Leserecht aller Data Dictionary Views (z. B. über die Rolle SELECT_CATALOG_ROLE).

  • V$SESSION: erfasst alle Benutzer- und Hintergrundprozesse, die auf dem Datenbankserver laufen. Hier sehen Sie, welche Benutzer von welchem Rechner aus über welches Programm auf die Datenbank zugreifen und welche Benutzer eine Transaktion gestartet haben. Dazu führen Sie folgenden SELECT durch:

SELECT s.sid, s.serial#, s.username, s.taddr, s.last_call_et, s.program, s.machine
FROM v$session s
WHERE s.username IS NOT NULL;

  • V$LOCKED_OBJECT: erfasst alle von einer Transaktion betroffenen (gesperrten) Objekte. Durch einen Join mit der View DBA_OBJECTS erhalten Sie die genauen Namen der gesperrten Objekte:

SELECT l.session_id, l.oracle_username, l.object_id, o.owner, o.object_name
FROM v$locked_object l, dba_objects o
WHERE l.object_id = o.object_id;

  • V$TRANSACTION: listet alle aktuellen Transaktionen und die zugewiesenen Undo-Segmente auf. Ein Join mit V$SESSION gibt an, von welchem Benutzer eine Transaktion wann gestartet wurde:

SELECT t.addr, t.xidusn, s.sid, s.serial#, s.username, t.start_time
FROM v$transaction t, v$session s
WHERE t.addr = s.taddr;

  • DBA_DML_LOCKS: gibt alle Objekte aus, die aufgrund einer DML-Anweisung von einer Sperre betroffen sind:

SELECT l.session_id, l.owner, l.name
FROM dba_dml_locks l;

  • DBA_WAITERS: enthält alle Sessions, die von anderen Sessions blockiert (gesperrt) werden.

SELECT w.waiting_session, w.holding_session, w.mode_held
FROM dba_waiters w
WHERE w.mode_held <> 'None';

Ermittlung der blockierenden Sessions

Der folgende SELECT ermittelt die blockierende Session, in deren Sperre Sie gelaufen sind.

SELECT  s.sid,
             s.serial#,
             s.username,
             w.holding_session
FROM    v$session s,
               dba_waiters w
WHERE    w.waiting_session(+) = s.sid
          AND      s.username = user
          AND      s.taddr is not null
          AND      w.mode_held(+) <> 'None'
UNION
SELECT  s.sid,
             s.serial#,
             s.username,
             NULL
FROM    v$session s
WHERE    s.sid IN (SELECT w.holding_session
                             FROM dba_waiters w
                            WHERE w.mode_held <> 'None');

       SID    SERIAL# USERNAME HOLDING_SESSION
---------- ---------- -------- ---------------
         9         33 TEST01
        16         23 TEST02                 9

Zurück zu Fall 2 unseres Ausgangsproblems. TEST02 hat mittels SELECT ... FOR UPDATE NOWAIT festgestellt, dass der zu ändernde Datensatz bereits gesperrt ist. Um festzustellen, welcher Benutzer für die Sperre verantwortlich ist, setzt er nun folgenden Befehl ab:

SELECT  l.session_id,
             s.serial#,
             l.oracle_username,
             o.object_name,
             c.sql_text
FROM    v$locked_object l,
               v$session s,
               dba_objects o,
               v$open_cursor c
WHERE    l.object_id = o.object_id
          AND    s.sid = l.session_id
          AND    c.address(+) = s.sql_address
          AND      o.owner = 'SCOTT'
          AND      o.object_name = 'DEPT';

SESSION_ID  SERIAL# ORACLE_USERNAME OBJECT_NAME SQL_TEXT
---------- -------- --------------- ----------- --------
         9       33 TEST01          DEPT        UPDATE scott.dept SET loc='BERLIN' WHERE deptno=40

Hinweis:

Dieses Beispiel ist für den Beitrag etwas idealisiert worden. Im Normalfall sehen Sie nicht zwingend, welche Session genau Ihre zu ändernde Zeile sperrt. Die Spalte SQL_TEXT gibt lediglich den zuletzt abgesetzten Befehl eines Benutzers an, dies muss aber nicht unbedingt der Befehl sein, mit dem Ihre Zeile(n) gesperrt worden ist. Haben mehrere Benutzer unterschiedliche Zeilen einer Tabelle gesperrt und Sie wollen feststellen, welche Session Sie blockiert, müssen Sie wohl oder übel in die Sperre laufen und über den zuvor aufgeführten SELECT die blockierende Session ausfindig machen.

Den Abschluss dieses Beitrags bildet ein SELECT, der Ihnen alle Benutzersessions auflistet, die eine Transaktion gestartet haben bzw. in eine Transaktionssperre gelaufen sind. Im konkreten Fall könnten dies mehrere Benutzer sein, die hintereinander in die selbe Zeilensperre laufen und damit alle blockiert sind. Nun gilt es herauszufinden, in welcher Reihenfolge die „hängengebliebenen“ Sessions wieder freigegeben werden. Als zusätzliche Information lassen Sie sich dazu die Zeit (LAST_CALL_ET), die seit dem Absetzen des letzten Befehls in einer Session verstrichen ist ausgeben.

SELECT  s.sid,
             s.serial#,
             s.username,
             w.holding_session,
             s.taddr,
             s.lockwait,
             s.last_call_et AS time,
             o.owner,
             o.object_name,
             c.sql_text
FROM    v$session s,
               dba_waiters w,
               v$locked_object l,
               dba_objects o,
               v$open_cursor c
WHERE    w.waiting_session(+) = s.sid
         AND    s.sid = l.session_id
         AND    l.object_id = o.object_id
         AND    c.address(+) = s.sql_address
         AND      s.username is not null
         AND      s.taddr is not null
         AND      w.mode_held(+) <> 'None'
GROUP BY    w.holding_session,
                     s.last_call_et,
                     s.sid,
                     s.serial#,
                     s.username,
                     s.taddr,
                     s.lockwait,
                     o.owner,
                    o.object_name,
                     c.sql_text
ORDER BY    w.holding_session DESC,
            s.last_call_et DESC;
SID SERIAL# USERNAME HOLDING_SESSION TADDR    LOCKWAIT TIME OWNER  OBJECT_NAME SQL_TEXT
--- ------- -------- --------------- -------- -------- ---- ------ ----------- ---------------------------------------------------------
   10   213 TEST01                   6A874D30           262 SCOTT  DEPT       update scott.dept set loc='MONTEREY' where deptno=40
   17    14 TEST04                   6A8520F4            90 SCOTT  EMP         update scott.emp set sal=sal*2 where empno=7900
   19    37 TEST05                17 69FF8088 69B1CEB4   60 SCOTT  EMP        update emp set sal=sal-200 where empno=7900
    9    33 TEST02                10 6A860DAC 69B1C920  211 SCOTT  DEPT       update scott.dept set dname='HEAD_QUARTER' where deptno=40
   16    23 TEST03                10 6A87494C 69B1C974  171 SCOTT  DEPT       update scott.dept set loc='SAN DIEGO' where deptno=40

Als Ausgabe erhalten Sie eine Übersicht über alle gestarteten Transaktionen (TADDR) und welche Session in eine bereits bestehende Sperre (LOCKWAIT) einer anderen Session (HOLDING_SESSION) gelaufen ist.

Fazit

Die in diesem Beitrag angesprochenen Data Dictionary Views sind lediglich die wichtigsten rund um das Thema Sessions, Transaktionen und Sperren. Mit Hilfe der hier aufgeführten Befehls-Beispiele soll es Ihnen möglich sein, sich die gewünschten Informationen übersichtlich anzeigen zu lassen. Eine wesentlich ausführlichere Darstellung weiterer Views und ihre Zusammenhänge finden Sie hier. 

Jede Menge Know-how für Sie!

In unserer Know-How Datenbank finden Sie mehr als 300 ausführliche Beiträge zu den Oracle-Themen wie DBA, SQL, PL/SQL, APEX und vielem mehr.
Hier erhalten Sie Antworten auf Ihre Fragen.