Locking a Table

[Former Member]

Hi all

I need help on locking a table. I am writing a program which updates a Z table , delete all the data and then load new data into it. The table should be locked while this operation (deleting and updating data) takes place.

If I use Enqueue and Dequeue ( If I am right) I need to give the key and that record only be locked. But I need the entire table to be locked during the update.

Please help me on this.

Thanks

[Former Member]

Hi Bobby,

Try like this : Locking table by using MANDT as Varkey.

*--- locking of tables...

data:varkey like rstable-varkey.

varkey = sy-mandt.

*---Enque LOCK............................

call function 'ENQUEUE_E_TABLE'

exporting

• MODE_RSTABLE = 'E'

tabname = 'ZTAB'

varkey = varkey

• X_TABNAME = ' '

• X_VARKEY = ' '

• _SCOPE = '2'

• _WAIT = ' '

• _COLLECT = 'X'

exceptions

foreign_lock = 1

system_failure = 2

others = 3

.

Regards,

Lanka

[Former Member]

Check this link

http://help.sap.com/saphelp_46c/helpdata/en/4f/991f82446d11d189700000e8322d00/frameset.htm

And i guess you can lock complete table for processing using ENQUEUE function module

[Former Member]

<b>Help on lock</b>

How is locking achieved?

Database systems do not usually provide commands for explicitly setting or releasing locks. Therefore, prior to executing the database operation, database locks are set implicitly when one of the Open SQL statements SELECT, INSERT, UPDATE, MODIFY, or DELETE is called (or when the corresponding Native SQL statement is called).

What is locked?

Database systems set physical locks on all lines affected by a database call. In the case of SELECT, these are the selected entries. In the case of UPDATE, DELETE, INSERT and MODIFY, they are the entries to be changed, deleted, and so on.

For example, the follwing call locks the entry in the table SFLIGHT for the Lufthansa flight 0400 on 16.05.1996.

SELECT SINGLE * FOR UPDATE FROM SFLIGHT

WHERE

CARRID = 'LH' AND

CONNID = '0400'

FLDATE = '19960516'.

It is not always the table line that is locked. Tables, data pages, and index pages can also be locked. The units to be locked depend on the database system you are using and the access being performed.

[Former Member]

Bear in mind that SAP locks are logical, not physical locks. So unless the Z program checks that the table is locked, it can try to process it even though you have used the enqueue function.

Rob

[Former Member]

Hi Murthy

I tried it but its not working. I am trying to lock the table and while its being locked I am running another program which will try to modify the table using Modify ztab. And the second program is modifying the table though it was locked by the first program (as per the subrc value of enqueue in first program )

[Former Member]

Hi Bobby,

Please note that you are the user who initiated the lock via Zprogram1 then try with another User Id and Run Zprogam2 to change the table data then you can find the locking mechanism working.

If you are testing with your own id in both the programs then system will release the lock.

Hope this will help you.

Lanka

[Laxmana_Appana_]

hi,

pass 'X' to _WAIT = ' ' parameter and check.

call function 'ENQUEUE_E_TABLE'

exporting

• MODE_RSTABLE = 'E'

tabname = 'ZTAB'

varkey = varkey

• X_TABNAME = ' '

• X_VARKEY = ' '

• _SCOPE = '2'

• _WAIT = ' '

• _COLLECT = 'X'

exceptions

foreign_lock = 1

system_failure = 2

others = 3

Laxman

[Former Member]

Hi

You can't manage it by lock created by SE11, you're only creating a virtual lock (you can see by trx SM12), not a database lock.

You could use that lock only if all programs update that table check your lock too.

Max

[Former Member]

Does the Z program enqueue a row of the table? If it doesn't, as I said, it will process it.

Rob

[ferry_lianto]

Hi Bobby,

You can use this FM VIEW_ENQUEUE to lock custom table.

Then set ENQUEUE_MODE parameter to 'E' (exclusive).

Hope this will help you.

Regards,

Ferry Lianto

[Former Member]

Hi all

Thanks for your replies. I checked running another program which will modify the table (with different user ID) then also lock is not working.

What I understood from the above replies is , even if we create a lock by enqueue in one program we must check the result of FM enqueue in another program before modifying the table ( if we don’t check and try to modify it will be done as its not a database lock)

Please correct me if I got it wrong

Thanks

[Former Member]

Looks good to me.

If your question is answered, please close the thread.

Rob

[Former Member]

That is correct. Every program that is updating this table has to enqueue and dequeue. Then only you can achieve the locking mechanism.