topic Re: How to prevent two DB update inconsistent issue? in Application Development and Automation Discussions

How to prevent two DB update inconsistent issue?

former_member587838 — Mon, 16 Jul 2018 02:41:10 GMT

Hello Community,

is there a possibility to prevent two DB update inconsistent?

I'm working on a program that want to update two add-on tables at the same time, but sometimes only header table (zheader) commit successfully, item table (zitem) not been update but without any exception.

Is any issue for below code?　or I need to use in "update task update" for below update?

MODIFY zheader FROM TABLE it_zheader.
IF sy-subrc = 0. 
  l_flag = 'X'.
ELSE.
  l_flag = ''.
ENDIF.


MODIFY zitem FROM TABLE it_zitem.
IF sy-subrc = 0 AND l_flag = 'X'.
  l_flag = 'X'.
ELSE.
  l_flag = ''.
ENDIF.

IF l_flag = 'X'.
   COMMIT WORK AND WAIT.
ELSE.
  ROLLBACK WORK.
  MESSAGE s398 DISPLAY LIKE 'I' WITH 'Update table Successfully!'.
ENDIF.

Thank you.

Regards,

Nicole

Re: How to prevent two DB update inconsistent issue?

matt — Mon, 16 Jul 2018 05:02:45 GMT

Please in future use the "code" button in the editor when posting code. I've done it for you this time.

Your coding is poor. First of all, l_flag is not a meaningful name for a variable. How about previous_modify_successful, and use the provided abap_true and abap_false instead of arbitrary '' and 'X'. But the structure is also not good. as you attempt an update of zitem even when the zheader update has already failed.

MODIFY zheader FROM TABLE it_zheader.
IF sy-subrc = 0.  
  MODIFY zitem FROM TABLE it_zitem.
  IF sy-subrc = 0 
    COMMIT WORK AND WAIT.
    db_modify_was_successful = abap_true.
  ENDIF.
ENDIF.
If db_modify_was_successful = abap_false.
  ROLLBACK WORK.
  MESSAGE s398 DISPLAY LIKE 'I' WITH 'Update table Successfully!'.
ENDIF

Do you know it_item isn't empty? Can you reproduce your observed issue? Have you tried debugging?

Re: How to prevent two DB update inconsistent issue?

former_member587838 — Mon, 16 Jul 2018 06:12:30 GMT

Dear Mattehew,
Thanks for your good comments. actually the structure in our production is not like this (this is just a sample), we have the logic in previously form to check it_item empty or not, and this program already run for some time in production environment. don't know why some times item table update failed (we had traced item structure not empty at that moment, no any exception or DB lock occurred).

Re: How to prevent two DB update inconsistent issue?

RaymondGiuseppi — Mon, 16 Jul 2018 07:05:32 GMT

Please move the success message in the successful IF case...
As MODIFY would, by conception, not raise an error for duplicate primary keys, you should look for secondary unique indexes and locked records (at database level , what did you code to prevent this case) or even in system or database log.

Re: How to prevent two DB update inconsistent issue?

former_member587838 — Tue, 17 Jul 2018 01:34:17 GMT

//Correct my code due my typo for message control as below, and highlight that I just need to update the exist record's status in these two tables, not insert new record, tks. 

MODIFY zheader FROM TABLE it_zheader.
IF sy-subrc = 0. 
  l_flag = 'X'.
ELSE.
  l_flag = ''.
ENDIF.

MODIFY zitem FROM TABLE it_zitem.
IF sy-subrc = 0 AND l_flag = 'X'.
  l_flag = 'X'.
ELSE.
  l_flag = ''.
ENDIF.

IF l_flag = 'X'.
   COMMIT WORK AND WAIT.
ELSE.
  ROLLBACK WORK.
  MESSAGE s398 DISPLAY LIKE 'I' WITH 'Update table Error!'.
ENDIF.

Re: How to prevent two DB update inconsistent issue?

former_member587838 — Tue, 17 Jul 2018 01:39:30 GMT

I just want to update some field's value for exist record's in these two tables, and I also can make sure that just no one update the same record at the same time, do I still need to lock the records?

Re: How to prevent two DB update inconsistent issue?

matt — Tue, 17 Jul 2018 07:09:36 GMT

You must use the SAP locking mechanism if there's a chance of concurrent update.

Re: How to prevent two DB update inconsistent issue?

RaymondGiuseppi — Tue, 17 Jul 2018 07:20:25 GMT

And each and every program that update those tables must use the same enqueue mechanism of course.

Re: How to prevent two DB update inconsistent issue?

former_member587838 — Wed, 18 Jul 2018 02:01:41 GMT

You mean use ENQUEUE and DEQUEUE lock mechanism to lock these two table? or any other?

Re: How to prevent two DB update inconsistent issue?

RaymondGiuseppi — Wed, 18 Jul 2018 06:42:02 GMT

Yes every program that update this table should use the same enqueue (e.g. E_TABLE)
The COMMIT/ROLLBACK WORK will remove the lock, so no explicit call of DEQUEUE should be required.