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.