Introduction

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.

1. Oracle Fixed size header
2. Oracle Variable size header
3. 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), 2^nd 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 !!

References:

Interested Transaction List (ITL) Waits Demystified

Oracle Real Application Cluster Handbook – By K. Gopalakrishnan