topic Database Deadlock occurred in Application Development and Automation Discussions

Database Deadlock occurred

Former Member — Tue, 14 Feb 2012 05:54:48 GMT

Hi friends,

I'm using FUNCTION 'IDOC_WRITE_AND_START_INBOUND' in a job (it will update the MDVM), sometimes this job failed with the folowing error:

Deadlock occurred

Database error 60 at UPD access to table MDVM

> ORA-00060: deadlock detected while waiting for resource

Run-time error "DBIF_RSQL_SQL_ERROR" occurred

I just want to know that whether it is a Database or ABAP issue? and how to solve this problem?

Thanks a lot !

some code is as following:

...... 
CALL FUNCTION 'IDOC_WRITE_AND_START_INBOUND'
 
      EXPORTING
 
        i_edidc        = lwa_idoc_control
 
        do_commit      = c_x
 
      IMPORTING
 
        docnum         = lwa_idoc_control-docnum
 
      TABLES
 
        i_edidd        = lt_edidd
 
      EXCEPTIONS
 
        idoc_not_saved = 1
 
        OTHERS         = 2.
 
    IF sy-subrc  0.
 
*     record the error
 
*    text-e10 =  Error occurs when calling u201CIDOC_WRITE_AND_START_INBOUND"
 
      <fs_idoc_info>-remark = text-e10.
 
      APPEND <fs_idoc_info> TO gt_idoc_err.
 
      g_error = c_x.
 
    ELSE.
 
      COMMIT WORK AND WAIT.
 
*     IDOC Number
 
      <fs_idoc_info>-idoc_num = lwa_idoc_control-docnum.
 
*     Status.
 
      SELECT SINGLE status FROM edidc INTO <fs_idoc_info>-
 
idoc_status
 
                         WHERE docnum = lwa_idoc_control-
 
docnum.
......

Re: Database Deadlock occurred

Former Member — Tue, 14 Feb 2012 05:59:21 GMT

HI,

It may be the connection problem.your coding id right.

Regards

Karthik.R

Re: Database Deadlock occurred

Former Member — Tue, 14 Feb 2012 06:11:29 GMT

Hi,

Debug your code and check at what line of code the error is comming.

Regards,

Goutam Kolluru.

Re: Database Deadlock occurred

Former Member — Tue, 14 Feb 2012 06:14:25 GMT

have you checked any of the program is using MDVM table other than yours

Re: Database Deadlock occurred

Former Member — Tue, 14 Feb 2012 06:37:26 GMT

Hello

I have debugged the codes, in CALL FUNCTION 'IDOC_WRITE_AND_START_INBOUND', it will update MDVM table.

Acctully, have test in online mode, it will run successfully; but sometimes it will fail when being run as a job.

When the job failed, In sm21, I got the fowlling infomation:

Deadlock occurred

Database error 60 at UPD access to table MDVM

> ORA-00060: deadlock detected while waiting for resource

Run-time error "DBIF_RSQL_SQL_ERROR" occurred

Re: Database Deadlock occurred

Former Member — Tue, 14 Feb 2012 07:08:54 GMT

Hi,

Is your error in the select query of the table TRFCQOUT, check this in the source code extract of the dump analysis in st22 transaction. If that is the cause for the error then implement the note 1606565 this will solve this issue.

This sort of termination is not very common. This issue is a BASIS related issue.

Go through the SAP not specified above. you will get more details.

Please let me know for further details.

<begging removed by moderator>

Regards!

Praveenkumar T.

Edited by: Thomas Zloch on Feb 14, 2012

Re: Database Deadlock occurred

Former Member — Tue, 14 Feb 2012 07:12:55 GMT

Hi,

Since the error code is related to Oracle database

ORA-00060: deadlock detected while waiting for resource

It is a database specific error.

Regards,

Amit Mittal.

Re: Database Deadlock occurred

GauthamV — Tue, 14 Feb 2012 07:19:34 GMT

This defnitely seems to be a database error.

Tell your basis consultant to check the Oracle trace file.

http://help.sap.com/saphelp_nw04/helpdata/en/8d/2d5fac955fbb43871e348257f6c49d/content.htm

Re: Database Deadlock occurred

Former Member — Tue, 14 Feb 2012 07:34:37 GMT

Hi I have got the Oracle trace file as below:

more /oracle/PRC/saptrace/usertrace/prc_ora_1323174.trc

Dump file /oracle/PRC/saptrace/usertrace/prc_ora_1323174.trc

Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production

With the Partitioning and Data Mining options

ORACLE_HOME = /oracle/PRC/102_64

System name: AIX

Node name: sfhprd01

Release: 3

Version: 5

Machine: 000B69E6D700

Instance name: PRC

Redo thread mounted by this instance: 1

Oracle process number: 48

Unix process pid: 1323174, image: oraclePRC@sfhprd01

2012-02-12 05:01:15.479
ACTION NAME:(5987) 2012-02-12 05:01:15.469
MODULE NAME:(SAPLDISP ) 2012-02-12 05:01:15.469
SERVICE NAME:(SYS$USERS) 2012-02-12 05:01:15.469
SESSION ID:(1050.34) 2012-02-12 05:01:15.469

DEADLOCK DETECTED

[Transaction Deadlock]

Current SQL statement for this session:

UPDATE "MDVM" SET "GSAEN" = :A0 , "AKKEN" = :A1 , "MPSKZ" = :A2 , "ERDAT" = :A3 , "USTMP" = :A4 WHERE "MANDT" = :A5 AND "MAFID" = :A6 AND "MDKEY" = :A7 AND ( "GSAEN" <> :A8 OR "AKK

EN" <> :A9 OR "MPSKZ" <> :A10 )

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-0009000e-0001afeb 48 1050 X 54 1043 X

TX-00050001-0001a6ba 54 1043 X 48 1050 X

session 1050: DID 0001-0030-00000004 session 1043: DID 0001-0036-00000004

session 1043: DID 0001-0036-00000004 session 1050: DID 0001-0030-00000004

Rows waited on:

Session 1043: obj - rowid = 0001D747 - AAAddHAAWAABZW5ABV

(dictionary objn - 120647, file - 22, block - 366009, slot - 85)

Session 1050: obj - rowid = 0001D747 - AAAddHAAWAABZW9AA7

(dictionary objn - 120647, file - 22, block - 366013, slot - 59)

Information on the OTHER waiting sessions:

Session 1043:

pid=54 serial=17 audsid=604347 user: 27/SAPSR3

O/S info: user: prcadm, term: , ospid: 1122488, machine: sfhprd01

program: dw.sapPRC_DVEBMGS00@sfhprd01 (TNS V1-V3)

client info: 0

application name: SAPLDISP , hash value=2512065060

action name: 5987, hash value=493565349

Current SQL Statement:

UPDATE "MDVM" SET "GSAEN" = :A0 , "AKKEN" = :A1 , "MPSKZ" = :A2 , "ERDAT" = :A3 , "USTMP" = :A4 WHERE "MANDT" = :A5 AND "MAFID" = :A6 AND "MDKEY" = :A7 AND ( "GSAEN" <> :A8 OR "A

KKEN" <> :A9 OR "MPSKZ" <> :A10 )

End of information on OTHER waiting sessions.

sfhprd01:oraprc 23>

As you can see, it is "a deadlock due to user error in the design of an application or from issuing incorrect ad-hoc SQL. "

Re: Database Deadlock occurred

RaymondGiuseppi — Tue, 14 Feb 2012 12:53:18 GMT

It is a common error in Oracle, first read [Note 84348 - Oracle deadlocks, ORA-00060|https://service.sap.com/sap/support/notes/84348], due to some inherent problem of shared lock during insert in Oracle.

Did you read [Note 1023278 - Deadlocks during transfer from SCM to ERP|https://service.sap.com/sap/support/notes/1023278] which solved a similar problem grouping data by material number, so preventing two treatments/luw to wait for each over.

Regards,

Raymond

Re: Database Deadlock occurred

Former Member — Wed, 15 Feb 2012 08:31:54 GMT

Helle,

I already checked in the ST22 dump file , There is no related table TRFCQOUT information as attachment.

So may be the note#1606565 is not fit for this issue I think.

below is the part of the Dump file:

Runtime Errors DBIF_RSQL_SQL_ERROR

Exception CX_SY_OPEN_SQL_DB

Date and Time 2012.02.12 05:01:17

Short text

SQL error in the database when accessing a table.

What happened?

The database system detected a deadlock and avoided it by rolling back

your transaction.

What can you do?

If possible (and necessary), repeat the last database transaction in the

hope that locking the object will not result in another deadlock.

Note which actions and input led to the error.

For further help in handling the problem, contact your SAP administrator

You can use the ABAP dump analysis transaction ST22 to view and manage

termination messages, in particular for long term reference.

Note which actions and input led to the error.

For further help in handling the problem, contact your SAP administrator

You can use the ABAP dump analysis transaction ST22 to view and manage

termination messages, in particular for long term reference.

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

procedure "UPDATE_MDVM" "(FORM)", nor was it propagated by a RAISING clause.

Since the caller of the procedure could not have anticipated that the

exception would occur, the current program is terminated.

The reason for the exception is:

The database system recognized that your last operation on the database

would have led to a deadlock.

Therefore, your transaction was rolled back

to avoid this.

ORACLE always terminates any transaction that would result in deadlock.

The other transactions involved in this potential deadlock

are not affected by the termination.

How to correct the error

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

resource"

Internal call code.........: "[RSQL/UPDT/MDVM ]"

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"

"SAPLDISP" or "LDISPF29"

"UPDATE_MDVM"

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

"UPDATE_MDVM" "(FORM)", or its possible occurrence must be declared in the

RAISING clause of the procedure.

To prevent the exception, note the following:

System environment

SAP-Release 700

Application server... "sfhprd01"

Network address...... "10.27.35.102"

Operating system..... "AIX"

Release.............. "5.3"

Hardware type........ "000B69E6D700"

Character length.... 16 Bits

Pointer length....... 64 Bits

Work process number.. 25

Shortdump setting.... "full"

Database server... "sfhprd01"

Database type..... "ORACLE"

Database name..... "PRC"

Database user ID.. "SAPSR3"

Char.set.... "C"

SAP kernel....... 700

created (date)... "Nov 18 2008 21:27:59"

create on........ "AIX 2 5 005DD9CD4C00"

Database version. "OCI_102 (10.2.0.2.0) "

Patch level. 185

Patch text.. " "

Database............. "ORACLE 9.2.0.., ORACLE 10.1.0.., ORACLE 10.2.0.."

SAP database version. 700

Operating system..... "AIX 1 5, AIX 2 5, AIX 3 5, AIX 1 6"

Memory consumption

Roll.... 16192

EM...... 37708632

Heap.... 0

Page.... 2072576

MM Used. 5284104

MM Free. 3092792

User and Transaction

Client.............. 300

User................ "SFHBGJOB"

Language key........ 1

Transaction......... " "

Transactions ID..... "4F36DE6EBCC40051E10080000A1B2366"

Program............. "SAPLDISP"

Screen.............. "SAPMSSY0 1000"

Screen line......... 6

Information on where terminated

Termination occurred in the ABAP program "SAPLDISP" - in "UPDATE_MDVM".

The main program was "SAPMSSY4 ".

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

of the (Include) program "LDISPF29".

The program "SAPLDISP" was started as a background job.

Job Name....... "SFH_D_PP02_PROD. PLANNING_0330"

Job Initiator.. "SFHBGJOB"

Job Number..... 05005200

The termination is caused because exception "CX_SY_OPEN_SQL_DB" occurred in

procedure "UPDATE_MDVM" "(FORM)", but it was neither handled locally nor

declared

in the RAISING clause of its signature.

The procedure is in program "SAPLDISP "; its source code begins in line

4 of the (Include program "LDISPF29 ".

Source Code Extract

Line SourceCde

71 WHERE mafid = am61d-mafid

72 AND mdkey = am61d-mdkey

73 AND ( gsaen <> xflag "TF091095

74 OR akken <> am61d-akken

75 OR resdt <> am61d-resdt

76 OR mpskz <> am61d-mpskz ).

77 ENDIF.

79 IF am61d-akken NE space

80 AND am61d-resdt EQ space

81 AND am61d-resal NE space.

82 UPDATE mdvm

83 SET gsaen = xflag

84 akken = am61d-akken

85 resal = am61d-resal

86 mpskz = am61d-mpskz

87 erdat = sy-datlo

88 ustmp = l_ustmp

89 WHERE mafid = am61d-mafid

90 AND mdkey = am61d-mdkey

91 AND ( gsaen <> xflag "TF091095

92 OR akken <> am61d-akken

93 *(del) OR RESAL <> AM61D-RESDT "Tippfehler "TF 3.0F

94 OR resal <> am61d-resal "TF 3.0F

95 OR mpskz <> am61d-mpskz ).

96 ENDIF.

98 IF am61d-akken NE space

99 AND am61d-resdt EQ space

100 AND am61d-resal EQ space.

>>>>> UPDATE mdvm

102 SET gsaen = xflag

103 akken = am61d-akken

104 mpskz = am61d-mpskz

105 erdat = sy-datlo

106 ustmp = l_ustmp

107 WHERE mafid = am61d-mafid

108 AND mdkey = am61d-mdkey

109 AND ( gsaen <> xflag "TF091095

110 OR akken <> am61d-akken

111 OR mpskz <> am61d-mpskz ).

112 ENDIF.

113

114 IF am61d-akken EQ space

115 AND am61d-resdt NE space

116 AND am61d-resal NE space.

117 UPDATE mdvm

118 SET gsaen = xflag

119 resdt = am61d-resdt

120 resal = am61d-resal