Database Lock

[Former Member]

Hi All,

I have a dumb security/basis related question.

I want to lock a database table for updation until I further want to release it.

Lets say I want to lock a table for any updation/entry today and want to unlock it only when I come back on Monday. Is this possible?

Regards,

Karthik

[Former Member]

TRY THIS:

CALL FUNCTION 'ENQUEUE_E_TABLEE'

EXPORTING

MODE_RSTABLE = 'E'

TABNAME = 'ZTABLE'

EXCEPTIONS

FOREIGN_LOCK = 1

SYSTEM_FAILURE = 2

OTHERS = 3.

IF SY-SUBRC <> 0.

• MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO

• WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.

clear MESSTAB[].

messtab-MSGTYP = 'E'.

MESSTAB-MSGV1 = 'Table is been locked temporarily, try again'.

leave program.

ENDIF.

CALL FUNCTION 'DEQUEUE_E_TABLEE'

EXPORTING

MODE_RSTABLE = 'E'

TABNAME = 'ZTABLE'.

[Former Member]

Create a lock object on the table.

go to SE11. Select lock object. Enter name as EZ_lock_table. Give table name, select lock mode as Write mode.

In the lock parameter tab, select the keys based on which u want to lock the table.

When you activate, this will create two fm: ENQUEUE_EZ_LOCK_TABLE (to lock the table) and DEQUEUE_EZ_LOCK_TABLE (to unlock the table).

Use these in your program and lock the tables.

Regards,

Subramanian

[Former Member]

In addition to above replys i would like to add some more information about lock objects . I hope it would be useful for you.

The SAP R/3 system protects data if multiple users attempt to change it simultaneously. This guarantees data correctness and consistency even when a large number of users are connected to the SAP system. In a SAP environment, for example, only a single developer (session) can change the source code of an ABAP program at a time, and only one user is allowed to view the maintenance screen of a particular customizing table.

What makes this all happen is an additional SAP locking mechanism that enables you to synchronize concurrent read or write requests for a particular set of data. The purpose is to prevent writing data that is being read by someone or restricting data from being read that is already in the editing mode.

Lock objects

Before applying locking in your programs, you must create lock objects for that particular set of data. You can create new lock objects or display (or change) existing ones via the standard ABAP Dictionary (SE11) transaction. Typical examples of lock objects are:

E_TABLES��� Shared Lock Object for Table Views

ESRDIRE������ Lock Objects for ABAP/4 programs

EPPRELE������ Lock Object for Employee Number Range

Here, the lock conditions and lock modes are specified.

Lock conditions (defined by lock parameters)

While defining the lock object, you need to specify the field names that constitute the lock condition. The lock conditions depict the table fields you want to prevent from being concurrently accessed, usually key data that uniquely specifies a particular object or table entry. For example, if I wanted to lock the personnel number in a particular client, I would use the fields PERNR and MANDT as my lock parameters.

During formulation of these conditions, an AND condition is implied. For instance, you might want to lock employee 2900 in client 300. Then, the condition would be interpreted as "Lock the table entry where PERNR is equal to 2900 AND MANDT is 300."

Lock modes

SAP enables you to specify a lock mode that best suits your business requirement. This gives an idea of the intention of the application that employs a particular lock in its code. Let's consider the standard payroll program that locks all the personnel numbers whose payroll results are computed. During execution of this program, no other application is allowed to set a lock for changing the respective employees' data.

There are three modes of SAP locks:

Shared. The shared lock is requested by an application that wants to read a particular set of data. If it succeeds, any other application requesting shared locks (on the same data) is allowed. However, any such write lock requests are denied.

Exclusive and cumulative. This mode is also known as a write lock. It's requested by transactions that update the contents of a particular table. The same transaction may request additional write locks. A cumulative counter, initialized to 0, is provided for this purpose. Each time a particular data is locked via a cumulative lock, the counter is incremented by 1. Any external read locks are denied.

Exclusive and not cumulative. This mode behaves in a similar fashion to the cumulative lock. The only difference is that cumulative facility is not permissible; a transaction cannot set additional lock entries on data that it has already locked.

Where do we go from here?

Two function modules, one for setting locks and the other for releasing them, are automatically generated upon successful activation of the lock object in the ABAP Dictionary. They are of the form ENQUEUE_* and DEQUEUE_*. If we created the lock object E_MYLOCKOBJ, the corresponding locking and unlocking function modules generated would be ENQUEUE_ E_MYLOCKOBJ and DEQUEUE_E_MYLOCKOBJ, respectively.

Any SAP transaction that wants to employ the SAP locking facility must call these function modules with appropriate parameters. A pseudocode format of all application programs that employ the SAP locking facility appears below:

CALL relevant ENQUEUE function

MAKE update

CALL relevant DEQUEUE function

A program that sets a lock can only reverse or unlock it. At the end of all transactions, all locks are automatically released.

The identical interfaces of the ENQUEUE and DEQUEUE modules are of primary importance. These include the lock parameters (previously defined in the ABAP Dictionary) that specify the table entry or entries that are to be locked. It is not necessary to specify lock parameters in full at runtime. If we supply only partial parameters, the generic lock is implied.

For example, while calling the DEQUEUE function in our PERNR example, we could pass "300" in place of parameter MANDT and leave the parameter PERNR blank. In this case, all employee entries in client 300 would be locked regardless of their personnel numbers.

When the ENQUEUE function is successful, the value of the return code SY-SUBRC is set to 0. Failure in locking the given entry raises the exception FOREIGN_LOCK. This exception implies that the table entries corresponding to the given parameters have already been locked by another application.

Lock table

A lock table is a centralized table in the main memory of the ENQUEUE server that acts as a directory of all locked objects. You can view the contents of this table by using the standard SAP transaction SM12.

Whenever a new ENQUEUE function is called by an application, an entry is made in this table. Conversely, a DEQUEUE function clears the corresponding entry. When a different program attempts to lock the same object, SAP refers to this table to find out if it is already in a locked state. If not, it is allowed to proceed.

Included fields are:

Lock arguments. This is made from the key data of the lock parameter.

Cumulative counter. In the case of the cumulative lock, this shows the number of times locks have been applied.

User name. This is the name of the user who has run the application that has applied the lock.

You can use this table to view the type and arguments of the lock. All lock entries set by a particular transaction are automatically deleted from the table upon the transaction's termination. In case of abrupt termination by the ENQUEUE transaction, you may also manually delete lock entries from this table.

Conclusion

Knowledge of the SAP locking mechanism is essential to write even the simplest database update programs. Always follow the golden rule: Try to express your problem in terms of the data fields that are to be locked to formulate the lock condition.

//KIRAN

[Former Member]

Hello Karthik,

In case it's table maintenance is attached with a T Code, then you can lock the TCODE itself. Also in the Table Maintenance Generator, you have an Authorizaion group (For Line Oriented Authorization).You can use the above things to lock the table til you wish.

Regards

Saket Sharma

[Former Member]

Thanks Saket. I will try that out.