读oracle trc
死锁是两个或多个用户等待数据相互锁定的情况。死锁会阻止某些事务继续工作。
Oracle 数据库会自动检测死锁,并通过回滚死锁中涉及的一条语句,释放一组冲突的行锁来解决死锁。数据库向经历语句级回滚的事务返回相应的消息。回滚的语句属于检测死锁的事务。通常,应显式回滚已发出信号的事务,但它可以在等待后重试回滚语句。
模拟






发生死锁后应用端收到报错ora-00060
00060, 00000, "deadlock detected while waiting for resource"
// *Cause: Transactions deadlocked one another while waiting for resources.
// *Action: Look at the trace file to see the transactions and resources
// involved. Retry if necessary.
告警日志中也会有异常
rac环境中由LMD进程每10秒检测一次死锁,发现后进行回滚,记录到trc文件中,部分信息可能记录在另一个节点上。

LMD进程的trc文件头部信息
DLM Resource Hashmasks Initialised
*** 2022-08-11 10:40:30.891298 [krsa.c:2949]
Acquiring krso process latch [krso.c:553] IX0
*** 2022-08-11 10:40:30.891313 [krsa.c:2972]
Successfully acquired krso process latch IX+
trc文件下面会有很多信息,摘取部分主要的
50879进程执行的update被51602进程执行的update阻塞了。
快捷分析定位方法:在LMD的trc中直接搜WFG
检查锁状态:
lock table命令(很少用)

模式说明
Oracle 数据库会自动检测死锁,并通过回滚死锁中涉及的一条语句,释放一组冲突的行锁来解决死锁。数据库向经历语句级回滚的事务返回相应的消息。回滚的语句属于检测死锁的事务。通常,应显式回滚已发出信号的事务,但它可以在等待后重试回滚语句。
模拟
发生死锁后应用端收到报错ora-00060
00060, 00000, "deadlock detected while waiting for resource"
// *Cause: Transactions deadlocked one another while waiting for resources.
// *Action: Look at the trace file to see the transactions and resources
// involved. Retry if necessary.
- 2022-08-29T10:27:13.243920+08:00
- Global Enqueue Services Deadlock detected (DID = 4_0_172). More information in file
- /oracle/app/oracle/diag/rdbms/ORCLp/ORCL1/trace/ORCL1_lmd0_38295.trc.
- 2022-08-29T10:27:23.691194+08:00
- Global Enqueue Services Deadlock detected (DID = 4_1_202). More information in file
- /oracle/app/oracle/diag/rdbms/ORCLp/ORCL2/trace/ORCL2_lmd0_53074.trc on Instance 2.
- 2022-08-29T11:05:10.782985+08:00
- Thread 1 advanced to log sequence 588 (LGWR switch), current SCN: 24798531
- Current log# 2 seq# 588 mem# 0: +DATADG/ORCLP/ONLINELOG/group_2.258.1111942287
- Current log# 2 seq# 588 mem# 1: +ARCHDG/ORCLP/ONLINELOG/group_2.259.1111942289
- 2022-08-29T11:05:10.956312+08:00
- ARC5 (PID:39189): Archived Log entry 1944 added for T-1.S-587 ID 0xaf1a0265 LAD:1
- 2022-08-29T11:05:13.827028+08:00
- Thread 1 advanced to log sequence 589 (LGWR switch), current SCN: 24799636
- Current log# 3 seq# 589 mem# 0: +DATADG/ORCLP/ONLINELOG/group_3.265.1111942289
- Current log# 3 seq# 589 mem# 1: +ARCHDG/ORCLP/ONLINELOG/group_3.260.1111942291
- 2022-08-29T11:10:13.554742+08:00
- Global Enqueue Services Deadlock detected (DID = 4_0_173). More information in file
- /oracle/app/oracle/diag/rdbms/ORCLp/ORCL1/trace/ORCL1_lmd0_38295.trc.
- 2022-08-29T11:10:18.680934+08:00
- Global Enqueue Services Deadlock detected (DID = 4_0_174). More information in file
- /oracle/app/oracle/diag/rdbms/ORCLp/ORCL1/trace/ORCL1_lmd0_38295.trc.
LMD进程的trc文件头部信息
- Trace file /oracle/app/oracle/diag/rdbms/orclp/orcl1/trace/orcl1_lmd0_38295.trc trace文件名称及路径
- Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production 数据库版本
- Version 19.14.0.0.0 数据库补丁版本
- Build label: RDBMS_19.14.0.0.0DBRU_LINUX.X64_211224.3 补丁编译信息
- ORACLE_HOME: /oracle/app/oracle/product/19.3.0/db_1 ORACLE_HOME路径
- System name: Linux 操作系统类型
- Node name: db1 主机名
- Release: 3.10.0-957.el7.x86_64 操作系统内核
- Version: #1 SMP Thu Oct 4 20:48:51 UTC 2018 操作系统版本
- Machine: x86_64 主机架构
- Instance name: orcl1 实例名
- Redo thread mounted by this instance: 0 <none> 线程序号
- Oracle process number: 23 Oracle内部进程号(LMD进程)
- Unix process pid: 38295, image: oracle@db1 (LMD0) 操作系统进程号及名称
- *** 2022-08-11T10:40:30.891234+08:00 trace文件生成日期
- *** CLIENT ID:() 2022-08-11T10:40:30.891259+08:00 客户端ID
- *** SERVICE NAME:() 2022-08-11T10:40:30.891263+08:00 服务名
- *** MODULE NAME:() 2022-08-11T10:40:30.891267+08:00 模块
- *** ACTION NAME:() 2022-08-11T10:40:30.891270+08:00 行为
- *** CLIENT DRIVER:() 2022-08-11T10:40:30.891273+08:00 客户端驱动
DLM Resource Hashmasks Initialised
*** 2022-08-11 10:40:30.891298 [krsa.c:2949]
Acquiring krso process latch [krso.c:553] IX0
*** 2022-08-11 10:40:30.891313 [krsa.c:2972]
Successfully acquired krso process latch IX+
*** 2022-08-24T10:10:15.039249+08:00 其中一个死锁的开始时间,通常会有多个类似的
Global blockers dump start:---------------------------------
2022-08-24 10:10:15.038*:kjdggblkrdmp(): DUMP LOCAL BLOCKER/HOLDER: block level 5 res [0xa001e][0x19f9e],[TX][ext 0x0,0x0][domid 0x0]
输出本地阻塞者/持有者信息
[0xa001e][0x19f9e]是事务ID,下面会看到事务详细内容
[TX]代表死锁类型,主要有几种:
TX block level 5 说明是独占事务锁
TX in Share 这种情况下应该会看到block level 3,共享事务锁
TM 全表锁 一般是缺少外键
IV Instance Validation 实例有效性 应该很少见,一般是BUG
LB Library Cache Lock 类库缓存,应该很少见,与外部表统计信息BUG相关
----------resource 0x1bc5160020----------------------
resname : [0xa001e][0x19f9e],[TX][ext 0x0,0x0][domid 0x0]
lmdid : 0
rht group : 0
rht ptr : 0x1be5b8cc68
rht bucket idx: 14775
hp : 0x1c284685b0
domain ptr : 0x167fd71f00
hash mask : x7
Local inst : 1 本地实例号
dir_inst : 1
master_inst : 1
hv idx : 16 hv = hash value
hv last r.inc : 4
current inc : 4
hv status : 0
hv master inst: 2
open options : deadlock detection=Y, cached=N, varvblk=N, slock=N
Held mode : KJUSERNL 持有模型 KJ=kernel lock 内部锁模块, USER=用户, NL=NULL 没有锁,详见下面
Cvt mode : KJUSERNL 转换模型 Cvt=convert 转换
Next Cvt mode : KJUSERNL
msg_seq : 0x0
res_seq : 1 (0x1)
grant_bits : KJUSERNL KJUSEREX EX=Excluve 独占锁,详见下面
grant mode : KJUSERNL KJUSERCR KJUSERCW KJUSERPR KJUSERPW KJUSEREX 持有模型- KJUSERNL=Null 空锁 例如select语句
- KJUSERCR=Row-S (SS) 行级共享锁 只能查Select for update、Lock for update、Lock row share
- KJUSERCW=Row-X (SX) 行级独占锁 在提交前不允许做DML操作Insert、Update、Delete、Lock row share
KJUSERPR=Share 共享锁 Create index、Lock share
KJUSERPW=S/Row-X (SSX) 共享行级排他锁 Lock share row exclusive
KJUSEREX=Exclusive 独占锁 Alter table、Drop table、Drop index、Truncate table、Lock exclusive - count : 5 0 0 0 0 1 持有模型计数,空锁5个,独占锁1个
val_state : KJUSERVS_NOVALUE
valblk : 0xd88a6da71b0000001205020000000000 .m
access_inst : 1
vbreq_state : 0
state : x0
resp : 0x1bc5160020
entry : DIR=Y, MASTER=Y
On Scan_q? : N
On Cache? : N
On Remote_q? : Y
frozen : 0
Total accesses: 35
Imm. accesses: 32
Granted_locks : 1
Cvting_locks : 5
Reqing_locks : none
value_block: d8 8a 6d a7 1b 00 00 00 12 05 02 00 00 00 00 00
GRANTED_Q: 持有序列
lp 0x1c467ceca0 gl KJUSEREX rp 0x1bc5160020 [0xa001e][0x19f9e],[TX][ext 0x0,0x0][domid 0x0]
master 1 gl owner 0x1c623b8080 possible pid 51602 xid CF000-0001-7FFD0000020B rseq 1 mseq 0 bast none
history FREE > KJLALC > REF_RES > LOC_AST > CLOSE > FREE > KJLALC > REF_RES > LOC_AST
open opt KJUSERDEADLOCK flags 0x1 sec since mv2grQ 10
CONVERT_Q: 转换序列
lp 0x1c0794e7e0 gl KJUSERNL rl KJUSEREX rp 0x1bc5160020 [0xa001e][0x19f9e],[TX][ext 0x0,0x0][domid 0x0]
master 1 gl owner 0x1be1f12598 possible pid 50879 xid 147000-0001-0000000F rseq 1 mseq 0 bast none
history FREE > KJLALC > REF_RES > LOC_AST > CLOSE > FREE > KJLALC > REF_RES > GR2CVT
convert opt KJUSERGETVALUE flags 0x0
lp 0x1c2a083de0 gl KJUSERNL rl KJUSEREX rp 0x1bc5160020 [0xa001e][0x19f9e],[TX][ext 0x0,0x0][domid 0x0]
master 1 gl owner 0x1c6259c480 possible pid 50850 xid 12D000-0001-0000000C rseq 1 mseq 0 bast none
history FREE > KJLALC > REF_RES > LOC_AST > CLOSE > FREE > KJLALC > REF_RES > GR2CVT
convert opt KJUSERGETVALUE flags 0x0
lp 0x173b013c08 gl KJUSERNL rl KJUSEREX rp 0x1bc5160020 [0xa001e][0x19f9e],[TX][ext 0x0,0x0][domid 0x0]
master 1 owner 2 rseq 1 mseq 0x1 bast armed
history NONE > NONE > NONE > NONE > NONE > REF_RES > REM_AST > GR2CVT > MSGSENT
convert opt KJUSERGETVALUE flags 0x4
lp 0x1c29861908 gl KJUSERNL rl KJUSEREX rp 0x1bc5160020 [0xa001e][0x19f9e],[TX][ext 0x0,0x0][domid 0x0]
master 1 gl owner 0x1ba20acbd0 possible pid 33412 xid 106000-0001-0000013B rseq 1 mseq 0 bast none
history FREE > KJLALC > REF_RES > LOC_AST > CLOSE > FREE > KJLALC > REF_RES > GR2CVT
convert opt KJUSERGETVALUE flags 0x0
lp 0x1c669c4db0 gl KJUSERNL rl KJUSEREX rp 0x1bc5160020 [0xa001e][0x19f9e],[TX][ext 0x0,0x0][domid 0x0]
master 1 gl owner 0x1c61f25500 possible pid 33415 xid 14C000-0001-00000081 rseq 1 mseq 0 bast none
history FREE > KJLALC > REF_RES > LOC_AST > CLOSE > FREE > KJLALC > REF_RES > GR2CVT
convert opt KJUSERGETVALUE flags 0x0
----------enqueue 0x1c467ceca0------------------------
lock version : 10007
Owner inst : 1
grant_level : KJUSEREX
req_level : KJUSEREX
bast_level : KJUSERNL
notify_func : none
resp : 0x1bc5160020
procp : 0x1c64b5dd90
pid : 50879 进程ID,下面多次涉及此pid
proc version : 14
oprocp : (nil)
opid : 50879
group lock owner : 0x1c623b8080
possible pid : 51602 可能的阻塞进程ID
xid : CF000-0001-0000020B
dd_time : 0.0 secs
dd_count : 0
timeout : 0.0 secs
On_timer_q? : N
On_dd_q? : N
sec since mv2grQ : 10
lock_state : GRANTED 锁状态:已经持有TX
ast_flag : 0x0
flags : 0x1
Open Options : KJUSERDEADLOCK
Convert options : KJUSERNOQUEUE KJUSERNODEADLOCKWAIT
History : FREE > KJLALC > REF_RES > LOC_AST > CLOSE > FREE > KJLALC > REF_RES > LOC_AST
Msg_Seq : 0x0
res_seq : 1
valblk : 0xfc82de1cb3300febfc82f47df02ef514 .0}.
user session for deadlock lock 0x1c467ceca0
sid: 4467 ser: 12694 audsid: 62289 user: 105/scott
flags: (0x41) USR/- flags2: (0x40009) -/-/INC
flags_idl: (0x1) status: BSY/-/-/- kill: -/-/-/-
pid: 207 O/S info: user: grid, term: UNKNOWN, ospid: 51602
image: oracle@db1
client details:
O/S info: user: scott, term: unknown, ospid: 1234
machine: db1 program: JDBC Thin Client
application name: JDBC Thin Client, hash value=2546894660
current SQL: 当前SQL
update act_hist
SET stat = :1 ,
PROC_DEF_sn = :2 ,
ASSIGNEE_ = :3
where sn = :4
and stat = :5
2022-08-24 10:10:15.039*:kjdglblkrdmpint(): DUMP LOCAL BLOCKER: initiate state dump for DEADLOCK
possible owner[207.51602] on resource TX-000A001E-00019F9E-00000000-00000000
2022-08-24 10:10:15.039 :kjzddmp(): Submitting asynchronized dump request [1c]. summary=[ges process stack dump (kjdglblkrdm1)].
----------enqueue 0x1c0794e7e0------------------------
lock version : 32121
Owner inst : 1
grant_level : KJUSERNL
req_level : KJUSEREX
bast_level : KJUSERNL
notify_func : none
resp : 0x1bc5160020
procp : 0x1c64b5dd90
pid : 50879
proc version : 14
oprocp : (nil)
opid : 50879
group lock owner : 0x1be1f12598
possible pid : 50879
xid : 147000-0001-0000000F
dd_time : 10.0 secs 死锁时间,来自隐含参数_lm_dd_interval
dd_count : 1 死锁次数
timeout : 0.0 secs
On_timer_q? : N
On_dd_q? : Y
sec since mv2grQ : N/A
lock_state : OPENING CONVERTING
ast_flag : 0x0
flags : 0x0
Open Options : KJUSERDEADLOCK
Convert options : KJUSERGETVALUE
History : FREE > KJLALC > REF_RES > LOC_AST > CLOSE > FREE > KJLALC > REF_RES > GR2CVT
Msg_Seq : 0x0
res_seq : 1
valblk : 0xbb527488a57f00000180adfbfd7f0000 .Rt
user session for deadlock lock 0x1c0794e7e0
sid: 671 ser: 20082 audsid: 62167 user: 105/scott 用户信息
flags: (0x41) USR/- flags2: (0x40009) -/-/INC
flags_idl: (0x1) status: BSY/-/-/- kill: -/-/-/-
pid: 327 O/S info: user: grid, term: UNKNOWN, ospid: 50879
image: oracle@db1
client details:
O/S info: user: scott, term: unknown, ospid: 1234
machine: db2 program: JDBC Thin Client
application name: JDBC Thin Client, hash value=2546894660
current SQL: 当前SQL
update task_info_table
SET stat = :1 ,
ASSIGNEE_ = :2 ,
CLAIM_TIME_ = :3 ,
LAST_UPDATED_TIME_ = :4
where sn = :5
and stat = :6
2022-08-24 10:10:15.039*:kjdglblkrdmpint(): DUMP LOCAL BLOCKER: initiate state dump for DEADLOCK
possible owner[327.50879] on resource TX-000A001E-00019F9E-00000000-00000000
2022-08-24 10:10:15.039 :kjzddmp(): Submitting asynchronized dump request [1c]. summary=[ges process stack dump (kjdglblkrdm1)].
2022-08-24 10:10:15.039*:kjdggblkrdmp(): DUMP LOCAL BLOCKER/HOLDER: block level 5 res [0x90016][0x2df6],[TX][ext 0x0,0x0][domid 0x0]
----------resource 0x1799a4cc68----------------------
resname : [0x90016][0x2df6],[TX][ext 0x0,0x0][domid 0x0]
lmdid : 1
rht group : 0
rht ptr : 0x1bc4d61300
rht bucket idx: 13506
hp : 0x1be618c960
domain ptr : 0x167fd71f00
hash mask : x7
Local inst : 1
dir_inst : 1
master_inst : 1
hv idx : 96
hv last r.inc : 4
current inc : 4
hv status : 0
hv master inst: 2
open options : deadlock detection=Y, cached=N, varvblk=N, slock=N
Held mode : KJUSERNL
Cvt mode : KJUSERNL
Next Cvt mode : KJUSERNL
msg_seq : 0x0
res_seq : 3 (0x3)
grant_bits : KJUSERNL KJUSEREX
grant mode : KJUSERNL KJUSERCR KJUSERCW KJUSERPR KJUSERPW KJUSEREX
count : 12 0 0 0 0 1
val_state : KJUSERVS_NOVALUE
valblk : 0x00000000000000000100000000000000 .
access_inst : 1
vbreq_state : 0
state : x0
resp : 0x1799a4cc68
entry : DIR=Y, MASTER=Y
On Scan_q? : N
On Cache? : N
On Remote_q? : Y
frozen : 0
Total accesses: 63
Imm. accesses: 60
Granted_locks : 1
Cvting_locks : 12
Reqing_locks : none
value_block: 00 00 00 00 00 00 00 00 01 00 00 00 00 00 00 00
GRANTED_Q: 持有列表
lp 0x1c294e5600 gl KJUSEREX rp 0x1799a4cc68 [0x90016][0x2df6],[TX][ext 0x0,0x0][domid 0x0]
master 1 gl owner 0x1be1f12598 possible pid 50879 xid 147000-0001-7FFD0000000F rseq 3 mseq 0 bast none
history FREE > KJLALC > REF_RES > LOC_AST > CLOSE > FREE > KJLALC > REF_RES > LOC_AST
open opt KJUSERDEADLOCK flags 0x1 sec since mv2grQ 10
CONVERT_Q: 转换列表,有12条
lp 0x16bd727c50 gl KJUSERNL rl KJUSEREX rp 0x1799a4cc68 [0x90016][0x2df6],[TX][ext 0x0,0x0][domid 0x0]
master 1 owner 2 rseq 2 mseq 0x1 bast armed
history NONE > NONE > NONE > NONE > NONE > REF_RES > REM_AST > GR2CVT > MSGSENT
convert opt KJUSERGETVALUE flags 0x4
lp 0x1be76c3900 gl KJUSERNL rl KJUSEREX rp 0x1799a4cc68 [0x90016][0x2df6],[TX][ext 0x0,0x0][domid 0x0]
master 1 gl owner 0x1c623b8080 possible pid 51602 xid CF000-0001-0000020B rseq 3 mseq 0 bast none
history FREE > KJLALC > REF_RES > LOC_AST > CLOSE > FREE > KJLALC > REF_RES > GR2CVT
convert opt KJUSERGETVALUE flags 0x0
lp 0x1ba9512280 gl KJUSERNL rl KJUSEREX rp 0x1799a4cc68 [0x90016][0x2df6],[TX][ext 0x0,0x0][domid 0x0]
master 1 gl owner 0x1ba26408c0 possible pid 51606 xid E0000-0001-000000AB rseq 3 mseq 0 bast none
history FREE > KJLALC > REF_RES > LOC_AST > CLOSE > FREE > KJLALC > REF_RES > GR2CVT
convert opt KJUSERGETVALUE flags 0x0
lp 0x1ba8f432c8 gl KJUSERNL rl KJUSEREX rp 0x1799a4cc68 [0x90016][0x2df6],[TX][ext 0x0,0x0][domid 0x0]
master 1 gl owner 0x1ba1fc7158 possible pid 17602 xid 5F000-0001-00000169 rseq 3 mseq 0 bast none
history FREE > KJLALC > REF_RES > LOC_AST > CLOSE > FREE > KJLALC > REF_RES > GR2CVT
convert opt KJUSERGETVALUE flags 0x0
lp 0x1ba95125f8 gl KJUSERNL rl KJUSEREX rp 0x1799a4cc68 [0x90016][0x2df6],[TX][ext 0x0,0x0][domid 0x0]
master 1 gl owner 0x1ba2639108 possible pid 3861 xid 130000-0001-00000054 rseq 3 mseq 0 bast none
history FREE > KJLALC > REF_RES > LOC_AST > CLOSE > FREE > KJLALC > REF_RES > GR2CVT
convert opt KJUSERGETVALUE flags 0x0
lp 0x1bc5e27b30 gl KJUSERNL rl KJUSEREX rp 0x1799a4cc68 [0x90016][0x2df6],[TX][ext 0x0,0x0][domid 0x0]
master 1 gl owner 0x1c624aa280 possible pid 81797 xid 126000-0001-00000012 rseq 3 mseq 0 bast none
history FREE > KJLALC > REF_RES > LOC_AST > CLOSE > FREE > KJLALC > REF_RES > GR2CVT
convert opt KJUSERGETVALUE flags 0x0
lp 0x1c468f7f98 gl KJUSERNL rl KJUSEREX rp 0x1799a4cc68 [0x90016][0x2df6],[TX][ext 0x0,0x0][domid 0x0]
master 1 gl owner 0x1c02480568 possible pid 74897 xid 124000-0001-0000004F rseq 3 mseq 0 bast none
history FREE > KJLALC > REF_RES > LOC_AST > CLOSE > FREE > KJLALC > REF_RES > GR2CVT
convert opt KJUSERGETVALUE flags 0x0
lp 0x1bc5f51338 gl KJUSERNL rl KJUSEREX rp 0x1799a4cc68 [0x90016][0x2df6],[TX][ext 0x0,0x0][domid 0x0]
master 1 gl owner 0x1c21fcbed0 possible pid 33418 xid 154000-0001-00000088 rseq 3 mseq 0 bast none
history FREE > KJLALC > REF_RES > LOC_AST > CLOSE > FREE > KJLALC > REF_RES > GR2CVT
convert opt KJUSERGETVALUE flags 0x0
lp 0x1be721e678 gl KJUSERNL rl KJUSEREX rp 0x1799a4cc68 [0x90016][0x2df6],[TX][ext 0x0,0x0][domid 0x0]
master 1 gl owner 0x1ba219edd0 possible pid 33428 xid 15D000-0001-00000041 rseq 3 mseq 0 bast none
history FREE > KJLALC > REF_RES > LOC_AST > CLOSE > FREE > KJLALC > REF_RES > GR2CVT
convert opt KJUSERGETVALUE flags 0x0
lp 0x1be70f45e8 gl KJUSERNL rl KJUSEREX rp 0x1799a4cc68 [0x90016][0x2df6],[TX][ext 0x0,0x0][domid 0x0]
master 1 gl owner 0x1bc24fad60 possible pid 33525 xid 16E000-0001-0000001B rseq 3 mseq 0 bast none
history FREE > KJLALC > REF_RES > LOC_AST > CLOSE > FREE > KJLALC > REF_RES > GR2CVT
convert opt KJUSERGETVALUE flags 0x0
lp 0x1c29739048 gl KJUSERNL rl KJUSEREX rp 0x1799a4cc68 [0x90016][0x2df6],[TX][ext 0x0,0x0][domid 0x0]
master 1 gl owner 0x1c02390b50 possible pid 33523 xid 16D000-0001-00000016 rseq 3 mseq 0 bast none
history FREE > KJLALC > REF_RES > LOC_AST > CLOSE > FREE > KJLALC > REF_RES > GR2CVT
convert opt KJUSERGETVALUE flags 0x0
lp 0x1c0712cd40 gl KJUSERNL rl KJUSEREX rp 0x1799a4cc68 [0x90016][0x2df6],[TX][ext 0x0,0x0][domid 0x0]
master 1 gl owner 0x1be2589518 possible pid 33600 xid 178000-0001-00000025 rseq 3 mseq 0 bast none
history FREE > KJLALC > REF_RES > LOC_AST > CLOSE > FREE > KJLALC > REF_RES > GR2CVT
convert opt KJUSERGETVALUE flags 0x0
----------enqueue 0x1c294e5600------------------------
lock version : 195
Owner inst : 1
grant_level : KJUSEREX
req_level : KJUSEREX
bast_level : KJUSERNL
notify_func : none
resp : 0x1799a4cc68
procp : 0x1c64af1040
pid : 38344
proc version : 0
oprocp : (nil)
opid : 38344
group lock owner : 0x1be1f12598
possible pid : 50879
xid : 147000-0001-0000000F
dd_time : 0.0 secs
dd_count : 0
timeout : 0.0 secs
On_timer_q? : N
On_dd_q? : N
sec since mv2grQ : 10
lock_state : GRANTED
ast_flag : 0x0
flags : 0x1
Open Options : KJUSERDEADLOCK
Convert options : KJUSERNOQUEUE KJUSERNODEADLOCKWAIT
History : FREE > KJLALC > REF_RES > LOC_AST > CLOSE > FREE > KJLALC > REF_RES > LOC_AST
Msg_Seq : 0x0
res_seq : 3
valblk : 0x36d47f12000000000200000000000000 6
user session for deadlock lock 0x1c294e5600
sid: 671 ser: 20082 audsid: 62167 user: 105/scott
flags: (0x41) USR/- flags2: (0x40009) -/-/INC
flags_idl: (0x1) status: BSY/-/-/- kill: -/-/-/-
pid: 327 O/S info: user: grid, term: UNKNOWN, ospid: 50879
image: oracle@db1
client details:
O/S info: user: scott, term: unknown, ospid: 1234
machine: db2 program: JDBC Thin Client
application name: JDBC Thin Client, hash value=2546894660
current SQL:
update task_info_table
SET stat = :1 ,
ASSIGNEE_ = :2 ,
CLAIM_TIME_ = :3 ,
LAST_UPDATED_TIME_ = :4
where sn = :5
and stat = :6
2022-08-24 10:10:15.039*:kjdglblkrdmpint(): DUMP LOCAL BLOCKER: initiate state dump for DEADLOCK
possible owner[327.50879] on resource TX-00090016-00002DF6-00000000-00000000
2022-08-24 10:10:15.040 :kjzddmp(): Submitting asynchronized dump request [1c]. summary=[ges process stack dump (kjdglblkrdm1)].
Local hanganalyze find 1 local blockers blocking this session (pid=50879 sid=671).
Dumping blocker#0 (pid=51602 sid=4467)
2022-08-24 10:10:15.040*:kjdglblkrdmpint(): DUMP LOCAL BLOCKER: initiate state dump for DEADLOCK
possible owner[207.51602] on resource TX-00090016-00002DF6-00000000-00000000
2022-08-24 10:10:15.040 :kjzddmp(): Submitting asynchronized dump request [1c]. summary=[ges process stack dump (kjdglblkrdm1)].
----------enqueue 0x16bd727c50------------------------
lock version : 1
Owner inst : 2
grant_level : KJUSERNL
req_level : KJUSEREX
bast_level : KJUSERNL
notify_func : armed
resp : 0x1799a4cc68
procp : (nil)
pid : 0
proc version : 0
oprocp : (nil)
opid : 0
group lock owner : (nil)
xid : 0000-0000-00000000
dd_time : 0.0 secs
dd_count : 0
timeout : 0.0 secs
On_timer_q? : N
On_dd_q? : N
sec since mv2grQ : N/A
lock_state : GRANTED
ast_flag : 0x4
flags : 0x4
Open Options : KJUSERNO_XID
Convert options : KJUSERGETVALUE
History : NONE > NONE > NONE > NONE > NONE > REF_RES > REM_AST > GR2CVT > MSGSENT
Msg_Seq : 0x1
res_seq : 2
valblk : 0xbb527488a57f00000180adfbfd7f0000 .Rt
Global blockers dump end:-----------------------------------
========================================================================
Global Wait-For-Graph(WFG) for GES Deadlock ID=[4_0_1] 全局等待图
------------------------------------------------------------------------
Victim : (instance=1, lock=0x1c0794e7e0) 受害者来自实例1
Start (master) Instance : 1 发起位置:实例1
Number of Locks involved : 4 涉及的锁4个
Number of Sessions involved : 2 涉及的会话2个
User session identified by: 用户会话信息
{
User Name : scott 用户名
User Machine : db2 主机名
OS Terminal Name : unknown 终端名
OS Process ID : 12349 操作系统进程号
OS Program Name : JDBC Thin Client1 程序
Application Name : JDBC Thin Client 应用
Action Name : Flush KSXM hash table actionask actionave Flush 行为
Current SQL : 当前SQL(最重要的信息!)
update task_info_table
SET stat = :1 ,
ASSIGNEE_ = :2 ,
CLAIM_TIME_ = :3 ,
LAST_UPDATED_TIME_ = :4
where sn = :5 and stat = :6
Session Number : 671 会话ID
Session Serial Number : 20082 会话序号
Server Process ORAPID : 327 oracle进程号
Server Process OSPID : 50879 操作系统进程号
Instance : 1 会话所在实例号
}
waiting for Lock 0x1c0794e7e0 (Transaction):等待锁定的对象
{
Lock Level : KJUSEREX
Resource Name : TX 0xa001e.0x19f9e(ext 0x0,0x0) 资源名(事务id)
GES Transaction ID : 147000-0001-0000000F 全局事务ID
}
which is blocked by Lock 0x1c467ceca0 (Transaction): 上述对象被哪个事务锁住
{
Lock Level : KJUSEREX
Resource Name : TX 0xa001e.0x19f9e(ext 0x0,0x0)
GES Transaction ID : CF000-0001-0000020B
}
owned by the
User session identified by: 持有者信息
{
User Name : scott 持有者用户
User Machine : db1 持有者主机
OS Terminal Name : unknown
OS Process ID : 12342
OS Program Name : JDBC Thin Client1
Application Name : JDBC Thin Client(TNS V1-V3)V3)
Action Name : 0000014 FINISHED129o next slotesactionave
Current SQL :
update act_hist
SET stat = :1 ,
PROC_DEF_sn = :2 ,
ASSIGNEE_ = :3
where sn = :4 and stat = :5
Session Number : 4467 持有者会话ID
Session Serial Number : 12694 持有者会话序号
Server Process ORAPID : 207 持有者oracle进程号
Server Process OSPID : 51602 持有者操作系统进程号
Instance : 1 持有者进程所在实例号
}
waiting for Lock 0x1be76c3900 (Transaction):
{
Lock Level : KJUSEREX
Resource Name : TX 0x90016.0x2df6(ext 0x0,0x0)
GES Transaction ID : CF000-0001-0000020B
}
which is blocked by Lock 0x1c294e5600 (Transaction):
{
Lock Level : KJUSEREX
Resource Name : TX 0x90016.0x2df6(ext 0x0,0x0)
GES Transaction ID : 147000-0001-0000000F
}
owned by the first user session of the WFG.
------------------------------------------------------------------------
End of Global WFG for GES Deadlock ID=[4_0_1]
========================================================================
快捷分析定位方法:在LMD的trc中直接搜WFG
检查锁状态:
- SELECT dl.inst_id,
- s.sid,
- p.spid,
- dl.resource_name1,
- decode(substr(dl.grant_level, 1, 8),
- 'KJUSERNL','Null','KJUSERCR','Row-S (SS)',
- 'KJUSERCW','Row-X (SX)',
- 'KJUSERPR','Share',
- 'KJUSERPW','S/Row-X (SSX)',
- 'KJUSEREX','Exclusive',
- request_level) AS grant_level,
- decode(substr(dl.request_level,1,8),
- 'KJUSERNL','Null',
- 'KJUSERCR','Row-S (SS)',
- 'KJUSERCW','Row-X (SX)',
- 'KJUSERPR','Share',
- 'KJUSERPW','S/Row-X (SSX)',
- 'KJUSEREX','Exclusive',request_level) AS request_level,
- decode(substr(dl.state,1,8),'KJUSERGR','Granted','KJUSEROP','Opening',
- 'KJUSERCA','Canceling','KJUSERCV','Converting') AS state,
- s.sid, sw.event, sw.seconds_in_wait sec
- FROM gv$ges_enqueue dl, gv$process p, gv$session s, gv$session_wait sw
- WHERE blocker = 1
- AND (dl.inst_id = p.inst_id
- AND dl.pid = p.spid)
- AND (p.inst_id = s.inst_id
- AND p.addr = s.paddr)
- AND (s.inst_id = sw.inst_id
- AND s.sid = sw.sid)
- ORDER BY sw.seconds_in_wait desc;
lock table命令(很少用)
模式说明
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· DeepSeek “源神”启动!「GitHub 热点速览」
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· C# 集成 DeepSeek 模型实现 AI 私有化(本地部署与 API 调用教程)
· DeepSeek R1 简明指南:架构、训练、本地部署及硬件要求
· 2 本地部署DeepSeek模型构建本地知识库+联网搜索详细步骤