Deaclock(교착상태)
: Deadlock 발생 시, Oracle 에 의해 자동으로 Deadlock 상태를 해소 되나, TX-LOCK 이 유발됨
TX-LOCK 해소를 위해서, Application 에서 Deadlock Error 발생 시, Rollback 처리가 필요함
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 | SYS@TGDB=> create user test identified by test default tablespace users 2 temporary tablespace temp quota unlimited on users ; User created. SYS@TGDB=> grant connect, create table to test ; Grant succeeded. SYS@TGDB=> grant execute on dbms_lock to test ; Grant succeeded. SYS@TGDB=> grant select on v_$mystat to test ; Grant succeeded. SYS@TGDB=> -- 테스트 세션 1(52) SYS@TGDB=> conn test/test Connected. TEST@TGDB=> col ses_info new_value sname TEST@TGDB=> set termout off TEST@TGDB=> select 2 SYS_CONTEXT ('USERENV', 'SESSION_USER')||'_'||sys_context('USERENV','SID') ses_info 3 from dual; SES_INFO -------------------------------------------------------------------------------- TEST_52 TEST@TGDB=> set termout on TEST@TGDB=> set sqlprompt '&sname>' TEST_52> TEST_52> TEST_52>create table deadlock_1 ( id number, name varchar2(30)); Table created. TEST_52> create table deadlock_2 ( id number, name varchar2(30)); Table created. TEST_52>insert into deadlock_1 values ( 1, 'AAA'); 1 row created. TEST_52>insert into deadlock_2 values ( 2, 'BBB'); 1 row created. TEST_52>commit ; Commit complete. TEST_52> SYS@TGDB=> conn test/test Connected. TEST@TGDB=> col ses_info new_value sname TEST@TGDB=> set termout off TEST@TGDB=> select 2 SYS_CONTEXT ('USERENV', 'SESSION_USER')||'_'||sys_context('USERENV','SID') ses_info 3 from dual; SES_INFO -------------------------------------------------------------------------------- TEST_1 -- 테스트 세션 2(1) TEST@TGDB=> set termout on TEST@TGDB=> set sqlprompt '&sname>' TEST_1> -- 테스트 세션 1(52) 업데이트 TEST_52>update deadlock_1 set name ='SES1' where id = 1 ; 1 row updated. TEST_52> -- LOCK 체크 ( SID = 52 , DEADLOCK_1 , ROW LOCK ) SYS@TGDB=> set verify off SYS@TGDB=> col owner format a15 SYS@TGDB=> col object_name format a30 SYS@TGDB=> col locked_mode format a15 SYS@TGDB=> SYS@TGDB=> select /*+ first_rows leading(l) use_nl(o) */ 2 session_id, process, owner, object_name, decode(locked_mode,1,'Null',2,'Row-S',3,'Row-x',4,'Share',5,'S/Row-X',6,'Exclusive','None') lock_mode 3 from v$locked_object l, dba_objects o 4 where l.object_id = o.object_id 5 / SESSION_ID PROCESS OWNER OBJECT_NAME LOCK_MODE ---------- ------------------------ --------------- ------------------------------ --------- 52 5029 TEST DEADLOCK_1 Row-x SYS@TGDB=> -- 테스트 세션 2(1) 업데이트 TEST_1>update deadlock_2 set name ='SES2' where id = 2 ; 1 row updated. TEST_1> -- LOCK 체크 ( SID = 52 , DEADLOCK_1 , ROW LOCK ) -- LOCK 체크 ( SID = 1 , DEADLOCK_2 , ROW LOCK ) SYS@TGDB=> / SESSION_ID PROCESS OWNER OBJECT_NAME LOCK_MODE ---------- ------------------------ --------------- ------------------------------ --------- 1 5045 TEST DEADLOCK_2 Row-x 52 5029 TEST DEADLOCK_1 Row-x SYS@TGDB=> -- 테스트 세션 1(52) 업데이트 ( 세션 2가 LOCK HOLD 상태인 ROW를 대상으로 ) TEST_52>update deadlock_2 set name ='SES1' where id = 2 ; --> 대기 발생 -- LOCK 체크 ( SID = 52 , DEADLOCK_1 , ROW LOCK ) -- LOCK 체크 ( SID = 1 , DEADLOCK_2 , ROW LOCK ) -- LOCK 체크 ( SID = 52 , DEADLOCK_2 , ROW LOCK ) << 추가됨, 대기 상태 SYS@TGDB=> / SESSION_ID PROCESS OWNER OBJECT_NAME LOCK_MODE ---------- ------------------------ --------------- ------------------------------ --------- 1 5045 TEST DEADLOCK_2 Row-x 52 5029 TEST DEADLOCK_2 Row-x 52 5029 TEST DEADLOCK_1 Row-x SYS@TGDB=> -- 테스트 세션 2(1) 업데이트 ( 세션 1가 LOCK HOLD 상태인 ROW를 대상으로 ) TEST_52>update deadlock_1 set name ='SES2' where id = 1 ; --> 대기 발생 TEST_52>update deadlock_2 set name ='SES1' where id = 2 ; update deadlock_2 set name ='SES1' where id = 2 * ERROR at line 1: ORA-00060: deadlock detected while waiting for resource TEST_52> --> 테스트 세션 1(52)의 2번째 SQL 이 Cancel 된다 ( 주의 !! Rollback 이 아니다. ) <<Alert Log>> Wed Mar 07 10:55:52 2018 ORA-00060: Deadlock detected. More info in file /u01/app/oracle/diag/rdbms/tgdb/TGDB/trace/TGDB_ora_5031.trc. -- LOCK 체크 ( SID = 52 , DEADLOCK_1 , ROW LOCK ) -- LOCK 체크 ( SID = 1 , DEADLOCK_2 , ROW LOCK ) -- LOCK 체크 ( SID = 52 , DEADLOCK_2 , ROW LOCK ) << Cancel 됨 -- LOCK 체크 ( SID = 1 , DEADLOCK_1 , ROW LOCK ) << 추가됨 SYS@TGDB=> / SESSION_ID PROCESS OWNER OBJECT_NAME LOCK_MODE ---------- ------------------------ --------------- ------------------------------ --------- 1 5045 TEST DEADLOCK_1 Row-x 1 5045 TEST DEADLOCK_2 Row-x 52 5029 TEST DEADLOCK_1 Row-x SYS@TGDB=> -- 테스트 세션 2(1) 업데이트 ( 세션 1가 LOCK HOLD 상태인 ROW를 대상으로 ) TEST_1>update deadlock_1 set name ='SES2' where id = 1 ; --> 대기 상태 유지됨 ( 테스트 세션 1 의 첫번 째 SQL 이 commit/rollback 전임 ) --> Deadlock(교착상태)라 해소 되지만, TX-LOCK 대기 상태가 발생함 SYS@TGDB=> col w_sess for a50 SYS@TGDB=> col h_sess for a50 SYS@TGDB=> SELECT /*+ rule */ 2 sh.inst_id||' - '||sh.sid||' - '||decode(sh.status,'ACTIVE','A','I')||' : '||sh.event as H_Sess 3 , sw.inst_id||' - '||sw.sid||' - '||decode(sw.status,'ACTIVE','A','I')||' : '||sw.event as W_Sess 4 , lh.ctime w_sec 5 FROM 6 gv$lock lw 7 , gv$lock lh 8 , gv$session sw 9 , gv$session sh 10 , dba_objects ow 11 WHERE 1 = 1 12 AND lh.id1 = lw.id1 13 AND lh.id2 = lw.id2 14 AND lh.request = 0 15 AND lw.lmode = 0 16 AND (lh.id1, lh.id2) IN ( SELECT id1,id2 17 FROM gv$lock 18 WHERE request = 0 19 INTERSECT 20 SELECT id1,id2 21 FROM gv$lock 22 WHERE lmode = 0 ) 23 AND lw.inst_id = sw.inst_id 24 AND lw.sid = sw.sid 25 AND lh.inst_id = sh.inst_id 26 AND lh.sid = sh.sid 27 AND sw.ROW_WAIT_OBJ# = ow.object_id(+) 28 ORDER BY 29 w_sec desc 30 / H_SESS W_SESS W_SEC -------------------------------------------------- -------------------------------------------------- ---------- 1 - 52 - I : SQL*Net message from client 1 - 1 - A : enq: TX - row lock contention 1621 SYS@TGDB=> --> 테스트 세션 1(52) 이 Holder 이고, 테스트 세션 2(SID=1)은 대기 중으로 모니터링됨 -- 테스트 세션 1(52) ROLLBACK TEST_52>rollback ; Rollback complete. TEST_52> -- 테스트 세션 2(1) UPDATE 수행 완료 TEST_1>update deadlock_1 set name ='SES2' where id = 1 ; 1 row updated. | cs |