Database Reference
In-Depth Information
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.
EODA@ORA12CR1> insert into t (x,y)
2 select rownum, rpad('*',148,'*')
3 from dual
4 connect by level <= 46;
46 rows created.
EODA@ORA12CR1> select length(y),
2 dbms_rowid.rowid_block_number(rowid) blk,
3 count(*), min(x), max(x)
4 from t
5 group by length(y), dbms_rowid.rowid_block_number(rowid);
LENGTH(Y) BLK COUNT(*) MIN(X) MAX(X)
---------- ---------- ---------- ---------- ----------
148 23470 46 1 46
So, our table has 46 rows, all on the same block. I chose 148 characters because if it was one character more, we'd
need two blocks to hold these same 46 records. Now, we need a way to see what happens when many transactions try
to lock data on this single block simultaneously. For that, we'll use an AUTONOMOUS_TRANSACTION again, just so we can
use a single session and not have to run lots of concurrent SQL*Plus sessions. Our stored procedure will lock a row
in the table by the primary key starting with a primary key value of 1 (the first record inserted). If our procedure gets
the lock on this row without having to wait (without getting blocked), it will simply increase the primary key value by
1 and, using recursion, do it all over again. So, the second call will try to lock record 2, the third call record 3, and so
on. If the procedure is made to wait, it will raise an ORA-54 resource busy error and we'll print out “locked out trying
to select row <primary key value>”. That will indicate we ran out of transaction slots on this block before we ran out of
rows to lock. On the other hand, if we find no row to lock, that means we've already locked every row on this block and
we print out success (meaning, the transaction table in the block header was able to grow to accommodate all of the
transactions). Here is that stored procedure:
EODA@ORA12CR1> create or replace procedure do_update( p_n in number )
2 as
3 pragma autonomous_transaction;
4 l_rec t%rowtype;
5 resource_busy exception;
6 pragma exception_init( resource_busy, -54 );
7 begin
8 select *
9 into l_rec
10 from t
11 where x = p_n
12 for update NOWAIT;
13
 
Search WWH ::




Custom Search