關於RAC環境下鎖查詢的測試案例1-1

測試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/，如需轉載，請註明出處，否則將追究法律責任。