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.
What is locked?
Database systems set physical locks: All rows affected by a
database call are locked. In the case of SELECT , these are the
selected entries. In the case of UPDATE , DELETE ,
INSERT , and MODIFY , these are the lines to be changed,
deleted, or inserted.
The following call
SELECT SINGLE FOR UPDATE * FROM sflight
WHERE
CARRID = 'LH' AND
CONNID = '0400' AND
FLDATE = '19960516'.
for example, locks the entry in 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 you are using and the action being carried out.
Lock Mode
In principle, one type of lock is enough 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, 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 read s 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.
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.
Documentation extract taken from SAP system, � Copyright SAP AG. All rights reserved