第一段理解:
TM
For the lock type TM (table lock), column ID1 is the object_id (or the
data_object_id) for that table (could be joined with DBA_OBJECTS).
TX
For a
lock of type TX (transaction lock), TRUNC( ID1 / 65536 ) shows which
rollback segment that is used for the transaction and ID1 -
( rollbacksegment# * 65536 ) shows which slot number in the transaction
table that this sessions is using (or want to used).
第二段理解:
TM
id1:被修改表的标识(object_id)
id2:0
TX
id1:以十进制数值表示该事务所占用的回滚段号与该事务在该回滚段的事务表(Transaction table)中所占用的槽号(slot number,可理解为记录号)。其组成形式为: 0xRRRRSSSS ( RRRR = RBS number, SSSS = slot )。
id2:以十进制数值表示环绕(wrap)次数,即该槽(slot)被重用的次数。
第三段理解:
TX
TX的id1经过分解对应V$transaction 的 XIDUSN,XIDSLOT。
id2对应 XIDSQN。
上面的三段理解实际是一个含义,TX锁所在的ID实际是由回滚段号和槽号组成,可分解成V$TRANSACTION的XIDUSN和XIDSLOT字段,ID1/65536就等于V$TRANSACTION.XIDUSN。ID2是事务对应的槽号所使用的次数,对应V$TRANSACTION.XIDSQN字段。
下面通过多个会话事务向主键表插入重复值的例子讨论ID1和ID2的含义:
1).表结构:
SQL> desc x1
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER
NAME VARCHAR2(20)
其中ID是主键。
2).会话1:
SQL> insert into x1 values(5,'SB');
1 row created.
3).会话2:
SQL> insert into x1 values(5,'SBB');
由于插入了ID=5的重复值,执行被卡住。
4).会话3:
SQL> select sid,type,id1,id2,lmode,request,block from gv$lock where (id1,id2) =(
select id1,id2 from gv$lock where block=1); 2
SID TY ID1 ID2 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
141 TX 196612 1240 6 0 1
16 TX 196612 1240 0 4 0
SQL> select sid,blocking_instance,blocking_session from v$session where blocking_instance is not null;
SID BLOCKING_INSTANCE BLOCKING_SESSION
---------- ----------------- ----------------
16 1 141
SQL> select sid,type,id1,id2,lmode,request,block from gv$lock where sid=141;
SID TY ID1 ID2 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
141 AE 100 0 4 0 2(11gR2新出现的锁)
141 TX 196612 1240 6 0 1
141 TM 74637 0 3 0 2
V$TRANSACTION视图:
SQL> select xidusn,xidslot,xidsqn,xid from v$transaction ;
XIDUSN XIDSLOT XIDSQN XID
---------- ---------- ---------- ----------------
10 5 883 0A00050073030000
3 4 1240 03000400D8040000
从上面的执行可以观察出以下几点:
1.会话141阻塞了会话16。
2.TM表锁的ID2始终等于0。
3.会话16的REQUEST等于4,表明该会话在等待被TX锁住的其他资源。
4.会话141和会话16的ID1和ID2完全相同,这ID1和ID2表示的是BLOCK=1的事务的ID1和ID2。根据这个特性通常查找一个会话或一个数据库阻塞的锁就可以执行以下的两条SQL:
SELECT * FROM V$LOCK WHERE (ID1,ID2) IN(SELECT ID1,ID2 FROM V$LOCK WHERE SID=&SID);
SELECT * FROM V$LOCK WHERE (ID1,ID2) IN (SELECT ID1,ID2 FROM V$LOCK WHERE BLOCK=1);
5.会话141的ID1=196612,196612/65536=3=V$TRANSACTION.XIDUSN,会话141的ID2=1240=V$TRANSACTION.XIDSQN。
METALINK文章:
VIEW: "V$LOCK" Reference Note [ID 29787.1] | |||||
| |||||
修改时间 12-JAN-2011 类型 REFERENCE 状态 PUBLISHED |
"V$LOCK" Reference Note
- The information here contains the view description from the Oracle9i documentation along with any additional Useful Join Columns: SID - Join to <> . SID Support Notes: The meaning of ID1 and ID2 depend on the lock TYPE. --------------------------------------------------------------------------- Most Common Lock Types: ---------------------------------------------------------------------------
TYPE Name ID1 ID2 ~~~~ ~~~~ ~~~ ~~~ TX Transaction Decimal RBS & slot Decimal WRAP number (0xRRRRSSSS RRRR = RBS number, SSSS = slot ) A TX lock is requested in eXclusive mode if we are waiting on a ROW. A SHARE mode request implies we are waiting some other resource held by the TX (Eg: waiting for an ITL entry)
TM Table Locks Object id of table. Always 0. TS Temp Segment TS# Relative DBA ST Space Transaction Only ONE enqueue. UL User Locks --------------------------------------------------------------------------- Lock Conversion Summary --------------------------------------------------------------------------- Known Issues: Articles:
请注意METALINK文章中加红的部分。参考的讨论:http://www.itpub.net/thread-388459-1-1.html
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/23135684/viewspace-715468/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/23135684/viewspace-715468/