文档课题：模拟oracle死锁并确认相关表以及行等信息.
数据库：oracle 11.2.0.4
应用场景：开发反馈某时间段出现死锁现象，dba需提供造成死锁的相关信息，如具体哪张表、哪条语句、哪行数据等信息.
1、模拟异常
1.1、建测试表
--创建测试表t1和t2.
SYS@orcl> conn scott/tiger;
Connected.
SCOTT@orcl> create table t1 (a int);
Table created.
SCOTT@orcl> create table t2 (a int);
Table created.
SCOTT@orcl> insert into t1 values (1);
1 row created.
SCOTT@orcl> insert into t2 values (2);
1 row created.
SCOTT@orcl> commit;
Commit complete.
1.2、更新数据
--在session1更新表t1，且不提交
SYS@orcl> select sid from v$mystat where rownum<2;
SID
----------
40
SYS@orcl> select sid,serial# from v$session where sid=40;
SID SERIAL#
---------- ----------
40 45
SYS@orcl> conn scott/tiger;
Connected.
SCOTT@orcl> update t1 set a=1000 where a=1;
1 row updated.
--在session2更新表t2，且不提交
SYS@orcl> select sid from v$mystat where rownum<2;
SID
----------
39
SYS@orcl> select sid,serial# from v$session where sid=30;
SID SERIAL#
---------- ----------
39 155
SYS@orcl> conn scott/tiger;
Connected.
SCOTT@orcl> update t2 set a=2000 where a=2;
1 row updated.
1.3、模拟死锁
--session1中更新表t2
SCOTT@orcl> update t2 set a=3000 where a=2;
说明：此时session1 hang住，因为在session2中对该条记录执行的update语句未做提交，该行存在行级锁.注意此处是“锁等待”，不是“死锁”.
--session2中更新表t1
SCOTT@orcl> update t1 set a=1500 where a=1;
说明：此时session2 hang住，发生“死锁”.且session1中出现如下告警
SCOTT@orcl> update t2 set a=3000 where a=2;
update t2 set a=3000 where a=2
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
2、相关查询
说明：出现以上异常，需要查询以下信息，以便对死锁的分析.
2.1、查具体sql_id，死锁的session_id和blocking_session
set linesize 190 pagesize 190
col machine for a30
col event for a30
select to_char(sample_time, 'yyyymmdd hh24:mi:ss') st,
inst_id,
session_id,
sql_id,
machine,
event,
blocking_inst_id,
blocking_session
from gv$active_session_history
where sample_time > to_date('20230419 10:59', 'yyyymmdd hh24:mi')
and sample_time < to_date('20230419 11:00', 'yyyymmdd hh24:mi')
order by st;
ST INST_ID SESSION_ID SQL_ID MACHINE EVENT BLOCKING_INST_ID BLOCKING_SESSION
----------------- ---------- ---------- ------------- -------------------- ------------------------------ ---------------- ----------------
20230419 10:59:01 1 3 leo-oracle-11g
20230419 10:59:02 1 3 leo-oracle-11g
20230419 10:59:06 1 40 836u2mpgy418s leo-oracle-11g enq: TX - row lock contention 1 39
20230419 10:59:07 1 40 836u2mpgy418s leo-oracle-11g enq: TX - row lock contention 1 39
20230419 10:59:08 1 40 836u2mpgy418s leo-oracle-11g enq: TX - row lock contention 1 39
20230419 10:59:09 1 40 836u2mpgy418s leo-oracle-11g enq: TX - row lock contention 1 39
20230419 10:59:10 1 40 836u2mpgy418s leo-oracle-11g enq: TX - row lock contention 1 39
20230419 10:59:11 1 40 836u2mpgy418s leo-oracle-11g enq: TX - row lock contention 1 39
20230419 10:59:12 1 40 836u2mpgy418s leo-oracle-11g enq: TX - row lock contention 1 39
20230419 10:59:13 1 40 836u2mpgy418s leo-oracle-11g enq: TX - row lock contention 1 39
20230419 10:59:14 1 40 836u2mpgy418s leo-oracle-11g enq: TX - row lock contention 1 39
ST INST_ID SESSION_ID SQL_ID MACHINE EVENT BLOCKING_INST_ID BLOCKING_SESSION
----------------- ---------- ---------- ------------- -------------------- ------------------------------ ---------------- ----------------
20230419 10:59:15 1 40 836u2mpgy418s leo-oracle-11g enq: TX - row lock contention 1 39
20230419 10:59:16 1 40 836u2mpgy418s leo-oracle-11g enq: TX - row lock contention 1 39
20230419 10:59:17 1 40 836u2mpgy418s leo-oracle-11g enq: TX - row lock contention 1 39
20230419 10:59:18 1 40 836u2mpgy418s leo-oracle-11g enq: TX - row lock contention 1 39
20230419 10:59:19 1 40 836u2mpgy418s leo-oracle-11g enq: TX - row lock contention 1 39
20230419 10:59:20 1 40 836u2mpgy418s leo-oracle-11g enq: TX - row lock contention 1 39
20230419 10:59:21 1 40 836u2mpgy418s leo-oracle-11g enq: TX - row lock contention 1 39
20230419 10:59:21 1 3 leo-oracle-11g
20230419 10:59:22 1 40 836u2mpgy418s leo-oracle-11g enq: TX - row lock contention 1 39
20230419 10:59:23 1 40 836u2mpgy418s leo-oracle-11g enq: TX - row lock contention 1 39
20230419 10:59:24 1 40 836u2mpgy418s leo-oracle-11g enq: TX - row lock contention 1 39
ST INST_ID SESSION_ID SQL_ID MACHINE EVENT BLOCKING_INST_ID BLOCKING_SESSION
----------------- ---------- ---------- ------------- -------------------- ------------------------------ ---------------- ----------------
20230419 10:59:25 1 40 836u2mpgy418s leo-oracle-11g enq: TX - row lock contention 1 39
20230419 10:59:26 1 39 49p1yagwszdk5 leo-oracle-11g enq: TX - row lock contention 1 40
20230419 10:59:26 1 40 836u2mpgy418s leo-oracle-11g enq: TX - row lock contention 1 39
20230419 10:59:27 1 39 49p1yagwszdk5 leo-oracle-11g enq: TX - row lock contention 1 40
20230419 10:59:28 1 39 49p1yagwszdk5 leo-oracle-11g enq: TX - row lock contention 1 40
20230419 10:59:29 1 39 49p1yagwszdk5 leo-oracle-11g enq: TX - row lock contention 1 40
20230419 10:59:30 1 39 49p1yagwszdk5 leo-oracle-11g enq: TX - row lock contention 1 40
20230419 10:59:31 1 39 49p1yagwszdk5 leo-oracle-11g enq: TX - row lock contention 1 40
20230419 10:59:32 1 39 49p1yagwszdk5 leo-oracle-11g enq: TX - row lock contention 1 40
20230419 10:59:33 1 39 49p1yagwszdk5 leo-oracle-11g enq: TX - row lock contention 1 40
20230419 10:59:34 1 39 49p1yagwszdk5 leo-oracle-11g enq: TX - row lock contention 1 40
ST INST_ID SESSION_ID SQL_ID MACHINE EVENT BLOCKING_INST_ID BLOCKING_SESSION
----------------- ---------- ---------- ------------- -------------------- ------------------------------ ---------------- ----------------
20230419 10:59:35 1 39 49p1yagwszdk5 leo-oracle-11g enq: TX - row lock contention 1 40
20230419 10:59:36 1 39 49p1yagwszdk5 leo-oracle-11g enq: TX - row lock contention 1 40
20230419 10:59:37 1 39 49p1yagwszdk5 leo-oracle-11g enq: TX - row lock contention 1 40
20230419 10:59:38 1 39 49p1yagwszdk5 leo-oracle-11g enq: TX - row lock contention 1 40
20230419 10:59:39 1 39 49p1yagwszdk5 leo-oracle-11g enq: TX - row lock contention 1 40
20230419 10:59:39 1 3 leo-oracle-11g
20230419 10:59:40 1 39 49p1yagwszdk5 leo-oracle-11g enq: TX - row lock contention 1 40
20230419 10:59:41 1 39 49p1yagwszdk5 leo-oracle-11g enq: TX - row lock contention 1 40
20230419 10:59:42 1 39 49p1yagwszdk5 leo-oracle-11g enq: TX - row lock contention 1 40
20230419 10:59:43 1 39 49p1yagwszdk5 leo-oracle-11g enq: TX - row lock contention 1 40
20230419 10:59:44 1 39 49p1yagwszdk5 leo-oracle-11g enq: TX - row lock contention 1 40
ST INST_ID SESSION_ID SQL_ID MACHINE EVENT BLOCKING_INST_ID BLOCKING_SESSION
----------------- ---------- ---------- ------------- -------------------- ------------------------------ ---------------- ----------------
20230419 10:59:45 1 39 49p1yagwszdk5 leo-oracle-11g enq: TX - row lock contention 1 40
20230419 10:59:46 1 39 49p1yagwszdk5 leo-oracle-11g enq: TX - row lock contention 1 40
20230419 10:59:47 1 39 49p1yagwszdk5 leo-oracle-11g enq: TX - row lock contention 1 40
20230419 10:59:48 1 39 49p1yagwszdk5 leo-oracle-11g enq: TX - row lock contention 1 40
20230419 10:59:49 1 39 49p1yagwszdk5 leo-oracle-11g enq: TX - row lock contention 1 40
20230419 10:59:50 1 39 49p1yagwszdk5 leo-oracle-11g enq: TX - row lock contention 1 40
20230419 10:59:51 1 39 49p1yagwszdk5 leo-oracle-11g enq: TX - row lock contention 1 40
20230419 10:59:52 1 39 49p1yagwszdk5 leo-oracle-11g enq: TX - row lock contention 1 40
20230419 10:59:53 1 39 49p1yagwszdk5 leo-oracle-11g enq: TX - row lock contention 1 40
20230419 10:59:54 1 39 49p1yagwszdk5 leo-oracle-11g enq: TX - row lock contention 1 40
20230419 10:59:55 1 39 49p1yagwszdk5 leo-oracle-11g enq: TX - row lock contention 1 40
ST INST_ID SESSION_ID SQL_ID MACHINE EVENT BLOCKING_INST_ID BLOCKING_SESSION
----------------- ---------- ---------- ------------- -------------------- ------------------------------ ---------------- ----------------
20230419 10:59:56 1 39 49p1yagwszdk5 leo-oracle-11g enq: TX - row lock contention 1 40
20230419 10:59:57 1 39 49p1yagwszdk5 leo-oracle-11g enq: TX - row lock contention 1 40
20230419 10:59:57 1 3 leo-oracle-11g
20230419 10:59:58 1 39 49p1yagwszdk5 leo-oracle-11g enq: TX - row lock contention 1 40
20230419 10:59:59 1 39 49p1yagwszdk5 leo-oracle-11g enq: TX - row lock contention 1 40
60 rows selected.
说明：如上所示，sid为40的session1首先被sid为39的session2阻塞，然后sid为39的session2又被sid为40的session1阻塞，形成死锁.
2.2、查相关sql语句
--根据sql_id查具体sql语句
set long 10000
SYS@orcl> select sql_fulltext from v$sql where sql_id='49p1yagwszdk5';
SQL_FULLTEXT
--------------------------------------------------------------------------------
update t1 set a=1500 where a=1
SYS@orcl> select sql_fulltext from v$sql where sql_id='836u2mpgy418s';
SQL_FULLTEXT
--------------------------------------------------------------------------------
update t2 set a=3000 where a=2
说明：如上查出session hang住的sql语句.
2.3、查current_row#,current_block#,current_file#
--使用如下语句查出具体current_row#,current_block#,current_file#
set linesize 250 pagesize 190
col machine for a30
col event for a30
select to_char(sample_time, 'yyyymmdd hh24:mi:ss') st,
inst_id,
session_serial#,
session_id,
machine,
event,
blocking_inst_id,
blocking_session,
current_obj#,
current_file#,
current_block#,
current_row#
from gv$active_session_history
where sample_time > to_date('20230419 10:59', 'yyyymmdd hh24:mi')
and to_char(sample_time, 'yyyymmdd hh24:mi:ss') = '20230419 10:59:26'
order by st;
ST INST_ID SESSION_SERIAL# SESSION_ID MACHINE EVENT BLOCKING_INST_ID BLOCKING_SESSION CURRENT_OBJ# CURRENT_FILE# CURRENT_BLOCK# CURRENT_ROW#
----------------- ---------- --------------- ---------- ------------------------------ ------------------------------ ---------------- ---------------- ------------ ------------- -------------- ------------
20230419 10:59:26 1 49 40 leo-oracle-11g enq: TX - row lock contention 1 39 95411 4 179 0
20230419 10:59:26 1 157 39 leo-oracle-11g enq: TX - row lock contention 1 40 95410 4 171 0
2.4、查rowid
--根据current_row#,current_block#,current_file#查出具体rowid.
select *
from (select rowid,
dbms_rowid.rowid_object(rowid) obj_no,
dbms_rowid.rowid_relative_fno(rowid) rfile_no,
dbms_rowid.rowid_block_number(rowid) block_no,
dbms_rowid.rowid_row_number(rowid) row_no
from scott.t2)
where rfile_no || ',' || block_no || ',' || row_no in
('4,171,0', '4,179,0');
ROWID OBJ_NO RFILE_NO BLOCK_NO ROW_NO
------------------ ---------- ---------- ---------- ----------
AAAXSzAAEAAAACzAAA 95411 4 179 0
select *
from (select rowid,
dbms_rowid.rowid_block_number(rowid) block_no,
dbms_rowid.rowid_object(rowid) obj_no,
from scott.t1)
dbms_rowid.rowid_relative_fno(rowid) rfile_no,
dbms_rowid.rowid_block_number(rowid) block_no,
dbms_rowid.rowid_row_number(rowid) row_no
from scott.t1)
where rfile_no || ',' || block_no || ',' || row_no in
9 ('4,171,0', '4,179,0');
ROWID OBJ_NO RFILE_NO BLOCK_NO ROW_NO
------------------ ---------- ---------- ---------- ----------
AAAXSyAAEAAAACrAAA 95410 4 171 0
2.5、查具体行信息
--根据具体rowid查行信息.
SYS@orcl> select * from scott.t2 where rowid='AAAXSzAAEAAAACzAAA';
A
----------
2
SYS@orcl> select * from scott.t1 where rowid='AAAXSyAAEAAAACrAAA';
A
----------
1活动
天翼云最新优惠活动，涵盖免费试用,产品折扣等，助您降本增效！
模拟oracle死锁并确认相关表以及行等信息
模拟oracle死锁并确认相关表以及行等信息
2024-12-23 09:16:28 阅读次数：1
版权声明：本文内容来自第三方投稿或授权转载，原文地址：https://blog.51cto.com/u_12991611/6206192，作者：Liujun_Deng，版权归原作者所有。本网站转在其作品的目的在于传递更多信息，不拥有版权，亦不承担相应法律责任。如因作品内容、版权等问题需要同本网站联系，请发邮件至ctyunbbs@chinatelecom.cn沟通。
上一篇：redis主从复制集群环境搭建
作者介绍
天翼云用户
查看更多
最新文章
模拟数据库序列间断场景
2024-12-20 07:55:03
oracle 11g分区表相关知识
2024-12-16 08:18:54
oracle Height-balanced直方图无法准确评估字段值出现频率的相关研究
2024-12-16 08:18:54
centos7mini静默安装oracle11gr2
2024-12-04 08:12:30
oracle数据库导出与导入
2024-11-28 08:59:52
oracle索引介绍
2024-11-20 09:47:05
查看更多
热门文章
oracle 数据库 中 如何将不带汉字的日期格式转换成带“年月日时分秒”格式的日期
2023-04-06 06:11:29
关于jpa的Specification自定义函数,实现oracle的decode；以及如何在静态方法中调用注入的service
2022-12-26 09:32:18
oracle字符串处理函数（部分）
2022-12-29 09:29:46
Oracle 10g在solaris 10下的自动运行脚本
2022-11-16 11:58:11
ORA-00942: 表或视图不存在
2023-04-27 06:29:38
oracle 、sql server 、mysql 复制表数据
2023-05-05 09:59:12
售前咨询热线
400-810-9889转1
关注天翼云
- APP
©2024
天翼云科技有限公司版权所有 增值电信业务经营许可证A2.B1.B2-20090001
公司地址：北京市东城区青龙胡同甲1号、3号2幢2层205-32室
