Row level locking of database table

SAPThe default behavior is if a person opens table maintenance generator and tries to maintain the table, the table is locked and no one else can maintain the same table at the same time.

This is because there is a “table-level lock” by default in SAP. This means only one user can maintain any table at a time through SM30 or any transaction that calls table maintenance generator. In the tutorial below you will find out how to replace table level lock with row level lock.
By row-level lock you ensure 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 (the first one who accessed the row for maintenance). We will create a transaction for this purpose. This transaction will call our custom report where at first the table lock is deleted and subsequently the table maintenance generator will be called..

In our example we will create

  • Custom report ZREP_ROWLOCK
  • Transaction to call the custom report ZRLOCK
  • Custom table with maintenance generator ZROWLOCK

Transaction ZRLOCK will call report ZREP_ROWLOCK where we delete table-level lock on ZROWLOCK and then it calls the table maintenance where only selected rows will be locked. This will ensure that multiple users can access the table at the same time. But rows locked by one user can not be edited by anyone else until this user leaves the table maintenance and releases the row locks.

Create table ZROWLOCK

Go to SE11 and create new table ZROWLOCK with at least the following fields: MANDT, MATNR, WERKS and activate the table.

Table creation

Call table maintenance generator for the table.

In the top menu go to Utilities – Table Maintenance Generator and create new function group for this table. Generate code for one step/single screen.

Table maintenance generator

Create lock object

Go to SE11 and create new lock with name as EZROWLOCK
Enter the name of our custom table (ZROWLOCK) and select lock mode (Write)

Table lock creation 1

Now select all the key fields of table ZROWLOCK

Table lock creation 2

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

Generated lock/unlock function modules

Create report and transaction

Go to SE38 and create new report called ZREP_ROWLOCK.
This code will delete the table-level lock on ZROWLOCK table and call its table maintenance.

*&---------------------------------------------------------------------*
 *& Report  ZREP_ROWLOCK
 *&
 *&---------------------------------------------------------------------
 *& Author      : Miroslav Oprsteny
 *& Date        : 2013/04/04
 *& Description : Database table row-level lock demo
 *&---------------------------------------------------------------------*
 REPORT  zrep_rowlock.
* Selection screen for view maintenance
 DATA: BEGIN OF selekttab OCCURS 1.         
         INCLUDE STRUCTURE vimsellist.
 DATA: END OF selekttab,
* Table of inactive CUA functions for view maintenance
       BEGIN OF excl_cua_funct OCCURS 1.
         INCLUDE STRUCTURE vimexclfun.
 DATA: END OF excl_cua_funct.
 DATA: lt_enq_del TYPE STANDARD TABLE OF seqg3,
       lt_enq_read TYPE STANDARD TABLE OF seqg7,
       lw_enq_read TYPE seqg7,
       lw_enq_del TYPE seqg3,
       lv_subrc TYPE sy-subrc.
*Read all the lock details in system
 CALL FUNCTION 'ENQUE_READ2'
   EXPORTING
     gclient = sy-mandt
     gname   = ' '
     guname  = '*'
   TABLES
     enq     = lt_enq_read.
* Search entry for table-level lock for our table
 LOOP AT lt_enq_read INTO lw_enq_read
   WHERE gname EQ 'RSTABLE'
   AND   garg CS 'ZROWLOCK'.
   MOVE-CORRESPONDING lw_enq_read TO lw_enq_del.
   APPEND lw_enq_del TO lt_enq_del.
 ENDLOOP.
*Delete table level lock entry for our table
 CALL FUNCTION 'ENQUE_DELETE'
   EXPORTING
     check_upd_requests = 1
   IMPORTING
     subrc              = lv_subrc
   TABLES
     enq                = lt_enq_del.
*Now call the table maintenace generator.
 CALL FUNCTION 'VIEW_MAINTENANCE_CALL'
   EXPORTING
     action               = 'U'
     view_name            = 'ZROWLOCK'
     show_selection_popup = 'X'
   TABLES
     dba_sellist          = selekttab
     excl_cua_funct       = excl_cua_funct.

Go to SE93 and create new transaction for report ZREP_ROWLOCK

Transaction creation

 

Transaction creation 2

Modify the table maintenance

We have to modify the maintenance screen and its code so it locks only rows the user selected to be edited.
Run TCode SE80 and open function group ZROWLOCK and open the one step/single screen you created using table maintenance generator.

Modify table maintenance code

Now we add line with new module.

New module in table maintenance

The code inside this module will be as the following

MODULE change_locking OUTPUT.
*Call the function module corresponding to the lock object we created
  CALL FUNCTION 'ENQUEUE_EZROWLOCK'
    EXPORTING
      matnr          = zrowlock-matnr
      werks          = zrowlock-werks
    EXCEPTIONS
      foreign_lock   = 1
      system_failure = 2
      OTHERS         = 3.
  IF sy-subrc NE 0.
* row is locked..hence gray..
    LOOP AT SCREEN.
      screen-input = 0.
      MODIFY SCREEN.
    ENDLOOP.
  ENDIF.
ENDMODULE.                 " change_locking  OUTPUT

Testing

Now we are ready for testing. Call the transaction ZROWLOCK create some entries with random values in table. Since we have not maintained any check tables etc, the entered values may be invalid, but in a real scenario maintaining check tables should definitely be considered to be implemented.

Sample data creation

Save the changes and leave the transaction.

Now open a new session and in both sessions run TCode ZROWLOCK.

Session 1

In this we will try to open some existing values for table maintenance.

Session 1 selection

Note all selected entries are opened in change mode.

Session 1 - edit mode

Session 2

In  this session pass a range of materials including the one opened in Session 1.

Session 2 - selection screen

On the following you can see the row opened previously in Session 1 in edit mode is now disabled for editing. But the remaining rows are all enabled and locked for the current user/session.

Session 2 - edit mode

6 thoughts on “Row level locking of database table

  1. Hello,
    Thank you for this! I have implemented this solution and have one big problem still – the row locking works, but: when user #1 has a row locked, and user #2 (for example myself in another session) attempts to “ADD NEW ENTRIES” to the table, it will NOT allow the table to go into edit mode nor allow any new entries to be added by user #2 until the user #1 is completely out of the table. I need to be able to have users editing and/or adding new entries at the same time. Can you help? Thank you very much!

  2. Hi Thomas, If you just want to allow all people to add new entries, you can try to add in the module CHANGE_LOCKING called by PBO of screen 0100 a condition – all fields used in the ENQUEUE function module are not initial (otherwise I guess this would lock the empty rows for other users).
    In this example it would be like:

    IF zrowlock-matnr IS NOT INITIAL AND
    zrowlock-werks IS NOT INITIAL.
    CALL FUNCTION ENQUEUE

    screen-input = 0.

    ENDIF.

  3. Hello,

    Is it possible when modifying a line, the screen will be one step not two step? I don’t want the pop up window during edit. Thanks!

    Noreen

  4. Hello Noreen,
    Maybe I don’t fully understand your question – the maintenance screen is just one step in this example – you can see this on screenshot from SE80 where only the screen 0001 is available.

    But if you are asking how to avoid the popup for selecting data to be displayed, then you’ll have to modify the contents of ‘selekttab’ and ‘excl_cua_funct’ which are passed to FM call ‘VIEW_MAINTENANCE_CALL’ so it contains only data you’d like to see/modify.

Leave a Reply