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.
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
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.