Einleitung Wie entsteht ein Deadlock? Wie protokolliert die

Betrifft
Deadlock - Eine unangenehme Erfahrung
Autor
Peter Jensch ([email protected])
Art der Info
Technische Background Info (Juli 2002)
Quelle
Aus unserer Schulungs- und Beratungstätigkeit
Einleitung
Das Auftreten von Deadlocks in Oracle-Datenbank-Anwendungen ist unangenehm. Schon
bei gelegentlichem Auftreten ist ein sinnvolles Arbeiten mit der Anwendung nicht mehr
möglich! Die Akzeptanz gegenüber der Applikation ist somit nicht gegeben, zumal die
Deadlocks häufig ein Problem der Applikation sind. Die Vermeidung bzw. das Auffinden
und die korrekte Interpretation von Deadlocks ist daher ein sehr wichtiges Thema für den
Anwendungsentwickler und einen DBA.
Besonders anfällig für Deadlocks sind alle Tabellen mit expliziten Locks, um die Schreib- /
Lese-Konsistenz zu gewährleisten, nicht indizierte FK-Constraints (nicht mehr bei OracleVersion 9i) und unzureichende Storage - Parameter von Datensegmenten (im Wesentlichen
sind dies die Parameter INITTRANS, MAXTRANS und PCTFRE) können Ursachen für
ungewollte Locks und Deadlocks sein. Die Ursachen sind vielschichtig und müssen von
Fall zu Fall neu untersucht werden.
Wie entsteht ein Deadlock?
Versuchen zwei oder mehr User auf dieselben Datenquellen in unterschiedlicher
Reihenfolge zuzugreifen, kommt es zum (Dead-)Lock der Ressourcen. Hierbei kann der
jeweilige Lock auf Tabellen-, Partitions- oder Record-Level erfolgen.
Die Liste der möglichen Locks ist gross. Ich möchte an dieser Stelle lediglich die
wesentlichen Locks kurz aufführen.
DX
TM
TX
UL
...
Distributed TX
DML-Enqueue
Transaction
User-defined Locks
Wie protokolliert die Datenbank einen Deadlock?
Beim Deadlock (Oracle-Fehlermeldung: ora-00060) wird eine entsprechende Meldung ins
Alert-Log geschrieben. Zusätzlich wird im USER_DUMP_DESTINATION ein Trace-File
erzeugt. Der Eintrag im Alert-Log-File gibt einen Hinweis auf die File-Bezeichnung des
Trace-Files.
Den Alert-Log-File und den Trace-File zu analysieren ist sehr umfangreich und aufwendig.
Oft wird man erst durch mühsames Suchen fündig – wenn überhaupt. Eine schnelle und
detaillierte Problemlösung ist daher nur bedingt möglich.
Beispiel aus einer alert_log-Datei:
Mon Jul 29 09:14:42 2002
ORA-000060: Deadlock detected. More info in file d:\oracle\admin\DB1\udump\ORA01784.TRC.
Beispiel (Auszug) aus dem zugehörigen Trace-File ORA01784.TRC:
Dump file d:\oracle\admin\DB1\udump\ORA01784.TRC
Mon Jul 29 09:14:42 2002
ORACLE V9.0.1.2.1 - Production vsnsta=0
vsnsql=10 vsnxtr=3
Windows 2000 Version 5.0 Service Pack 2, CPU type 586
Oracle9i Enterprise Edition Release 9.0.1.2.1 - Production
With the Partitioning option
JServer Release 9.0.1.2.0 - Production
Windows 2000 Version 5.0 Service Pack 2, CPU type 586
Instance name: db1
Redo thread mounted by this instance: 1
Oracle process number: 12
Windows thread id: 1784, image: ORACLE.EXE
*** 2002-07-29 09:14:42.000
*** SESSION ID:(12.11) 2002-07-29 09:14:42.000
DEADLOCK DETECTED
Current SQL statement for this session:
update emp set sal = sal + 100
where deptno = 20
The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock:
Deadlock graph:
---------Blocker(s)-------- ---------Waiter(s)--------Resource Name
process session holds waits process session holds waits
TX-00080006-000008f8
12
12
X
11
10
X
TX-00090002-000008fc
11
10
X
12
12
X
session 12: DID 0001-000C-00000002
session 10: DID 0001-000B-00000002
session 10: DID 0001-000B-00000002
session 12: DID 0001-000C-00000002
Rows waited on:
Session 10: obj - rowid = 00007C6A - AAAHxqAABAAAO/CAAG
(dictionary objn - 31850, file - 1, block - 61378, slot - 6)
Session 12: obj - rowid = 00007C6A - AAAHxqAABAAAO/CAAA
(dictionary objn - 31850, file - 1, block - 61378, slot - 0)
SQL statements executed by the waiting sessions:
Session 10:
update emp set sal = sal + 100
where deptno = 10
===================================================
PROCESS STATE
------------Process global information:
process: 671C58C8, call: 67247E80, xact: 6689333C, curses: 671EC654, usrses: 671EC654
---------------------------------------SO: 671C58C8, type: 2, owner: 00000000, flag: INIT/-/-/0x00
(process) Oracle pid=12, calls cur/top: 67247E80/67247E80, flag: (0) int error: 0, call error: 0, sess error: 0, txn error 0
(post info) last post received: 120 0 4
last post received-location: kslpsr
last process to post me: 671c3e28 1 6
last post sent: 0 0 15
last post sent-location: ksasnd
last process posted by me: 671c3e28 1 6
(latch info) wait_event=0 bits=10
holding
19792c0 Parent+children enqueue hash chains level=4
Location from where latch is held: ksqcmi: kslgpl:
Sind diese Informationen nicht ausreichend, so kann über Datenbank-Events zusätzliche
Information erzeugt werden. Durch Hinzufügen des folgenden Init.ora-Parameters:
Event=“60 trace name errorstack level 3;name systemstate level 10“
Hierbei werden ggf. sehr grosse Trace-Files erzeugt. Die Grösse der Trace-Files wird über
den Init.ora-Parameter max_dump_file_size begrenzt. Einträge, die über diese FileBegrenzung hinausgehen, werden abgeschnitten – ggf. ist die Interpretation deutlich
eingeschränkt, da wesentliche Informationen abgeschnitten wurden.
Lösungsansatz:
Unter den Trivadis-Scripten, die Sie unter www.trivadis.com kostenlos herunterladen
können, findet man zwei Scripte (sslckwa1.sql und sslckwai.sql), die zum
Ausführungszeitpunkt alle gelockten Tabellen und weitere Infos rund um den „Locker“,
„Waiter“ und der „Herkunft“ ausgeben. Die Ausführung der Scripte ist auf den aktuellen
Moment bezogen, d.h. auftretende Deadlocks werden nicht protokolliert. Nachfolgend
wird ein Datenbank-Trigger und eine Datenbank-Procedure vorgestellt, die beim Auftreten
eine Protokollierung der Informationen in Log-Tabellen vornehmen. Hierbei wird beim
Auftreten des ORA-00060 der Datenbank-Trigger aktiviert und die wesentlichen
Informationen des Deadlocks werden in die beiden Tabellen eingefügt.
Nachfolgend werden Ihnen die notwendige Tabellen, der Datenbank-Trigger und die
PL/SQL-Procedure vorgestellt, die unter dem User Sys installiert werden:
Tabelle tvd_lock_info_1
CREATE TABLE tvd_lock_info_1 (
lock_date
DATE
, os_locker
VARCHAR2(30)
, locker_schema
VARCHAR2(30)
, locker_pid
VARCHAR2(9)
, os_waiter
VARCHAR2(30)
, waiter_schema
VARCHAR2(30)
, waiter_pid
VARCHAR2(9)
, sql_text_waiter
VARCHAR2(200)
);
Tabelle tvd_lock_info_2
CREATE TABLE tvd_lock_info_2 (
lock_date
DATE
, wait
VARCHAR2(3)
, os_user
VARCHAR2(30)
, process
VARCHAR2(9)
, locker
VARCHAR2(30)
, object_owner
VARCHAR2(30)
, object_name
VARCHAR2(30)
, program
varchar2(50)
)
TABLESPACE users
/
Procedure tvd_prc_lock_info
CREATE OR REPLACE PROCEDURE tvd_prc_lock_info
IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO tvd_lock_info_1
( lock_date, os_locker, locker_schema, locker_pid,
os_waiter, waiter_schema, waiter_pid, sql_text_waiter )
SELECT
/* first the table-level locks (TM) and mixed TM/TX TX/TM */
SYSDATE
, S_LOCKER.OSUSER
OS_LOCKER
, S_LOCKER.USERNAME
LOCKER_SCHEMA
, S_LOCKER.PROCESS
LOCKER_PID
, S_WAITER.OSUSER
OS_WAITER
, S_WAITER.USERNAME
WAITER_SCHEMA
, S_WAITER.PROCESS
WAITER_PID
, 'Table lock (TM): ' || U.NAME || '.' || O.NAME || ' - Mode held: '||
DECODE(L_LOCKER.LMODE,
0, 'None',
/* same as Monitor */
1, 'Null',
/* N */
2, 'Row-S (SS)',
/* L */
3, 'Row-X (SX)',
/* R */
4, 'Share',
/* S */
5, 'S/Row-X (SSX)', /* C */
6, 'Exclusive',
/* X */
'???: ' || to_char(L_LOCKER.LMODE)) || ' / Mode requested: '||
DECODE(L_WAITER.REQUEST,
0, 'None',
/* same as Monitor */
1, 'Null',
/* N */
2, 'Row-S (SS)',
/* L */
3, 'Row-X (SX)',
/* R */
4, 'Share',
/* S */
5, 'S/Row-X (SSX)', /* C */
6, 'Exclusive',
/* X */
'???: '||to_char(L_WAITER.REQUEST))
SQL_TEXT_WAITER
FROM
OBJ$
O
, USER$
U
, V$LOCK
L_WAITER
, V$LOCK
L_LOCKER
, V$SESSION
S_WAITER
, V$SESSION
S_LOCKER
WHERE S_WAITER.SID
= L_WAITER.SID
AND L_WAITER.TYPE
IN ('TM')
AND S_LOCKER.sid
= L_LOCKER.sid
AND L_LOCKER.ID1
= L_WAITER.ID1
AND L_WAITER.REQUEST > 0
AND L_LOCKER.LMODE
> 0
AND L_WAITER.ADDR
!= L_LOCKER.ADDR
AND L_WAITER.ID1
= O.OBJ#
AND U.USER#
= O.OWNER#
UNION
SELECT /* now the (usual) row-locks TX */
SYSDATE
, S_LOCKER.OSUSER
OS_LOCKER
, S_LOCKER.USERNAME
LOCKER_SCHEMA
, S_LOCKER.PROCESS
LOCK_PID
, S_WAITER.OSUSER
OS_WAITER
, S_WAITER.USERNAME
WAITER_SCHEMA
, S_WAITER.PROCESS
WAITER_PID
, 'TX: ' || O.SQL_TEXT
SQL_TEXT_WAITER
FROM
V$LOCK
L_WAITER
, V$LOCK
L_LOCKER
, V$SESSION
S_WAITER
, V$SESSION
S_LOCKER
, V$_LOCK1
L1_WAITER
, V$OPEN_CURSOR
O
WHERE S_WAITER.SID
= L_WAITER.SID
AND L_WAITER.TYPE
IN ('TX')
AND S_LOCKER.sid
= L_LOCKER.sid
AND L_LOCKER.ID1
= L_WAITER.ID1
AND L_WAITER.REQUEST
> 0
AND L_LOCKER.LMODE
> 0
AND L_WAITER.ADDR
!= L_LOCKER.ADDR
AND L1_WAITER.LADDR
= L_WAITER.ADDR
AND L1_WAITER.KADDR
= L_WAITER.KADDR
AND L1_WAITER.SADDR
= O.SADDR;
INSERT INTO tvd_lock_info_2
( lock_date, wait, os_user, process, locker,
object_owner, object_name, program )
SELECT SYSDATE
, DECODE(L.REQUEST,0,'NO','YES') WAIT
, S.OSUSER
, S.PROCESS
, S.USERNAME
LOCKER
, U.NAME
T_OWNER
, O.NAME
OBJECT_NAME
, ' '||S.PROGRAM
PROGRAM
FROM
V$LOCK
L
, USER$
U
, OBJ$
O
, V$SESSION S
WHERE
U.USER# = O.OWNER#
AND
S.SID = L.SID
AND
L.ID1 = O.OBJ#
AND
L.TYPE = 'TM'
AND
U.NAME != 'SYS'
UNION
SELECT SYSDATE
, DECODE(L.REQUEST,0,'NO','YES') WAIT
, S.OSUSER
, S.PROCESS
, S.USERNAME
LOCKER
, '-'
, 'Record(s)'
, ' '||S.PROGRAM
PROGRAM
FROM
V$LOCK
L
,
V$SESSION S
WHERE
S.SID = L.SID
AND
L.TYPE = 'TX';
COMMIT;
END;
/
show errors
Trigger log_deadlock_errors
CREATE OR REPLACE TRIGGER log_deadlock_errors
AFTER SERVERERROR
ON DATABASE
BEGIN
IF (IS_SERVERERROR (60)) THEN
sys.tvd_prc_lock_info;
END IF;
END log_deadlock_errors;
/
show errors
Test des neuen Triggers
Anhand eines kleinen Testfalles kann ein Deadlocks erzeugt werden:
Verbinden Sie sich mit zwei SQL-Plus Sessions als User Scott auf der Datenbank,
nachfolgend werden diese Sessions als Session A und Session B bezeichnen. Um den
Deadlock zu provozieren, muss in beiden Sessions ein Lock auf eine Tabelle ausgeführt
werden. Werden die beiden Befehle zu den beiden Sessions hin vertauscht, so entsteht der
Deadlock:
Sequenz
1
2
Session A
Session B
UPDATE emp SET sal = sal + 100
WHERE deptno = 10;
UPDATE emp SET sal = sal + 200
WHERE deptno = 20;
3
UPDATE emp SET sal = sal + 200
WHERE deptno = 20;
4
UPDATE emp SET sal = sal + 100
WHERE deptno = 10;
5
UPDATE emp SET sal = sal + 200
WHERE deptno = 20
*
ERROR at line 1:
ORA-00060: deadlock detected while
waiting for resource
Auch wenn die beiden Scott-Sessions wieder beendet werden, lassen sich die
protokollierten Informationen zu einem späteren Zeitpunkt über den User SYS aus den
beiden Tabellen auswerten:
SELECT
,
,
,
FROM
to_char(lock_date,'HH24:MI:SS') lock_date
locker_schema
LCK
waiter_schema
WAI
sql_text_waiter
tvd_lock_info_1;
LOCK_DAT
-------09:14:42
09:14:42
09:14:42
09:14:42
09:14:42
09:14:42
09:14:42
LCK
----------SCOTT
SCOTT
SCOTT
SCOTT
SCOTT
SCOTT
SCOTT
WAI
----------SCOTT
SCOTT
SCOTT
SCOTT
SCOTT
SCOTT
SCOTT
SQL_TEXT_WAITER
---------------------------------------------------------------TX: SELECT ATTRIBUTE
FROM V$CONTEXT WHERE NAMESPACE = 'LBAC$L
TX: SELECT MAX(TAG#)
FROM LBAC$LAB
TX: SELECT POL#
FROM LBAC$POL ORDER BY POL#
TX: SELECT POL#,PACKAGE
FROM LBAC$POL WHERE BITAND(FLAGS,1) =
TX: select pol#, usr_name, usr_labels, package, privs from lbac$
TX: table_1_0_139_0_0_0
TX: update emp set sal = sal + 100
where deptno = 10
Bereits durch Ausführen eines Select-Befehls auf die tvd_lock_info_1-Tabelle lässt sich
feststellen WER - WAS - WANN gelockt hat. Durch den weiteren Select-Befehl auf die
Tabelle tvd_lock_info_2-Tabelle lassen sich weitere Informationen bestimmen: Welche
weiteren Sessions, Tabellen und Programme sind betroffen.
SELECT to_char(lock_date,'HH24:MI:SS') lock_date, wait, os_user, locker,
program, object_owner, object_name
FROM tvd_lock_info_2;
LOCK_DAT
-------09:14:43
09:14:43
09:14:43
09:14:43
09:14:43
WAI
--NO
NO
NO
NO
YES
OS_USER
-------------TRIVADIS\pej
TRIVADIS\pej
TRIVADIS\pej
TRIVADIS\pej
TRIVADIS\pej
LOCKER
------SCOTT
SCOTT
SCOTT
SCOTT
SCOTT
PROGRAM
-----------sqlplusw.exe
sqlplusw.exe
sqlplusw.exe
sqlplusw.exe
sqlplusw.exe
OBJECT_OWNER
---------------SCOTT
SCOTT
-
OBJECT_NAME
-------------Record(s)
EMP
Record(s)
EMP
Record(s)
Eine Datei mit den notwendigen Create-Table, Create-Procedure und dem Database-Trigger
(cslcktrg.sql) und zur formatierten Ausgabe (lslcksta.sql) finden Sie auf unserer Website im
Bereich Tools und Services zum kostenlosen herunterladen.
Weitere Informationen über die Themen Transaktionsverhalten, Locks und Deadlocks
finden Sie in früheren Ausgaben der DOAG, auf unserer Website und in unseren OracleSchulungen!
Viel Spass mit Oracle und keine Applikationsabbrüche und Wartezeiten durch Deadlocks!
Peter Jensch
Trivadis AG
Peter Jensch
Max-Lang-Strasse 56
D-70771 Leinfelden-Echterdingen
Internet: http://www.trivadis.com
Mail: [email protected]
Tel: +49 711 90 36 32 30
Fax: +49 711 90 36 32 59