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

23 dbms_output.put_line( 'we finished - no problems' );
24 commit;
25 end;
26 /
Procedure created.
The magic is on line 14 where we recursively call ourselves with a new primary key value to lock over and over.
If you run the procedure after populating the table with 148 character strings, you should observe:
EODA@ORA12CR1> exec do_update(1);
locked out trying to select row 38
PL/SQL procedure successfully completed.
This output shows that we were able to lock 37 rows but ran out of transaction slots for the 38th row. For this
given block, a maximum of 37 transactions can concurrently access it. If we redo the example with a slightly smaller
string we'll see that if finishes with no problems:
EODA@ORA12CR1> truncate table t;
Table truncated.
EODA@ORA12CR1> insert into t (x,y)
2 select rownum, rpad('*',147,'*')
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)
--------- ---------- ---------- ---------- ----------
147 23470 46 1 46
EODA@ORA12CR1> exec do_update(1);
we finished - no problems
PL/SQL procedure successfully completed..
This time we completed successfully—the difference a single byte makes! In this case, having the extra
46 bytes of space free on the block (each of the 46 strings was just one byte smaller) allowed us to have at least 9 more
transactions active on the block.
This example demonstrates what happens when many transactions attempt to access the same block
simultaneously—a wait on the transaction table may occur if there is an extremely high number of concurrent
transactions. Blocking may occur if the INITRANS is set low and there is not enough space on a block to dynamically
expand the transaction. In most cases, the default of 2 for INITRANS is sufficient, as the transaction table will
dynamically grow (space permitting), but in some environments you may need to increase this setting (to reserve
more room for slots) to increase concurrency and decrease waits.