topic Re: How to avoid dead lock error while updating ADRC table. in Application Development and Automation Discussions

How to avoid dead lock error while updating ADRC table.

Former Member — Sun, 10 Mar 2013 23:23:14 GMT

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

Re: How to avoid dead lock error while updating ADRC table.

guilherme_frisoni — Mon, 11 Mar 2013 02:17:40 GMT

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

Re: How to avoid dead lock error while updating ADRC table.

Former Member — Mon, 11 Mar 2013 08:59:27 GMT

Hi Deepthy,

Refer to link

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

Thanks,

Tooshar Bendale

Re: How to avoid dead lock error while updating ADRC table.

Former Member — Tue, 12 Mar 2013 06:43:30 GMT

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

Re: How to avoid dead lock error while updating ADRC table.

Former Member — Tue, 12 Mar 2013 12:09:43 GMT

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

Re: How to avoid dead lock error while updating ADRC table.

RaymondGiuseppi — Tue, 12 Mar 2013 12:38:31 GMT

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

Re: How to avoid dead lock error while updating ADRC table.

Former Member — Wed, 13 Mar 2013 04:10:21 GMT

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?

Re: How to avoid dead lock error while updating ADRC table.

Former Member — Wed, 13 Mar 2013 04:11:24 GMT

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?

Re: How to avoid dead lock error while updating ADRC table.

Former Member — Wed, 13 Mar 2013 04:13:13 GMT

Hi Raymond,

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

Re: How to avoid dead lock error while updating ADRC table.

RaymondGiuseppi — Wed, 13 Mar 2013 13:02:40 GMT

Will work fine if every program use the same lock.

Regards,

Raymond

Re: How to avoid dead lock error while updating ADRC table.

Former Member — Wed, 13 Mar 2013 13:42:25 GMT

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

Re: How to avoid dead lock error while updating ADRC table.

Former Member — Tue, 19 Mar 2013 05:17:21 GMT

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?

Re: How to avoid dead lock error while updating ADRC table.

RaymondGiuseppi — Tue, 19 Mar 2013 07:54:53 GMT

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

Re: How to avoid dead lock error while updating ADRC table.

gaurab_banerji — Tue, 19 Mar 2013 08:31:42 GMT

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

Re: How to avoid dead lock error while updating ADRC table.

Former Member — Tue, 19 Mar 2013 13:32:06 GMT

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

Re: How to avoid dead lock error while updating ADRC table.

Former Member — Thu, 21 Mar 2013 04:48:22 GMT

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.

Re: How to avoid dead lock error while updating ADRC table.

rosenberg_eitan — Thu, 21 Mar 2013 06:34:41 GMT

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.

Re: How to avoid dead lock error while updating ADRC table.

gaurab_banerji — Thu, 21 Mar 2013 07:20:50 GMT

BAPI_ADDRESSORG_CHANGE

ref:

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

Re: How to avoid dead lock error while updating ADRC table.

RaymondGiuseppi — Thu, 21 Mar 2013 12:56:42 GMT

and only if...

Re: How to avoid dead lock error while updating ADRC table.

RaymondGiuseppi — Thu, 21 Mar 2013 12:57:35 GMT

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

Regards,

Raymond