Lock Types - Oracle Database Transactions and Locking Revealed - page 31

We're now blocked in this session. If we run the V$ queries again, we see the following:
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 17 2 27 21201 0 6
EODA 22 2 27 21201 6 0
EODA 17 8 17 21403 6 0
EODA@ORA12CR1> select XIDUSN, XIDSLOT, XIDSQN from v$transaction;
XIDUSN XIDSLOT XIDSQN
---------- ---------- ----------
2 27 21201
8 17 21403
What we see here is that a new transaction has begun, with a transaction ID of (8,17,21403). Our new session,
SID=17 , has two rows in V$LOCK this time. One row represents the locks that it owns (where LMODE=6 ). It also has a
row that shows a REQUEST with a value of 6. This is a request for an exclusive lock. The interesting thing to note here is
that the RBS/SLOT/SEQ values of this request row are the transaction ID of the holder of the lock. The transaction with
SID=22 is blocking the transaction with SID=17. We can see this more explicitly simply by doing a self-join of V$LOCK :
EODA@ORA12CR1> select
2 (select username from v$session where sid=a.sid) blocker,
3 a.sid,
4 ' is blocking ',
5 (select username from v$session where sid=b.sid) blockee,
6 b.sid
7 from v$lock a, v$lock b
8 where a.block = 1
9 and b.request > 0
10 and a.id1 = b.id1
11 and a.id2 = b.id2;
BLOCKER SID 'ISBLOCKING' BLOCKEE SID
--------------- ---------- ------------- --------------- ----------
EODA 22 is blocking EODA 17