|
Thanasis,
I have to question why you think you have contention for the Interested
Transaction List (ITL) in a deadlock situation.
The ITL is used to control transactions interested in modifying a block.
There are two parameters that control the ITL. They are INITRANS and
MAXTRANS. INITRANS specifies the initial number of transactions that the ITL
can keep track of. For tables, this defaults to '1'. For indexes, this
defaults to '2'. MAXTRANS specifies the maximum number of transactions that
the ITL can keep track of. For both indexes and tables, this defaults to
'255'.
As more and more transactions are interested in modifying a block (table or
index), the ITL grows from INITRANS entries to a limit of MAXTRANS entries.
Except in the most extreme cases, you do not need to change the default values for these parameters. In some rare cases where a great deal of transactions will vie for the same block, you may wish to increase INITRANS to speed up the transaction's execution. This way, the ITL doesn't have to grow since it has already been allocated to be bigger. But like I said, this is in rare cases, and I doubt that it will help you.
Setting a higher PCTFREE will mean that there are less rows being stored in
your block. This will spread out your rows among more blocks. This also means that you may have more wasted space. By spreading out rows among more blocks, then your transactions are also spread out among the blocks.
I doubt that altering the INITRANS or PCTFREE values and rebuilding the
tables will resolve your deadlock situation.
A deadlock situation occurs because transaction A holds a lock on one
resource (resource_1) and is requesting a lock on another resource
(resource_2). Transaction B holds a lock on the second resource (resource_2)
and requests a lock on the first resource (resource_1). Transaction A is now
waiting on Transaction B to release its lock. At the same time (and this is
the key, "at the same time"), Transaction B is waiting for Transaction A to
release its lock. These two transactions are *deadlocked*. In other words, they are both waiting on the other to release their locks. And they won't release their locks until they complete. They can't complete until they obtain the other lock. A "Catch 22" is now in play. The Oracle RDBMS automatically detects deadlocks and rolls back the process that first detected the deadlock.
So you can now see that deadlocks occur when trying to obtain locks on rows
in a table or entries in an index. This has nothing to do with the ITL (and
INITRANS) or the amount of free space left in the block (and PCTFREE). Adjusting these values will not resolve your deadlock situation. And this is
very easy to test. Rebuild your table with different values and rerun your
application exactly as you did before. You will still see deadlocks. And this most commonly occurs when the same operation is run in parallel.
But don't worry because you have half the battle already won!!! You have
already identified the specific place where you are getting a deadlock situation. This can be the hardest thing to track down. Now you can look at
your application and see how it is obtaining locks. You might want to implement different transactional control (such as a SERIALIZABLE transaction) to keep your deadlock situation from occuring. You can read more about transactional control in the Oracle 8i Concepts Guide (specifically Chapter 24 Data Concurrency and Consitency).
For More Information
|
