デッドロックとは、2つ以上のセッションのトランザクションがお互いにロック解除待ち状態に陥る状態です。
お互いのセッションのトランザクションが相手のロック解除を待っているため、いずれのセッションもトランザクションを終了できず、ロック競合を解消できなくなります。
デッドロックが発生した場合、デッドロックが検出されたセッション(ORA-00060が発生したセッション)のトランザクションが自動的にロールバックされます。
ロールバック後、トランザクション制御できるようになるため、そのトランザクション全体をコミットまたはロールバックしてトランザクションを終了し、ロック競合を解除します。
■検証環境
OS：Oracle Linux 6.5
DB/GI：Oracle Database 12c Release 1 (12.1.0.2.0) Enterprise Edition
※2ノードRAC(管理者管理型DB)
■前提
・非CDB
■検証パターン
①別々のセッションから同じリソースに対して更新
■検証
①別々のセッションから同じリソースに対して更新
セッション1、セッション2で同じ表の別々のレコードに対してロック取得後、セッション1からセッション2がロック取得中のレコードに対して更新、セッション2からセッション1がロック取得中のレコードに対して更新しようとしたときデッドロック検出されるか検証します
【検証手順】
1. セッション1(SCOTT)で特定のレコードに対してロック取得
2. セッション2(HR)で同じ表の別レコードに対してロック取得
3. セッション1(SCOTT)からセッション2(HR)がロック取得中のレコードに対して更新
4. セッション2(HR)からセッション1(SCOTT)がロック取得中のレコードに対して更新
【作業ログ】
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- ---------- ---------- -------- ---------- ---------- ----------
7369 SMITH CLERK 7902 80-12-17 800 20
7499 ALLEN SALESMAN 7698 81-02-20 1600 300 30
7521 WARD SALESMAN 7698 81-02-22 1250 500 30
7566 JONES MANAGER 7839 81-04-02 2975 20
7654 MARTIN SALESMAN 7698 81-09-28 1250 1400 30
7698 BLAKE MANAGER 7839 81-05-01 2850 30
7782 CLARK MANAGER 7839 81-06-09 2450 10
7788 SCOTT ANALYST 7566 87-04-19 3000 20
7839 KING PRESIDENT 81-11-17 5000 10
7844 TURNER SALESMAN 7698 81-09-08 1500 0 30
7876 ADAMS CLERK 7788 87-05-23 1100 20
7900 JAMES CLERK 7698 81-12-03 950 30
7902 FORD ANALYST 7566 81-12-03 3000 20
7934 MILLER CLERK 7782 82-01-23 1300 10
14行が選択されました。
SQL> update emp set SAL = 1000 where EMPNO = 7369;
1行が更新されました。
2. セッション2(HR)で同じ表の別レコードに対してロック取得
SQL> select * from scott.emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- ---------- ---------- -------- ---------- ---------- ----------
7369 SMITH CLERK 7902 80-12-17 800 20
7499 ALLEN SALESMAN 7698 81-02-20 1600 300 30
7521 WARD SALESMAN 7698 81-02-22 1250 500 30
7566 JONES MANAGER 7839 81-04-02 2975 20
7654 MARTIN SALESMAN 7698 81-09-28 1250 1400 30
7698 BLAKE MANAGER 7839 81-05-01 2850 30
7782 CLARK MANAGER 7839 81-06-09 2450 10
7788 SCOTT ANALYST 7566 87-04-19 3000 20
7839 KING PRESIDENT 81-11-17 5000 10
7844 TURNER SALESMAN 7698 81-09-08 1500 0 30
7876 ADAMS CLERK 7788 87-05-23 1100 20
7900 JAMES CLERK 7698 81-12-03 950 30
7902 FORD ANALYST 7566 81-12-03 3000 20
7934 MILLER CLERK 7782 82-01-23 1300 10
14行が選択されました。
SQL> update scott.emp set SAL = 2000 where EMPNO = 7521;
1行が更新されました。
3. セッション1(SCOTT)からセッション2(HR)がロック取得中のレコードに対して更新
SQL> update emp set SAL = 5000 where EMPNO = 7521;
★応答なし
update emp set SAL = 5000 where EMPNO = 7521
*
行1でエラーが発生しました。:
ORA-00060: リソース待機の間にデッドロックが検出されました。
★「4」実行後、数秒経ってからORA-00060発生
4. セッション2(HR)からセッション1(SCOTT)がロック取得中のレコードに対して更新
SQL> update scott.emp set SAL = 6000 where EMPNO = 7369;
★応答なし
セッション1(SCOTT)でトランザクション全体をコミットまたはロールバック後、制御が戻った(更新できた)
※デッドロック発生後のロック状態
SQL> select sid, serial#, username, blocking_session from v$session where username in('SCOTT', 'HR');
SID SERIAL# USERNAME BLOCKING_SESSION
---------- ---------- --------------- ----------------
16 31780 HR 372
372 65257 SCOTT
SQL> select SID, TYPE, decode(LMODE, 0, 'NONE(ロックなし)',1 ,'NULL',2, 'RS/SS(行共有ロック)',3 , 'RX/SX(行排他ロック)',4, 'S(共有ロック)',5, 'SRX/SSX(共有行排他ロック)',6 , 'X(排他ロック)', 'Unknown') as lck_mode, BLOCK from v$lock where type in('TX', 'TM');
SID TYPE LCK_MODE BLOCK
---------- ------ ------------------------------ ----------
372 TM RX/SX(行排他ロック) 2
16 TM RX/SX(行排他ロック) 2
16 TX X(排他ロック) 2
372 TX X(排他ロック) 1
16 TX NONE(ロックなし) 0
SQL> select o.owner, o.object_name, o.object_type, lo.SESSION_ID, decode(lo.LOCKED_MODE, 0, 'NONE(ロックなし)',1 ,'NULL',2, 'RS/SS(行共有ロック)',3 , 'RX/SX(行排他ロック)',4, 'S(共有ロック)',5, 'SRX/SSX(共有行排他ロック)',6 , 'X(排他ロック)', 'Unknown') as lck_mode from v$locked_object lo, dba_objects o where lo.OBJECT_ID = o.OBJECT_ID;
OWNER OBJECT_NAME OBJECT_TYPE SESSION_ID LCK_MODE
--------------- --------------- --------------- ---------- ------------------------------
SCOTT EMP TABLE 372 RX/SX(行排他ロック)
SCOTT EMP TABLE 16 RX/SX(行排他ロック)
【参考】
Sun Jan 01 10:47:40 2023
Global Enqueue Services Deadlock detected (DID = 3_0_1). More information in file
/u01/app/oracle/diag/rdbms/orcl/orcl1/trace/orcl1_lmd0_5951.trc.
★デッドロックが検出されたノード(本検証ではノード1)のDBアラートログに出力された。ノード2のDBアラートログには何も出力されなかった
●トレースファイル
*** 2023-01-01 10:47:40.171
DUMP LOCAL BLOCKER/HOLDER: block level 5 res [0x1d0020][0x133],[TX][ext 0x0,0x2]
----------resource 0xa6636938----------------------
resname : [0x1d0020][0x133],[TX][ext 0x0,0x2]
lmdid : 0
rht group : 0
rht ptr : 0xa62830d8
rht bucket idx: 1860
hash mask : x3
Local inst : 1
dir_inst : 1
master_inst : 1
hv idx : 39
hv last r.inc : 3
current inc : 3
hv status : 0
hv master inst: 1
open options : deadlock detection=Y, cached=N, varvblk=N, slock=N
grant_bits : KJUSERNL KJUSEREX
grant mode : KJUSERNL KJUSERCR KJUSERCW KJUSERPR KJUSERPW KJUSEREX
count : 1 0 0 0 0 1
val_state : KJUSERVS_NOVALUE
valblk : 0x0300000000000000419bbd6000000000 .A`
access_inst : 1
vbreq_state : 0
state : x0
resp : 0xa6636938
entry : DIR=Y, MASTER=Y
On Scan_q? : N
On Cache? : N
On Remote_q? : N
history : x11
frozen : 0
Total accesses: 29
Imm. accesses: 16
Granted_locks : 1
Cvting_locks : 1
value_block: 03 00 00 00 00 00 00 00 41 9b bd 60 00 00 00 00
GRANTED_Q:
lp 0xa52578c8 gl KJUSEREX rp 0xa6636938 [0x1d0020][0x133],[TX][ext 0x0,0x2]
master 1 gl owner 0xa9c0e6e8 possible pid 19700 xid 38000-0001-0000001D rseq 1 mseq 0 bast none
history REF_RES > LOC_AST > CLOSE > FREE > REF_RES > LOC_AST
open opt KJUSERDEADLOCK sec since grant 10
CONVERT_Q:
lp 0xa5257e98 gl KJUSERNL rl KJUSEREX rp 0xa6636938 [0x1d0020][0x133],[TX][ext 0x0,0x2]
master 1 gl owner 0xa9ee04e8 possible pid 19535 xid 33000-0001-7FF300000025 rseq 1 mseq 0 bast none
history REF_RES > LOC_AST > CLOSE > FREE > REF_RES > GR2CVT
convert opt KJUSERGETVALUE
----------enqueue 0xa52578c8------------------------
lock version : 2073
Owner inst : 1
grant_level : KJUSEREX
req_level : KJUSEREX
bast_level : KJUSERNL
notify_func : none
resp : 0xa6636938
procp : 0xa76d49a8
pid : 19535
proc version : 19
oprocp : (nil)
opid : 19535
group lock owner : 0xa9c0e6e8
possible pid : 19700
xid : 38000-0001-0000001D
dd_time : 0.0 secs
dd_count : 0
timeout : 0.0 secs
On_timer_q? : N
On_dd_q? : N
sec since grant : 10
lock_state : GRANTED
ast_flag : 0x0
Open Options : KJUSERDEADLOCK
Convert options : KJUSERNOQUEUE KJUSERNODEADLOCKWAIT
History : REF_RES > LOC_AST > CLOSE > FREE > REF_RES > LOC_AST
Msg_Seq : 0x0
res_seq : 1
valblk : 0x0008000000000000a0cd8c56f37f0000 .V
user session for deadlock lock 0xa52578c8
sid: 16 ser: 31780 audsid: 2930374 user: 102/HR
flags: (0x41) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
flags2: (0x40009) -/-/INC
pid: 56 O/S info: user: oracle, term: UNKNOWN, ospid: 19700
image: oracle@node1.oracle12c.jp (TNS V1-V3)
client details:
O/S info: user: oracle, term: pts/1, ospid: 19699
machine: node1.oracle12c.jp program: sqlplus@node1.oracle12c.jp (TNS V1-V3)
application name: SQL*Plus, hash value=3669949024
current SQL:
update scott.emp set SAL = 6000 where EMPNO = 7369
DUMP LOCAL BLOCKER: initiate state dump for DEADLOCK
possible owner[56.19700] on resource TX-001D0020-00000133-00000000-00000002
2023-01-01 10:47:40.183766 :kjzddmp(): Submitting asynchronized dump request [1c]. summary=[ges process stack dump (kjdglblkrdm1)].
----------enqueue 0xa5257e98------------------------
lock version : 1337
Owner inst : 1
grant_level : KJUSERNL
req_level : KJUSEREX
bast_level : KJUSERNL
notify_func : none
resp : 0xa6636938
procp : 0xa76d49a8
pid : 19535
proc version : 19
oprocp : (nil)
opid : 19535
group lock owner : 0xa9ee04e8
possible pid : 19535
xid : 33000-0001-00000025
dd_time : 10.0 secs
dd_count : 1
timeout : 0.0 secs
On_timer_q? : N
On_dd_q? : Y
sec since grant : N/A
lock_state : OPENING CONVERTING
ast_flag : 0x0
Open Options : KJUSERDEADLOCK
Convert options : KJUSERGETVALUE
History : REF_RES > LOC_AST > CLOSE > FREE > REF_RES > GR2CVT
Msg_Seq : 0x0
res_seq : 1
valblk : 0x17000000000000000000000000000000 .
user session for deadlock lock 0xa5257e98
sid: 372 ser: 65257 audsid: 2930373 user: 110/SCOTT
flags: (0x41) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
flags2: (0x40009) -/-/INC
pid: 51 O/S info: user: oracle, term: UNKNOWN, ospid: 19535
image: oracle@node1.oracle12c.jp (TNS V1-V3)
client details:
O/S info: user: oracle, term: pts/0, ospid: 19534
machine: node1.oracle12c.jp program: sqlplus@node1.oracle12c.jp (TNS V1-V3)
application name: SQL*Plus, hash value=3669949024
current SQL:
update emp set SAL = 5000 where EMPNO = 7521 ★デッドロックの原因となったSQL
DUMP LOCAL BLOCKER: initiate state dump for DEADLOCK
possible owner[51.19535] on resource TX-001D0020-00000133-00000000-00000002
2023-01-01 10:47:40.185572 :kjzddmp(): Submitting asynchronized dump request [1c]. summary=[ges process stack dump (kjdglblkrdm1)].
DUMP LOCAL BLOCKER/HOLDER: block level 5 res [0x1b0012][0x147],[TX][ext 0x0,0x2]
----------resource 0xa6a6e638----------------------
resname : [0x1b0012][0x147],[TX][ext 0x0,0x2]
lmdid : 1
rht group : 0
rht ptr : 0xa62832b0
rht bucket idx: 717
hash mask : x3
Local inst : 1
dir_inst : 1
master_inst : 1
hv idx : 45
hv last r.inc : 3
current inc : 3
hv status : 0
hv master inst: 1
open options : deadlock detection=Y, cached=N, varvblk=N, slock=N
grant_bits : KJUSERNL KJUSEREX
grant mode : KJUSERNL KJUSERCR KJUSERCW KJUSERPR KJUSERPW KJUSEREX
count : 1 0 0 0 0 1
val_state : KJUSERVS_NOVALUE
valblk : 0x01000000000000006f8025a500000000 .o%
access_inst : 1
vbreq_state : 0
state : x0
resp : 0xa6a6e638
entry : DIR=Y, MASTER=Y
On Scan_q? : N
On Cache? : N
On Remote_q? : N
history : x11
frozen : 0
Total accesses: 44
Imm. accesses: 31
Granted_locks : 1
Cvting_locks : 1
value_block: 01 00 00 00 00 00 00 00 6f 80 25 a5 00 00 00 00
GRANTED_Q:
lp 0xa5095dd8 gl KJUSEREX rp 0xa6a6e638 [0x1b0012][0x147],[TX][ext 0x0,0x2]
master 1 gl owner 0xa9ee04e8 possible pid 19535 xid 33000-0001-00000025 rseq 5 mseq 0 bast none
history REF_RES > LOC_AST > CLOSE > FREE > REF_RES > LOC_AST
open opt KJUSERDEADLOCK sec since grant 6
CONVERT_Q:
lp 0xa50963a8 gl KJUSERNL rl KJUSEREX rp 0xa6a6e638 [0x1b0012][0x147],[TX][ext 0x0,0x2]
master 1 gl owner 0xa9c0e6e8 possible pid 19700 xid 38000-0001-7FF30000001D rseq 5 mseq 0 bast none
history REF_RES > LOC_AST > CLOSE > FREE > REF_RES > GR2CVT
convert opt KJUSERGETVALUE
----------enqueue 0xa5095dd8------------------------
lock version : 991
Owner inst : 1
grant_level : KJUSEREX
req_level : KJUSEREX
bast_level : KJUSERNL
notify_func : none
resp : 0xa6a6e638
procp : 0xa76dad80
pid : 19700
proc version : 11
oprocp : (nil)
opid : 19700
group lock owner : 0xa9ee04e8
possible pid : 19535
xid : 33000-0001-00000025
dd_time : 0.0 secs
dd_count : 0
timeout : 0.0 secs
On_timer_q? : N
On_dd_q? : N
sec since grant : 6
lock_state : GRANTED
ast_flag : 0x0
Open Options : KJUSERDEADLOCK
Convert options : KJUSERNOQUEUE KJUSERNODEADLOCKWAIT
History : REF_RES > LOC_AST > CLOSE > FREE > REF_RES > LOC_AST
Msg_Seq : 0x0
res_seq : 5
valblk : 0x01000000000000009c91260000000000 .&
user session for deadlock lock 0xa5095dd8
sid: 372 ser: 65257 audsid: 2930373 user: 110/SCOTT
flags: (0x41) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
flags2: (0x40009) -/-/INC
pid: 51 O/S info: user: oracle, term: UNKNOWN, ospid: 19535
image: oracle@node1.oracle12c.jp (TNS V1-V3)
client details:
O/S info: user: oracle, term: pts/0, ospid: 19534
machine: node1.oracle12c.jp program: sqlplus@node1.oracle12c.jp (TNS V1-V3)
application name: SQL*Plus, hash value=3669949024
current SQL:
update emp set SAL = 5000 where EMPNO = 7521 ★デッドロックの原因となったSQL
DUMP LOCAL BLOCKER: initiate state dump for DEADLOCK
possible owner[51.19535] on resource TX-001B0012-00000147-00000000-00000002
2023-01-01 10:47:40.186533 :kjzddmp(): Submitting asynchronized dump request [1c]. summary=[ges process stack dump (kjdglblkrdm1)].
----------enqueue 0xa50963a8------------------------
lock version : 645
Owner inst : 1
grant_level : KJUSERNL
req_level : KJUSEREX
bast_level : KJUSERNL
notify_func : none
resp : 0xa6a6e638
procp : 0xa76dad80
pid : 19700
proc version : 11
oprocp : (nil)
opid : 19700
group lock owner : 0xa9c0e6e8
possible pid : 19700
xid : 38000-0001-0000001D
dd_time : 4.0 secs
dd_count : 0
timeout : 0.0 secs
On_timer_q? : N
On_dd_q? : Y
sec since grant : N/A
lock_state : OPENING CONVERTING
ast_flag : 0x0
Open Options : KJUSERDEADLOCK
Convert options : KJUSERGETVALUE
History : REF_RES > LOC_AST > CLOSE > FREE > REF_RES > GR2CVT
Msg_Seq : 0x0
res_seq : 5
valblk : 0x17000000000000000000000000000000 .
user session for deadlock lock 0xa50963a8
sid: 16 ser: 31780 audsid: 2930374 user: 102/HR
flags: (0x41) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
flags2: (0x40009) -/-/INC
pid: 56 O/S info: user: oracle, term: UNKNOWN, ospid: 19700
image: oracle@node1.oracle12c.jp (TNS V1-V3)
client details:
O/S info: user: oracle, term: pts/1, ospid: 19699
machine: node1.oracle12c.jp program: sqlplus@node1.oracle12c.jp (TNS V1-V3)
application name: SQL*Plus, hash value=3669949024
current SQL:
update scott.emp set SAL = 6000 where EMPNO = 7369
DUMP LOCAL BLOCKER: initiate state dump for DEADLOCK
possible owner[56.19700] on resource TX-001B0012-00000147-00000000-00000002
2023-01-01 10:47:40.186987 :kjzddmp(): Submitting asynchronized dump request [1c]. summary=[ges process stack dump (kjdglblkrdm1)].
Global blockers dump end:-----------------------------------
========================================================================
Global Wait-For-Graph(WFG) for GES Deadlock ID=[3_0_1]
------------------------------------------------------------------------
Victim : (instance=1, lock=0xa5257e98)
Start (master) Instance : 1
Number of Locks involved : 4
Number of Sessions involved : 2
User session identified by:
{
User Name : oracle
User Machine : node1.oracle12c.jp
OS Terminal Name : pts/0
OS Process ID : 19534
OS Program Name : sqlplus@node1.oracle12c.jp (TNS V1-V3)
Application Name : SQL*Plusnode1.oracle12c.jp (TNS V1-V3)
Action Name : AQ LB CoordinatorCLeaNupholdst
Current SQL : update emp set SAL = 5000 where EMPNO = 7521 ★デッドロックの原因となったSQL
Session Number : 372
Session Serial Number : 65257
Server Process ORAPID : 51
Server Process OSPID : 19535
Instance : 1
}
waiting for Lock 0xa5257e98 (Transaction):
{
Lock Level : KJUSEREX
Resource Name : TX 0x1d0020.0x133(ext 0x0,0x2)
GES Transaction ID : 33000-0001-00000025
}
which is blocked by Lock 0xa52578c8 (Transaction):
{
Lock Level : KJUSEREX
Resource Name : TX 0x1d0020.0x133(ext 0x0,0x2)
GES Transaction ID : 38000-0001-0000001D
}
owned by the
User session identified by:
{
User Name : oracle
User Machine : node1.oracle12c.jp
OS Terminal Name : pts/1
OS Process ID : 19699
OS Program Name : sqlplus@node1.oracle12c.jp (TNS V1-V3)
Application Name : SQL*Plusnode1.oracle12c.jp (TNS V1-V3)
Action Name : KDILM background CLeaNupholds
Current SQL : update scott.emp set SAL = 6000 where EMPNO = 7369
Session Number : 16
Session Serial Number : 31780
Server Process ORAPID : 56
Server Process OSPID : 19700
Instance : 1
}
waiting for Lock 0xa50963a8 (Transaction):
{
Lock Level : KJUSEREX
Resource Name : TX 0x1b0012.0x147(ext 0x0,0x2)
GES Transaction ID : 38000-0001-0000001D
}
which is blocked by Lock 0xa5095dd8 (Transaction):
{
Lock Level : KJUSEREX
Resource Name : TX 0x1b0012.0x147(ext 0x0,0x2)
GES Transaction ID : 33000-0001-00000025
}
owned by the first user session of the WFG.
------------------------------------------------------------------------
End of Global WFG for GES Deadlock ID=[3_0_1]
========================================================================
kjddprg: Transaction Deadlock added to the fixed table.
*** 2023-01-01 10:47:40.976
* Cancel deadlock victim lockp 0xa5257e98
Global blockers dump start:---------------------------------
■ロック解除方法
トランザクション制御を戻すためのロック解除方法は主に2種類あります
①トランザクションのコミットまたはロールバック
②セッション切断
①トランザクションのコミットまたはロールバック
--1.セッション1(SCOTT)でデータ更新
SQL> update scott.emp set SAL = 1000 where EMPNO = 7369;
1行が更新されました。
--2.セッション2(HR)でデータ更新
SQL> update scott.emp set SAL = 2000 where EMPNO = 7369;
★応答なし
セッション1(SCOTT)でトランザクション全体をコミットまたはロールバック後、制御が戻った(更新できた)
②セッション切断
--1.セッション1(SCOTT)でデータ更新
SQL> update scott.emp set SAL = 1000 where EMPNO = 7369;
1行が更新されました。
★「3」でセッション切断後、exitでDBログアウト時に下記メッセージが出力されました
SQL> exit
ERROR:
ORA-00028: セッションは強制終了されました。
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options (障害を含んでいます。)との接続が切断されました。
--2.セッション2(HR)でデータ更新
SQL> update scott.emp set SAL = 2000 where EMPNO = 7369;
★応答なし
--3.別セッションでセッション切断
--セッションID、シリアルナンバー確認
SQL> select sid, serial#, username, blocking_session from v$session where username in('SCOTT', 'HR');
SID SERIAL# USERNAME BLOCKING_SESSION
---------- ---------- --------------- ----------------
24 16459 HR 251
251 9114 SCOTT
--ロック解除待ちの原因となっているセッションを切断
SQL> alter system kill session '251, 9114';
システムが変更されました。
SQL> --セッション切断後
SQL> select sid, serial#, username, blocking_session from v$session where username in('SCOTT', 'HR');
SID SERIAL# USERNAME BLOCKING_SESSION
---------- ---------- --------------- ----------------
24 16459 HR
251 9114 SCOTT
