Figure 6-35. A highly abstracted Oracle data block variable data area highlighting the three interested transaction lists (ITLs). The ITLs are deeply involved with row-level locking; that is, concurrency control.

Each Oracle data block is created with a specific number of ITLs. The initial number of ITLs is controlled by the table's initrans space parameter and can be queried from the dba_tables view's ini_trans column. Starting with Oracle9i, the default ini_trans value is 1, yet a simple block dump clearly shows two ITLs are always created. With two ITLs, a single block can be subjected to two concurrently active transactions.

Suppose a third transaction wants to change a unlocked row in a block with only two ITLs. The third transaction's server process will try to dynamically create an additional ITL. However, the server process must first ensure the maximum number of ITLs (max_trans) will not be exceeded and also that there is free space available in the block. If the server process cannot create an additional ITL, it will post a TX enqueue wait, and the process will patiently wait. To reduce the likelihood of this occurring, both the default value and the maximum number of ITLs a single block can contain are set to 255. While the value cannot be exceeded, it can be reduced by issuing a simple alter table command.11