oracle封鎖測試

封鎖測試[@more@]

10:16:39 > select * from v$version
10:16:54 2 ;

BANNER
================================================================
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
PL/SQL Release 9.2.0.4.0 - Production
CORE 9.2.0.3.0 Production
TNS for 32-bit Windows: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 - Production

已用時間: 00: 00: 00.00

一、TX事務鎖

1) 在一個session中執行update操作，sid=9

20:59:29 scott@bus# update dept set deptno = deptno + 10;

已更新4行。

已用時間: 00: 00: 00.00

--查詢lock情況

20:59:48 scott@bus# select username,v$lock.sid,trunc(id1/power(2,16)) rbs,

21:00:30 2 bitand(id1,to_number('ffff','xxxx'))+0 slot,

21:00:53 3 id2 seq,lmode,request

21:01:07 4 from v$lock,v$session where v$lock.type = 'TX'

21:01:29 5 and v$lock.sid=v$session.sid

21:01:43 6 and v$session.username = user;

USERNAME SID RBS SLOT SEQ LMODE

============================== =========== =========== =========== =========== ===========

REQUEST

===========

SCOTT 9 3 45 3467 6

0

注：rbs,slot,seq表示事務ID。只有一個獨佔鎖而沒有請求。這裡update了4行資料， 可能猜測在v$lock表中應該存在4行，但必須記住的是oracle不儲存每一個鎖定行的主列表。如果要找出一行是否被鎖定，必須到那一行才能確定。

已用時間: 00: 00: 03.03

--查詢事務ID

21:03:30 scott@bus# select xidusn,xidslot,xidsqn from v$transaction

21:04:42 2 ;

XIDUSN XIDSLOT XIDSQN

=========== =========== ===========

3 45 3467

已用時間: 00: 00: 00.00

--在另一個session中也執行操作後，再查詢lock情況

21:04:44 scott@bus# ed

已寫入檔案 afiedit.sql

1 select username,v$lock.sid,trunc(id1/power(2,16)) rbs,

2 bitand(id1,to_number('ffff','xxxx'))+0 slot,

3 id2 seq,lmode,request

4 from v$lock,v$session where v$lock.type = 'TX'

5 and v$lock.sid=v$session.sid

6* and v$session.username = user

21:12:44 scott@bus# /

USERNAME SID RBS SLOT SEQ LMODE REQUEST

============================== =========== =========== =========== =========== =========== ===========

SCOTT 9 3 45 3467 6 0

SCOTT 10 3 45 3467 0 6

SCOTT 10 8 32 3430 6 0

注：session 10被session 9阻塞

已用時間: 00: 00: 07.01

21:12:55 scott@bus# select xidusn,xidslot,xidsqn from v$transaction;

XIDUSN XIDSLOT XIDSQN

=========== =========== ===========

8 32 3430

3 45 3467

已用時間: 00: 00: 00.00

--查詢阻塞情況

21:14:05 scott@bus# select (select username from v$session where sid=a.sid) blocker,

21:16:25 2 a.sid,'is blocking',

21:16:36 3 (select username from v$session where sid=b.sid) blockee,b.sid

21:17:08 4 from v$lock a,v$lock b

21:17:18 5 where a.block=1 and b.request>0 and a.id1=b.id1 and a.id2=b.id2;

BLOCKER SID 'ISBLOCKING BLOCKEE SID

============================== =========== =========== ============================== ===========

SCOTT 9 is blocking SCOTT 10

已用時間: 00: 00: 02.06

3) session 9 提交

21:17:53 scott@bus# commit;

提交完成。

已用時間: 00: 00: 00.00

--提交後再查詢lock情況，發現阻塞解除，session 10立即被喚醒

21:19:02 scott@bus# ed

已寫入檔案 afiedit.sql

1 select username,v$lock.sid,trunc(id1/power(2,16)) rbs,

2 bitand(id1,to_number('ffff','xxxx'))+0 slot,

3 id2 seq,lmode,request

4 from v$lock,v$session where v$lock.type = 'TX'

5 and v$lock.sid=v$session.sid

6* and v$session.username = user

21:19:33 scott@bus# /

USERNAME SID RBS SLOT SEQ LMODE REQUEST

============================== =========== =========== =========== =========== =========== ===========

SCOTT 10 8 32 3430 6 0

已用時間: 00: 00: 06.08

21:19:42 scott@bus#

2) 在另一個session再一次執行update操作,sid=10

21:11:27 scott@bus> update dept set deptno = deptno - 10;

--該session 10被session 9阻塞後處於等待狀態

已更新4行。

已用時間: 00: 06: 44.03

21:18:34 scott@bus>

二、TM（DML入隊）鎖

19:53:30 scott@bus> create table t1(x number);

表已建立。

已用時間: 00: 00: 00.00

19:53:55 scott@bus> create table t2(x number);

表已建立。

已用時間: 00: 00: 00.00

19:54:06 scott@bus> insert into t1 values(1);

已建立 1 行。

已用時間: 00: 00: 00.00

19:54:22 scott@bus> insert into t2 values(1);

已建立 1 行。

已用時間: 00: 00: 00.00

20:01:56 scott@bus> ed

已寫入檔案 afiedit.sql

1 select/*+ rule */ username,v$lock.sid,id1,id2,

2 lmode,request,block,v$lock.type

3 from v$lock,v$session

4 where v$lock.sid=v$session.sid

5* and v$session.username=user

20:02:20 scott@bus> /

USERNAME SID ID1 ID2 LMODE REQUEST BLOCK TY

============================== =========== =========== =========== =========== =========== =========== ==

SCOTT 9 524335 3522 6 0 0