Dead Lock ...in Oracle

[samirm]

Hi,

I am facing a problem ( dead lock ) during month end when mostly users do their transactions ..i Database

we have AVLOWN system running in Oracle Database ..Version 8.1.7 with AIX 4.3.3 on RS/6000 F80.

let me give you in details what kind of problem I am facing :-

When a user complaints ..that his entry screen is showing in stage "Working" ...more than expected time ..we see through script ..that the relevant object ( table ) is showing dead lock against that particular user.

After kill that session by
alter system kill session ".."
it is showing system altered ...
but unfortunately ....after few sec ..another user complaints ..stating his entry screen is also showing "Working" ....

and we follow the same process and found again dead lock on that particular object with some other user.. So, eventually after even kill session, the object is not getting free from that particular user and finally none of them getting that object to use....

Finally situation arising to restart the database ....
frankly speaking I did even kill session command 18 times ..for 18 users ...and finally got irritated ...
and restarted the database ..After restart the problem solved ....

But this kind of problem encountered for last two months ..mostly during month end.

Besides all I would say there is no issue at performace level on Database side ....It is running fantastic ..!!

If you can suggest what action should be taken to avoid such problem ....!!! I need in depth concept as well.

Thanking you ....

Sam

 

[sem]

I suppose that your application should be redesigned.

Regards, Dima

 

[stefanhei]

Hi.
Does your alert.log show a deadlock (ORA-000060) too? If so there should a tracefile be produced.
Usually deadlocks indicate, that the application has to be altered (you'll read that in the tracefiles too).

Stefan

 

[sem]

I suppose that this is not a deadlocking, just waiting for locked resources due to poor application design.

Regards, Dima

 

[jimbopalmer]

A number of third party apps can tell you which session is locking (Bradmark NORAD, Quest Eye on Oracle, etc.) but the fact is, the app is badly designed if the users lock each other out. Check with the vendor for updates about this.

I tried to remain child-like, all I acheived was childish.