10:16:39 scott@bus> 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
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/802415/viewspace-823032/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/802415/viewspace-823032/