Question Re: Oracle Deadlock in Technology Q&A

Oracle Deadlock

Former Member — Tue, 24 Sep 2013 23:54:22 GMT

Hi Experts,

We are encountering oracle deadlocks recently in our ECC system mainly during begging of the months,

we have implemented various solutions recommended by SAP OSS, but nothing helped.

any suggestions.

Thank you.

Grace.

Re: Oracle Deadlock

stefan_koehler — Wed, 25 Sep 2013 16:10:29 GMT

Hi Grace,

unfortunately i have lost my crystal ball for recommending a solution to your specific problem

Why not posting or attaching a specific deadlock graph here? It can be application or database driven.

Regards

Stefan

Re: Oracle Deadlock

former_member184473 — Wed, 25 Sep 2013 16:44:33 GMT

Hi Grace,

Check note 84348 as provide the deadlock graph.

Regards,

Eduardo Rezende

Re: Oracle Deadlock

Former Member — Sat, 28 Sep 2013 23:12:57 GMT

Grace,

Based on you case that deadlock is occurring only during month end/beginning,

Try to narrow down to exact time of the occurrence at least to 1 hour, you may do this by monitoring DB01 and based on user compiling about the issue.

In general a Report/ User/ Bg Job should be the culprit most of the time.

Monitor via DB01 for deadlocks, SM37 for Jobs & Reports running at that time, Deadlock may occur if users are repeatedly accessing(updating) same table or the kind of Report they are using to.

Below is quick SQL query to monitor locks from Oracle level.

sql> select l1.sid, ' IS BLOCKING ', l2.sid from v$lock l1, v$lock l2 where l1.block =1 and l2.request > 0 and l1.id1=l2.id1 and l1.id2=l2.id2;

You may also keep an eye on Temp Table space, look at Oracle Alert log for abnormalities during that time, Also look at your latest EW report.

Re: Oracle Deadlock

Former Member — Sun, 29 Sep 2013 00:08:08 GMT

Hi Raghu,

It was a Report which was causing the issue, not properly tested in QA but transported to Prd system.

Re: Oracle Deadlock

Former Member — Sun, 29 Sep 2013 11:21:54 GMT

Hi,

deadlock may happend in each environment

thnaks

Re: Oracle Deadlock

former_member207463 — Thu, 10 Oct 2013 19:51:09 GMT

Hello,

I would advise you to check SAP KBA #1872040 for details.

Best regards,

Jeisson

Re: Oracle Deadlock

Former Member — Fri, 11 Oct 2013 11:30:38 GMT

Jeisson

I'm new in community, ..how/where write down KBA #1872040?

thanks

Re: Oracle Deadlock

Former Member — Fri, 01 Nov 2013 01:04:57 GMT

Great note. I have one question though, if I have situation where I have identified the process causing the deadlock (application error X) but as its a production system and I have jobs waiting for the deadlock to clear for more than 10 hours. What cause of action would you take ? Do you stop the SAP process which is causing the lock or would you clear it from the Oracle database ?

Thanks.