Unexpected database commit when displaying data using cl_salv_table

Question

In an ABAP program I have noticed unexpected persistence of data when displaying a local table using the class cl_salv_table.

To reproduce I have created a minimal code sample. The program does an insert, displays data in an ALV, then does a ROLLBACK WORK. I expect the inserted value to be present in the database BEFORE the rollback, and absent AFTER the rollback.

However, if between the insert and the rollback, an ALV grid is displayed, the data is persisted beyond the rollback, and immediately visible to other transactions.

Is this expected behaviour, and if so, how can I avoid this? We do use this class quite often and it looks like we may inadvertently commit values to database when we don't actually want to.

This is the code:

*&---------------------------------------------------------------------*
*& Report  zok_alv_commit
*&
*&---------------------------------------------------------------------*
*&
*&
*&---------------------------------------------------------------------*
REPORT zok_alv_commit.

SELECTION-SCREEN BEGIN OF BLOCK b1.

PARAMETERS: p_showtb TYPE boolean AS CHECKBOX DEFAULT abap_false.

SELECTION-SCREEN END OF BLOCK b1.

START-OF-SELECTION.

  DATA: lt_table TYPE TABLE OF zok_alv,
        ls_table TYPE zok_alv.

  """"""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""
  " Create new GUID and insert into table
  """"""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""
  TRY.
      ls_table-guid = cl_system_uuid=>create_uuid_c22_static( ).
    CATCH cx_uuid_error.
      " Error creating UUID
      MESSAGE e836(/basf/dps3_apodata).
  ENDTRY.
  WRITE: |Create guid { ls_table-guid } |, /.

  INSERT zok_alv FROM ls_table.
  APPEND ls_table TO lt_table.

  """"""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""
  " The important bit: show something in an ALV
  """"""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""

  IF p_showtb = abap_true.
    cl_salv_table=>factory(
      IMPORTING r_salv_table = DATA(lo_alv)
      CHANGING t_table = lt_table
    ).

    lo_alv->display( ).
  ENDIF.

  """"""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""
  " Check existence in table before and after rollback
  " Expectation: If the ALV is shown above, the data is already committed,
  " so the ROLLBACK WORK will not have an effect.
  """"""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""

  SELECT SINGLE guid FROM zok_alv INTO @DATA(lv_ignored) WHERE guid = @ls_table-guid.
  IF sy-subrc = 0.
    WRITE: 'GUID exists on DB before rollback.', /.
  ELSE.
    WRITE: 'GUID does NOT exist on DB before rollback.', /.
  ENDIF.

  ROLLBACK WORK.

  SELECT SINGLE guid FROM zok_alv INTO @lv_ignored WHERE guid = @ls_table-guid.
  IF sy-subrc = 0.
    WRITE: 'GUID exists on DB after rollback.', /.
  ELSE.
    WRITE: 'GUID does NOT exist on DB after rollback.', /.
  ENDIF.

It requires a table ZOK_ALV with only MANDT and a 22-character field GUID as primary key, nothing else.

When executing the code with p_showtb unchecked:

As you can see, the value is not present after the rollback, and not present in the table - as expected.

When executing the code with p_showtb checked:

At this point, already, the id is visible in SE16 in another session:

(I leave the ALV screen with Back (F3) at this point) The code confirms, the value is still present, even after the rollback:

Even after leaving the program, the values persist in the DB.

Well, that might be completely ok, because there are plenty of possibilities when an [implicit commit](https://help.sap.com/doc/abapdocu_752_index_htm/7.52/en-US/abendb_commit.htm#@@ITOC@@ABENDB_COMMIT_1) happens. — Jagger, Mar 29 '19 at 13:50
True, which still leaves us with the question "how can I avoid this" while still being able to display values in an ALV. (rewriting the application so that an implicit commit is not an issue is always the better option, but that may not be possible in all cases) — founderio, Mar 29 '19 at 13:57
From a design perspective, wouldn't it be much safer to display the data before writing to the DB, and provide a button to do the DB write, rather write to the DB, display, and try to rollback? — Bryan Cain, Mar 29 '19 at 15:31
Correct, which is also my perferred way of doing this. However, we may have to do some operations with user confirmation (e.g. ALV display) inside user exits working on uncommited state. This is just a thought experiment, though. — founderio, Mar 29 '19 at 15:37
@Jagger the link you have posted answers the main question ("*database commit is performed implicitly in the following situation: Completion of a dialog step ...*"), shouldn't you post it as an answer? — Sandra Rossi, Mar 29 '19 at 21:24
@founderio as your question focuses mainly on the implicit commit done by the dialog step, you should post another question for the "how to avoid", by giving the details of why you did it like that (insert + display + rollback). You can probably do it differently, like Bryan said. — Sandra Rossi, Mar 29 '19 at 21:28
@SandraRossi you are correct in that my question focuses on the implicit commit and "Is that a bug?", so your answer actually covers my issue. The original code does not exist in this form anymore, so I guess "That's the way it is" is an agreeable outcome. Quite right, I can do it differently :) — founderio, Mar 30 '19 at 22:32

score 6 · Accepted Answer · answered Mar 30 '19 at 17:38

To answer the 2 questions:

1) Yes, this is the "expected behavior" as stated in the database commit documentation :

a database commit is performed implicitly in the following situation: Completion of a dialog step ...

(it means that any display does a database commit)

This is because when the screen is displayed, SAP doesn't do anything except waiting for the user action, so the workprocess which was used to execute the ABAP code prior to the display can be reused for executing the ABAP code of requests from other users.

So that the workprocess can be reused, the memory of the workprocess (variables) is to be switched, it's called the roll-out/roll-in, which also requires that some system database tables are updated for internal SAP stuff and a database commit is needed for that. This is better explained in the documentation of SAP LUWs. I read that somewhere but I don't remember exactly where.

2) No, you can't "avoid this behavior", but considering your current logic of insert + display + rollback the insert, you can do one of these solutions but I recommend only the first one in your case, not the second one:

Change your logic to conform the SAP rule (i.e. any display does a database commit so bear with it). If your logic is really the one you said, then why do you want to insert something in the database and rollback it? Without further details, my answer is to just remove the insert and the rollback and keep the display. It would be pure speculation to answer something else because you didn't give enough details how your actual class really works (there must be a reason why it does insert + display + rollback, but what is missing in your explanation?) You'd better post another question and give all the details.
Second solution ("non-recommended, counter-performing and dangerous"), if you really really want to stick to your current logic: move your display to a RFC-enabled function module, and do CALL FUNCTION '...' DESTINATION 'NONE' KEEPING LOGICAL UNIT OF WORK (cf documentation). It's not recommended because it's for internal use only. It's counter-performing because it occupies 2 workprocesses at the same time. It's dangerous because "the worst case scenario may be a system shutdown".

score 4 · Answer 2 · edited Jun 20 '20 at 09:12

4

How to avoid implicit commit

To avoid implicit commit, you can wrap the INSERT in a UPDATE FUNCTION MODULE. CALL FUNCTION IN UPDATE TASK as SAP LUW, the actual insert will only happen when you call COMMIT WORK.

Quote from you：

However, if between the insert and the rollback, an ALV grid is displayed, the data is persisted beyond the rollback, and immediately visible to other transactions.

I suppose your database supports Committed Read

Committed read

In committed reads, only the user of a database LUW has access to data modified in the LUW. Other database users cannot access the modified data before a database commit is performed. In the case of reads, a shared lock is set (not possible for the modified data due to the existing exclusive lock). Data can be accessed only when released by a database commit.

But in SAP LUW, you cannot expect that you can select the inserted entry which is inserted in a UPDATE FUNCTION MODULE before the COMMIT WORK statement.

I propose you should always work on a internal table to do insert and ALV display, and provide a SAVE button to trigger the insert from internal tables to database table.

edited Jun 20 '20 at 09:12

Community

1
1

answered Mar 30 '19 at 14:39

Haojie

5,665
1
15
14

The way it's said is inexact: you can't "avoid the implicit commit" but you can delay the `INSERT` after the display, thus the implicit commit won't commit the insert. "Registering" the insert to be done after the display, and doing the insert after the display, are equivalent, so I don't see what bring the **update function modules** to the question. Secondly, the **committed reads** would concern other users but if their LUW start right after the insert/commit, then a committed read is of no use. Moreover, the OP doesn't want to commit, he wants to rollback. I just agree with the conclusion. – Sandra Rossi Mar 31 '19 at 18:30
No. You are not right. You can never rely on “delay the insert after the display”. Because you never know the below situations: a error message dialogue pops, a remote RFC called after you called some other service APIs , there would be implicit commit. The update function module only commit when you explicitly call COMMIT WORK on one SAP LUW. The author is manipulating the “committed reads” to insert and read with database table instead of internal table, what he does is totally wrong. – Haojie Apr 02 '19 at 22:46

Unexpected database commit when displaying data using cl_salv_table

2 Answers2

How to avoid implicit commit