This post gives a brief idea about how Oracle locks the rows on behalf of the transactions. Before going further on locking, it will be good for you to check the previous post on transaction management to understand the isolation levels of Oracle. Here we will discuss an important area present inside Oracle block that is ITL (Interested Transaction List).
Imagine the huge amount of data present in Oracle database and transactions are continuously jumping on database to get the data. If the locking and concurrency system of oracle is made centralized then imagine how bad the performance of oracle database would be. Because at this level of scalability it is impossible to centralize the locking mechanism for database. That’s the reason oracle make a intelligent decision of decentralizing the locking mechanism. In Oracle database, we dont have a central lock manager and locks on data is managed at block level. Every block is responsible for providing the locks on rows that it maintains. This keeps up the performance of database.
Now lets consider a database is up and running and a transaction hit the database to one of the data block. How does the data block grant the locks? Where will it store the locking information for the rows the transaction is asking for? Here comes the structure – Interested Transaction List (ITL). This is a small structure in oracle block, which holds the information about the list of transactions, which are interested in some of the rows of that block. That’s why it’s called Interested Transaction List. ITL is present in the variable portion of the Oracle block. To understand the exact location of ITL, lets have a brief explanation about the structure of database block.
Oracle Data block is divided into 3 major portions.
- Oracle Fixed size header
- Oracle Variable size header
- Oracle Data content space
Here, Oracle fixed size header is at the top of data block, followed by Oracle variable size header and then left over space is for Oracle data content as shown in the following diagram.
At the end we have block consistency checking. Variable header info grows from the top down (just below the fixed header) if necessary and rows are inserted from the bottom up (just above the tail). ITL (Interested Transaction List) resides in variable portion of data block header. It is this portion of data block, which holds the information about locking.
This variable portion of the data block contains slots for transactions to put the locking information. When a row in the block is locked for the first time, the transaction places a lock in one of the slots with the rowid of the row that is locked. In other words, the transaction makes it known that it is interested in the row (hence the name “Interested Transaction List”). When the same transaction or another one locks another row, the information is stored in another slot, and so on.
The next logical question that comes up is, how many slots are typically available? During the table creation, the INITRANS parameter defines how many slots are initially created in the ITL. When the transactions exhaust all the available slots and a new transaction comes in to lock a row, the ITL grows to create another slot. The ITL can grow up to the number defined by the MAXTRANS parameter of the table, provided there is space in the block. Nevertheless, if there is no more room in the block, even if the MAXTRANS is high enough, the ITL cannot grow.
What if there are not enough slots available in ITL? What will happen? Obviously the new transaction asking for the lock on the rows have to wait until the existing transaction is finished and frees up the slot. This is called ITL wait event. Once the existing transaction finishes and frees up the slot, the new transaction can proceed.
ITL Wait in detail
Let’s see this description of the wait in action. Assume our table has INITRANS of one and MAXTRANS 11. A typical data block right after the creation of the table will look like figure 1 below.
Since the INITRANS is one, there is only one slot for the ITL. The rest of the block is empty. Now we inserted three rows into the table. These will go into this block, and the block will look like figure 2.
Note how the empty space is reduced. At this point, a transaction called Txn1 updates Row1, but does not commit. This locks Row1, and the transaction places the lock in the slot number one in the ITL as shown in figure 3.
Then another transaction, Txn2, updates the row Row2 and wants to lock the row. However, there are no more slots in the ITL available to service the transaction. The MAXTRANS entry is 11, meaning the ITL can grow up to 11 slots and the block has empty space. Therefore, ITL can grow by another slot and Slot number two is created and allocated to Txn2 (refer to figure 4).
Now the empty space in the block is severely limited, and it will not be able to fit another ITL slot. If at this time another transaction comes in to update the row three, it must have a free slot in the ITL. The MAXTRANS is 11 and currently only two slots have been created, so another one is possible; but since there is no room in the block to grow, the slot can’t be created. Therefore, the Txn3 has to wait until either of the other transactions rolls back or commits and the slot held by it becomes free. At this time the session will experience an ITL waits event as seen from the view V$SESSION_WAIT.
ITL structure in detail
Every ITL slot occupies 24 Bytes of free space in variable header portion of data block. Maximum number of slot is controlled by MAXTRANS parameter. However the size of variable part of data block header cannot exceed 50% of block size. This puts the upper limit on the number of ITL allowed. If we put very high value for MAXTRANS it wont be able to fulfill the same because size of variable header will grow beyond limit.
An ITL contains the unique transaction ID (XID), which is a pointer to an entry in transaction table to rollback segment. Any transaction who want to execute DML statement on the rows present in the data block should get an ITL slot before it can proceed. An ITL entry consist of an transaction ID (XID), undo block address (UBA), flags showing the status of transaction and lock count which gives the number of rows locked by this transaction. XID uniquely identifies the transaction and gives undo information for that transaction. Following figure explains the relation between ITL entries the rows locked and the transaction table in rollback segment.
While the transaction commits, Oracle completes the bare minimum task it has to do since Oracle is optimized for maximum throughput. This is called fast commit. It will update the status of transaction in the transaction table to rollback segment and it will not visit the block again. So commit is just going to change the flag in transaction table while the actual values to be changed (data values) are already updated by transaction when it was in progress. During the time when the transaction is in progress the ITL entry is called open ITL. If the instance crashed before the transaction is committed or roll backed a transaction recovery is performed using the data from rollback segments.
While a transaction is in progress and having an open ITL, if another transaction wants to select the data, then to get the consistent read (CR), 2nd transaction looks up the transaction table in rollback segment to find the status of transaction. If the transaction is uncommitted, the second transaction will create a copy of data present in memory (This data might have been changed by first transaction) and will apply the undo data present in rollback segments to get the consistent read data. If the status in transaction table shows the transaction as committed then the transaction is deemed committed. In that case rows are no longer locked by transaction, but the lock byte in row header is not cleared until next DML is performed on the block. The lock byte cleanout is piggybacked with DML operation. The block cleanout is piggybacked by some time interval because of fast commit. This cleanout operation closes the open ITL for committed transactions and generate redo information, as block cleanout may involve updating block with new SCN. This is why we see redo generation for some select statement.
So in short when we commit the transaction only the status flag in transaction table of rollback segment is updated and marked that transaction is committed. Next time when a new DML or select statement check for the header in data blocks, it sees that those rows are getting updated and also gets the undo block address. When that new transaction checks for that undo block address in transaction table of rollback segment, it finds that the transaction is committed (or rolled backed) and then that new transaction updated the block header and also clears the lock byte in row header. This generates some redo information.
Hope this helps !!
Interested Transaction List (ITL) Waits Demystified
Oracle Real Application Cluster Handbook – By K. Gopalakrishnan
12 thoughts on “Interested Transaction List (ITL)”
MAXTRANS parameter starting from 10G is deprecated.Whatever value we set it is completely ignored by oracle.
So if we set maxtrans even 1 would not cause any other session encountering any lock.
“The ITL can grow up to the number defined by the MAXTRANS parameter of the table, provided there is space in the block.”
does not hold for 10g and above.
Can you please give me more idea in the link between these transaction table, rollback segement worked with Commit and rollback and how the clear the locks….
This is a great explanation of a complicated topic. I like it. Thanks for sharing your knowledge!
its is an excellent explanation for ITL
Thank you for this explanation… I’m waiting response from a TAR/SR just to get the amount of bytes an ITL slot takes. Plus want to share something that happened when playing with INITRANS, seems that upto 10g2 DB blocks get temporarily corrupted… tell you no more, please follow this link http://oracledisect.blogspot.com/2009/10/how-to-innocently-corrupt-data-table.html.
I have two questions, which I believe are related, therefore I adress both of them in this one message.
1) You write “When the same transaction or another one locks another row, the information is stored in another slot, and so on.”
-> This would imply that a transaction could update at most as much rows in a block as there are available ITL slots. This seems quite restrictive to me and also I wasn’t able to verify that in corresponding experiments I performed. Could you please confirm if my conclusion is correct or tell me what I misunderstood.
2) You also write “An ITL entry consist of an transaction ID (XID), undo block address (UBA), flags showing the status of transaction and lock count which gives the number of rows locked by this transaction.”
-> From this statement I understand that one ITL entry can lock several rows. How is the ITL entry linked to the rows locked by this entry? As an ITL slot 24 is bytes large, I don’t see the possibility for a list of rowids being stored within the ITL slot, so how does Oracle keep track of which rows are locked by a given ITL slot?
Thank you for your help
I have an addition to point 1) in my comment from February 17.
The way I understand the explanation above, there may even result a deadlock, if one transaction tries to update more rows than ITL slots are available. Assume there is only one transaction updating a block and the block has enough free space to hold MAXTRANS ITL slots. The transaction can thus update MAXTRANS rows. When trying to update another row, there are no more ITL slots available, so the session has to wait – as all ITL slots are occupied by this same session however, the session will wait indefinitely. So I am quite sure, that I missunderstood something.
you said “The lock byte cleanout is piggybacked with DML operation.” and “If the status in transaction table shows the transaction as committed then the transaction is deemed committed.”
what happens if a user query one block which has an open ITL at moment t0, another DML transaction was commited on the same block at moment t -1, the block is still in memory because of querys, the undo retention was not set correctly and the info about that commited transaction is lost, how oracle will know if the rows modified in the bloc are commited (the ones for wich the ITL slot was open)?
is oracle updating the flag of transaction in the ITL slot every time the transaction is commited? and only the lock byte is “waiting” for a DML?
I didn’t get you completely here. But I think you are saying what will happen if the undo gets overwritten and query is accessing the block has open ITL slot pointing to UNDO.
Well, in that case I guess before undo gets overwritten the ITL slot will be closed.
You mentioned that “When the SAME transaction or another one locks another row, the information is stored in another slot, and so on…”, does it mean the 1 transaction ( the word the SAME transaction )
will occupied more than one ITL slots if it modify more the one rows in the data block; I really doubt it although I am not so sure, can you please confirm?
I had the same question when reading this article (see my questions from February 17 and April 19 2010).
Now I know that one transaction will only occupy one ITL slot in a block, no matter how many rows it modifies in that block. The row itself contains a lock byte, which is a pointer into the ITL, indicating which ITL entry (and therefore transaction) is currently locking the row. You find a description of this in http://www.ixora.com.au/q+a/cr.htm or in the excellent book “Oracle Core” from Jonathan Lewis (chapters 2 and 3).
Imagine if it was true that a transaction occupies one ITL slot per row it modifies in the block. A corollary to this would be that a transaction might modify at most as many rows in a block as there are available ITLs. Depending on the INITRANS/MAXTRANS values of the segment and depending on how full the block is, this might be only very few rows. This would be a functional limitation, which I believe would not be acceptable to most applications.
Thanks Martin, actually I also did some testing with the block dump, and it’s 100% confirmed a
transaction only occupies 1 ITL slot regardless of the number of rows beeing modified.