lock objects

[Former Member]

Hi all .

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

Regards

Prajwala.k

[former_member223537]

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

[Former Member]

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

[amit_khare]

Refer the link -

Regards,

Amit

Reward all helpful replies.