oracle封锁账户,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:29scott@bus# update dept set deptno = deptno + 10;

已更新4行。

已用时间:00: 00: 00.00

--查询lock情况

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

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

21:00:533id2 seq,lmode,request

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

21:01:295and v$lock.sid=v$session.sid

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

USERNAMESIDRBSSLOTSEQLMODE

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

REQUEST

===========

SCOTT934534676

0

注：rbs,slot,seq表示事务ID。只有一个独占锁而没有请求。这里update了4行数据，可能猜测在v$lock表中应该存在4行，但必须记住的是oracle不存储每一个锁定行的主列表。如果要找出一行是否被锁定，必须到那一行才能确定。

已用时间:00: 00: 03.03

--查询事务ID

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

21:04:422;

XIDUSNXIDSLOTXIDSQN

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

3453467

已用时间:00: 00: 00.00

--在另一个session中也执行操作后，再查询lock情况

21:04:44scott@bus# ed

已写入文件afiedit.sql

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

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

3id2 seq,lmode,request

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

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

6*and v$session.username = user

21:12:44scott@bus# /

USERNAMESIDRBSSLOTSEQLMODEREQUEST

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

SCOTT934534676 0

SCOTT10345346706

SCOTT10832343060

注：session 10被session 9阻塞

已用时间:00: 00: 07.01

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

XIDUSNXIDSLOTXIDSQN

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

8323430

3453467

已用时间:00: 00: 00.00

--查询阻塞情况

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

21:16:252a.sid,'is blocking',

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

21:17:084from v$lock a,v$lock b

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

BLOCKERSID 'ISBLOCKING BLOCKEESID

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

SCOTT9 is blocking SCOTT10

已用时间:00: 00: 02.06

3) session 9提交

21:17:53scott@bus# commit;

提交完成。

已用时间:00: 00: 00.00

--提交后再查询lock情况，发现阻塞解除，session 10立即被唤醒

21:19:02scott@bus# ed

已写入文件afiedit.sql

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

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

3id2 seq,lmode,request

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

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

6* and v$session.username = user

21:19:33scott@bus# /

USERNAMESIDRBSSLOTSEQLMODEREQUEST

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

SCOTT10832343060

已用时间:00: 00: 06.08

21:19:42scott@bus#

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

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

--该session 10被session 9阻塞后处于等待状态

已更新4行。

已用时间:00: 06: 44.03

21:18:34scott@bus>

二、TM(DML入队)锁

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

表已创建。

已用时间:00: 00: 00.00

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

表已创建。

已用时间:00: 00: 00.00

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

已创建1行。

已用时间:00: 00: 00.00

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

已创建1行。

已用时间:00: 00: 00.00

20:01:56scott@bus> ed

已写入文件afiedit.sql

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

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

3from v$lock,v$session

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

5* and v$session.username=user

20:02:20scott@bus> /

USERNAMESIDID1ID2LMODEREQUESTBLOCK TY

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

SCOTT95243353522600