Get Example source ABAP code based on a different SAP table
ARTICLE
Database Locks Any database permitting simultaneous access to multiple transactions requires database locks to manage and synchronize access. 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
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 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.
How does the lock concept work? Database systems do not usually provide commands for explicitly setting or releasing locks. Database locks are therefore set implicitly when one of the Open SQL statements SELECT, INSERT , UPDATE, MODIFY , or DELETE (or the corresponding Native SQL statement) is called before the database operation is executed.
Shared lock Shared locks allow the system to set other shared locks, but prevent other transactions from setting exclusive locks for the objects in question.
Exclusive lock Exclusive locks do not allow other transactions to set any locks for the objects in question.
How are locks set? Exclusive locks are set by the Open SQL statements SELECT SINGLE FOR UPDATE, INSERT, UPDATE , MODIFY, and DELETE (or the appropriate Native SQL statements). Whether the Open SQL command SELECT (or the corresponding Native SQL command) sets a lock depends on the isolation level of the transaction. There are two possible isolation levels:
Uncommitted read (or dirty read) A program that uses an 'uncommitted read' to read data does not set locks for data objects. For this reason, programmers must bear in mind that their programs might read data that is still protected by an exclusive lock and has not yet been finally written to the database by a database commit and could therefore still be deleted from the database by a database rollback. 'Uncommitted read' is the default setting for the isolation level in AS ABAP. (Exception: Oracle databases do not support uncommitted reads and always set committed reads.
Committed read A program that uses a 'committed read' to read data sets a shared lock for a data object, reads the data, and immediately releases the lock. Programmers are therefore assured their programs will read only data that is not protected by an exclusive lock and that has been finally written to the database by a database commit. INTHINT You can set the isolation level of a AS INTHINT ABAP by calling function module DB_SET_ISOLATION_LEVEL INTHINT to 'Committed read'. The next database commit or rollback INTHINT will reset the isolation level to its default, INTHINT as will calling the function module INTHINT DB_RESET_ISOLATION_TO_DEFAULT. INTHINT (Exception: 'Committed read' is always set for INTHINT Oracle databases.) Many database systems employ additional isolation levels (such as 'cursor stability' and 'repeatable read'). These work in a similar way to 'committed read', but the shared 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 AS ABAP. 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 code 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 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.
How long is a lock retained? All database locks are released no later than the next database commit or rollback. Shared locks are usually retained for a shorter period. Sometimes, this causes problems for transactions that involve multiple 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 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 user's point of view, 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.