Database Reference
In-Depth Information
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. In Chapter 10, we'll get into the details of the block format, but suffice it to say that 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 : The maximum size to which this structure may grow. It defaults to 255 and has a
minimum of 2 practically. In Oracle 10 g and above, this setting has been deprecated , so it no
longer applies. MAXTRANS is always 255 in that release and later.
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
 
Search WWH ::




Custom Search