topic Re: Re : Database Deadlock in Application Development and Automation Discussions

Re : Database Deadlock

Former Member — Tue, 29 Oct 2013 13:04:51 GMT

Hi Experts!!

We need your valuable inputs regarding the following scenario..

Some databse deadlock issue is occuring while uers executing few transactions which involves standard transactions.

I have given few scraps which we got from database lock , First of all we are not sure whether we can do anything from SAP abap technical part or not as it might need basis database admintrators.

Can anyone give us some inpits regarding the same ?

-------------------------------start------------------------

The following deadlock is not an ORACLE error. It is a

deadlock due to user error in the design of an application

or from issuing incorrect ad-hoc SQL. The following

information may aid in determining the deadlock:

------

Deadlock graph:

---------Blocker(s)-------- ---------Waiter(s)---------

Resource Name process session holds waits process session holds waits

TX-01700003-000264fd 766 4288 X 226 3457 X

TX-00640020-00035bc1 226 3457 X 766 4288 X

session 4288: DID 0001-02FE-00000117 session 3457: DID 0001-00E2-000000F4

session 3457: DID 0001-00E2-000000F4 session 4288: DID 0001-02FE-00000117

Rows waited on:

Session 4288: obj - rowid = 00030003 - AAAwADACDAAIoOHAAd

(dictionary objn - 196611, file - 131, block - 2261895, slot - 29)

Session 3457: obj - rowid = 00030003 - AAAwADACJAAH7oPAAJ

(dictionary objn - 196611, file - 137, block - 2079247, slot - 9)

----- Information for the OTHER waiting sessions -----

Session 3457:

sid: 3457 ser: 45823 audsid: 5013813 user: 41/SAPSR3

flags: (0x1000045) USR/- flags_idl: (0x1) BSY/-/-/-/-/-

flags2: (0x40009) -/-/INC

pid: 226 O/S info: user: orap00, term: UNKNOWN, ospid: 47251658

image: oracle@sappp00s

client details:

O/S info: user: p00adm, term: , ospid: 32702718

machine: sappa06 program: dw.sapP00_D17@sappa06 (TNS V1-V3)

client info: 0::RSM13000

application name: RMCSS062, hash value=4213879826

action name: 3718, hash value=2275214098

current SQL:

UPDATE "S062" SET "WAERS"=:A0 , "IAMATKOSE"="IAMATKOSE" +:A1 , "IAPERKOSF"="IAPERKOSF" +:A2 , "IAPERKOSE"="IAPERKOSE" +:A3 , "IAMATKOSF"="IAMATKOSF" +:A4 , "IADIENKOS"="IADIENKOS" +:A5 , "IASONKOST"="IASONKOST" +:A6 , "IAERTRAG"="IAERTRAG" +:A7 , "IAOFN"="IAOFN" +:A8 , "IASOF"="IASOF" +:A9 , "IAPLANM"="IAPLANM" +:A10 , "IAUNGPL"="IAUNGPL" +:A11 , "IAABG"="IAABG" +:A12 , "IAKOSPLAN"="IAKOSPLAN" +:A13 , "IAKOSIST"="IAKOSIST" +:A14 WHERE "MANDT"=:A15 AND "SSOUR"=:A16 AND "VRSIO"=:A17 AND "SPMON"=:A18 AND "SPTAG"=:A19 AND "SPWOC"=:A20 AND "SPBUP"=:A21 AND "SKLSOB"=:A22 AND "SUBMT"=:A23 AND "HERST"=:A24 AND "BAUJJ"=:A25 AND "BAUTL"=:A26

----- End of information for the OTHER waiting sessions -----

Re: Re : Database Deadlock

Ruediger_Plantiko — Tue, 29 Oct 2013 13:30:57 GMT

Hi Rinki,

the information from the deadlock dump is not complete. You need to find out the two database table rows in question - I see only one, an update request on statistics info system table S062.

The first step would be to find out the other ressource which was to be locked. Also, the two code parts which issued these locks have to be identified.

S062 update can come from standard code, or equally well from Z code.

Then, in a second step, you can think about how to prevent these deadlocks in future.

Common resolutions for deadlocks of this kind could be:

Putting the two ressources into a group and locking them jointly, for example with the call of an enqueue object,

or: Putting the processing of the ressources into a defined sequence. If all consuments work with the same defined sequence, the deadlock cannot happen.

Regards,

Rüdiger

Re: Re : Database Deadlock

Ruediger_Plantiko — Tue, 29 Oct 2013 13:35:27 GMT

Seems this note is a perfect hit: Please check:

1619751 - Database deadlocks on S061 and S062 tables

Re: Re : Database Deadlock

Former Member — Mon, 04 Nov 2013 12:12:28 GMT

Hi Rudiger,

Thanks a lot for your reply.

We have alreday gone through the note but we really did not get the solutions for the same.

It does not tells anything regarding changing in any standard code or anything.

can you please provide us some inputs regarding the same ?

Re: Re : Database Deadlock

Former Member — Mon, 04 Nov 2013 12:13:28 GMT

Adding another thing,
can the structure changes mentioned in the note implemented by basis team of PM functional owner ?

Re: Re : Database Deadlock

Former Member — Mon, 04 Nov 2013 12:21:06 GMT

Hi Rinki,

in option 2 you have to change update mode in transaction OMOS to V3. After that you have to plan a job for V3 update with transaction MC3V.

This seems to be really clear.

Regards,

Klaus

Re: Re : Database Deadlock

RaymondGiuseppi — Mon, 04 Nov 2013 12:24:59 GMT

No code change is required, you shouldt read the note again (read "resolution") and change the update mode to V3 (transaction OMOS) and then schedule a periodic job which will update the table.

Regards,

Raymond

Re: Re : Database Deadlock

RaymondGiuseppi — Mon, 04 Nov 2013 12:25:59 GMT

Klaus was faster..

Regards,

Raymond