Oracle Deadlock Collection

Oracle Deadlock collection

Erstellen der Tabllen in denen die Deadlock Informationen gesammelt werden.

CREATE TABLE SYS.HUA_LOCK_INFO_1
(
  LOCK_DATE        DATE,
  OS_LOCKER        VARCHAR2(30 BYTE),
  LOCKER_SCHEMA    VARCHAR2(30 BYTE),
  LOCKER_PID       VARCHAR2(9 BYTE),
  OS_WAITER        VARCHAR2(30 BYTE),
  WAITER_SCHEMA    VARCHAR2(30 BYTE),
  WAITER_PID       VARCHAR2(9 BYTE),
  SQL_TEXT_WAITER  VARCHAR2(200 BYTE)
)
TABLESPACE SYSTEM
PCTUSED    40
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            FREELISTS        1
            FREELIST GROUPS  1
            BUFFER_POOL      DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
MONITORING;

CREATE TABLE SYS.HUA_LOCK_INFO_2
(
  LOCK_DATE     DATE,
  WAIT          VARCHAR2(3 BYTE),
  OS_USER       VARCHAR2(30 BYTE),
  PROCESS       VARCHAR2(9 BYTE),
  LOCKER        VARCHAR2(30 BYTE),
  OBJECT_OWNER  VARCHAR2(30 BYTE),
  OBJECT_NAME   VARCHAR2(30 BYTE),
  PROGRAM       VARCHAR2(50 BYTE)
)
TABLESPACE USERS
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          1M
            NEXT             1M
            MAXSIZE          UNLIMITED
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
MONITORING;

Erstellen einer Prozedur zum Abfragen der Informationen zum Deadlock und Protokollieren in den Tabellen.

CREATE OR REPLACE PROCEDURE SYS.HUA_LOCK_INFO
IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO HUA_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
, 'Tablelock(TM):'||U.NAME||'.'||O.NAME||'-Modeheld:'||
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))||'/Moderequested:'||
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 hua_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;
/

Erstellen eines Triggers der auf den Oracle Fehler eines Deadlocks reagiert und die Prozedur aufruft.

CREATE OR REPLACE TRIGGER SYS.HUA_LOG_DEADLOCK_ERRORS
AFTER SERVERERROR
ON DATABASE
DISABLE
BEGIN
IF (IS_SERVERERROR (60)) THEN
sys.HUA_LOCK_INFO;
END IF;
END HUA_LOG_DEADLOCK_ERRORS;
/