T_TYPE, CREATED, LAST_DDL_TIME, STATUS, NAMESPACE SYS T 74585 TABLE 22-12月-14 04-1月 -15 VALID 1 对于TM锁来说，ID1表示被锁定的对象的对象ID，ID2始终为0
对于TX锁来说，ID1表示事务使用的回滚段编号以及在事务表中对应的记录编号，ID2表示该记录编号被重用的次数（wrap）
使用sessionID为35进行更新t表操作： SQL> update t set name='ww' where id = 2;
//没有执行结果说明被锁住了 下面是查询sessionID为29、35的连接所产生的事务信息： select sid,type,id1,id2,
decode(lmode,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive') lock_mode,
decode(request,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive')
request_mode,block
from v$lock
where sid in(29,35)
order by sid;
SID, TYPE, ID1, ID2, LOCK_MODE, REQUEST_MODE, BLOCK 29 TM 74585 0 Row Exclusive None 0
29 AE 100 0 Share None 0
29 TX 327710 1099 Exclusive None 1//锁住了一个事务
35 TM 74585 0 Row Exclusive None 0
35 AE 100 0 Share None 0
35 TX 327710 1099 None Exclusive 0//请求一个排他锁，因为上面的29锁住了
使用sessionID为38进行更新t表操作： SQL> update t set name='ww' where id = 2;
//没有执行结果说明被锁住了
查询v$enqueue_lock来获得锁定队列中的session信息：
select sid,type,
decode(request,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive')
request_mode
from v$enqueue_lock
where sid in(35,38);
SID, TYPE, REQUEST_MODE 35 AE None
38 AE None
35 TX Exclusive
38 TX Exclusive 可以知道sessionID为35,38的连接需要锁为X锁
查询几个锁之间的关系： select a.sid blocker_sid,a.serial#,a.username as blocker_username,b.type,
decode(b.lmode,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive') lock_mode,
b.ctime as time_held,c.sid as waiter_sid,
decode(c.request,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive') request_mode,
c.ctime time_waited
from v$lock b, v$enqueue_lock c, v$session a
where a.sid = b.sid and b.id1= c.id1(+) and b.id2 = c.id2(+) and c.type(+) = 'TX' and b.type = 'TX' and b.block = 1
order by time_held, time_waited;
BLOCKER_SID, SERIAL#, BLOCKER_USERNAME, TYPE, LOCK_MODE, TIME_HELD, WAITER_SID, REQUEST_MODE, TIME_WAITED 29 557 SYS TX Exclusive 2452 38 Exclusive 274
29 557 SYS TX Exclusive 2452 35 Exclusive 712 BLOCKER_SID：锁的sessionID为29，BLOCKER_USERNAME：锁的用户名是sys， TYPE：锁的类型为TX（行级锁）LOCK_MODE：锁的模型为排他锁，TIME_HELD：锁所持续的时间 WAITER_SID：该锁被哪个sessionID锁等待，REQUEST_MODE：请求锁的类型，TIME_WAITED：请求时间 通过上面的信息就可以知道某个连接所持有的锁时间，假如时间过长说明该连接存在问题，就可以杀掉该连接 alter system kill session '29,557';//第一个参数BLOCKER_SID，第二个为SERIAL#参数
一个事务修改多行，产生一个TX锁
select sid from v$mystat where rownum=1;
SQL> update t set name='rr' where id=2;
1 row updated.
SQL> update t set name='rr' where id=2;
1 row updated.
SQL> update t set name='rr' where id=2;
1 row updated.
SQL>
select sid,type,id1,id2,
decode(lmode,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive') lock_mode,
decode(request,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive')request_mode,block
from v$lock
where sid=38;
SID, TYPE, ID1, ID2, LOCK_MODE, REQUEST_MODE, BLOCK 38 AE 100 0 Share None 0
38 TM 74585 0 Row Exclusive None 0
38 TX 327680 1099 Exclusive None 0
可以获得的TX锁定的总个数由初始化参数transactions决定，而可以获得的TM锁定的个数则由初始化参数dml_locks决定：
select name,value from v$parameter where name in('transactions','dml_locks');
NAME, VALUE dml_locks 1084
transactions 271
查询资源的使用情况：
select resource_name as "R_N",current_utilization as "C_U",max |