All the Views expressed here are my own and do not reflect opinions or views of the anyone else.All the views are tested on my testing environment and kindly test the post before applying anything on production.You can reach to me at neeraj.vishen@gmail.com .

Tuesday, May 29, 2012

Interested Transaction List ( ITL ) in Oracle

Few days back, I received an e-mail from a reader , who is having doubt on locking mechanism i.e, related to ITL . This post will give some good concept about the ITL . Let's start from the beginning i.e, from the transaction .

Transaction : A transaction starts when an update to data such as insert, update or delete occurs (or the intention to do so, e.g. SELECT FOR UPDATE) and ends when the session issues a commit or rollback. Like everything else, a specific transaction should have a name or an identifier to differentiate it from another one of the same type. Each transaction is given a transaction ID.

Locks : In multi-user systems, many users may update the same information at the same time. Locking allows only one user to update a particular data at a time , another person cannot modify the same data. When any user start the transaction then oracle automatically locks the data . Oracle uses a different locking method then most other databases, Oracle locking policy consists of the following :

Oracle locks data at the row level on modification only. There is no lock escalation to a block or table level, ever.

Oracle never locks data just to read it. There are no locks placed on rows of data by simple reads.

A writer of data does not block a reader of data .

A writer of data is blocked only when another writer of data has already locked the row it was going after.

What is Interested Transaction List (ITL) ?

It is a simple data structure called "Interested Transaction List" (ITL), a list that maintains information on transaction. The transaction identifier will be stored as an entry in the ITL in the header of the data block. The ITL contains several placeholders (or slots) for transactions. When a row in the block is locked for the first time, the transaction places a lock in one of the slots. In other words, the transaction makes it known that it is interested in some rows (hence the term "Interested Transaction List").

When a different transaction locks another set of rows in the same block, that information is stored in another slot and so on. When a transaction ends after a commit or a rollback, the locks are released and the slot which was used to mark the row locks in the block is now considered free .

ITL slots are required for every transaction, it contains the transaction ID (XID) which is a pointer to an entry in the transaction table of a rollback segment. We can still read the data but other processes wanting to change the data must wait until the lock is released (commit or rollback). The ITL entry contains a XID, undo byes address (UBA) information, flags indicating the transaction status (Flag) and lock count (Lck) showing the number of rows locked by this transaction within the block and SCN at which the transaction is updated.

How many slots are typically available in ITL ?

During the table creation, the INITRANS parameter defines how many slots are initially created in the ITL. INITRANS is a block level storage parameter which can be specified while creating an object (table). INITRANS and MAXTRANS parameters are used to control the concurrent access to the same block . 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. the maximum value for MAXTRANS parameter is 255.

Each ITL entry in the block transaction variable header takes 24 bytes. Though a block can have a maximum of 255 different ITL entries, the ITL allocation is limited by block size. The database block size plays an important role when allocating the number of inital ITL entries for the block. The rule is "the total size allocated for initial ITLs SHOULD be LESS THAN 50% of the database block size" .

i.e, sizeof(INITIAL ITLs) < ( 50% of the DATABASE BLOCK SIZE )

BLOCK SIZE(B) NO OF ITL entries allocated in block header

============ =============================

2048 41

4096 83

8192 169

What happens when a transaction does not find a free slot to place its lock information?

This can occur because either

1.) the block is so packed that the ITL cannot grow to create a free slot, or

2.) the MAXTRANS has already been reached. In this case, the transaction that needs to lock a row has to wait until a slot becomes available. This wait is termed as ITL waits and can be seen from the view v$session_wait, in which the session is waiting on an event named "enqueue" Since the INITRANS is one, there is only one slot for the ITL. The rest of the block is empty.

How to Diagnose the ITL Wait How do we know that a segment is experiencing ITL waits?

To check for ITL waits, set up the STATISTICS_LEVEL to TYPICAL in init.ora or via ALTER SYSTEM, then examine the segment statistics for the waits.

When we suspect that a database is suffering from these waits, query the view v$session_wait. If the event on which the system is waiting is "enqueue," then the session might be experiencing ITL waits. However, enqueue is a very broad event that encompasses any type of locks, so it does not accurately specify the ITL waits. When the wait event is experienced, issue the following complex query a table tab1 (say)

Note how the sessions 36 and 52 both have a TM (DML) lock on the table TAB1 of type 3 (Row Exclusive), but session 52 also holds a TX (Transaction) lock on the rollback segment of mode 6 (Exclusive) and Session 36 is waiting for a mode 4 (Share) lock. If this combination of locking occurs, we can be sure that session 36 is waiting for ITL on the table TAB1. Beware of a similar but different diagnosis when two sessions try to insert the same key value (a real locking – primary key violation). In that case, we would also see an additional TX lock on a rollback segment from the session that is waiting; for ITL waits, this TX lock on the rollback segment would not be seen. To know more about working of ITL click here

Examining ITL allocation :

1.) Create a table with INITRANS 4.

SQL> create table test1 (I number) INITRANS 4 ;

2.) Insert few record for testing purpose. A blockdump can be taken later.

SQL> insert into test1 values (10) ;

SQL> commit ;

now updated the table from sessions too these transaction are in hang state .

itc: 10 = Number of interested transaction list(ITL) entry in the block.

typ: 1 = data –> Indicates this is an data block

Flag = State for current transaction (C – Commited) U- indicates that the particular ITL was used.

Lck = Number of Lock held by the transacion

csc: 0x00.2a1372 = This is the comit SCN number of the block

If we use locally managed tablespaces with automatic segment space management , we can more or less forget about these parameters as Oracle takes care of it for us . In Oracle 10g and above, this is now more to concern . In the past they were much more important that they are now .

Note :

Lck” column shows a value. It shows “2”, meaning two rows in the blocks is locked by a transaction. But, which transaction? To get that answer, note the value under the “Xid” column. It shows the transaction ID - 0x0001.001.00000380 . These numbers are in hexadecimal (as indicated by the 0x at the beginning of the number).

A transaction is identified by a transaction ID (XID) which is a set of three numbers – undo segment#, undo slot# and undo record# - separated by periods.

We can also check all the active transactions in the view v$transaction, where the columns XIDUSN, XIDSLOT and XIDSQN denote the undo segment#, undo slot# and undo rec# - the values that make up the transaction ID.

The v$transaction view also contains the session address under SES_ADDR column, which can be used to join with the SADDR column of v$session view to get the session details

Search This Blog

Translate My Page

About Me

I have started this blog to share my knowledge and experience with other Oracle DBA enthusiasts.I have experience on database architecture ,design and administrating ranging from 9i,10g,11g on various platforms.My main interests are high availability and disaster recovery solutions for mission critical 24×7 systems.