How to avoid dead lock error while updating ADRC t...

Former Member · ‎2013 Mar 10

Hi,

There is one enhancement while changing vendor address which will update tables LFA1, ADR6 and ADRC.

While updating table ADRC, i'm receiving deadlock error.

DBIF_RSQL_SQL_ERROR - ORA-00060: deadlock detected while waiting for resource

>>>>> UPDATE adrc SET name1 = lv_name_first

name2 = lv_name_second

name3 = lv_name_third

mc_name1 = lv_fullname

flagcomm6 = lv_email_flg

WHERE addrnumber = lv_adrnr.

IF sy-subrc = 0.

COMMIT WORK.

ENDIF.

Any suggestion, how can I avoid this error?

Thanks

Deepthi

guilherme_frisoni · ‎2013 Mar 11

Hi Deepthi,

are you sure the error is in this sql satement?

It seems to be correct.

What are the others sql statements before this one? Are they inside any loop?

Guilherme Frisoni

Former Member · ‎2013 Mar 11

Hi Deepthy,

Refer to link

http://scn.sap.com/thread/1436188

Thanks,

Tooshar Bendale

Former Member · ‎2013 Mar 12

Hi Deepthi,

Instead of Directly specifying Fields in the query, use a Internal table like adrc and update adrc from that internal table.

Thank You,

Pratheek

Former Member · ‎2013 Mar 13

Hi Pratheek,

Instead of Directly specifying Fields in the query, use a Internal table like adrc and update adrc from that internal table.

How will it make any difference. Can you please explain further?

Former Member · ‎2013 Mar 12

Hello Deepthi,

Lock objects should always be requested before updating a database.

Are you requesting the lock object ESADRC (function module ENQUEUE_ESADRC ) before the update?

If not, this will resolve your issue.

Don't forget to release the lock object via DEQUEUE_ESADRC before leaving the ehancement point.

Regards,

Kim

Former Member · ‎2013 Mar 13

Hi Kim,

Lock objects should always be requested before updating a database.
Are you requesting the lock object  ESADRC (function module ENQUEUE_ESADRC ) before the update?
 If not, this will resolve your issue. 
 Don't forget to release the lock object via DEQUEUE_ESADRC before leaving the ehancement point.

I'm leaning towards this solution. Is there any way we can prove this solution in Development system first?

Former Member · ‎2013 Mar 13

Hi Deepthi,

Sure, you can prove this in development.

To test, you just need to have an address open for edit while you try to update via the enhancement.

I highly suggest, you debug your deadlock error to understand "who" requested the lock first. Then you can recreate the same scenario in development.

You can utilize t-code SM12 in your analysis. View the locks in SM12 at the time your enhancement requests the lock.

Also, understanding "who" locks the record will drive your solution.

For instance, it may be the case that the program that executes your enhancement may lock the record before it reaches your enhancement.

I ran into this once. In my solution, I ran the ENQUEUE function module. If it returns an error, I check the returned message and see if the lock was requested by the same user as SY-UNAME.

If so, I executed the DEQUEUE, followed by the ENQUEUE ( to block anyone else). If ENQUEUE was successful, applied my update. You may or may not need to run the DEQUEUE again, depending on what happens next, in the processes following the enhancement.

Alternatively, you can follow Raymond's suggestion of using SELECT WITH UPDATE.

Place the SELECT WITH UPDATE in a separate form or method.

CALL the new form/method and catch any exceptions.

If the exception is a deadlock, you could perform the ENQUEUE/DEQUEUE approach or keep calling the SELECT WITH UPDATE for a limited number of times.

I am interested to know what you find when investigate the origination of the first lock.

Regards,

Kim

Former Member · ‎2013 Mar 19

Hi Kim,

I highly suggest, you debug your deadlock error to understand "who" requested the lock first.  Then you can recreate the same scenario in development.
You can utilize t-code SM12 in your analysis. View the locks in SM12 at the time your enhancement requests the lock.
Also, understanding "who" locks the record will drive your solution.

I tried to replicate the scenario in TEST system by

1. Execute ENQUEUE_ESADRC from SE37 by providing Address number. Once it is done, we can see the lock entry in SM12 for table ADRC with my address number.

2. Execute XK02 where it will touch enhancement code. It is updating the values without any short dumps or locking error.

Now it is more confusing now as update is working fine even after locking the address. Any suggestions?

RaymondGiuseppi · ‎2013 Mar 19

As I already wrote, the problem does not come from an Abap lock but from a database lock...

So my suggestion to try a SELECT ... FOR UPDATE with a TRY/CATCH CX_SY_OPEN_SQL_DB/ENDTRY in a DO/ENDDO loop trying the update. If too many tries don't success to lock the record, then your own/previous program stack may have locked the record, especially if you are executing from an exit or a BAdI.

Regards,

Raymond

Former Member · ‎2013 Mar 19

Hi Deepthi,

Have a different developer run SE37 for the first lock. Then you run XK02 and see if you get the deadlock error.

If the deadlock error occurs in this scenario, then you know it happens when some other user ID has the address record locked.

In this case, you call the lock object (ENQUEUE_ESADRC) using the 'WAIT' option.

Regards,

Kim

Former Member · ‎2013 Mar 21

Hi Raymond & Kim,

I took oracle log trace files from Basis. Attached 2 sample files.

My enhancement is in program SAPMF02K in subroutine "Leave_to_transaction".

I took below information from 5 trace files where my dead lock error happens.

1. client info: 0:FK03:RSM13000

application name: SAPLSZA0, hash value=1928680605

2. client info: 0:XK03:RSM13000

application name: SAPLSZA0, hash value=1928680605

3. client info: 0:XK03:RSM13000

application name: SAPLF02K, hash value=3214628157

4. client info: 0:XK03:RSM13000

application name: SAPLSZA0, hash value=1928680605

5. client info: 0:XK03:RSM13000

application name: SAPLSZA0, hash value=1928680605

I couldn't able to replicate this error in TEST, but what I found is looks like ADRC is trying to update twice when my enhancement runs . Below ST05 trace analysis log for reference, over there I can see clearly that ADRC is updating twice.

ADRC REEXEC 1 0 UPDATE SET "DATE_TO"=99991231 , "TITLE"=' ' , "NAME1"='TEST' , "NAME2"='BUGEJA' , "NAME3"=' ' , "NAME4"=' ' , "NAME_TEXT"=' ' , "NAME_CO"=' ' , "CITY1"='MOLONG' , "CITY2"=' ' , "CITY_CODE"=' ' , "CITYP_COD...

LFA1 PREPARE 0 UPDATE SET "NAME1"=:A0 , "NAME2"=:A1 , "NAME3"=:A2 , "MCOD1"=:A3 WHERE "MANDT"=:A4 AND "LIFNR"=:A5 29,504

LFA1 EXECSTM 1 0 UPDATE SET "NAME1"='TEST BUGEJA' , "NAME2"=' ' , "NAME3"=' ' , "MCOD1"='NICOLE BUGEJA' WHERE "MANDT"='410' AND "LIFNR"='0100044392'

ADRC PREPARE 0 UPDATE SET "NAME1"=:A0 , "NAME2"=:A1 , "NAME3"=:A2 , "MC_NAME1"=:A3 , "FLAGCOMM6"=:A4 WHERE "CLIENT"=:A5 AND "ADDRNUMBER"=:A6 1,837

ADRC EXECSTM 1 0 UPDATE SET "NAME1"='TEST BUGEJA' , "NAME2"=' ' , "NAME3"=' ' , "MC_NAME1"='TEST BUGEJA' , "FLAGCOMM6"='X' WHERE "CLIENT"='410' AND "ADDRNUMBER"='0000109983'

COMMIT WORK ON CONNECTION 0

I tried to see if the address number is locking when my UPDATE statement runs. I switched on debugging and I stopped at UPDATE point and checked in FM "ENQUEUE_ESADRC" to see if any locks are there for this address number, I couldn't able to find any locks.

I want to replicate this scenario first in TEST before I put any of my code. Any suggestions.

Former Member · ‎2013 Mar 21

Hello Deepthi,

In your ST05 trace analysis it looks like these are two different records:

NAME1 = 'TEST' and NAME1 = 'TEST BUGEJA'.

Is that correct?

Regards,

Kim

RaymondGiuseppi · ‎2013 Mar 12

This is a database lock, a solution could be to lock record of ADRC before updating - read syntax of FOR UPDATE in SELECT statement, and loop at the statement if sy-subrc eq 8.

Regards,

Raymond

Former Member · ‎2013 Mar 13

Hi Raymond,

Thinking of to implement function module ENQUEUE_ESADRC and DEQUEUE_ESADRC before and after updating ADRC. What do you think?

RaymondGiuseppi · ‎2013 Mar 13

Will work fine if every program use the same lock.

Regards,

Raymond

RaymondGiuseppi · ‎2013 Mar 21

and only if...

gaurab_banerji · ‎2013 Mar 19

do 50 times.

call function ENQUEUE_ESADRC.

if sy-subrc eq 0.

exit.

endif.

enddo.

if sy-subrc = 0 "that means it is enqueued

endif.

then dequeue

rosenberg_eitan · ‎2013 Mar 21

Hi all.

I was under the impression that direct update of SAP table is a no no...
Is there no bapi to do the job ?

Regards.

gaurab_banerji · ‎2013 Mar 21

BAPI_ADDRESSORG_CHANGE

ref:

https://scn.sap.com/thread/71032

RaymondGiuseppi · ‎2013 Mar 21

There are FM to update database, usually those are Update FM executed in update task.

Regards,

Raymond

By Category

Related Content

Activity Groups

Industry Groups

Influence and Feedback Groups

Interest Groups

Location Groups

Customer Only Groups

Forums

Related Resources

Products

Learning and Support

About

My SAP Profile

My SAP Profile

How to avoid dead lock error while updating ADRC table.