14.06.2017
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; /