lock相关
<DIV>1.v$lock中查询会话间相互阻塞</DIV><DIV>在v$lock视图中没有直接体现那个会话阻塞那个会话的信息,但某个会话阻塞另一个会话的话,其ID1和ID2字段值是相同的,同时阻塞的会话的字段block=1,被阻塞的会话的request的字段值大于0,这样通过如下查询就能知道那个会话阻塞了那些会话.</DIV>
<DIV>
<DIV id=codeText class=codeText>
<OL style="PADDING-BOTTOM: 5px; MARGIN: 0px 1px 0px 0px; PADDING-LEFT: 0px; PADDING-RIGHT: 0px; PADDING-TOP: 5px" class=dp-css>
<LI><SPAN style="COLOR: #000000">select l1.sid, ' is blocking ', l2.sid<BR>from v$lock l1, v$lock l2<BR>where l1.block = 1<BR>and l2.request > 0<BR>and l1.id1 = l2.id1<BR>and l1.id2 = l2.id2;</SPAN></LI>
<LI><SPAN style="COLOR: #000000"><BR></LI>
<LI> SID <SPAN style="COLOR: #ff00ff">'ISBLOCKING'</SPAN> SID<BR></LI>
<LI><SPAN style="COLOR: #ff9900">---------- ------------- ----------<BR></LI>
<LI></SPAN><BR></LI>
<LI> 17 <SPAN style="COLOR: #0000ff">IS</SPAN> BLOCKING 137<BR></LI>
<LI> 17 <SPAN style="COLOR: #0000ff">IS</SPAN> BLOCKING 13</SPAN></LI></OL></DIV></DIV>
<P>查询结果显示会话17同时阻塞了会话13和137.</P>
<P>2. 锁类型以及ID1和ID2代表的含义</P>
<P>v$lock常出现的所有UL,TX和TM <BR>UL lock: 用户自定义锁,在DBMS_LOCK 中定义<BR>TX lock: 行事物锁,ID1和ID2分别代表的是该事物占用的回滚段号和事物ID.<BR>TM lock: DML锁,id1代表当天修改对象的ID</P>
<P>3.锁模式</P>
<P>4.查询锁对象</P>
<P>5.查询被阻塞的行</P>
<DIV id=codeText class=codeText>
<OL style="PADDING-BOTTOM: 5px; MARGIN: 0px 1px 0px 0px; PADDING-LEFT: 0px; PADDING-RIGHT: 0px; PADDING-TOP: 5px" class=dp-css><SPAN style="COLOR: #000000">
<LI>column object_name format a20<BR>column obj# format 999999<BR>column file# format 999999<BR>column block# format 999999<BR>column row# format 999999<BR>column rid format a20<BR>select do.object_name object_name,<BR> row_wait_obj# obj#,<BR> row_wait_file# file#,<BR> row_wait_block# block#,<BR> row_wait_row# row#,<BR> dbms_rowid.rowid_create(1,<BR> row_wait_obj#,<BR> row_wait_file#,<BR> row_wait_block#,<BR> row_wait_row#) as rid<BR> from v$session s, dba_objects do<BR> where sid = 13 --被阻塞的会话<BR> and s.row_wait_obj# = do.object_id;</LI>
<LI></LI>
<LI>OBJECT_NAME OBJ# FILE# BLOCK# ROW# RID<BR>-------------------- ------- ------- ------- ------- --------------------<BR>TB_HXL_USER_MID 110521 12 699608 0 AAAa+5AAMAACqzYAAA</LI></SPAN></OL></DIV>
<P>获取到rowid后即可查询到具体的行</P>
<DIV id=codeText class=codeText>
<OL style="PADDING-BOTTOM: 5px; MARGIN: 0px 1px 0px 0px; PADDING-LEFT: 0px; PADDING-RIGHT: 0px; PADDING-TOP: 5px" class=dp-css>Select * From TB_HXL_USER_MID Where Rowid='AAAa+5AAMAACqzYAAA'</OL></DIV>
页:
[1]