会话执行存储过程，等待被阻塞，Kill session场景模拟
测试表结构、insert存储过程A、update存储过程B如下所示： #会话一：sys用户调用存储B，更新id=1,name='one' =>查询：被锁定的对象及锁定次数 #本次实验有个遗憾： 小结:
发布日期：2021-05-09 05:10:04
浏览次数：21
分类：技术文章
本文共 8658 字，大约阅读时间需要 28 分钟。
本次场景来源：
通知某个会话：执行execute addupp(1,'five');类似的存储过程，会话等待：（会话等待两种情况：一种确实执行，但是未完成；另一种就是执行的操作无法获取资源，等待资源释放，即锁等待），本次测试锁：行锁等待测试：
本次场景测试：如何快速定位持有锁的会话，kill 释放资源
本次场景操作：
存储过程：执行Update操作，行锁等待，多个会话执行造成：行锁争用，会话被堵塞
模拟环境：行锁如何模拟：争用并发及锁，Lock 有序列，排队
#场景模拟在线： 用户scott，测试表ceshi #模拟会话一：调用存储A，插入3条测试数据 #模拟会话一：调用存储B，更新一条数据 SYS #模拟会话二：调用储存B，更新（会话一）上条已经更新未提交的数据行，造成阻塞Yang #模拟会话三：直接Update更新相同的数据行 cc用户 #模拟会话五、业务操作:更新数据发现阻塞：scott用户
SQL> desc ceshi Name Null? Type ---------------------------------- ID NUMBER(38) NAME VARCHAR2(10) #SYS用户创建存储过程：#存储A create or replace procedure addnew (n_id NUMBER, n_name VARCHAR2 ) as begin insert into scott.ceshi(id,name) values(n_id,n_name); commit; end addnew; /#存储B create or replace procedure addupp (u_id NUMBER, u_name VARCHAR2 ) as begin update scott.ceshi set name='u_name' where id=u_id; end addupp; /
#创建完成查询：
SQL> select OBJECT_NAME,OBJECT_TYPE,STATUS from user_objects where object_type='PROCEDURE' and OBJECT_NAME like 'ADD%';
OBJECT_NAME OBJECT_TYPE STATUS
------------------------------------
ADDNEW PROCEDURE VALID
ADDUPP PROCEDURE VALID
#测试场景权限、数据处理：
SYS用户调用存储A：Insert 3条数据 SQL> execute addnew(1,'yang'); SQL> execute addnew(2,'cheng'); SQL> execute addnew(3,'hehe');#授予测试yang / cc 两个用户对测试表的对象权限： SQL> grant select on scott.ceshi to yang; SQL> grant update on ceshi to yang; SQL> grant select on scott.ceshi to cc; SQL> grant update on scott.ceshi to cc; 存储过程授权： grant execute on addupp to yang; grant execute on addupp to cc;
SQL> show user USER is "SYS" SQL> execute addupp(1,'one');SQL> select * from scott.ceshi; ID NAME ---------- 1 one 2 cheng 3 hehe
#会话二：yang用户调用存储B，更新id=1,name='two'
SQL> show user USER is "YANG" SQL> execute addupp(1,'two'); --等待
#会话三：cc用户调用存储B，更新id=1,name='three'
SQL> show user USER is "CC" SQL> update scott.ceshi set name='three' where id=1;
#会话四：Scott用户调用存储B，更新id=1,name='five'
SQL> show user USER is "SCOTT" SQL> execute addupp(1,'five');
##################以上是场景模拟，在线还原#################################
#######################################以下是解决问题######################我们很可能收到的通知是：执行操作发生等待，无法执行后，上报给我们xxx无法执行，上锁了
####SQL> execute addupp(1,'five'); @无法执行
处理思路：=>等待、无法执行原因：锁
=>什么对象
=>哪些会话在等待，持有这些资源
=>查询会话ID,SERIAL，Kill杀死会话，释放资源，让业务能快速恢复
=>查询对象类型：
SQL> select OWNER,OBJECT_NAME,OBJECT_TYPE from dba_objects where owner='SCOTT' and object_name='ADDUPP';OWNER OBJECT_NAME OBJECT_TYPE--------------------------------------------------------------------------------SCOTT ADDUPP PROCEDURE
视图V$DB_OBJECT_CACHE： 找到被锁定的对象
#通过此视图：可以找出某个用户下：被锁定的对象，及锁定次数 col name for a10 col type for a20 col locks for 999 col locked_total 9999 select name,type,locks,locked_total from v$db_object_cache where owner='SCOTT' and locks is not null;NAME TYPE LOCKS LOCKED_TOTAL---------- -------------------- ----- ------------CESHI TABLE 0 60DBMS_OUTPUT CURSOR 0 17ADDUPP PROCEDURE 1 2SCOTT PUB SUB INTERNAL INF 1 3 #根据应用反馈的命令 or 用户对象，本次关注点：是ADDUPP 调用程序，无法执行
#通过被锁定的对象：找到被锁定的SID
视图：V$ACCESS #显示当前施加在库高速缓存对象上的锁定信息： SID 访问对象的会话编号 OWNER 对象拥有者 OBJECT 对象名称 TYPE 对象类型 #通过此视图：找到那些用户对此对象进行了锁定 select * from v$access;select * from v$access where type='PROCEDURE' and object='ADDUPP'; SID OWNER OBJECT TYPE---------- ---------------------------------------------------------------- 40 SCOTT ADDUPP PROCEDURE 42 YANG ADDUPP PROCEDURE
#####注意：RAC环境下：需要注意Inst_id 实例序号：
#通过SID找到会话serial#
#v$session 视图： select sid,serial#,username,TIME_SINCE_LAST_WAIT_MICRO,BLOCKING_SESSION_STATUS from v$session where sid in (40,42); SID SERIAL# USERNAME TIME_SINCE_LAST_WAIT_MICRO BLOCKING_SE---------- ---------- ------------------------------ ---------------- 40 275 SCOTT 0 VALID 42 897 YANG 0 VALID #SID 会话标识符 #SERIAL# 会话标识符，可重复使用，为了避免重复：会话序列号标识： sid+ serial#= 唯一会话信息 #USERNAME 会话登录用户名 #TIME_SINCE_LAST_WAIT_MICRO 会话最后一次执行操作--距离现在多少秒，会话空闲时间 #BLOCKING_SESSION_STATS 会话是否被阻塞：被阻塞则状态为valid
#此时可以使用如下命令：立即杀死被锁定的会话：
# alter system kill session 'sid,serial#' immediate;
#但是对于本次实验测试来说，锁还是存在的，SYS用户是源头，还未查询得到信息：
#继续查询分析：如何找到锁的源头：
#当：BLOCKING_SESSION_STATUS的状态为vaild代表会话被阻塞： #找到阻塞的源头会话： SQL> select sid,serial#,username,blocking_session_status,BLOCKING_INSTANCE, BLOCKING_SESSION,FINAL_BLOCKING_SESSION_STATUS,FINAL_BLOCKING_INSTANCE, FINAL_BLOCKING_SESSION from v$session where sid in (40,42); ----------------- ---------------- ----------- ----------------------- SID 40 42 会话ID SERIAL# 275 897 会话序列号 USERNAME SCOTT YANG 会话用户名 blocking_session_status VALID VALID 会话是否被阻塞：VALID有阻塞 no holder没有阻塞 BLOCKING_INSTANCE 1 1 阻塞会话的实例号 BLOCKING_SESSION 51 51 阻塞会话ID FINAL_BLOCKING_SESSION_STATUS VALID VALID 有一个阻塞源头会话： no holder没有会话阻塞 FINAL_BLOCKING_INSTANCE 1 1 阻塞源头会话实例IDFINAL_BLOCKING_SESSION 51 51 阻塞源头ID 51 #yang用户在等待会话ID 51释放行锁：#51用户的信息：SQL> select sid,serial#,username from v$session where sid=51; SID SERIAL# USERNAME---------- ---------- ------------------------------ 51 3135 SYS
三个update都是使用存储过程调用执行，
其中两个会话通过V$ACCESS堵塞对象进行查询，
另一个会话通过v$session阻塞源头会话查询得到
但是？ 还有一个会话cc 使用的是update直接操作执行，本次未分析得到：
##########如何快速，链路分析锁等待#######
使用工具： oradebug
有锁，使用Oradebug 工具生成trace文件进行分析：
#操作 SQL> oradebug hanganalyze 3 Hang Analysis in /picclife/app/oracle/diag/rdbms/aa/dingding/trace/dingding_ora_6118.trc
分析trace文件：
# 处理 攻击：命令：挂起分析级别3Processing Oradebug command 'hanganalyze 3'*** 2018-04-08 12:03:34.037===============================================================================HANG ANALYSIS: 挂起分析： instances (db_name.oracle_sid): aa.dingding 数据库.实例名称 oradebug_node_dump_level: 3 analysis initiated by oradebug 分析：每秒采样一次
#最有可能引起的 Hang链路Chains most likely to have caused the hang:# 来自客户端sql*net消息，行锁 [a] Chain 1 Signature: 'SQL*Net message from client'<='enq: TX - row lock contention' Chain 1 Signature Hash: 0x38c48850 [b] Chain 2 Signature: 'SQL*Net message from client'<='enq: TX - row lock contention' Chain 2 Signature Hash: 0x38c48850 [c] Chain 3 Signature: 'SQL*Net message from client'<='enq: TX - row lock contention' Chain 3 Signature Hash: 0x38c48850
三条链路：行锁：
第一条链路：
Chain 1: 链路1 session id: 40 会话ID session serial #: 275 会话序列号 #######通过如上信息查询：为SCOTT用户，等待用户######### SQL> select username from v$session where sid=40 and serial#=275;USERNAME------------------------------SCOTT is waiting for 'enq: TX - row lock contention' with wait info: time in wait: 50 min 58 sec ###等待时间 50分钟 58秒 要执行的SQL： update current sql: UPDATE CESHI SET NAME='u_name' WHERE ID=:B1
and is blocked by 阻塞用户（罪魁祸首）： session id: 51 阻塞用户ID session serial #: 3135 阻塞用户序列号 ：如下等待客户端返回信息
####链1签名：“来自客户机的SQL*Net消息”=“Enq:TX-行锁定争用”#####Chain 1 Signature: 'SQL*Net message from client'<='enq: TX - row lock contention'
第二条链路：
Chain 2: session id: 35 会话35： session serial #: 583 SERIAL# 583
######经过查询：此等待用户是CC用户SQL> select username from v$session where sid=35 and serial#=583;USERNAME------------------------------CC
###也是Udate信息 ：是直接执行，不是调用程序 current sql: update scott.ceshi set name='three' where id=1 # 本会话是链路一的成员
# 被实例一，会话ID 51,SESSION阻塞， and is blocked by 'instance: 1, os id: 5854, session id: 51', which is a member of 'Chain 1'.
第三条链路：
Chain 3: session id: 42 session serial #: 897 #######是用户yang，等待会话ID51释放锁 SQL> select username from v$session where sid=42 and serial#=897;USERNAME------------------------------YANG current sql: UPDATE SCOTT.CESHI SET NAME='u_name' WHERE ID=:B1 #同样被会话id 51堵塞，是链路一的成员 and is blocked by 'instance: 1, os id: 5854, session id: 51', which is a member of 'Chain 1'. Chain 3 Signature: 'SQL*Net message from client'<='enq: TX - row lock contention'
链路一、scott用户：调用存储过程更新SCOTT.CESHI：被会话51堵塞，链路一
链路二、CC用户：update操作SCOTT.CESHI：被会话51堵塞，链路一成员
链路三、YANG用户：调用存储过程更新SCOTT.CESHI：被会话51堵塞，链路一
#SID 51用户的信息：SQL> select sid,serial#,username from v$session where sid=51; SID SERIAL# USERNAME---------- ---------- ------------------------------ 51 3135 SYS
#找到链路上堵塞源头：+堵塞会话，剩下就是考虑杀谁，放谁了
发表评论
最新留言
哈哈，博客排版真的漂亮呢~
[***.90.31.176]2025年04月29日 18时49分26秒
关于作者
喝酒易醉，品茶养心，人生如梦，品茶悟道，何以解忧？唯有杜康！
-- 愿君每日到此一游！
推荐文章
$scope angular在controller之外调用
2023-01-25
&和&&的区别
2023-01-25
(ios实战)单个ViewControl适配不同ios版本xib文件实现
2023-01-25
(type interface {}) to type string
2023-01-25
(五)java多线程之Lock类
2023-01-25
asp.net MVC 强类型视图表单Ajax提交的注意事项
2023-01-25
canvas设置文字阴影
2023-01-26
Centos 5.3 ADSL拨号组建中小型企业网络
2023-01-26
Centos 5.8 安装后不能使用ifconfig
2023-01-26
Centos 6 & 7 LVM 逻辑盘卷管理
2023-01-26
CentOS 6 时间，时区，设置修改及时间同步
2023-01-26
Centos 6.3 64bit安装KVM总结
2023-01-26
CentOS 6.4 yum安装chrome
2023-01-26
CentOS 6.4下编译安装MySQL 5.6.14
2023-01-26
Centos 6.4搭建网络源
2023-01-26
CentOS 6.5 Zabbix-server安装
2023-01-26
CentOS 6.5 伪分布式 安装 hadoop 2.6.0
2023-01-26
CentOS 6.5 配置IP地址的三种方法
2023-01-26
白红宇的个人博客 - 记录点点滴滴的事 - 您是第 465719768 位访客