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:
- Lock table / entries.
- Enclose database operation statements for logically linked tables in TRY-CATCH-ENDTRY.
- Perform DB operations. Check sy-dbcnt.
- Perform ROLLBACK WORK in CATCH.
- If no exception caught, perform COMMIT WORK.
- 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 |
|
|
UPDATE | |
Succeed | Fail |
|
|
DELETE | |
Succeed | Fail |
|
|
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.