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 two for indexes and tables.
•\ MAXTRANS: The maximum size to which this structure may grow. In recent releases of Oracle, MAXTRANS is always set to 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 (so, if you have an 8KB block size, 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:
$ sqlplus eoda/foo@PDB1
SQL> set serverout on
SQL> create table t( x int primary key,y varchar2(4000));Table created.
SQL> insert into t (x,y)select rownum, rpad(‘‘,148,’‘)from dualconnect by level <= 46;46 rows created. SQL> select length(y),dbms_rowid.rowid_block_number(rowid) blk,count(), min(x), max(x)from tgroup 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 one (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 one 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 ”. 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:
SQL> create or replace procedure do_update( p_n in number )as pragma autonomous_transaction;l_rec t%rowtype;resource_busy exception;pragma exception_init( resource_busy, -54 ); beginselect *into l_recfrom twhere x = p_nfor update NOWAIT;do_update( p_n+1 );commit; exception when resource_busy then dbms_output.put_line( ‘locked out trying to select row ‘ || p_n ); commit; when no_data_found then dbms_output.put_line( ‘we finished – no problems’ ); commit;end;/ 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
SQL> exec do_update(1);
locked out trying to select row 38 PL/SQL procedure successfully completed.
Leave a Reply