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

lock objects

Hi all .

please tell me what i lock objects and how to lock and unlock the database table is in program.

Regards

Prajwala.k

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

3 Answers

  • Posted on Sep 10, 2007 at 05:32 AM

    Goto SE11

    Click Radio button Lock Object. Enter name as ZMARA

    Click on Create

    Enter the table name & key fields based on which you want to lock the table record.

    Activate it.

    Click GOTO => Function modules.

    It will display 2 FM's. with ENQUEUE_ & DEQUEUE_ as starting

    In your program, to lock use

    ENQUEUE_ ***** FM

    Pass the table level field value details to this FM

    To unlock use

    DEQUEUE_***** FM

    Best regards,

    Prashant

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Sep 10, 2007 at 05:33 AM

    Database Locking

    Any database permitting simultaneous access by several users requires a locking mechanism to manage and synchronize access. The tasks of this mechanism are to:

    Protect data objects that a transaction is currently changing or reading from being changed by other transactions at the same time.

    Protect a transaction against reading data objects which have not yet been fully written back by another transaction.

    Example: In a flight reservation system, suppose you want to make a booking for Lufthansa flight 0400 on 16.05.1996. This is only possible if there are still enough free seats. To prevent two bookings from being made at the same time and thus avoid overbooking, the entry in the database table SFLIGHT corresponding to this flight must be protected against changes by other transactions. This ensures that the query about the number of free seats in the field SEATSOCC, the booking of the flight, and the update of the field SEATSOCC can proceed undisturbed by other transactions.

    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.

    Lock mode

    In principle, one type of lock is enough to control conflicting data accesses. However, to achieve a greater degree of parallel running among transactions, database systems use several types of locks. These can vary from system to system, but the following two are sufficient to gain an understanding of how locking works:

    Read lock (shared lock)

    Read locks allow the system to set other read locks, but prevent other transactions from setting write locks for the objects in question.

    Write lock (exclusive lock)

    Write locks do not allow other transactions to set any locks for the objects in question.

    How are locks set?

    You set write locks with the Open SQL statements SELECT SINGLE FOR UPDATE, INSERT, UPDATE, MODIFY, and DELETE (or with the appropriate Native SQL statements).

    The decision as to whether or not the Open SQL command SELECT (or the appropriate Native SQL command) sets the lock depends on the isolation level of the transaction. There are two possible isolation levels:

    Uncommitted read (or dirty read)

    A program using an "uncommitted read" to read data does not set locks on data objects and does not obey them. For this reason, programmers must bear in mind that their programs might read data that has not yet been finally written to the database with a database commit and could thus still be deleted from the database by a database rollback. "Uncommitted read" is the default setting in the R/3 system for the isolation level.

    Committed read

    A program using a "committed read" to read data obeys the locks on data objects. This means that programmers can be sure that their programs will read only data which has been finally written to the database with a database commit. You can set the isolation level in the R/3 system to "committed read" by calling the function module DB_SET_ISOLATION_LEVEL. The next database commit or rollback will reset the isolation level to its default setting, as will calling the function module DB_RESET_ISOLATION_TO_DEFAULT.

    Many database systems employ additional isolation levels (such as "cursor stability" and "repeatable read"). These work like "committed read", but the read lock is retained until the next data object is read or until the database cursor is closed. Since these isolation levels are not sufficiently standardized, they are not currently used in the R/3 System.

    If a transaction cannot lock an object because it is already locked by another transaction, it waits until the other transaction has released the lock. This can result in a deadlock. A deadlock occurs, for example, when two transactions are waiting for a lock held by the other.

    The following program fragment demonstrates a solution to this problem:

    DATA: SFLIGHT_WA TYPE SFLIGHT, SBOOK_WA TYPE SBOOK.

    SELECT SINGLE * FOR UPDATE FROM SFLIGHT

    INTO SFLIGHT_WA

    WHERE

    CARRID = 'LH' AND

    CONNID = '0400' AND

    FLDATE = '19960516'.

    IF SY-SUBRC <> 0.

    MESSAGE E...

    ENDIF.

    IF SFLIGHT_WA-SEATSOCC < SFLIGHT_WA-SEATSMAX.

    SBOOK_WA-CARRID = 'LH'.

    SBOOK_WA-CONNID = '0400'.

    SBOOK_WA-FLDATE = '19960516'.

    ...

    INSERT SBOOK FROM SBOOK_WA.

    IF SY-SUBRC <> 0.

    MESSAGE E...

    ENDIF.

    UPDATE SFLIGHT

    SET

    SEATSOCC = SEATSOCC + 1

    WHERE

    CARRID = 'LH ' AND

    CONNID = '0400' AND

    FLDATE = '19960516'.

    ELSE.

    MESSAGE E...

    ENDIF.

    COMMIT WORK.

    The table line selected by SELECT SINGLE FOR UPDATE and inserted by INSERT is locked until the end of the Logical Unit of Work (LUW). This prevents both overbooking the flight and any inconsistency between the tables SFLIGHT and SBOOK in the event of a database rollback after an error.

    How long is a lock retained?

    In database locking, all locks are released no later than the next database commit or rollback (see Logical Unit of Work (LUW)). Read locks are usually retained for a shorter period. Sometimes, this causes problems for transactions which cover several dialog steps:

    After the user has selected a flight in the above example, he or she usually performs further dialog steps to enter additional data for the reservation. Here, the flight reservation is added in a different LUW than the original selection of the flight. Database locking does not prevent another transaction from booking this flight in the meantime. This means that the scheduled booking may have to be canceled after all.

    From the user's point of view, this solution is very inconvenient. To avoid this scenario, a flight reservation system must use the SAP locking mechanism (see SAP Locking) to lock the flight for the entire duration of the transaction.

    Locking Conflicts in R/3 on DB2 for OS/390

    If the R/3 System uses the database system DB2 for OS/390, even read-only operations require regular commits at least once per minute to ensure that any locks collected meanwhile on the database are released.

    Locks are not only set by modifying transactions, but also by buffered tables, and SE>cluster and pool tables. The database system uses the "Read Stability" isolation level that uses "shared locks" to prevent table contents from being changed simultaneously.

    Locking conflicts cannot only lead to timeouts and deadlocks but also to errors in DDL ( Data Definition Language) statements like CREATE, ALTER and DROP. What is more, they can slow down online reorganisation.

    Should it be necessary to retain the position of the cursor beyond the time of commit, you can use the WITH HOLD option of the OPEN CURSOR statement.

    The commits should be executed as database commits. You can use Native SQL as follows:

    IF SY-DBSYS = 'DB2'.

    EXEC SQL.

    COMMIT WORK

    ENDEXEC.

    ENDIF.

    You should include these database commits into all programs with long read operations.

    Rewards if useful.................

    Minal

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Sep 10, 2007 at 05:33 AM

    Refer the link -

    lock-object

    Regards,

    Amit

    Reward all helpful replies.

    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.