Locking and Latching - Expert Oracle Database Architecture - page 225

Now, let's look at the state of the system at this point. This example assumes a single-user system; otherwise, you
may see many rows in V$TRANSACTION . Even in a single-user system, do not be surprised to see more than one row in
V$TRANSACTION , as many of the background Oracle processes may be performing a transaction as well.

EODA@ORA12CR1> select username,
2 v$lock.sid,
3 trunc(id1/power(2,16)) rbs,
4 bitand(id1,to_number('ffff','xxxx'))+0 slot,
5 id2 seq,
6 lmode,
7 request
8 from v$lock, v$session
9 where v$lock.type = 'TX'
10 and v$lock.sid = v$session.sid
11 and v$session.username = USER;

USERNAME SID RBS SLOT SEQ LMODE REQUEST
--------------- ---------- ---------- ---------- ---------- ---------- ----------
EODA 22 2 27 21201 6 0

EODA@ORA12CR1> select XIDUSN, XIDSLOT, XIDSQN from v$transaction;

XIDUSN XIDSLOT XIDSQN
---------- ---------- ----------
2 27 21201

The interesting points to note here are as follows:

LMODE is 6 in the V$LOCK table and the REQUEST is 0. If you refer to the definition of the
V$LOCK table in the Oracle Database Reference manual, you will find that LMODE=6 is an
exclusive lock. A value of 0 in the request means you are not making a request; you have
the lock.

• The

V$LOCK table is more of a queuing table than a lock
table. Many people expect four rows in V$LOCK since we have four rows locked. Remember,
however, that Oracle does not store a master list of every row locked anywhere. To find out if a
row is locked, we must go to that row.

• There is only one row in this table. This

ID1 and ID2 columns and performed some manipulation on them. Oracle needed to
save three 16-bit numbers, but only had two columns in order to do it. So, the first column ID1
holds two of these numbers. By dividing by 2^16 with trunc(id1/power(2,16)) rbs , and by
masking out the high bits with bitand(id1,to_number('ffff','xxxx'))+0 slot , I am able to
get back the two numbers that are hiding in that one number.

• I took the

RBS , SLOT , and SEQ values match the V$TRANSACTION information. This is my
transaction ID.
Now we'll start another session using the same username, update some rows in EMP , and then try to update DEPT :

• The

EODA@ORA12CR1> update emp set ename = upper(ename);
14 rows updated.

EODA@ORA12CR1> update dept set deptno = deptno-10;