We run on a E10000 SUn /Solaris box with 24 processors. We continue to have a deadlock (ora-60) error always at...
the same point when a specific batch process runs in parallel. It seems to be without doubt an itl problem (waits for share locks holding exclusive locks from both processes as initrans equals to 1). We've 20 processes going in parallel and always stopped from Oracle at the same update statement. So the solution seems to be drop and create the table with n initrans higher (how high?). Somebody says that could be negative for the block density, so the way would be to increase pctfree. What do you think about this? Opt for prctfree or initrans, in which case the density becomes a problem from these options. And also, I would like to know more on how to read the dump created from Oracle in case of a deadlock. How can I interpret these dumps, and especially how can I identify the resource hold from the process being stopped that the other session waits for? I think that could help to identify the contention on a resource other than the obvious indicated from the statement interrupted.
Thanks in advance for your advice.
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
- What do you think about this answer? E-mail us at editor@searchDatabase.com with your feedback.
- Ask your technical Oracle questions--or help out your peers by answering them--in our live discussion forums.
Dig Deeper on Oracle database design and architecture
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.