测试rac环境下的锁
create table system.akdas (A1 number, Col1 Varchar2(10), Col2 Varchar2(10));
insert into system.akdas values(5,'Hello','Hi');
insert into system.akdas values(6,'Sudip','Datta');
insert into system.akdas values(7,'Preetam','Roy');
insert into system.akdas values(8,'Michael','Polaski');
From Node 1:
==========
update system.akdas set a1=11 where a1=6;
From Node 2:
==========
update system.akdas set a1=12 where a1=7;
update system.akdas set a1=11 where a1=6; /* this will wait for Node1: to complete the transaction */
查询RAC环境锁的状态
select
a.INST_ID, a.SID, a.TYPE, a.CTIME,
b.INST_ID, b.SID, b.TYPE, b.CTIME
from gv$lock a,gv$lock b
where (a.ID1,a.ID2,a.TYPE) in (select a.ID1,a.ID2,a.TYPE from gv$lock where request>0)
and a.ID1 = b.ID1 and a.ID2 = b.ID2
and a.type like 'TX' and b.type like 'TX'
and a.LMODE = 6
and b.LMODE = 0
INST_ID SID TY CTIME INST_ID SID TY CTIME
------- ------- -- ---------- ------- ------- -- ----------
1 2148 TX 3036 2 1051 TX 3015
prompt CTIME is in Seconds
prompt REQUEST is waiter
prompt REQUEST = 0 LMODE = 6 is holder
set lines 120
col BLOCK for 9
col LMODE for 9
col INST_ID for 9
col REQUEST for 9
col SID for 999999
select INST_ID, SID, TYPE, ID1, ID2, LMODE, REQUEST, CTIME, BLOCK
from gv$lock where (ID1,ID2,TYPE) in
(select ID1,ID2,TYPE from gv$lock where request>0);
INST_ID SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
------- ------- -- ---------- ---------- ----- ------- ---------- -----
1 2148 TX 655451 82666 6 0 46 2
2 1051 TX 655451 82666 0 6 25 0
prompt ===================================================================
prompt This Scripts is get who is waiter
prompt Query 1. Waiting for TX Enqueue where mode is Exclusive
prompt =====================================
prompt
set linesize 100
set pagesize 66
col c1 for a15
col c1 heading "Program Name "
select l.inst_id,l.SID,program c1,l.TYPE,l.ID1,l.ID2,l.LMODE,l.REQUEST
from gv$lock l,gv$session s
where l.type like 'TX' and l.REQUEST =6
and l.inst_id=s.inst_id and l.sid=s.sid
order by id1
/
INST_ID SID Program Name TY ID1 ID2 LMODE REQUEST
------- ------- --------------- -- ---------- ---------- ----- -------
2 1051 sqlplus@SERV-TEST TX 655451 82666 0 6
2 (TNS V1-V3)
prompt
prompt This Scripts is get who is holder
prompt Query 2. Holding for TX Enqueue where mode greater than 6
prompt =======================================
prompt
set linesize 100
set pagesize 66
col c1 for a15
col c1 heading "Program Name "
select l.inst_id,l.SID,program c1,l.TYPE,l.ID1,l.ID2,l.LMODE,l.REQUEST
from gv$lock l,gv$session s
where l.type like 'TX' and l.LMODE =6 and (l.ID1,l.ID2) in
(select id1,id2 from gv$lock where type like 'TX' and REQUEST =6)
and l.inst_id=s.inst_id and l.sid=s.sid
order by id1
/
INST_ID SID Program Name TY ID1 ID2 LMODE REQUEST
------- ------- --------------- -- ---------- ---------- ----- -------
1 2148 sqlplus@SERV-TEST TX 655451 82666 6 0
1 (TNS V1-V3)
prompt
prompt
prompt Query 3. Object# ,File#, Block# and Slot# TX Enqueue in detail
prompt ========================================
prompt
set linesize 110
col c0 for 999
col c0 heading "INS"
col c1 for a15
col c1 heading "Program Name "
select inst_id c0,sid,program c1,ROW_WAIT_OBJ# object_no, ROW_WAIT_FILE# Rfile_no,
ROW_WAIT_BLOCK# Block_no ,ROW_WAIT_ROW# Row_no
from gv$session
where (inst_id,sid) in (select inst_id,sid from gv$session_wait where p1='1415053318')
/
INS SID Program Name OBJECT_NO RFILE_NO BLOCK_NO ROW_NO
---- ------- --------------- ---------- ---------- ---------- ----------
2 1051 sqlplus@SERV-TEST 74485 1 22302 1
2 (TNS V1-V3)
prompt
prompt
prompt Query 4. Object Involve for TX Enqueue in detail
prompt ===============================
prompt
set linesize 100
set pagesize 100
col owner for a10
col object_name for a20
col object_type for a10
select owner,object_name,object_id,object_type
from
dba_objects,
(select ROW_WAIT_OBJ# from gv$session where (inst_id, sid) in (select inst_id,sid from gv$session_wait where p1='1415053318')) b
where
object_id = b.ROW_WAIT_OBJ#
OWNER OBJECT_NAME OBJECT_ID OBJECT_TYP
---------- -------------------- ---------- ----------
SYSTEM AKDAS 74485 TABLE
prompt
prompt
prompt Query 5. Finding the row value
prompt ====================
prompt
--select * from .
where rowid like--DBMS_ROWID.ROWID_CREATE(1,&Object_No,&Rfile_No, &Block_No, &Row_Number)
From query 3 and 4 we will get the value for all variables.
Owner = SYSTEM
Table_Name = AKDAS
Object_No = 74485
Rfile_No = 1
Block_No = 22302
Row_Number = 1
select * from system.AKDAS where rowid like
DBMS_ROWID.ROWID_CREATE(1,74485,1, 22302, 1)
/
A1 COL1 COL2
---------- ---------- ----------
6 Sudip Datta
[@more@]
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/48010/viewspace-1015839/,如需转载,请注明出处,否则将追究法律责任。
<%=items[i].content%>
<%if(items[i].items.items.length) { %><%=items[i].items.items[j].username%> 回复 <%=items[i].items.items[j].tousername%>: <%=items[i].items.items[j].content%>
转载于:http://blog.itpub.net/48010/viewspace-1015839/