Deadlock when deleting from rsstatmanpsa during DSO activation

[robertbuttram]

We are experiencing intermittent Oracle deadlocks during DSO activation in our BI system. The deadlocks occur when the following SQL statement is executed:

delete from rsstatmanpsa where

partnr >= l_s_status-rnr_sid and

psa = l_s_status-dta.

This is at line 83 of the SET STATUS method of class CL_RSSM_STATMAN. (See below for runtime error details.)

When these errors occur the background job for the DSO activation is cancelled with the message ABAP/4 processor: DBIF_RSQL_SQL_ERROR. Strangely, when this occurs as part of a process chain, the status of the process variant does not get updated until/unless someone displays the process chain log, at which point the status changes and follow-on events are triggered. In other words, the process chain does not seem to be receiving word that the DSO activation process has abended, so we do not receive alerts that a problem has occurred.

Has anyone else experienced this issue?

Thanks,

Bob

P.S. We are running BI 7.0, Patch Level 15 on Oracle 10.2.0.4.0

Runtime Errors DBIF_RSQL_SQL_ERROR

Exception CX_SY_OPEN_SQL_DB

-

---

Error analysis

An exception occurred that is explained in detail below.

The exception, which is assigned to class 'CX_SY_OPEN_SQL_DB', was not caught

in

procedure "SET_STATUS" "(METHOD)", nor was it propagated by a RAISING clause.

-

---

-

---

How to correct the error

Database error text........: "ORA-00060: deadlock detected while waiting for

resource"

Internal call code.........: "[RSQL/DELE/RSSTATMANPSA ]"

Please check the entries in the system log (Transaction SM21).

If the error occures in a non-modified SAP program, you may be able to

find an interim solution in an SAP Note.

If you have access to SAP Notes, carry out a search with the following

keywords:

"DBIF_RSQL_SQL_ERROR" "CX_SY_OPEN_SQL_DB"

"CL_RSSM_STATMAN===============CP" or "CL_RSSM_STATMAN===============CM008"

"SET_STATUS"

If you cannot solve the problem yourself and want to send an error

notification to SAP, include the following information:

1. The description of the current problem (short dump)

To save the description, choose "System->List->Save->Local File

(Unconverted)".

2. Corresponding system log

Display the system log by calling transaction SM21.

Restrict the time interval to 10 minutes before and five minutes

after the short dump. Then choose "System->List->Save->Local File

(Unconverted)".

3. If the problem occurs in a problem of your own or a modified SAP

program: The source code of the program

In the editor, choose "Utilities->More

Utilities->Upload/Download->Download".

4. Details about the conditions under which the error occurred or which

actions and input led to the error.

The exception must either be prevented, caught within proedure

"SET_STATUS" "(METHOD)", or its possible occurrence must be declared in the

RAISING clause of the procedure.

To prevent the exception, note the following:

-

---

-

---

Information on where terminated

Termination occurred in the ABAP program "CL_RSSM_STATMAN===============CP" -

in "SET_STATUS".

The main program was "RSPROCESS ".

In the source code you have the termination point in line 83

of the (Include) program "CL_RSSM_STATMAN===============CM008".

The program "CL_RSSM_STATMAN===============CP" was started as a background job.

Job Name....... "BI_PROCESS_ODSACTIVAT"

Job Initiator.. "ALEREMOTE"

Job Number..... 05302800

The termination is caused because exception "CX_SY_OPEN_SQL_DB" occurred in

procedure "SET_STATUS" "(METHOD)", but it was neither handled locally nor

declared

in the RAISING clause of its signature.

The procedure is in program "CL_RSSM_STATMAN===============CP "; its source

code begins in line

1 of the (Include program "CL_RSSM_STATMAN===============CM008 ".

-

---

-

---

Source Code Extract

-

---

Line

SourceCde

-

---

53	if i_with_internal_check is initial.
54	l_s_status = i_s_status.
55	call function 'RSSM_GET_TIME'
56	importing
57	e_timestamps = l_s_status-ts_last_changed.
58	if l_s_status-ts_proc_started <= '10000101000000'.
59	l_s_status-ts_proc_started = l_s_status-ts_last_changed.
60	endif.
61	if l_s_status_exist is initial.
62	insert rsstatmanstatus from l_s_status.
63	if sy-subrc <> 0.
64	message x000.
65	endif.
66	else.
67	modify rsstatmanstatus from l_s_status.
68	if sy-subrc <> 0.
69	message x000.
70	endif.
71	endif.
72	delete from rsstatmanpart where
73	rnr = l_s_status-rnr and
74	dta = l_s_status-dta and
75	dta_type = l_s_status-dta_type.
76	select single * from rsstatmanpsa into l_s_psa where
77	rnr = l_s_status-rnr and
78	psa = l_s_status-dta.
79	if sy-subrc = 0.
80	delete from rsmdatastate_psa where
81	psa = l_s_status-dta and
82	type = l_s_status-dta_type.
>>>>>	delete from rsstatmanpsa where
84	partnr >= l_s_status-rnr_sid and
85	psa = l_s_status-dta.
86	else.
87	select single * from rsmdatastate_psa into l_ds_psa where
88	psa = l_s_status-dta and
89	type = l_s_status-dta_type.
90	if sy-subrc = 0 and l_ds_psa-sid_checked > l_s_status-rnr_sid.
91	delete from rsmdatastate_psa where
92	psa = l_s_status-dta and
93	type = l_s_status-dta_type.
94	delete from rsstatmanpsa where
95	partnr >= l_s_status-rnr_sid and
96	psa = l_s_status-dta.
97	endif.
98	endif.
99	if i_with_commit = 'X'.
100	call function 'DB_COMMIT'.
101	endif.
102	endif.

-

---

[former_member223744]

Hi.

Did you apply there notes in your system?

1079193 - P16:STATMAN: Several parallel requests and enqueue problem

1083305 - P16:STATMAN:Dump in function module RSM_INFOCUBE_WRITE_CHECK

1090952 - P16:Request creation is locked too restictively

1089064 - 16:PSA:STATMAN:Slow access for RSREQDONE with enqueue

Thanks,

Walter Oliveira

[robertbuttram]

Walter,

Thanks for the suggestion. This is the same recommendation I just received from SAP.

We are currently in the midst of regression testing for a major release, so cannot implement these corrections immediately. I'll post again after we implement the corrections and let you know if they solve our problem.

Thanks again.

Bob