Avoid ORA-00060: Deadlock during SPAM update

[benoit-schmid]

Hello,

We have patched our Solman from SPS 28 to SPS 30.

To reduce the time of the SPAM, we have configured parallel R3Trans processing with the following method.

- tx spam -> Extra -> Settings -> Import Queue -> Number of parallel processes per R3trans = 8

The SPS 30 has been applied successfully.

But I have had the following errors in the Oracle alert during SPAM processing:

Deadlock detected. More info in file /oracle/SSM/saptrace/diag/rdbms/ssm/SSM/trace/SSM_ora_22599.trc

In fact I have had quite a lot of them during the 2 hours of SPAM:

% grep ORA-00060 alert_SSM.log_2013-01-08_08-43-44 | wc -l

      69

The detail of the deadlock update is on table REPOSRC table:

UPDATE "REPOSRC" SET "SDATE"=:A0 , "STIME"=:A1

WHERE "PROGNAME"=:A2

AND "R3STATE"=:A3

AND ("SDATE"<:A4 OR ("SDATE"=:A5 AND "STIME"<:A6 ) )

Is there a way to avoid these deadlocks?

Thanks in advance for your answer.

[stefan_koehler · Accepted solution]

Hi Benoit,

as always ... it depends

What is the root cause of the ORA-00060? Is it just "application based" by TX locks or maybe due to ITL issues? The deadlock graph tells us.

Regards

Stefan

[benoit-schmid · Accepted solution]

Hello Stefan,

I have found the SAP Note 1454536.

It seems to describe my problem.

The bad news is that it specifies:

As of kernel release 720, the algorithm that R3trans uses to

invalidate the "ABAP-Loads" has been fundamentally improved, resulting in a

drastic reduction in the probability of deadlocks on the table REPOSRC.

But as I am running 7.20EXT, I still have a lot of problem.

What are ITL issues?

As far as I understand several Work Processes and/or R3Trans are trying to update this table.

Regards,

[stefan_koehler · Accepted solution]

Hi Benoit,

you need to allocate an ITL slot before you can modify an oracle data block. If all slots are used (or no space for additional allocation is available) you can also end up in dead locks based on this.

Please post the deadlock graph information from a particular trace file.

Regards

Stefan

[benoit-schmid · Accepted solution]

Hello Stefan,

Here is your information:

Deadlock graph:

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

Resource Name          process session holds waits  process session holds waits

TX-002c0013-00071362        53     275     X             55     302           X

TX-002d0001-00002b5b        55     302     X             53     275           X

session 275: DID 0001-0035-0000002C     session 302: DID 0001-0037-0000004C

session 302: DID 0001-0037-0000004C     session 275: DID 0001-0035-0000002C

Rows waited on:

  Session 275: obj - rowid = 000026AD - AAACatAAJAAFxhhAAI

  (dictionary objn - 9901, file - 9, block - 1513569, slot - 😎

  Session 302: obj - rowid = 000026AD - AAACatAAIAAAbawAAB

  (dictionary objn - 9901, file - 8, block - 112304, slot - 1)

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

Session 302:

  sid: 302 ser: 149 audsid: 1564414 user: 28/SAPSR3

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

    flags2: (0x40008) -/-

Regards,

[stefan_koehler · Accepted solution]

Hi Benoit,

the graph indicates pure application based (TX) deadlocks. No possibility to do anything on database level here.

Regards

Stefan

[Former Member]

Hi Benoît,

Set abap/pxa = gen_local in your SAP profile. After the SPAM complete the process respectfully, delete the parameter from the profile.

Please note that the system needs to be restarted after you change the parameter.

Best regards,

Orkun Gedik

[benoit-schmid]

Hello Orkun,

What is the effect of setting abap/pxa = gen_local?

Regards,

[Former Member]

It has been described in the RZ11 as "ABAP programs are generated in user LUW".

This problem happens because of the remote generation. It means that the ABAP programs have been generated in the separate LUW, instead of its application transaction. Before the first commit operation, the other processes try to lock the REPOSRC table. At this stage, the other processes have to wait for release lock which leads to deadlock situation.

The situation has been explained in the Note 597885 - Excluding Lockwaits on table REPOSRC

Best regards,

Orkun Gedik

[benoit-schmid]

Hello,

The Note you have provided concerns a kernel version that is above our version.

The note specifies that it is solved for more recent release.

Therefore it does not apply to us.

Orkun Gedik wrote:
The situation has been explained in the Note 597885 - Excluding Lockwaits on table REPOSRC

[Former Member]

Hi,

I suggested this note in order to describe the situation occured at the backend. Even though the kernel release is different, I think that abap/pxa parameter change applicable to your situation. If you have a test system to perform same process, you can check the results.

Best regards,

Orkun Gedik