How Oracle Locking Works
When a Lock isn't a lock!
In the last installment of this series "Thing your Probably didn ' t Know about Oracle" you learned how Oracle locks The rows of a table. This is what your learned in a nutshell:
(1) When a transaction modifies a record, the pre-change image was stored in the undo segments, which are required for Vario US things; The most important of which is to provide a read consistent version of the row when another sessions wants it.
(2) The transaction is assigned a transaction identifier this shows the undo segment number, slot# and record of the undo Information.
(3) The transaction locks the rows (since it did not commits) by placing a special type of data in the block header known a s interested Transaction List (ITL) entry. The ITL entry shows the transaction ID and other information.
(4) When a new transaction wants to update the same rows (locked by the previous transaction) it checks the ITL entries in The block-a-to-check if there is a lock.
(5) Since The lock information of rows are stored in the "block itself" and the ITL entries in the blocks refer to the locks On the "rows in" block alone, there are no need to have a and dispense the Locks. This makes the locking process isn't only immensely scalable but feasible as down since there is no theoretical limit to the Number of locks.
[Updated 22, 2011] [Thank, Randolph Geist (info@www.sqltools-plusplus.org) for pointing it out. I follow his blog http://oracle-randolf.blogspot.com/and which is a treasure trove of information.
(6) The information that a row are locked is stored along with the row in the form of a lock byte.
[End of Update 22, 2011]
While the article might have answered some of the vexing questions your may have had or needed some on the clarity You are were somewhat familiar with, I sincerely hope it has piqued the curiosity to learn the more about this even. If I am Successful in explanation and now you should is satisfied, you are should have more questions. If you don ' t have any, then I completely failed to my explanation.
So, what are the questions? For starters, how did you know what objects being locked in the transaction? It ' s actually quite trivial. The view V$lock has provided that information for years, albeit in a convoluted form. A new view v$locked_object is a bit more user-friendly. Let ' s examine this with an example. A row of the update a:
sql> Update itltest Set col2 = ' CHANGED by session AGAIN ' WHERE col1 = 221
2/
1 row updated.
We can check the transaction ID:
Sql> Select dbms_transaction.local_transaction_id from dual '
local_transaction_id
--------------------- -----------------------------------------------------------
2.16.41316
1 row selected.
As you learned from the previous installment into this series, the transaction ID is a series of numbers denoting undo seg ment number, slot# and record# (also known as sequence#) respectively, separated by periods.
Now, check the view v$locked_object:
Sql> SELECT * FROM V$locked_object
2/
xidusn xidslot xidsqn object_id session_id
---------- ----------------------------------------
oracle_username os_user_name
------------------------- -----------------------------------
PROCESS locked_mode
-----------------------------------
2 41316 95263
ARUP Oracle
13181 3
The view shows undo segment# (Xidusn), Undo slot# (Xidslot) and undo rec# (XIDSQN), which can is used to construct the TRA Nsaction ID to is joined with the v$transaction to get the details. The view contains the column object_id. Another Important column is Locked_mode, which shows the "MODE" Rows are LOCKED. In this case, it's ' 3 ', which means Row Exclusive. This is the script this decodes the modes as the as as the reports the object name.
Select owner Object_owner, object_n Ame object_name, session_id  ORACLE_SID, & nbsp; oracle_username Db_user, decode (Locked_mode, & nbsp 0, ' None ', 1, ' Null ', 2, ' Row Share ', 3, ' Row Exclusive ', 4, ' Share ', 5, ' Sub Share Exclusive ', 6, ' Exclusive ', locked_mode   ; ) Locked_mode from V$locke D_object Lo, dba_objects do where (xi dusn| | '. ' | | xidslot| | '. '|| XIDSQN) = (' &transid ') and &NBSP ;
do.object_id = lo.object_id/
Save this script and execute it in need further details on the transaction. The script'll ask for the transaction ID which to the can pass in the format reported by Dbms_transaction.local_transaction _id.
Next, you draw I attention to the point #3 above. If there are records in the blocks and a transaction updated (and therefore locked) all ten of them, how many ITL entrie s would be used–one or ten?
Good question (I have to say so, since I asked that:) I Suppose can answer that yourself. Ten ITL slots may feasible; But what if the block has 10,000 records? Are it possible to have that many ITL slots in the block header? Let's ponder on ' this for a second. There'll be two the big issues and that many ITL slots.
A, each ITL slot, by the way, is bytes long. So, 10000 slots'll take up 240,000 bytes or almost KB. A typical Oracle block is 8KB (I know, it could being 2K, 4K or 16K; but suppose it is the default 8K). Of course it can ' t accommodate 22KB.
Second, even if the total size of the ITL slots are less than the size of the block, where would be the room to hold data? In addition, there should is some space for the data block overhead; Where would that spaces come from?
Obviously, these are genuine problems which make one ITL slot per row impractical. Therefore Oracle does not create a ITL entry for each locked row. Instead, it creates the ITL entry for each transaction, which may have updated a number of rows. Let me repeat That–each ITL slot in the blocks header actually refers to a transaction; Not the individual rows. That's the reason why you won't find the rowid of the rows locked in the ITL slot. Here's the ITL entry from the block header, again:
Itl Xid Uba Flag Lck scn/fsc 0x01 0x000a.019.00007c05 0x00c00288.1607.0e
----1 FSC 0x0000.00000000
0x02 0x0003.017.00009e24 0x00c00862.190a.0f C---0 SCN 0x0000.02234e2b
There is a reference to a transaction ID; But not rowid. When a transaction wants to update a row in the block, it checks the ITL entries. If There is none, the it means rows in this block are unlocked. However, if there are some ITL entries, does it mean that some rows into the block are locked? Not necessarily. It simply means that the rows of the block were locked earlier; But that lock may or may is active now. To check if a row was locked, the transaction checks for the lock byte stored along with the row.
That's brings up a interesting question. If presence of an ITL slot does not mean a record in the ' block is locked ' when does the ITL slot get cleared so it CA n is reused, or when does that ITL slot disappear? Shouldn ' t that ITL slot disappear when the transaction ends by commit or rollback? That should is the next burning question throbbing in your head right now.
Clearing of ITL Slots
To answer so question, consider this SCENARIO:A transaction updates 10000 records, on 10000 different. Naturally there would be 10000 ITL slots, one on each block and all pointing to the same transaction ID. The transaction commits; And the locks are released. Should Oracle revisit each block and remove the ITL entry corresponding to the transaction as a part of the commit Operati On?
If that were the processing logic, the commit would have taken a very long. Acquiring the buffers of the 10000 blocks and updating the ITL entry won't be quick; It'll take a very long time, prolonging the commit processing. From Part 1 of the series, your learned that commit processing are actually very quick, with a flush of the the log buffer T o Redo logs and the writing of the commit marker in the redo stream. Even a checkpoint to the datafiles are not do as a part of commit processing–all the effort going towards making the PR Ocess fast, very fast. Had Oracle added the logic of altering ITL slots, the commit processing would have-been potentially long, very long. Therefore Oracle does not remove the ITL entries after this transaction ends (by committing, or rolling back); The slots are just left behind as artifacts.
The proof, as they say, is in the pudding. Let ' s-with-an example:
Sql> CREATE TABLE Itltest (col1 number, col2 varchar2);
Table created.
Sql> begin
2 for I-1..1000 loop
3 insert INTO itltest values (
4 I, ' INITIAL VALUE of COLUMN ');
5 end Loop;
6 end;
7/
Pl/sql procedure successfully completed.
Sql> commit;
Commit complete.
This inserts 1000 records. Let's find out the file and blocks records go to:
1 select 2 DBMS_ROWID.ROWID_RELATIVE_FNO (ROWID) file#, 3 Dbms_rowid.rowid_block_number (ROWID) block#, 4 COUNT (1) 5 From Itltest 6 group by 7 DBMS_ROWID.ROWID_RELATIVE_FNO (ROWID), 8 Dbms_rowid.rowid_block_number (ROWID) 9 order by 10* 1,2 SQL& Gt / file# block# COUNT (1)------------------------------ &NB Sp 7 4027 117 7 4028 223 7 4029 &N bsp;220 7 4030 220 & nbsp 7 4031  
220 5 rows selected.
Let's identify the rows in a specific blocks, block# 4028, for instance.
