Re : Database Deadlock

[Former Member]

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 -----

[Ruediger_Plantiko]

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

[Ruediger_Plantiko]

Seems this note is a perfect hit: Please check:

1619751 - Database deadlocks on S061 and S062 tables

[Former Member]

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 ?

[Former Member]

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

[Former Member]

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

[RaymondGiuseppi]

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

[RaymondGiuseppi]

Klaus was faster..

Regards,

Raymond