tx鎖之ROW_WAIT_OBJ#和object_id關(guān)聯(lián)排障
實(shí)驗(yàn)：
session 1:
SQL> show user;
USER is "SYS"
SQL>
SQL> create table t_all_objs as select owner,object_id,object_name from all_objects where 0=1;
Table created.
SQL> alter table T_ALL_OBJS add constraint pk_t_all_objs primary key (OBJECT_ID);
Table altered.
SQL> insert into t_all_objs(owner,object_id,object_name) values('TEST',2013011701,'test1');
1 row created.
SQL> insert into t_all_objs(owner,object_id,object_name) values('TEST',2013011702,'test2');
1 row created.
SQL> commit;
Commit complete.
SQL> select sid from v$mystat where rownum<2;
SID
----------
61
SQL>
SQL> select * from t_all_objs;
OWNER OBJECT_ID OBJECT_NAME
------------------------------ ---------- ------------------------------
TEST 2013011701 test1
TEST 2013011702 test2
SQL> update t_all_objs set object_name='test11' where object_id=2013011701;
1 row updated.
未提交...................................
session 2:
SQL> update t_all_objs set object_name='test101' where object_id=2013011701;
1 row updated.
hang住了...........................
--顯然是有阻塞，假設(shè)我們只知道阻塞的對(duì)象是T_ALL_OBJS表，則排障如下，主要是根據(jù)將object_id關(guān)聯(lián)到v$session的ROW_WAIT_OBJ#，如下：
set lines 200 pages 999
col ORACLE_USERNAME for a14
col OBJECT_NAME for a20
col MACHINE for a14
col OS_USER_NAME for a14
col terminal for a14
select l.session_id sid,
s.serial#,
l.locked_mode,
l.oracle_username,
l.os_user_name,
s.machine,
s.terminal,
o.object_name,
o.object_type,
o.object_id,
s.logon_time
from v$locked_object l, dba_objects o, v$session s
where l.object_id = o.object_id
and o.object_name='T_ALL_OBJS'
and l.session_id = s.sid
order by sid, s.serial#;
SID SERIAL# LOCKED_MODE ORACLE_USERNAM OS_USER_NAME MACHINE TERMINAL OBJECT_NAME OBJECT_TYPE OBJECT_ID LOGON_TIME
---------- ---------- ----------- -------------- -------------- -------------- -------------- -------------------- ------------------- ---------- ------------
59 165 3 SYS oracle wang pts/9 T_ALL_OBJS TABLE 89985 06-NOV-17
61 721 3 SYS oracle wang pts/8 T_ALL_OBJS TABLE 89985 06-NOV-17
或者直接查詢dba_object的object_id值。。。。。。。。。。。
接著關(guān)聯(lián)到ROW_WAIT_OBJ#=89985，即ROW_WAIT_OBJ#=object_id
SQL> select sid,sql_id,status,blocking_session, ROW_WAIT_OBJ#,ROW_WAIT_FILE#,ROW_WAIT_BLOCK#,ROW_WAIT_ROW# from v$session where event='enq: TX - row lock contention' and ROW_WAIT_OBJ#=89985;
SID SQL_ID STATUS BLOCKING_SESSION ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW#
---------- ------------- -------- ---------------- ------------- -------------- --------------- -------------
59 c53uad8st2u8t ACTIVE 61 89985 1 102393 0
--接著根據(jù)blocking_seesin=61，查詢：
SQL> select sid,serial#,sql_id,status,event,blocking_session, ROW_WAIT_OBJ#,ROW_WAIT_FILE#,ROW_WAIT_BLOCK#,ROW_WAIT_ROW# from v$session where sid=61;
SID SERIAL# SQL_ID STATUS EVENT BLOCKING_SESSION ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW#
---------- ---------- ------------- -------- ---------------------------------------------------------------- ---------------- ------------- -------------- --------------- -------------
61 721 INACTIVE SQL*Net message from client -1 0 0 0
--找到原因sid,殺掉：
SQL> alter system kill session '61,721' immediate;
System altered.
SQL>
--發(fā)現(xiàn)session 2已經(jīng)提交了
SQL> update t_all_objs set object_name='test101' where object_id=2013011701;
1 row updated.
--commit提交后查詢
SQL> commit;
Commit complete.
SQL> select * from t_all_objs;
OWNER OBJECT_ID OBJECT_NAME
------------------------------ ---------- ------------------------------
TEST 2013011701 test101
TEST 2013011702 test2
SQL>
============================================================================================
或者直接用如下三種方法排查：
select SID,TYPE,ID1,ID2,LMODE,REQUEST,CTIME,BLOCK from V$lock whereblock=1 or request<>0;
SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
---------- -- ---------- ---------- ---------- ---------- ---------- ----------
59 TX 393249 10702 0 6 127 0
61 TX 393249 10702 6 0 135 1
select a.sid hold_sid, b.sid wait_sid, a.type, a.id1, a.id2, a.ctime
from v$lock a, v$lock b
where a.id1 = b.id1
and a.id2 = b.id2
and a.block = 1
and b.block = 0;
HOLD_SID WAIT_SID TY ID1 ID2 CTIME
---------- ---------- -- ---------- ---------- ----------
61 59 TX 393249 10702 108
select decode(request,0,'holder: ','waiter: ') ||
sid session_id, id1, id2, lmode, request, type
from v$lock
where (id1, id2, type) in (select id1, id2, type from v$lock where request > 0)
order by id1, request;
SESSION_ID ID1 ID2 LMODE REQUEST TY
------------------------------------------------ ---------- ---------- ---------- ---------- --
holder: 61 393249 10702 6 0 TX
waiter: 59 393249 10702 0 6 TX
session 1:
SQL> show user;
USER is "SYS"
SQL>
SQL> create table t_all_objs as select owner,object_id,object_name from all_objects where 0=1;
Table created.
SQL> alter table T_ALL_OBJS add constraint pk_t_all_objs primary key (OBJECT_ID);
Table altered.
SQL> insert into t_all_objs(owner,object_id,object_name) values('TEST',2013011701,'test1');
1 row created.
SQL> insert into t_all_objs(owner,object_id,object_name) values('TEST',2013011702,'test2');
1 row created.
SQL> commit;
Commit complete.
SQL> select sid from v$mystat where rownum<2;
SID
----------
61
SQL>
SQL> select * from t_all_objs;
OWNER OBJECT_ID OBJECT_NAME
------------------------------ ---------- ------------------------------
TEST 2013011701 test1
TEST 2013011702 test2
SQL> update t_all_objs set object_name='test11' where object_id=2013011701;
1 row updated.
未提交...................................
session 2:
SQL> update t_all_objs set object_name='test101' where object_id=2013011701;
1 row updated.
hang住了...........................
--顯然是有阻塞，假設(shè)我們只知道阻塞的對(duì)象是T_ALL_OBJS表，則排障如下，主要是根據(jù)將object_id關(guān)聯(lián)到v$session的ROW_WAIT_OBJ#，如下：
set lines 200 pages 999
col ORACLE_USERNAME for a14
col OBJECT_NAME for a20
col MACHINE for a14
col OS_USER_NAME for a14
col terminal for a14
select l.session_id sid,
s.serial#,
l.locked_mode,
l.oracle_username,
l.os_user_name,
s.machine,
s.terminal,
o.object_name,
o.object_type,
o.object_id,
s.logon_time
from v$locked_object l, dba_objects o, v$session s
where l.object_id = o.object_id
and o.object_name='T_ALL_OBJS'
and l.session_id = s.sid
order by sid, s.serial#;
SID SERIAL# LOCKED_MODE ORACLE_USERNAM OS_USER_NAME MACHINE TERMINAL OBJECT_NAME OBJECT_TYPE OBJECT_ID LOGON_TIME
---------- ---------- ----------- -------------- -------------- -------------- -------------- -------------------- ------------------- ---------- ------------
59 165 3 SYS oracle wang pts/9 T_ALL_OBJS TABLE 89985 06-NOV-17
61 721 3 SYS oracle wang pts/8 T_ALL_OBJS TABLE 89985 06-NOV-17
或者直接查詢dba_object的object_id值。。。。。。。。。。。
接著關(guān)聯(lián)到ROW_WAIT_OBJ#=89985，即ROW_WAIT_OBJ#=object_id
SQL> select sid,sql_id,status,blocking_session, ROW_WAIT_OBJ#,ROW_WAIT_FILE#,ROW_WAIT_BLOCK#,ROW_WAIT_ROW# from v$session where event='enq: TX - row lock contention' and ROW_WAIT_OBJ#=89985;
SID SQL_ID STATUS BLOCKING_SESSION ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW#
---------- ------------- -------- ---------------- ------------- -------------- --------------- -------------
59 c53uad8st2u8t ACTIVE 61 89985 1 102393 0
--接著根據(jù)blocking_seesin=61，查詢：
SQL> select sid,serial#,sql_id,status,event,blocking_session, ROW_WAIT_OBJ#,ROW_WAIT_FILE#,ROW_WAIT_BLOCK#,ROW_WAIT_ROW# from v$session where sid=61;
SID SERIAL# SQL_ID STATUS EVENT BLOCKING_SESSION ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW#
---------- ---------- ------------- -------- ---------------------------------------------------------------- ---------------- ------------- -------------- --------------- -------------
61 721 INACTIVE SQL*Net message from client -1 0 0 0
--找到原因sid,殺掉：
SQL> alter system kill session '61,721' immediate;
System altered.
SQL>
--發(fā)現(xiàn)session 2已經(jīng)提交了
SQL> update t_all_objs set object_name='test101' where object_id=2013011701;
1 row updated.
--commit提交后查詢
SQL> commit;
Commit complete.
SQL> select * from t_all_objs;
OWNER OBJECT_ID OBJECT_NAME
------------------------------ ---------- ------------------------------
TEST 2013011701 test101
TEST 2013011702 test2
SQL>
============================================================================================
或者直接用如下三種方法排查：
select SID,TYPE,ID1,ID2,LMODE,REQUEST,CTIME,BLOCK from V$lock whereblock=1 or request<>0;
SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
---------- -- ---------- ---------- ---------- ---------- ---------- ----------
59 TX 393249 10702 0 6 127 0
61 TX 393249 10702 6 0 135 1
select a.sid hold_sid, b.sid wait_sid, a.type, a.id1, a.id2, a.ctime
from v$lock a, v$lock b
where a.id1 = b.id1
and a.id2 = b.id2
and a.block = 1
and b.block = 0;
HOLD_SID WAIT_SID TY ID1 ID2 CTIME
---------- ---------- -- ---------- ---------- ----------
61 59 TX 393249 10702 108
select decode(request,0,'holder: ','waiter: ') ||
sid session_id, id1, id2, lmode, request, type
from v$lock
where (id1, id2, type) in (select id1, id2, type from v$lock where request > 0)
order by id1, request;
SESSION_ID ID1 ID2 LMODE REQUEST TY
------------------------------------------------ ---------- ---------- ---------- ---------- --
holder: 61 393249 10702 6 0 TX
waiter: 59 393249 10702 0 6 TX
文章標(biāo)題：tx鎖之ROW_WAIT_OBJ#和object_id關(guān)聯(lián)排障
網(wǎng)站鏈接：http://www.chinadenli.net/article30/jdhpso.html
成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)站內(nèi)鏈、外貿(mào)建站、網(wǎng)站設(shè)計(jì)公司、搜索引擎優(yōu)化、定制開(kāi)發(fā)、品牌網(wǎng)站制作
聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源： 創(chuàng)新互聯(lián)
- 品牌網(wǎng)站建設(shè)與普通的網(wǎng)站制作有何區(qū)別呢？ 2016-11-12
- 品牌網(wǎng)站建設(shè)公司哪家更可靠呢？ 2022-12-26
- 為什么要做品牌網(wǎng)站建設(shè)？ 2021-12-04
- 品牌網(wǎng)站建設(shè)：企業(yè)為什么要做屬于自己的官網(wǎng)？ 2023-03-09
- 品牌網(wǎng)站建設(shè)過(guò)程中應(yīng)該注意哪些問(wèn)題 2016-09-18
- 這些小元素是網(wǎng)站設(shè)計(jì)師帶給我們的美?? 2022-06-17
- 創(chuàng)新互聯(lián)：品牌網(wǎng)站建設(shè)和普通企業(yè)網(wǎng)站建設(shè)有何關(guān)系 2022-11-14
- 品牌網(wǎng)站建設(shè)第一步應(yīng)該先從了解企業(yè)品牌開(kāi)始 2017-07-01
- 網(wǎng)站制作模板_好的品牌網(wǎng)站建設(shè)公司重視哪些問(wèn)題 2016-09-26
- 企業(yè)要做品牌網(wǎng)站建設(shè)的四大理由 2017-12-14
- 品牌網(wǎng)站建設(shè)中小企業(yè)要如何才能實(shí)現(xiàn)品牌網(wǎng)站的建設(shè) 2023-03-20
- 品牌網(wǎng)站建設(shè)中視覺(jué)規(guī)劃的重要性 2021-08-27