Results 1 to 2 of 2

Thread: Screen shots: Row level locking of database table

  1. #1

    Screen shots: Row level locking of database table

    Advertisement

    By Swetabh Shukla, Infosys Technologies
    Normally if a person opens table maintenance generator and tries to maintain the table, no one else can maintain the same table at the same time. This is because of table level lock by default in SAP. Only one user can maintain any table at a time through SM30 or any transaction that calls table maintenance generator. In the tutorial below we will see how to remove table level lock and apply row level lock. This way any number of users can modify the table at same time. But any particular row can be modified by only one user at a time. We will create a transaction for this purpose. The transaction will call our custom report. This custom report will call the table maintenance generator of the table after deleting table level lock.
    In current example letís create following:
    Report: ZREP_SHUKS3
    Transaction: ZTEST_SHUKS3
    Table: ZTEST_SHUKS3 with table maintenance generator.
    Using transaction ZTEST_SHUKS3 we will delete the table level lock and put row level lock so that multiple users can maintain table at same time. Rows locked by one user will not be editable by other user.
    1. Create table ZTEST_SHUKS3.
    2. Create table maintenance generator for the table.
    We will make single screen maintenance for this table. Save it. So finally we have table maintenance code automatically generated in function group ZTEST_SHUKS3.
    3. Create lock object EYTSS_E433SH in SE11. Give it name as EZTEST_SHUKS3.


    Now save and activate the Lock object. SAP creates two function modules corresponding to lock object for enqueue and dequeue of the table.

    4. Now create a report ZREP_SHUKS3 and transaction code ZTEST_SHUKS3 to call this report. This tcode will call table maintenance generator of table ZTEST_SHUKS3 .
    5. Normally if a person opens table maintenance generator and tries to maintain the table, no one else can maintain table at the same time. This is because of table level lock by default in SAP. Only one user can maintain any table at a time. In report ZREP_SHUKS3 we will delete the table level lock and put row level lock so that multiple users can maintain table at same time. Rows locked by one user will not be editable by other user. Check the report and comments given below.
    *&---------------------------------------------------------------------*
    *& Report ZREP_SHUKS3
    *&
    *&---------------------------------------------------------------------
    *& Author : Swetabh Shukla
    *& Date : 05/22/2009
    *& Description : To delete table level lock from table.
    *&---------------------------------------------------------------------*
    REPORT zrep_shuks3.

    **Selection range for view maintenance
    DATA:
    BEGINOF selekttab OCCURS1. "Selektionsbereich
    INCLUDESTRUCTURE vimsellist.
    DATA: ENDOF selekttab,

    **Table of inactive CUA functions for view maintenance
    BEGINOF excl_cua_funct OCCURS1. "inaktive CUA-Fkt bei View-Pflege
    INCLUDESTRUCTURE vimexclfun.
    DATA: ENDOF excl_cua_funct.

    DATA: lt_enq_del TYPESTANDARDTABLEOF seqg3,
    lt_enq_read
    TYPESTANDARDTABLEOF seqg7,
    lw_enq_read
    TYPE seqg7,
    lw_enq_del
    TYPE seqg3,
    lv_subrc
    TYPE sy-subrc.

    *Read all the lock details in system
    CALLFUNCTION'ENQUE_READ2'
    EXPORTING
    gclient = sy-mandt
    gname =
    ' '
    guname =
    '*'
    TABLES
    enq = lt_enq_read.

    *We will search entry for table level lock for our table
    LOOPAT lt_enq_read INTO lw_enq_read
    WHERE gname EQ'RSTABLE'
    AND garg CS'ZTEST_SHUKS3'.
    MOVE-CORRESPONDING lw_enq_read TO lw_enq_del.
    APPEND lw_enq_del TO lt_enq_del.Advertisement

    ENDLOOP.

    *Delete table level lock entry for our table
    CALLFUNCTION'ENQUE_DELETE'
    EXPORTING
    check_upd_requests =
    1
    IMPORTING
    subrc = lv_subrc
    TABLES
    enq = lt_enq_del.

    *Now call the table maintenace generator.
    CALLFUNCTION'VIEW_MAINTENANCE_CALL'
    EXPORTING
    action =
    'U'
    view_name =
    'ZTEST_SHUKS3'
    show_selection_popup =
    'X'
    TABLES
    dba_sellist = selekttab
    excl_cua_funct = excl_cua_funct.

    Source: SAP Techincal

  2. #2
    6. Just one more change in table maintenance screen. Now open table maintenance function group(ZTEST_SHUKS3) in SE80.We know for table maintenance SAP automatically creates code in the function group. Now we will make some modification in that existing code to change the behavior. Open the screen you created through table maintenance and add one module.

    Open the screen 0001 and add one module in PBO of the screen as shown in figure below.

    Check the code below to be added in the module m_change_locking.
    MODULE m_change_locking OUTPUT.
    *Call the function module corresponding to the lock object we created
    CALLFUNCTION'ENQUEUE_EZTEST_SHUKS3'
    EXPORTING
    matnr = ztest_shuks3-matnr
    werks = ztest_shuks3-werks
    EXCEPTIONS
    foreign_lock =
    1
    system_failure =
    2
    OTHERS = 3.
    IF sy-subrc NE0.
    * row is locked..hence gray..
    LOOPATSCREEN.
    screen-input = 0.
    MODIFYSCREEN.
    ENDLOOP.
    ENDIF.
    ENDMODULE. " m_change_locking OUTPUT
    7. Now we are ready for testing. Call the transaction once and make some entries in table. In below screenshot I have entered some random values. Since we have not maintained any check tables etc, so values may be invalid. That can be taken care in real scenario.

    Save data.
    Letís call two sessions of the transaction ZTEST_SHUKS3.
    Session 1

    In first session we will call the transaction ZTEST_SHUKS3 and try to open some existing values for table maintenance.

    Letís open an existing value material = MAT1 and Plant = 0678.

    So the entry opens in change mode.
    Now open a second session of the transaction and see. Now the same material and plant combination can not be changed by any other user or session.
    Session 2

    Open transaction ZTEST_SHUKS3


    So we can see that our transaction is able to achieve a row level locking and has removed table

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •