Home » RDBMS Server » Performance Tuning » Dead lock (oracle 9.2.0.7 ,windows server 2003)
Dead lock [message #342337] Fri, 22 August 2008 01:25 Go to next message
jayarajkrishna
Messages: 7
Registered: August 2008
Location: Mumbai,India
Junior Member
More deadlocks are generating on a particular table.
How can i avoid these dead locks?its blocking other transactions
Re: Dead lock [message #342340 is a reply to message #342337] Fri, 22 August 2008 01:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
How can i avoid these dead locks?

Dead locks are (almost all the time) application bugs.
You have to identify which part(s) of application generates these dead-locks and change it (them).

Regards
Michel
Re: Dead lock [message #342750 is a reply to message #342337] Sun, 24 August 2008 21:20 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
I remember my first year of working with Oracle (1985 I think it was). Some Oracle guru from Oracle Corp. was called in at 1500$/day to diagnose and treat a deadlock issue for my group. I was a lowly developer and sat an every word this guy uttered (little did I know). He said this and as far as I know, still a good description of one possible fix:

Quote:
Deadlock is caused by taking locks on rows and tables. The order in which tables are accessed to get locks is important. If you write an application such that it always takes locks from tables in the same order, then you will be able to avoid deadlock.


This is of course a paraphrasing of what he said. Additionally, the issue of deadlock has become simpler and more complex at the same time, as Oracle RDBMS has evolved. It takes two transactions to deadlock. This used to mean two different sessions, but with the advent of AUTONOMOUS TRANSACTIONS it is now possible for a single session to deadlock itself (woohoo! what fun). Furthermore he played down the fact that removing deadlock generally means exchanging it for blocking waits so instead of one transaction dieing, it just waits forever so for many situations you have simply swapped one problem for another. Lastly he never really commented too much on how one achieved the ideal of getting locks on rows in the same order across multiple applications so implementing the idea can take some doing.

Still, Deadlock really begs for a discussion of what is called TRANSACTION DESIGN. I however do not want to discuss it in a forum as it can get lengthly so I suggest you do some googling of the terms ("DEADLOCK", "TRANSACTION DESIGN") and see what you can read.

Good luck, Kevin
Re: Dead lock [message #343308 is a reply to message #342337] Tue, 26 August 2008 22:13 Go to previous messageGo to next message
varunvir
Messages: 363
Registered: November 2007
Senior Member
One way of avoiding deadlock is:-
distributed_lock_timeout ---increase the value of this parameter.
Re: Dead lock [message #343309 is a reply to message #343308] Tue, 26 August 2008 22:26 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
http://download.oracle.com/docs/cd/B10501_01/server.920/a96536/ch156.htm#1016801

varunvir wrote on Tue, 26 August 2008 20:13
One way of avoiding deadlock is:-
distributed_lock_timeout ---increase the value of this parameter.


Ignore the incorrect response above.
DISTRIBUTED_LOCK_TIMEOUT affects only distributed transactions;
& does not affect deadlock detection.

See below about deadlock detection & avoidance
http://download.oracle.com/docs/cd/B10501_01/server.920/a96524/c21cnsis.htm#9204
Re: Dead lock [message #343311 is a reply to message #342337] Tue, 26 August 2008 22:47 Go to previous messageGo to next message
varunvir
Messages: 363
Registered: November 2007
Senior Member

distributed transaction is one form of deadlock.Isn't it.
Regards,
Varun Punj
Re: Dead lock [message #343314 is a reply to message #342337] Tue, 26 August 2008 23:01 Go to previous message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
Post a reproducable test case that proves your contention that
increasing DISTRIBUTED_LOCK_TIMEOUT is "One way of avoiding deadlock".
Previous Topic: ANSI - SQL or Traditional SQL
Next Topic: Row Chaining problem
Goto Forum:
  


Current Time: Thu Dec 08 00:30:03 CST 2016

Total time taken to generate the page: 0.08564 seconds