ORA-02049: 시간초과: 분산 트랜잭션이 잠금으로 대기중 입니다

ORA-02049: 시간초과: 분산 트랜잭션이 잠금으로 대기중 입니다

ORA-02049: timeout: distributed transaction waiting for lock

오전에 울 사무실 막내가 프로시저를 실행하다가 이와 같은 오류를 만났다.

테이블에 insert를 하고 나서, commit/rollback 하는걸 깜빡하고

해당 테이블에 동일한 record를 insert 하도록 하는 프로시저를 실행한 것이다. 

어처구니 없는 실수라고 생각할 수 있지만,

사실 개발을 오래한 사람들도 가끔씩 만나게 되는 상황이다.

먼저 lock의 원인을 찾아야 한다.

SELECT a.sid,

       a.serial#,

       a.username,

       a.process,

       b.object_name,

       DECODE (c.lmode,

               2, 'RS',

               3, 'RX',

               4, 'S',

               5, 'SRX',

               8, 'X',

               'NO')

          "TABLE LOCK",

       DECODE (a.command,

               2, 'INSERT',

               3, 'SELECT',

               6, 'UPDATE',

               7, 'DELETE',

               12, 'DROP TABLE',

               26, 'LOCK TABLE',

               'UNknown')

          "SQL",

       DECODE (a.lockwait, NULL, 'NO wait', 'Wait') "STATUS"

  FROM v$session a, dba_objects b, v$lock c

 WHERE a.sid = c.sid AND b.object_id = c.id1 AND c.TYPE = 'TM';

결과로 나온 SID와 SERIAL#를 이용하여

lock이 걸린 세션을 Kill 한다.

alter system kill session 'SID,SERIAL#';

---

  SELECT se.username,

         NULL,

         se.sid,

         DECODE (se.command,

                 0, 'No command',

                 1, 'CREATE TABLE',

                 2, 'INSERT',

                 3, 'SELECT',

                 4, 'CREATE CLUSTER',

                 5, 'ALTER CLUSTER',

                 6, 'UPDATE',

                 7, 'DELETE',

                 8, 'DROP CLUSTER',

                 9, 'CREATE INDEX',

                 10, 'DROP INDEX',

                 11, 'ALTER INDEX',

                 12, 'DROP TABLE',

                 13, 'CREATE SEQUENCE',

                 14, 'ALTER SEQUENCE',

                 15, 'ALTER TABLE',

                 16, 'DROP SEQUENCE',

                 17, 'GRANT',

                 18, 'REVOKE',

                 19, 'CREATE SYNONYM',

                 20, 'DROP SYNONYM',

                 21, 'CREATE VIEW',

                 22, 'DROP VIEW',

                 23, 'VALIDATE INDEX',

                 24, 'CREATE PROCEDURE',

                 25, 'ALTER PROCEDURE',

                 26, 'LOCK TABLE',

                 27, 'NO OPERATION',

                 28, 'RENAME',

                 29, 'COMMENT',

                 30, 'AUDIT',

                 31, 'NOAUDIT',

                 32, 'CREATE DATABASE LINK',

                 33, 'DROP DATABASE LINK',

                 34, 'CREATE DATABASE',

                 35, 'ALTER DATABASE',

                 36, 'CREATE ROLLBACK SEGMENT',

                 37, 'ALTER ROLLBACK SEGMENT',

                 38, 'DROP ROLLBACK SEGMENT',

                 39, 'CREATE TABLESPACE',

                 40, 'ALTER TABLESPACE',

                 41, 'DROP TABLESPACE',

                 42, 'ALTER SESSION',

                 43, 'ALTER USER',

                 44, 'COMMIT',

                 45, 'ROLLBACK',

                 46, 'SAVEPOINT',

                 47, 'PL/SQL EXECUTE',

                 48, 'SET TRANSACTION',

                 49, 'ALTER SYSTEM SWITCH LOG',

                 50, 'EXPLAIN',

                 51, 'CREATE USER',

                 52, 'CREATE ROLE',

                 53, 'DROP USER',

                 54, 'DROP ROLE',

                 55, 'SET ROLE',

                 56, 'CREATE SCHEMA',

                 57, 'CREATE CONTROL FILE',

                 58, 'ALTER TRACING',

                 59, 'CREATE TRIGGER',

                 60, 'ALTER TRIGGER',

                 61, 'DROP TRIGGER',

                 62, 'ANALYZE TABLE',

                 63, 'ANALYZE INDEX',

                 64, 'ANALYZE CLUSTER',

                 65, 'CREATE PROFILE',

                 67, 'DROP PROFILE',

                 68, 'ALTER PROFILE',

                 69, 'DROP PROCEDURE',

                 70, 'ALTER RESOURCE COST',

                 71, 'CREATE SNAPSHOT LOG',

                 72, 'ALTER SNAPSHOT LOG',

                 73, 'DROP SNAPSHOT LOG',

                 74, 'CREATE SNAPSHOT',

                 75, 'ALTER SNAPSHOT',

                 76, 'DROP SNAPSHOT',

                 79, 'ALTER ROLE',

                 85, 'TRUNCATE TABLE',

                 86, 'TRUNCATE CLUSTER',

                 88, 'ALTER VIEW',

                 91, 'CREATE FUNCTION',

                 92, 'ALTER FUNCTION',

                 93, 'DROP FUNCTION',

                 94, 'CREATE PACKAGE',

                 95, 'ALTER PACKAGE',

                 96, 'DROP PACKAGE',

                 97, 'CREATE PACKAGE BODY',

                 98, 'ALTER PACKAGE BODY',

                 99, 'DROP PACKAGE BODY',

                 TO_CHAR (se.command))

            command,

         DECODE (lo.TYPE,

                 'MR', 'Media Recovery',

                 'RT', 'Redo Thread',

                 'UN', 'User Name',

                 'TX', 'Transaction',

                 'TM', 'DML',

                 'UL', 'PL/SQL User Lock',

                 'DX', 'Distributed Xaction',

                 'CF', 'Control File',

                 'IS', 'Instance State',

                 'FS', 'File Set',

                 'IR', 'Instance Recovery',

                 'ST', 'Disk Space Transaction',

                 'TS', 'Temp Segment',

                 'IV', 'Library Cache Invalidation',

                 'LS', 'Log Start or Switch',

                 'RW', 'Row Wait',

                 'SQ', 'Sequence Number',

                 'TE', 'Extend Table',

                 'TT', 'Temp Table',

                 'JQ', 'Job Queue',

                 lo.TYPE)

            ltype,

         DECODE (lo.lmode,

                 0, 'NONE',                          /* Mon Lock equivalent */

                 1, 'Null Mode',                    /* N */

                 2, 'Row-S (SS)',                 /* L */

                 3, 'Row-X (SX)',                 /* R */

                 4, 'Share (S)',                    /* S */

                 5, 'S/Row-X (SSX)',           /* C */

                 6, 'Excl (X)',                      /* X */

                 lo.lmode)

            lmode,

         DECODE (lo.request,

                 0, 'NONE',                         /* Mon Lock equivalent */

                 1, 'Null',                            /* N */

                 2, 'Row-S (SS)',                /* L */

                 3, 'Row-X (SX)',                /* R */

                 4, 'Share (S)',                   /* S */

                 5, 'S/Row-X (SSX)',          /* C */

                 6, 'Excl (X)',                     /* X */

                 TO_CHAR (lo.request))

            request,

         lo.ctime ctime,

         DECODE (lo.block,

                 0, 'No Block',

                 1, 'Blocking',

                 2, 'Global',

                 TO_CHAR (lo.block))

            blkothr,

         'SYS' owner,

         ro.name image

    FROM v$lock lo,

         v$session se,

         v$transaction tr,

         v$rollname ro

   WHERE     se.sid = lo.sid

         AND se.taddr = tr.addr(+)

         AND tr.xidusn = ro.usn(+)

         AND lo.TYPE = 'TM'

ORDER BY sid