Maintain Data Consistency When Using Database Operations

By
SAP Flash
-

It is crucial to maintain data integrity and consistency especially for complex programs that will update several custom tables that are logically linked together via primary and foreign keys.

In this case study, I’ll show you how bad practices can lead to data inconsistencies and the solution to it.

My key approach to maintain data consistency across several tables while performing database operations:

  1. Lock table / entries.
  2. Enclose database operation statements for logically linked tables in TRY-CATCH-ENDTRY.
    • Perform DB operations. Check sy-dbcnt.
    • Perform ROLLBACK WORK in CATCH.
  3. If no exception caught, perform COMMIT WORK.
  4. Unlock table / entries.

As you may have already aware, there are several ways to make changes to data in the database such as INSERT, UPDATE, DELETE and MODIFY statement. Let’s look at what happens for each statement (except MODIFY as I personally don’t recommend it due to its performance inefficient nature) when it succeeded or failed.

Database Operations Behaviour

Summary

DB Operation Scenario Status sy-subrc Short Dump
Insert
Insert new entries without duplicates in internal table. Success 0 –
Insert new entries where key fields’ value in internal table already exists in database table. Failed – X
Insert new entries with key fields’ duplicates in internal table. Failed – X
Insert new entries with duplicates in internal table with ACCEPTING DUPLICATE KEYS. Failed 4 –
Update
Update existing entries without duplicates in internal table. Success 0 –
Update existing entries with duplicates in internal table. Success 0 –
Update entries from internal table but not exist in database table. Failed 4 –
Delete
Delete existing entries without duplicates in internal table. Success 0 –
Delete certain entries failed. Success 0 –
No entries can be deleted. Failed 4 –
Delete entries from internal table but not exist in database table. Failed 4 –

Details

INSERT
Succeed Fail
  • Number of entries inserted will be set at sy-dbcnt.
  • Insert new entries where key fields value in internal table already exist in database table entry will lead to short dump and this must be handled by catch exception.
  • Insert new entries with key fields duplicates in internal table will lead to short dump and this must be handled by catch exception and perform ROLLBACK WORK. If not, system will still save the first entry in the internal table to the database through implicit commit.
  • Insert new entries with duplicates in internal table with ACCEPTING DUPLICATE KEYS, this will not lead to short dump, but it will lead to sy-subrc = 4. We must use IF sy-subrc <> 0 and raise an exception to ROLLBACK WORK, else program will still insert first entry of all duplicate sets from the internal table to database via implicit commit.
UPDATE
Succeed Fail
  • Number of entries updated will be set at sy-dbcnt.
  • Update entries with duplicates in internal table will not lead to short dump but lead to sy-subrc = 0. Table will be updated with the value from the last entry of the internal table.
  • If there’s a row of entry in internal table to be updated not exist in database table, short dump will not happen but sy-subrc = 4 will occur. Other entries will still get updated if it exists in database table.
DELETE
Succeed Fail
  • Number of entries deleted will be set at sy-dbcnt.
  • When there is more than 1 database entries have to be deleted using internal table entries exist in database table, as long as 1 entry can be deleted, sy-subrc will still be 0. Thus, it is not accurate to verify using sy-subrc. We must use sy-dbcnt and compare with the number of records in the internal table.
  • When no rows can be deleted, sy-subrc will be 4.
  • If there’s a row of entry in internal table to be deleted not exist in database table, short dump will not happen but sy-subrc = 4 will occur. Entries that exist in database table will still be deleted even though sy-subrc = 4. Thus, ROLLBACK WORK is required if you do not want partial deletion.

It is important to use ROLLBACK WORK in case of failure to insert/update/delete table entries. Otherwise, implicit commit will still cause data inconsistencies. Several scenarios that can cause implicit commit are explained here.

Let’s start our case study. For this example, we will be updating 3 custom tables as follows.

Bad Practice

Scenario 1: Insert Data

Data inconsistency will happen when not all 3 tables are inserted with entries contained in the internal tables. Normally, I’d use object oriented approach with MVC, but I have omitted it here for simplicity purpose.

***********************************************************************
* REPORT         : ZLOCAL_OO
***********************************************************************
* SAPFlash
***********************************************************************
REPORT zsf_data_consistency.

*----------------------------------------------------------------------*
*       CLASS cl_local_app DEFINITION
*----------------------------------------------------------------------*
*       Class Definition for cl_local_app
*----------------------------------------------------------------------*
CLASS cl_local_app DEFINITION FINAL.

  PUBLIC SECTION.

    METHODS:
      insert_data.

  PRIVATE SECTION.

    DATA:
      mt_tbl01_ins TYPE STANDARD TABLE OF ztbl01,
      mt_tbl02_ins TYPE STANDARD TABLE OF ztbl02,
      mt_tbl03_ins TYPE STANDARD TABLE OF ztbl03.
ENDCLASS.
*----------------------------------------------------------------------*
*       CLASS cl_local_app IMPLEMENTATION
*----------------------------------------------------------------------*
*       Class Implementation for cl_local_app
*----------------------------------------------------------------------*
CLASS cl_local_app IMPLEMENTATION.
*&---------------------------------------------------------------------*
*&      INSERT_DATA
*&---------------------------------------------------------------------*
*       Insert data
*----------------------------------------------------------------------*
  METHOD insert_data.

    "Assuming all internal tables are already filled
    INSERT ztbl01 FROM TABLE mt_tbl01_ins.
    INSERT ztbl02 FROM TABLE mt_tbl02_ins.
    INSERT ztbl03 FROM TABLE mt_tbl03_ins.
  ENDMETHOD.
ENDCLASS.

START-OF-SELECTION.

  DATA(gr_app) = NEW cl_local_app( ).
  gr_app->insert_data( ).

Scenario 2: Delete Data

Data inconsistency will happen when not all 3 tables are deleted with entries contained in the internal tables.

***********************************************************************
* REPORT         : ZLOCAL_OO
***********************************************************************
* SAPFlash
***********************************************************************
REPORT zsf_data_consistency.

*----------------------------------------------------------------------*
*       CLASS cl_local_app DEFINITION
*----------------------------------------------------------------------*
*       Class Definition for cl_local_app
*----------------------------------------------------------------------*
CLASS cl_local_app DEFINITION FINAL.

  PUBLIC SECTION.

    METHODS:
      delete_data.

  PRIVATE SECTION.

    DATA:
      mt_tbl01_del TYPE STANDARD TABLE OF ztbl01,
      mt_tbl02_del TYPE STANDARD TABLE OF ztbl02,
      mt_tbl03_del TYPE STANDARD TABLE OF ztbl03.
ENDCLASS.
*----------------------------------------------------------------------*
*       CLASS cl_local_app IMPLEMENTATION
*----------------------------------------------------------------------*
*       Class Implementation for cl_local_app
*----------------------------------------------------------------------*
CLASS cl_local_app IMPLEMENTATION.
*&---------------------------------------------------------------------*
*&      DELETE_DATA
*&---------------------------------------------------------------------*
*       Delete data
*----------------------------------------------------------------------*
  METHOD delete_data.

    "Assuming all internal tables are already filled
    DELETE ztbl01 FROM TABLE mt_tbl01_del.
    DELETE ztbl02 FROM TABLE mt_tbl02_del.
    DELETE ztbl03 FROM TABLE mt_tbl03_del.
  ENDMETHOD.
ENDCLASS.

START-OF-SELECTION.

  DATA(gr_app) = NEW cl_local_app( ).
  gr_app->delete_data( ).

Scenario 3: Delete and Insert Data

Data inconsistency will happen if deletions are successful but insertion failed, assuming in this case every line deleted must be inserted with new line because we want to update key field value (VALTO).

***********************************************************************
* REPORT         : ZLOCAL_OO
***********************************************************************
* SAPFlash
***********************************************************************
REPORT zsf_data_consistency.

*----------------------------------------------------------------------*
*       CLASS cl_local_app DEFINITION
*----------------------------------------------------------------------*
*       Class Definition for cl_local_app
*----------------------------------------------------------------------*
CLASS cl_local_app DEFINITION FINAL.

  PUBLIC SECTION.

    METHODS:
      del_ins_data.

  PRIVATE SECTION.

    DATA:
      mt_tbl01_ins TYPE STANDARD TABLE OF ztbl01,
      mt_tbl02_ins TYPE STANDARD TABLE OF ztbl02,
      mt_tbl03_ins TYPE STANDARD TABLE OF ztbl03,
      mt_tbl01_del TYPE STANDARD TABLE OF ztbl01,
      mt_tbl02_del TYPE STANDARD TABLE OF ztbl02,
      mt_tbl03_del TYPE STANDARD TABLE OF ztbl03.
ENDCLASS.
*----------------------------------------------------------------------*
*       CLASS cl_local_app IMPLEMENTATION
*----------------------------------------------------------------------*
*       Class Implementation for cl_local_app
*----------------------------------------------------------------------*
CLASS cl_local_app IMPLEMENTATION.
*&---------------------------------------------------------------------*
*&      DEL_INS_DATA
*&---------------------------------------------------------------------*
*       Delete and insert data
*----------------------------------------------------------------------*
  METHOD del_ins_data.

    "Assuming all internal tables are already filled
    DELETE ztbl01 FROM TABLE mt_tbl01_del.
    DELETE ztbl02 FROM TABLE mt_tbl02_del.
    DELETE ztbl03 FROM TABLE mt_tbl03_del.

    "Assuming all internal tables are already filled
    INSERT ztbl01 FROM TABLE mt_tbl01_ins.
    INSERT ztbl02 FROM TABLE mt_tbl02_ins.
    INSERT ztbl03 FROM TABLE mt_tbl03_ins.
  ENDMETHOD.
ENDCLASS.

START-OF-SELECTION.

  DATA(gr_app) = NEW cl_local_app( ).
  gr_app->del_ins_data( ).

Scenario 4: Update Data

Data inconsistencies will happen if not all 3 tables are successfully updated, assuming in this case all 3 tables have update dependencies.

***********************************************************************
* REPORT         : ZLOCAL_OO
***********************************************************************
* SAPFlash
***********************************************************************
REPORT zsf_data_consistency.

*----------------------------------------------------------------------*
*       CLASS cl_local_app DEFINITION
*----------------------------------------------------------------------*
*       Class Definition for cl_local_app
*----------------------------------------------------------------------*
CLASS cl_local_app DEFINITION FINAL.

  PUBLIC SECTION.

    METHODS:
      update_data.

  PRIVATE SECTION.

    DATA:
      mt_tbl01_upd TYPE STANDARD TABLE OF ztbl01,
      mt_tbl02_upd TYPE STANDARD TABLE OF ztbl02,
      mt_tbl03_upd TYPE STANDARD TABLE OF ztbl03.
ENDCLASS.
*----------------------------------------------------------------------*
*       CLASS cl_local_app IMPLEMENTATION
*----------------------------------------------------------------------*
*       Class Implementation for cl_local_app
*----------------------------------------------------------------------*
CLASS cl_local_app IMPLEMENTATION.
*&---------------------------------------------------------------------*
*&      UPDATE_DATA
*&---------------------------------------------------------------------*
*       Update data
*----------------------------------------------------------------------*
  METHOD update_data.

    "Assuming all internal tables are already filled
    UPDATE ztbl01 FROM TABLE mt_tbl01_upd.
    UPDATE ztbl02 FROM TABLE mt_tbl02_upd.
    UPDATE ztbl03 FROM TABLE mt_tbl03_upd.
  ENDMETHOD.
ENDCLASS.

START-OF-SELECTION.

  DATA(gr_app) = NEW cl_local_app( ).
  gr_app->update_data( ).

I’d like to highlight for update operations, even if you have duplicate key fields entries in internal table, program will not lead to short dump but sy-subrc = 0. All entries will be updated into the table from first to last entry by overwriting the same field in accordance to the key fields. Thus, only the value from the last entry of the internal table is retained.

Here’s an example data to be updated with duplicate key fields (MANDT and MATNR are the key fields).

This is existing data in database table.

As you can see below, FIELD1 is updated with the value of the last entry from the internal table.

Good Practice

Step 1: Locking

There are 2 ways for locking; either locks the entire table or specific entries.
For this scenario, I shall use lock by entries.

"Assuming all internal tables are already filled
LOOP AT mt_tbl01_del ASSIGNING FIELD-SYMBOL(<ls_tbl01_del>).
  CALL FUNCTION 'ENQUEUE_EZTBL01'
    EXPORTING
      mode_ztbl01    = 'X'
      mandt          = sy-mandt
      matnr          = <ls_tbl01_del>-matnr
    EXCEPTIONS
      foreign_lock   = 1
      system_failure = 2
      OTHERS         = 3.
  IF sy-subrc <> 0.
    "Issue message data has been locked by other user
    RAISE EXCEPTION TYPE zcx_exp
      MESSAGE s001(zmc) WITH sy-msgv1.
  ENDIF.
ENDLOOP.

LOOP AT mt_tbl02_del ASSIGNING FIELD-SYMBOL(<ls_tbl02_del>).
  CALL FUNCTION 'ENQUEUE_EZTBL02'
    EXPORTING
      mode_ztbl02    = 'X'
      mandt          = sy-mandt
      matnr          = <ls_tbl02_del>-matnr
      kunnr          = <ls_tbl02_del>-kunnr
    EXCEPTIONS
      foreign_lock   = 1
      system_failure = 2
      OTHERS         = 3.
  IF sy-subrc <> 0.
    "Issue message data has been locked by other user
    RAISE EXCEPTION TYPE zcx_exp
      MESSAGE s001(zmc) WITH sy-msgv1.
  ENDIF.
ENDLOOP.

LOOP AT mt_tbl03_del ASSIGNING FIELD-SYMBOL(<ls_tbl03_del>).
  CALL FUNCTION 'ENQUEUE_EZTBL03'
    EXPORTING
      mode_ztbl03    = 'X'
      mandt          = sy-mandt
      matnr          = <ls_tbl03_del>-matnr
      valto          = <ls_tbl03_del>-valto
    EXCEPTIONS
      foreign_lock   = 1
      system_failure = 2
      OTHERS         = 3.
  IF sy-subrc <> 0.
    "Issue message data has been locked by other user
    RAISE EXCEPTION TYPE zcx_exp
      MESSAGE s001(zmc) WITH sy-msgv1.
  ENDIF.
ENDLOOP.

This section contains premium content accessible by paid members only. Please click here to join our membership to support us.

For premium members, please login below to access this content.

RELATED ARTICLESMORE FROM AUTHOR