标签：10 11g 59 20230419 行等 死锁 oracle row
文档课题：模拟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
标签：10,
11g,
59,
20230419,
行等,
死锁,
oracle,
row
From： https://blog.51cto.com/u_12991611/6206192