Database Reference
In-Depth Information
14 do_update( p_n+1 );
15 commit;
16 exception
17 when resource_busy
18 then
19 dbms_output.put_line( 'locked out trying to select row ' || p_n );
20 commit;
21 when no_data_found
22 then
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.
