最近数据库中经常有锁出现,网上查到一个查询锁的相关脚本,但是不太清楚v$lock中的 ID1,ID2是什么意思,google了一把,做了一下试验,简单明白一些了:
1. 模拟锁产生
SQL> conn ecc_view/ecc@devdb1 (sid=133)
Connected.
SQL> update test set id=3 where id=1;(不提交,也不回滚)
2 rows updated.
2. 查询v$lock对象
addr kaddr sid type id1 id2 lmode request ctime block
------------- ------------- ----- ------ -------- ------- -------- ---------- ------- -------
2C3D5848 2C3D5860 133 TM 57276 0 3 0 189 2
2C3F6DF0 2C3F6E14 133 TX 196651 2397 6 0 189 2
说明: TM : 表级锁 TX : 事务锁
当 v$lock.type=TM 时, ID1=57276: 锁定的对象的 object_id , ID2=0
select * from dba_objects a where a.object_id = '57276' --object_name = 'TEST'
当 v$lock.type=TX 时, ID1: v$transaction.XIDUSN和v$transaction.XIDSLOT(ID1的高16位为XIDUSN,低16位为XIDSLOT)
ID2: v$transaction.XIDSQN
SELECT s.XIDUSN,s.XIDSLOT, s.XIDSQN FROM v$transaction s
xidusn xidslot xidsqn
----- --------- ------
3 43 2397
ID1 转换 v$transaction.XIDUSN和v$transaction.XIDSLOT
SQL> SELECT trunc(196651/power(2,16)) XIDUSN FROM dual;
XIDUSN
----------
3
SQL> SELECT bitand(196651,to_number('ffff','xxxx'))+0 XIDSLOT FROM dual;
XIDSLOT
----------
43
3. 新开启2个session
sid=152
执行SQL> update test set id=3 where id=1;
处于等待状态
sid=131
执行SQL> update test set id=3 where id=1;
处于等待状态
4. 查看等待对象
SELECT DECODE(request,0,'Holder: ','Waiter: ')|| sid sess, id1, id2, lmode, request, type
from v$LOCK
WHERE (id1, id2, type) IN (SELECT id1, id2, type FROM V$LOCK WHERE request>0)
ORDER BY id1, request
sid sess id1 id2 lmode request type
--------------- --------- ------ --------- ---------- -------
Holder: 133 196651 2397 6 0 TX
Waiter: 152 196651 2397 0 6 TX
Waiter: 131 196651 2397 0 6 TX
select OBJECT_ID,SESSION_ID,ORACLE_USERNAME,PROCESS,LOCKED_MODE
from v$locked_object;
object_id session_id oracle_username process locked_mode
------------ --------------- ------------------------- ----------- -----------------
57276 131 ECC_VIEW 11642 3
57276 133 ECC_VIEW 25171 3
57276 152 ECC_VIEW 25523 3
select s.SID, p.SPID
from v$process p, v$session s
where p.ADDR = s.PADDR
and s.SID = '133'
可以从os级kill掉相应的spid
参考文献:
1. http://space.itpub.net/471666/viewspace-310326
2. http://www.itpub.net/redirect.php?fid=2&tid=933295&goto=nextnewset
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/9252210/viewspace-608954/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/9252210/viewspace-608954/