Skip to Content
author's profile photo Former Member
Former Member

How to use COMMIT

Hi,

I want to update a lot of rows, say for example 1 million rows.

If I do it like a for loop:

LOOP

UPDATE

SET

WHERE

ENDLOOP

Do I have to use the keyword "commit work" somewhere?

Or is commit being done automatically after each update or after the whole abap report is done?

regards

Baran

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

5 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Oct 17, 2006 at 01:34 PM

    Baran,

    It is best not to use within an uppdate program.

    IF the program dumps during execution, NONE of the records are updated (committed) to the DB; and you can address the issue. Then you can re-run the update program. This follows SAP's concept of a LUW (Logic Unit of Work).

    IF you add in "re-run" logic that can track IF a record has been processed, then using a COMMIT WORK within the code is "OK"... but it still violates the LUW concept.

    As a rule, you should try VERY hard to not use COMMIT WORK within your code.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Oct 17, 2006 at 01:24 PM

    hi,

    you can do either way ... say commit work after the loop or anywhere within the program .. it is not mandatatory to use

    ... it is better to use in your case as you are updating too many records ....

    from help/\...

    COMMIT

    Variants:

    1. COMMIT WORK.

    2. COMMIT CONNECTION con.

    Effect

    Executes a database commit and thus closes a Logical Unit of Work ( LUW) (see also Transaction Processing). This means that:

    All database updates are made irrevocable and cannot be reversed with ROLLBACK WORK

    All database locks are released.

    COMMIT WORK belongs to the Open-SQL command set.

    Variant 1

    COMMIT WORK.

    Addition:

    ... AND WAIT

    Effect

    COMMIT WORK also

    Calls the subroutines specified by PERFORM ... ON COMMIT

    Executes asynchronously any update requests (see CALL FUNCTION ... IN UPDATE TASK) specified in these subroutines or started just before

    Processes the function modules specified in CALL FUNCTION ... IN BACKGROUND TASK

    Resets the time slice counter to 0.

    Ensures that the locks of the SAP locking concept are handled in accordance with its SCOPE Parameter.

    Executes a database commit on all database connections

    Closes all open database cursors (OPEN CURSOR) on all database connections

    Releases all database locks on all database connections

    After the COMMIT WORK has been executed, the event RAISE_TRANSACTION_FINISHED of the class CL_SYSTEM_TRANSACTION_STATE with parameter value CL_SYSTEM_TRANSACTION_STATE=>COMMIT_WORK is triggered. However, this does not apply to the COMMIT WORK at the end of the update.

    The Return Code SY-SUBRC is set to 0.

    Notes

    All subroutines called with PERFORM ... ON COMMIT are processed in the LUW concluded by the COMMIT WORK command. All V1 update requests specified in CALL FUNCTION ... IN UPDATE TASK are also executed in one LUW. When all V1 update requests have been successfully concluded, the V2 update requests (update with start delayed ) are processed, each in one LUW. Parallel to this, the function modules specified in CALL FUNCTION ... IN BACKGROUND TASK are each executed in one LUW per destination.

    COMMIT WORK commands processed within CALL DIALOG processing do all of the following:

    - A database commit on all database connections (see above),

    - Close all open database cursors on all database connections

    - Reset the time slice counter

    - Call the function modules registered in CALL DIALOG processing using CALL FUNCTION IN BACKGROUND TASK

    However, subroutines and function modules called with PERFORM ... ON COMMIT or CALL FUNCTION ... IN UPDATE TASK in CALL DIALOG processing are not executed in the calling transaction until a COMMIT WORK occurs.

    Since COMMIT WORK closes all open database cursors, any attempt to continue a SELECT loop after a COMMIT WORKresults in a runtime error. For the same reason, a FETCH after a COMMIT WORK on the now closed cursors also produces a runtime error. You must therefore ensure that any open cursors are no longer used after the COMMIT WORK.

    COMMIT WORK may not be used during the update (CALL FUNCTION ... IN UPDATE TASK) or during the execution of FORMs that were registered using PERFORM ... ON COMMIT or PERFORM ... ON ROLLBACK.

    With batch input and CALL TRANSACTION ... USING, COMMIT WORK successfully concludes the processing.

    REgards,

    Santosh

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Oct 17, 2006 at 01:37 PM

    If you are updating a lot of records, you will probably have to put a commit work after every few thousand records; otherwise, you will run into problems when you exceed the space limit of the roll area.

    I would loop at the internal table into a second one of the second structure. Every 5000 records or so, update the database table from the second table, commit, clear the table and continue with the loop.

    Rob

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Oct 17, 2006 at 01:42 PM

    Hi,

    If situation demands to use COMMIT WORK then use it but make sure you are not comitting millions of transaction in one go. User record count then commit it 10000/15000 transaction in one go.

    If helpful pl reward.

    Cheers.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Oct 17, 2006 at 02:00 PM

    hi

    good

    A COMMIT statement ( commit_statement) terminates the current transaction and starts a new one <commit_statement> ::= COMMIT [WORK]

    The commit statement terminates the current transaction. This means that the modifications executed within the transaction are recorded and are thus visible to concurrent users as well.

    The COMMIT statement implicitly opens a new transaction. Any locks set within the new transaction are assigned to this transaction.

    thanks

    mrutyun^

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.