简单定位Oracle数据库中锁会话的源头会话

ora_ckpt_Lu

548人浏览 · 2025-05-08 09:41:39

ora_ckpt_Lu · 2025-05-08 09:41:39 发布

一、模拟环境：

时间1，会话144执行update操作但不要提交：

SQL> update emp set sal=8000 where empno=7788;

1 row updated.

SQL> select * from v$mystat where rownum=1;

       SID STATISTIC#	   VALUE
---------- ---------- ----------
       144	    0	       0

时间2，会话142执行两个DML操作，执行update的时候会卡住，顺便打一个rollback

SQL> select * from v$mystat where rownum=1;

       SID STATISTIC#	   VALUE
---------- ---------- ----------
       142	    0	       0

SQL> 
SQL> 
SQL> delete from emp where empno=7839;

1 row deleted.

SQL> update emp set job='MANAGER' where empno=7788;
rollback;

时间3，会话18执行update操作，卡住，顺便打一个rollback

SQL> select * from v$mystat where rownum=1;

       SID STATISTIC#	   VALUE
---------- ---------- ----------
	18	    0	       0

SQL> update emp set sal=1500 where empno=7839;
rollback;

时间4，会话140执行update操作，卡住

SQL> select * from v$mystat where rownum=1;

       SID STATISTIC#	   VALUE
---------- ---------- ----------
       140	    0	       0

SQL> update emp set job='CEO' where empno=7839;
rollback;

方法一：

这个时候使用如下脚本抓住会话之间的阻塞关系：

col event for a30
col process for a5
col tree for a35
col program for a25
col machine for a7
set lines 1800 pages 1800

select a.inst_id,
       a.process,
       a.sid,
       a.serial#,
       a.sql_id,
       a.event,
       a.status,
       a.program,
       a.machine,
       connect_by_isleaf as isleaf,
       sys_connect_by_path(a.SID || '@' || a.inst_id, ' <- ') tree,
       level as tree_level
  from gv$session a
 start with a.blocking_session is not null
connect by (a.sid || '@' || a.inst_id) = prior
           (a.blocking_session || '@' || a.blocking_instance)
           order by tree_level desc ;

可以看到tree_level是3的基本上都指向inst_id为1 的144会话

把这个会话干掉，阻塞链条就全解开了

方法二：

使用oradebug降数据库阻塞信息打印出来：

oradebug -g all hanganalyze 3

[oracle@centos7 trace]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Apr 16 16:49:30 2025

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> oradebug setmypid;    
Statement processed.
SQL> oradebug tracefile_name;
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_3808.trc
SQL> oradebug -g all hanganalyze 3 --集群环境下使用
ORA-32730: Command cannot be executed on remote instance
SQL> oradebug hanganalyze 3
Hang Analysis in /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_3808.trc
SQL>

查看hanganalyze的内容如下：

*** 2025-04-16 16:50:38.501
Processing Oradebug command 'hanganalyze 3'

*** 2025-04-16 16:50:38.674
===============================================================================
HANG ANALYSIS:
  instances (db_name.oracle_sid): orcl.orcl
  oradebug_node_dump_level: 3
  analysis initiated by oradebug
  os thread scheduling delay history: (sampling every 1.000000 secs)
    0.000000 secs at [ 16:50:38 ]
      NOTE: scheduling delay has not been sampled for 0.180253 secs    0.000000 secs from [ 16:50:34 - 16:50:39 ], 5 sec avg
    0.000000 secs from [ 16:49:39 - 16:50:39 ], 1 min avg
    0.000116 secs from [ 16:45:39 - 16:50:39 ], 5 min avg
  vktm time drift history
===============================================================================
 
Chains most likely to have caused the hang:
 [a] Chain 1 Signature: 'SQL*Net message from client'<='enq: TX - row lock contention'<='enq: TX - row lock contention'
     Chain 1 Signature Hash: 0x42598823
 [b] Chain 2 Signature: 'SQL*Net message from client'<='enq: TX - row lock contention'<='enq: TX - row lock contention'
     Chain 2 Signature Hash: 0x42598823
 
===============================================================================
Non-intersecting chains:
 
-------------------------------------------------------------------------------
Chain 1:
-------------------------------------------------------------------------------
    Oracle session identified by:
    {
                instance: 1 (orcl.orcl)
                   os id: 2194
              process id: 29, oracle@centos7 (TNS V1-V3)
              session id: 140
        session serial #: 31
    }
    is waiting for 'enq: TX - row lock contention' with wait info:
    {
                      p1: 'name|mode'=0x54580006
                      p2: 'usn<<16 | slot'=0x70015
                      p3: 'sequence'=0x753
            time in wait: 1 min 18 sec
           timeout after: never
                 wait id: 36
                blocking: 0 sessions
             current sql: update emp set job='CEO' where empno=7839
             short stack: ksedsts()+465<-ksdxfstk()+32<-ksdxcb()+1927<-sspuser()+112<-__sighandler()<-semtimedop()+10<-skgpwwait()+160<-ksliwat()+2022<-kslwaitctx()+163<-ksqcm
i()+2848<-ksqgtlctx()+3501<-ksqgelctx()+557<-ktuGetTxForXid()+131<-ktcwit1()+336<-kdddgb()+8364<-kdusru()+461<-updrowFastPath()+1209<-qerupFetch()+2366<-updaul()+1321<-updThre
ePhaseExe()+318<-updexe()+418<-opiexe()+10378<-kpoal8()+2118<-opiodr()+917<-ttcpip()+2183<-opitsk()+1710<-opiino()+969<-opiodr()+917<-opidrv()+570<-sou2o()+103<-opimai_real()+
133<-ssthrdmain()+26
            wait history:
              * time between current wait and wait #1: 0.000218 sec
              1.       event: 'SQL*Net message from client'
                 time waited: 4 min 51 sec
                     wait id: 35              p1: 'driver id'=0x62657100
                                              p2: '#bytes'=0x1
              * time between wait #1 and #2: 0.000004 sec
              2.       event: 'SQL*Net message to client'
                 time waited: 0.000001 sec
                     wait id: 34              p1: 'driver id'=0x62657100
                                              p2: '#bytes'=0x1
              * time between wait #2 and #3: 0.000018 sec
              3.       event: 'log file sync'
                 time waited: 0.001804 sec
                     wait id: 33              p1: 'buffer#'=0xd12
                                              p2: 'sync scn'=0x2ab7e7
    }
    and is blocked by
 => Oracle session identified by:
    {
                instance: 1 (orcl.orcl)
                   os id: 1771
              process id: 27, oracle@centos7 (TNS V1-V3)
              session id: 142
        session serial #: 17
    }
    which is waiting for 'enq: TX - row lock contention' with wait info:
    {
                      p1: 'name|mode'=0x54580006
                      p2: 'usn<<16 | slot'=0x20014
                      p3: 'sequence'=0x84b
            time in wait: 1 min 43 sec
           timeout after: never
                 wait id: 58
                blocking: 2 sessions
             current sql: update emp set job='MANAGER' where empno=7788
             short stack: ksedsts()+465<-ksdxfstk()+32<-ksdxcb()+1927<-sspuser()+112<-__sighandler()<-semtimedop()+10<-skgpwwait()+160<-ksliwat()+2022<-kslwaitctx()+163<-ksqcm
i()+2848<-ksqgtlctx()+3501<-ksqgelctx()+557<-ktuGetTxForXid()+131<-ktcwit1()+336<-kdddgb()+8364<-kdusru()+461<-updrowFastPath()+1209<-qerupFetch()+2366<-updaul()+1321<-updThre
ePhaseExe()+318<-updexe()+418<-opiexe()+10378<-kpoal8()+2118<-opiodr()+917<-ttcpip()+2183<-opitsk()+1710<-opiino()+969<-opiodr()+917<-opidrv()+570<-sou2o()+103<-opimai_real()+
133<-ssthrdmain()+26
            wait history:
              * time between current wait and wait #1: 0.000182 sec
              1.       event: 'SQL*Net message from client'
                 time waited: 4.141404 sec
                     wait id: 57              p1: 'driver id'=0x62657100
                                              p2: '#bytes'=0x1
              * time between wait #1 and #2: 0.000006 sec
              2.       event: 'SQL*Net message to client'
                 time waited: 0.000003 sec
                     wait id: 56              p1: 'driver id'=0x62657100
                                              p2: '#bytes'=0x1
              * time between wait #2 and #3: 0.002762 sec
              3.       event: 'SQL*Net message from client'
                 time waited: 4 min 36 sec
                     wait id: 55              p1: 'driver id'=0x62657100
                                              p2: '#bytes'=0x1
    }
    and is blocked by
 => Oracle session identified by:
    {
                instance: 1 (orcl.orcl)
                   os id: 1588
              process id: 19, oracle@centos7 (TNS V1-V3)
              session id: 144
        session serial #: 39
    }
    which is waiting for 'SQL*Net message from client' with wait info:
    {
                      p1: 'driver id'=0x62657100
                      p2: '#bytes'=0x1
            time in wait: 1 min 54 sec
           timeout after: never
                 wait id: 103
                blocking: 3 sessions
             current sql: update emp set sal=8000 where empno=7788
             short stack: ksedsts()+465<-ksdxfstk()+32<-ksdxcb()+1927<-sspuser()+112<-__sighandler()<-__read()+12<-ntpfprd()+117<-nsbasic_brc()+376<-nsbrecv()+69<-nioqrc()+495
<-opikndf2()+978<-opitsk()+831<-opiino()+969<-opiodr()+917<-opidrv()+570<-sou2o()+103<-opimai_real()+133<-ssthrdmain()+265<-main()+201<-__libc_start_main()+235<-41D589495541F6
89
            wait history:
              * time between current wait and wait #1: 0.000005 sec
              1.       event: 'SQL*Net message to client'
                 time waited: 0.000001 sec
                     wait id: 102             p1: 'driver id'=0x62657100
                                              p2: '#bytes'=0x1
              * time between wait #1 and #2: 0.000066 sec
              2.       event: 'Disk file operations I/O'
                 time waited: 0.000035 sec
                     wait id: 101             p1: 'FileOperation'=0x2
                                              p2: 'fileno'=0x3
                                              p3: 'filetype'=0x2
              * time between wait #2 and #3: 0.005585 sec
              3.       event: 'SQL*Net message from client'
                 time waited: 4 min 48 sec
                     wait id: 100             p1: 'driver id'=0x62657100
                                              p2: '#bytes'=0x1
    }
 
Chain 1 Signature: 'SQL*Net message from client'<='enq: TX - row lock contention'<='enq: TX - row lock contention'
Chain 1 Signature Hash: 0x42598823
-------------------------------------------------------------------------------
 
===============================================================================
Intersecting chains:
 
-------------------------------------------------------------------------------
Chain 2:
-------------------------------------------------------------------------------
    Oracle session identified by:
    {
                instance: 1 (orcl.orcl)
                   os id: 2022
              process id: 28, oracle@centos7 (TNS V1-V3)
              session id: 18
        session serial #: 9
    }
    is waiting for 'enq: TX - row lock contention' with wait info:
    {
                      p1: 'name|mode'=0x54580006
                      p2: 'usn<<16 | slot'=0x70015
                      p3: 'sequence'=0x753
            time in wait: 1 min 33 sec
           timeout after: never
                 wait id: 35
                blocking: 0 sessions
             current sql: update emp set sal=1500 where empno=7839
             short stack: ksedsts()+465<-ksdxfstk()+32<-ksdxcb()+1927<-sspuser()+112<-__sighandler()<-semtimedop()+10<-skgpwwait()+160<-ksliwat()+2022<-kslwaitctx()+163<-ksqcm
i()+2848<-ksqgtlctx()+3501<-ksqgelctx()+557<-ktuGetTxForXid()+131<-ktcwit1()+336<-kdddgb()+8364<-kdusru()+461<-updrowFastPath()+1209<-qerupFetch()+2366<-updaul()+1321<-updThre
ePhaseExe()+318<-updexe()+418<-opiexe()+10378<-kpoal8()+2118<-opiodr()+917<-ttcpip()+2183<-opitsk()+1710<-opiino()+969<-opiodr()+917<-opidrv()+570<-sou2o()+103<-opimai_real()+
133<-ssthrdmain()+26
            wait history:
              * time between current wait and wait #1: 0.000246 sec
              1.       event: 'SQL*Net message from client'
                 time waited: 4 min 41 sec
                     wait id: 34              p1: 'driver id'=0x62657100
                                              p2: '#bytes'=0x1
              * time between wait #1 and #2: 0.000003 sec
              2.       event: 'SQL*Net message to client'
                 time waited: 0.000001 sec
                     wait id: 33              p1: 'driver id'=0x62657100
                                              p2: '#bytes'=0x1
              * time between wait #2 and #3: 0.000020 sec
              3.       event: 'log file sync'
                 time waited: 0.001873 sec
                     wait id: 32              p1: 'buffer#'=0xd11
                                              p2: 'sync scn'=0x2ab7e4
    }
    and is blocked by 'instance: 1, os id: 1771, session id: 142',
    which is a member of 'Chain 1'.
 
Chain 2 Signature: 'SQL*Net message from client'<='enq: TX - row lock contention'<='enq: TX - row lock contention'
Chain 2 Signature Hash: 0x42598823
-------------------------------------------------------------------------------
 
===============================================================================
Extra information that will be dumped at higher levels:
[level  4] :   1 node dumps -- [LEAF] [LEAF_NW] 
[level  5] :   3 node dumps -- [NO_WAIT] [INVOL_WT] [SINGLE_NODE] [NLEAF] [SINGLE_NODE_NW] 
 
State of ALL nodes
([nodenum]/cnode/sid/sess_srno/session/ospid/state/[adjlist]):
[17]/1/18/9/0x786a6f40/2022/NLEAF/[141]
[139]/1/140/31/0x7805ec60/2194/NLEAF/[141][17]
[141]/1/142/17/0x78058aa0/1771/NLEAF/[143]
[143]/1/144/39/0x780528e0/1588/LEAF/

*** 2025-04-16 16:50:38.675
===============================================================================
END OF HANG ANALYSIS
===============================================================================

*** 2025-04-16 16:50:38.675
===============================================================================
HANG ANALYSIS DUMPS:
  oradebug_node_dump_level: 3
===============================================================================
 
State of LOCAL nodes
([nodenum]/cnode/sid/sess_srno/session/ospid/state/[adjlist]):
[17]/1/18/9/0x786a6f40/2022/NLEAF/[141]
[139]/1/140/31/0x7805ec60/2194/NLEAF/[141][17]
[141]/1/142/17/0x78058aa0/1771/NLEAF/[143]
[143]/1/144/39/0x780528e0/1588/LEAF/
 
 
No processes qualify for dumping.
 
===============================================================================
HANG ANALYSIS DUMPS: END
===============================================================================

*** 2025-04-16 16:50:38.675
Oradebug command 'hanganalyze 3' console output: 
Hang Analysis in /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_3808.trc

发现有两条阻塞链条：

也是能够定位到144会话是锁的源头（LEAF），仔细推到锁的链条也可以看到最源头的会话：

方法三：

网上的一个比较好理解的SQL脚本：

set pages 1000
set lines 120
set heading off
column w_proc format a50 tru
column instance format a20 tru
column inst format a28 tru
column wait_event format a50 tru
column p1 format a16 tru
column p2 format a16 tru
column p3 format a15 tru
column Seconds format a50 tru
column sincelw format a50 tru
column blocker_proc format a50 tru
column fblocker_proc format a50 tru
column waiters format a50 tru
column chain_signature format a100 wra
column blocker_chain format a100 wra

SELECT * 
FROM (SELECT 'Current Process: '||osid W_PROC, 'SID '||i.instance_name INSTANCE, 
 'INST #: '||instance INST,'Blocking Process: '||decode(blocker_osid,null,'',blocker_osid)|| 
 ' from Instance '||blocker_instance BLOCKER_PROC,
 'Number of waiters: '||num_waiters waiters,
 'Final Blocking Process: '||decode(p.spid,null,'',
 p.spid)||' from Instance '||s.final_blocking_instance FBLOCKER_PROC, 
 'Program: '||p.program image,
 'Wait Event: ' ||wait_event_text wait_event, 'P1: '||wc.p1 p1, 'P2: '||wc.p2 p2, 'P3: '||wc.p3 p3,
 'Seconds in Wait: '||in_wait_secs Seconds, 'Seconds Since Last Wait: '||time_since_last_wait_secs sincelw,
 'Wait Chain: '||chain_id ||': '||chain_signature chain_signature,'Blocking Wait Chain: '||decode(blocker_chain_id,null,
 '',blocker_chain_id) blocker_chain
FROM v$wait_chains wc,
 gv$session s,
 gv$session bs,
 gv$instance i,
 gv$process p
WHERE wc.instance = i.instance_number (+)
 AND (wc.instance = s.inst_id (+) and wc.sid = s.sid (+)
 and wc.sess_serial# = s.serial# (+))
 AND (s.final_blocking_instance = bs.inst_id (+) and s.final_blocking_session = bs.sid (+))
 AND (bs.inst_id = p.inst_id (+) and bs.paddr = p.addr (+))
 AND ( num_waiters > 0
 OR ( blocker_osid IS NOT NULL
 AND in_wait_secs > 10 ) )
ORDER BY chain_id,
 num_waiters DESC)
WHERE ROWNUM < 101;

结果：

二、遗留问题

如何排查历史会话中锁的源头会话呢？

参考：

https://mp.weixin.qq.com/s/yYWUbBBperUD3xvA4qPDZA?poc_token=HNtz_2ejEgdCd6e_TiwGqjx6z-FEhKEnrMSeJvHu

天工造物局

北京人形旗下天工造物具身智能开源社区，聚焦具身天工与慧思开物两大平台

更多推荐

Handy 离线语音转写教程：免费开源，本地跑不连网

你肯定遇到过：语音输入 5 分钟，云转写服务要登录、要付费；或者出差没网，想口述一段会议纪要，发现全都没法用。Handy 离线语音转写工具就是为解决这个问题做的，声音全程不出你的电脑。## 🎯 这个工具是什么Handy 是一款免费、开源、跨平台（Windows / macOS / Linux）的离线语音转写应用。按住快捷键说话，松手后文字直接粘贴进任意输入框，全程不联网。核心能力就三条：

天工造物局

Supabase Edge Functions 编写与部署完整指南：Deno 运行时上的无服务器利器

Supabase Edge Functions 是基于 Deno 运行时编写、面向 Web 的无服务器函数服务，帮你把后端逻辑部署到全球边缘节点。作为 Postgres 开发平台的核心能力之一，它让你用 TypeScript 就能快速搭建低延迟的 API 端点、Webhook 接收器和 AI 集成服务，无需管理任何服务器。本文将从零带你完成一个 Edge Function 的创建、本地调试与全球部

天工造物局

celld从v0.1.0到v0.2.0为何不能滚动升级：两个破坏性变更详解

celld 是一个自托管、分布式的 Durable Objects 开源守护进程，它在你自己的机器上运行 Cloudflare Workers，每个对象对应一个独立的 SQLite 数据库，并持续复制到你自己拥有的 S3 兼容存储桶中。如果你正准备把 v0.1.0 的集群（fleet）升级到 v0.2.0，这篇文章必须先读：这次升级**不能滚动升级**，必须采用"先停光、再启动"的一次性发布方式。