| Troubleshooting deadlocks [message #163892] |
Mon, 20 March 2006 09:58  |
madchaz
Messages: 65
Registered: October 2005
|
Member |
|
|
Hi all
I'm geting deadlocks many times a day on our prod system.
I've traced the issue down to the following procedure.
WSH_PICK_LIST.RELEASE_BATCH_SRS
The deadlocks always occur between 2 instances of this procedure.
It's owned by APPS, so I would expect it to have been made by oracle. I'm trying to figure out what is causing the deadlocks in that procedure, but it's proving kind of difficult. All there is in it is selects and a single (rather large) insert.
Can inserts create a deadlock? anyone as an idea where I should look?
|
|
|
|
| Re: Troubleshooting deadlocks [message #163926 is a reply to message #163892] |
Mon, 20 March 2006 18:04  |
nmacdannald
Messages: 460
Registered: July 2005
Location: Stockton, California - US...
|
Senior Member |
|
|
Humm, Perhaps the number of concurrent transactions a database row can have, defined during the create table or tablespace(inittrans) and changed using alter table or tablespace, are not high enough.
Just a thought.
We use Oracle Financials and see this deadlocking once in a while. You can kill one of the user's sessions, but that just makes someone angry.
|
|
|
|
|
|
| Re: Troubleshooting deadlocks [message #164000 is a reply to message #163995] |
Tue, 21 March 2006 03:58 |
orausern
Messages: 826
Registered: December 2005
|
Senior Member |
|
|
For deadlocks you should analyze the trace file created as a result of the deadlock.., you should also track down the tables involved and the sessions involved in the deadlock..all this info can be seen from the trace file. So you can do some analysis there.
One big source is deadlocks is unindexed foreign keys. So after the tables are identified, you can try to check , whether there are any unindexed foreign keys in them, and then test by indexing the foreign keys.
One approach to help is also : to decide exactly what causes deadlock and try to do the same from the backend. you should be able to exactly define what front end action leads to deadlocks..i know this is not easy but just if possible you can try..
|
|
|
|
|
|
| Re: Troubleshooting deadlocks [message #164046 is a reply to message #164000] |
Tue, 21 March 2006 08:24 |
madchaz
Messages: 65
Registered: October 2005
|
Member |
|
|
Well, oracle takes care of killing both sessions when a dead-lock occurs.
I've been reading the trace file. That's how I found that this procedure is what is causing the deadlock and that it's always dead-locking itself.
The trace file being rather huge, I'm having trouble figuring out what table is being accessed at the time the deadlock occurs.
Beside reading the entire file, is there any way to find this out that's a litle quicker?
|
|
|
|
| Re: Troubleshooting deadlocks [message #164048 is a reply to message #164046] |
Tue, 21 March 2006 08:31 |
orausern
Messages: 826
Registered: December 2005
|
Senior Member |
|
|
If you are sure, that you have located the cause as being that procedure then:
1) To cross check , you can execute the same procedure from sqlplus from two session and you should get a deadlock--that is an acid test to first establish the cause of deadlock.
2) Assuming that point 1) is through and you know that it is that procedure only which is causing issues then: try to study that procedure and see which tables are getting affected..(one quick suggestion would be check if these tables have any unindexed foreign keys..)
3) you can analyze what transactions are being set up by that procedure..it it the design of incorrect transactions that lead to deadlocks..
also, only one session involved in the deadlocks is killed by oracle, not both..
|
|
|
|
| Re: Troubleshooting deadlocks [message #164060 is a reply to message #164048] |
Tue, 21 March 2006 10:07 |
orausern
Messages: 826
Registered: December 2005
|
Senior Member |
|
|
Tips from oracle docs on avoiding deadlocks:
(oracle 9i concepts manual..it is available online , page 543)
Avoid Deadlocks
Multitable deadlocks can usually be avoided if transactions accessing the same
tables lock those tables in the same order, either through implicit or explicit locks.
For example, all application developers might follow the rule that when both a
master and detail table are updated, the master table is locked first and then the
detail table. If such rules are properly designed and then followed in all
applications, deadlocks are very unlikely to occur.
When you know you will require a sequence of locks for one transaction, consider
acquiring the most exclusive (least compatible) lock first.
|
|
|
|
| Re: Troubleshooting deadlocks [message #164061 is a reply to message #164060] |
Tue, 21 March 2006 10:14 |
madchaz
Messages: 65
Registered: October 2005
|
Member |
|
|
| niravshah wrote on Tue, 21 March 2006 10:07 |
Tips from oracle docs on avoiding deadlocks:
(oracle 9i concepts manual..it is available online , page 543)
Avoid Deadlocks
Multitable deadlocks can usually be avoided if transactions accessing the same
tables lock those tables in the same order, either through implicit or explicit locks.
For example, all application developers might follow the rule that when both a
master and detail table are updated, the master table is locked first and then the
detail table. If such rules are properly designed and then followed in all
applications, deadlocks are very unlikely to occur.
When you know you will require a sequence of locks for one transaction, consider
acquiring the most exclusive (least compatible) lock first.
|
Very interesting information, but considering this is code provided by oracle, not home grown, not that much I can do about changing it. (I stated it was oracle provided code in my first post)
This fact as also been the growing pain for me, as it's rather hard to search throught all the procedures it calls internaly to find where the lock is actualy occuring.
|
|
|
|
| Re: Troubleshooting deadlocks [message #164062 is a reply to message #164061] |
Tue, 21 March 2006 10:22 |
orausern
Messages: 826
Registered: December 2005
|
Senior Member |
|
|
If it is certain , that the code , which is responsible for generating deadlocks is given by oracle, then all you need to do is to contact oracle support and raise a TAR and ask oracle to provide the solution for it.
tracing out why deadlocks are ocurring is definately not the easiest task, but should become less painful perhaps,as in your case, oracle should analyze all the details...(not you)
|
|
|
|
| Re: Troubleshooting deadlocks [message #164063 is a reply to message #163892] |
Tue, 21 March 2006 10:25 |
madchaz
Messages: 65
Registered: October 2005
|
Member |
|
|
I'm eventualy going to open a TAR, but I would have liked to be able to find on what table the lock is occuring and, if possible, what query causes it. This way, I have more meat and can expect faster resolution. Plus, the entire thing is a learning experience for me.
[Updated on: Tue, 21 March 2006 10:25] Report message to a moderator |
|
|
|
| Re: Troubleshooting deadlocks [message #164070 is a reply to message #164063] |
Tue, 21 March 2006 11:05 |
orausern
Messages: 826
Registered: December 2005
|
Senior Member |
|
|
Since you say that
| Quote: |
...as it's rather hard to search throught all the procedures it calls internaly to find where the lock is actualy occuring.
|
there is no way , you would get to know that detail..to iron out the table, you will have to study the procedures which is a pain as you said, so , i dont know what could help out...best luck anyways..
|
|
|
|
| Re: Troubleshooting deadlocks [message #164084 is a reply to message #164070] |
Tue, 21 March 2006 13:17 |
madchaz
Messages: 65
Registered: October 2005
|
Member |
|
|
The following lines gave me the information I needed
Rows waited on:
Session 1473: obj - rowid = 00072D64 - AAGLOLAJZAAAAAAAAA
(dictionary objn - 470372, file - 601, block - 0, slot - 0)
Session 1071: obj - rowid = 0002462A - AAAkYqALeAAAAAAAAA
(dictionary objn - 149034, file - 734, block - 0, slot - 0)
They are at the begining of thr trace. I was able to lookup the dictionary objn in dba_objects and figure out that 2 indexes were involved, with the name.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
