hxl 发表于 2011-12-23 01:04

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 &gt; 0<BR>and l1.id1 = l2.id1<BR>and l1.id2 = l2.id2;</SPAN></LI>
<LI><SPAN style="COLOR: #000000"><BR></LI>
<LI>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SID <SPAN style="COLOR: #ff00ff">'ISBLOCKING'</SPAN> SID<BR></LI>
<LI><SPAN style="COLOR: #ff9900">---------- ------------- ----------<BR></LI>
<LI></SPAN><BR></LI>
<LI>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;17 <SPAN style="COLOR: #0000ff">IS</SPAN> BLOCKING 137<BR></LI>
<LI>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;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>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; row_wait_obj# obj#,<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; row_wait_file# file#,<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; row_wait_block# block#,<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; row_wait_row# row#,<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; dbms_rowid.rowid_create(1,<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; row_wait_obj#,<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; row_wait_file#,<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; row_wait_block#,<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; row_wait_row#) as rid<BR>&nbsp; from v$session s, dba_objects do<BR>&nbsp;where sid = 13 --被阻塞的会话<BR>&nbsp; and s.row_wait_obj# = do.object_id;</LI>
<LI></LI>
<LI>OBJECT_NAME&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; OBJ#&nbsp;&nbsp; FILE#&nbsp; BLOCK#&nbsp; ROW# RID<BR>-------------------- ------- ------- ------- ------- --------------------<BR>TB_HXL_USER_MID&nbsp;&nbsp; 110521&nbsp;&nbsp; 12&nbsp; 699608&nbsp;&nbsp; 0&nbsp;&nbsp; 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]
查看完整版本: lock相关