Ansicht
Dokumentation
ABENDB_LOCK - DB LOCK
ABAP Short Reference ABAP Short ReferenceThis documentation is copyright by SAP AG.
Database Locks
Simultaneous access by multiple transactions to the same dataset requires database locks to manage and synchronize access in each database. The tasks of this mechanism are to:
- protect data objects currently being changed or read by a transaction from being changed by other transactions at the same time
- protect a transaction from reading data objects that have not yet been committed by another transaction
The following sections are a short discussion of the properties of database locks:
Setting Locks
Database systems do not usually provide commands for explicitly setting locks. Database locks are set implicitly each time data on the database is accessed. In ABAP, databases are accessed in the following ways:
- Using a Native SQL statement.
- Calling AMDP methods.
Locked Objects
Database systems set physical locks: All rows affected by a database call are locked. In the case of SELECT, these are the selected rows, in the case of UPDATE, DELETE, INSERT, and MODIFY, these are rows to be changed, deleted, or inserted.
Take the following call, for example:
SELECT SINGLE FOR UPDATE * FROM sflight
WHERE
carrid = 'LH' AND
connid = '0400' AND
fldate = '19960516'
INTO ... .
It locks the entry in the table SFLIGHT for Lufthansa flight 0400 on May 16, 1996.
It is not always the table row that is locked. Tables, data pages, and index pages can also be locked, for example. The units locked depend on the database system used and the respective access.
Lock Mode
In principle, one type of lock is sufficient to control competing data access. However, to allow a larger number of transactions to run in parallel, database systems use a range of lock types. These can vary from system to system, but the following two examples outline sufficiently how locks work:
- Shared lock
- Shared locks allow the system to set other shared locks simultaneously, but prevent other transactions from setting exclusive locks for the locked objects.
- Exclusive lock
- Exclusive locks do not allow other transactions to set any locks for the locked objects.
Exclusive locks are set by the statements SELECT SINGLE FOR UPDATE, INSERT, UPDATE, MODIFY, and DELETE, the corresponding Native SQL statements or platform-dependent statements in AMDP methods, plus EXPORT TO DATABASE.
The current isolation level specifies whether the SQL statement SELECT sets a shared lock.
- The following settings are possible in all databases except the SAP HANA database and Oracle databases:
- Uncommitted reads (the default setting) do not attempt to set shared locks. Data is also read that is still protected by an exclusive lock and not yet committed using a database commit.
- Committed reads (which can be configured using the function module DB_SET_ISOLATION_LEVEL) set a shared lock during the read and then remove it again. This is possible only when exclusive locks do not exist, which can entail wait times.
- The SAP HANA database and Oracle databases do not set shared locks, but also only read data committed by a database commit.
If a transaction cannot lock an object because it is already locked by another transaction, it waits until the other transaction releases this lock. This can produce a deadlock. A deadlock occurs, for example, when two transactions are waiting for a lock held by the other.
Example
A booking needs to be made in a flight reservation system for Lufthansa flight 0400 on May 16, 1996. This is possible only if there are enough free seats. To prevent two bookings from being made at the same time and avoid overbooking, the entry in the DDIC database table SFLIGHT for this flight must be locked to prevent it from being changed by other transactions. This ensures that the query to determine the number of free seats in the SEATSOCC field can be carried out, the flight can be booked, and the SEATSOCC field can be updated by other transactions. The following program excerpt shows a solution for this problem:
DATA: sflight_wa TYPE sflight, sbook_wa type sbook.
SELECT SINGLE FOR UPDATE *
FROM sflight
WHERE
carrid = 'LH' AND
connid = '0400' AND
fldate = '19960516'
INTO @sflight_wa.
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 row selected by SELECT SINGLE FOR UPDATE and inserted by INSERT is locked until the end of the database LUW. This prevents the flight from being overbooked and inconsistencies from occurring between tables SFLIGHT and SBOOK in the event of a database rollback after an error.
Lock Duration
Database systems do not usually provide statements for explicitly releasing locks. All database locks are released no later than the next database commit or rollback. Shared locks usually have an even shorter lifetime. Sometimes, this causes problems for transactions that involve multiple dialog steps:
After the user has selected a flight in the above example, further dialog steps are usually performed to enter additional data for the reservation. Here, the flight reservation is added in a different database LUW than the original selection of the flight. Database locking does not prevent another transaction from booking this flight in the meantime, which can mean that the scheduled booking may have to be canceled after all.
From the point of view of the user, this solution is very inconvenient. To avoid this scenario, a flight reservation system must use the SAP locking mechanism to lock the flight for the entire duration of the transaction.
Monitoring Locks
The DBA Cockpit allows monitoring database locks.
rdisp/max_wprun_time - Maximum work process run time ROGBILLS - Synchronize billing plans
This documentation is copyright by SAP AG.
Length: 10319 Date: 20240420 Time: 151627 sap01-206 ( 129 ms )
