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

Now, if we commit our original transaction, SID=22 , and rerun our lock query, we find that the request row has gone:

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 8 17 21403 6 0

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

XIDUSN XIDSLOT XIDSQN
---------- ---------- ----------
8 17 21403

The request row disappeared the instant the other session gave up its lock. That request row was the queuing
mechanism. The database is able to wake up the blocked sessions the instant the transaction is completed. There
are prettier displays with various GUI tools, but in a pinch, having knowledge of the tables you need to look at is
very useful.
However, before we can say that we have a good understanding of how the row locking in Oracle works, we must
look at one last topic: how the locking and transaction information is managed with the data itself. It is part of the
block overhead. At the top of a database block is some leading overhead space in which to store a transaction table for
that block. This transaction table contains an entry for each real transaction that has locked some data in that block.
The size of this structure is controlled by two physical attribute parameters on the CREATE statement for an object:

•

INITRANS : The initial, preallocated size of this structure. This defaults to 2 for indexes and tables.

•

MAXTRANS : In Oracle 10 g and above, MAXTRANS is always 255.

Each block starts life with, by default, two transaction slots. The number of simultaneous active transactions that
a block can ever have is constrained by the value of MAXTRANS and by the availability of space on the block. You may
not be able to achieve 255 concurrent transactions on the block if there is not sufficient space to grow this structure.
We can artificially demonstrate how this works by creating a table with lots of rows packed into a single block
such that the block is very full from the start; there will be very little room left on the block after we initially load our
data. The presence of these rows will limit how large the transaction table can grow, due to the lack of space. I was
using an 8KB block size and I tested this particular example in all versions of Oracle from 9 i Release 2 through 12 c
Release 1 with the same results (so, if you have an 8KB blocksize, you should be able to reproduce this). We'll start by
creating our packed table. I played around with different lengths of data until I arrived at this very special size:

EODA@ORA12CR1> create table t
2 ( x int primary key,
3 y varchar2(4000)
4 )
5 /
Table created.