The meaning of Id1,id2 in V$lock

Original from: http://space.itpub.net/519536/viewspace-693689

The V$lock view is often used when diagnosing common failures such as locks waiting for such a database, and the meaning of ID1 and ID2 in this view is often mentioned.   Understanding the meaning of these two fields is significant to understanding the V$lock view. This paper simply describes and explores the meaning of ID1 and ID2 in V$lock view.

1.Oracle Description in the official document
Reference Link: http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/dynviews_1147.htm#i1407629
Only the following two obscure messages are recorded:
ID1 number Lock identifier #1 (depends on type)
ID2 number Lock identifier #2 (depends on type)

Such a description is not sufficient and sufficient. We ask for a very solution.

description in 2.MOS
The meanings of ID1 and ID2 in the V$lock view are described below in MoS 29787.1, and are described in more clear terms for reference.
The meaning of ID1 and ID2 depend on the lock TYPE.

---------------------------------------------------------------------------
Most Common Lock Types:
---------------------------------------------------------------------------
TYPE Name ID1 ID2
~~~~  ~~~~                  ~~~                     ~~~
TXTransaction 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)

TMTable Locks Object ID of table. Always 0.

TSTemp Segment ts# Relative DBA

STSpace Transaction only one enqueue.
ULUser Locks

3. Examples illustrate the meaning of ID1 and ID2 in TM table-level locksAs an example of the most common TM table-level locking, ID1 indicates that the OBJECT_ID,ID2 of the object being locked is "0" at this time.
1) Analog TM tag lock
(1) Create a test table T
Sec@ora10g> CREATE TABLE T (x varchar2 (8));
Table created.

(2) Inserting a piece of data into the table T
Sec@ora10g> INSERT into t values (' Secooler ');
1 row created.

2) Get lock information
The SID for the current session is 140, and to avoid querying for other information, qualify here.
sec@ora10g> Select Sid, type, ID1, id2 from V$lock where sid=140;

SID TYPE ID1 ID2
---------- -------------------- ---------- ----------
140 TM 10981 0
140 TX 655404 260

3 to locate the database object information of the operation according to the value of ID1
Sec@ora10g> Select Owner,object_name,object_id,object_type from dba_objects where object_id=10981;

OWNER object_name object_id object_type
------    -------------    ---------- -------------
SEC T 10981 TABLE

OK, to verify, here 10981 corresponds to the database object is our operation of the table T.

4. Examples illustrate the meaning of ID1 and ID2 when TX transaction locks
When the lock type is a TX transaction lock, the meanings of ID1 and ID2 are as follows:

ID1 corresponds to the xidusn field in the View v$transaction (undo segment Number: The undo segment sequence numbers for the transaction) and the xidslot field (Slot number: The slot numbers for the transaction). Among them, the high 16-bit ID1 is XIDUSN, and the low 16-bit is xidslot.

ID2 the corresponding view in the V$transactionxidsqn Field(Sequence number: The serial numbers for the transaction).

1) still take the record of the TX lock in the lock information above as an example to explain.
Excerpt information is as follows:

SID TYPE ID1 ID2
---------- -------------------- ---------- ----------
140 TX 655404 260

2 Use ID2 value "260" to retrieve v$transaction view
Sec@ora10g> Select Xidusn,xidslot,xidsqn from v$transaction where xidsqn=260;

Xidusn Xidslot xidsqn
---------- ---------- ----------
10 44 260

3) 10 and 44 corresponding to the value of the ID1 "655404" method
10*2^16+44=655404

4 The Xidusn and Xidslot methods are calculated using ID1 value
Sec@ora10g> Select Trunc (655404/power (2,16)) Xidusn from dual;

Xidusn
----------
10

Sec@ora10g> Select Bitand (655404,to_number (' ffff ', ' xxxx ')) +0 xidslot from dual;

Xidslot
----------
44

This is the simple and complex relationship between them.

5. Summary
For the application of V$lock, please refer to the article "experiment" "lock" "Lock Wait" simulation, diagnosis and processing method (http://space.itpub.net/519536/viewspace-605526)

As the most commonly used dynamic performance view V$lock, each of its fields should be thoroughly understood and explored. In order to be confident, with less effort.

More discussion: http://www.itpub.net/thread-973766-1-1.html
--The end--