User Guide

SAP AG November 2002
Request Locks
• Request locks implicitly
The lock mode can be determined by specifying an isolation level [Page 139] in the
CONNECT statement [See SAP DB Library].
In this case, the database system requests locks implicitly during processing of an SQL
statement in accordance with the specified isolation level. These locks are then assigned
to the transaction [Page 152] that contains this SQL statement. All SQL statements that
result in a change (INSERT/UPDATE/DELETE), continuously request an exclusive lock
[Page 115] implicitly.
• Request locks explicitly
The lock mode can be defined by explicitly specifying locks in the LOCK statement [See
SAP DB Library]. These locks are then assigned to the transaction that contains this
LOCK statement.
Individual rows of a table can be locked by specifying a LOCK option [See SAP DB
Library] in an SQL statement. The LOCK option can also be used to change the isolation
level for the specified SQL statement, and thereby the locks for the complete table.
Explicit lock requests are possible with every isolation level.
If the database system has to wait too long for locks to be released when setting explicit or
implicit locks, it issues a return code to this effect. The user can then respond to this return
code, e.g., by terminating the transaction. In this case, the database system does not execute
an implicit ROLLBACK WORK.
Release Locks
The locks assigned to a transaction are usually released at the end of the transaction, making
the respective database objects accessible again to other transactions.
The locks assigned to a transaction by the LOCK statement are usually released when the
transaction ends provided that the
or the
that end a transaction do not contain a LOCK
statement.
COMMIT statements [See SAP DB Library]
ROLLBACK statements [See SAP DB Library]
The length of time for which an implicit
is maintained also
depends on the isolation level. Exclusive locks set implicitly cannot be released within a
transaction.
shared lock [Page
11 ]4
•
• Exclusive locks for rows that have not yet been modified and shared locks on rows can
be released by the UNLOCK statement [See SAP DB Library]
before the end of the
transaction.
• Exclusive locks on updated rows, exclusive locks that were requested by an SQL
statement that leads to a change (INSERT/UPDATE/DELETE), and table blocks cannot
be released within a transaction, only after the transaction has ended.
Isolation Level
The lock mode plays an important part in the . The lock mode is
determined by specifying an isolation level.
lock behavior [Page 113]
By defining an isolation level, you specify whether and how shared locks [Page
114] and
115 are implicitly requested or released (
).
exclusive locks [Page ] Requesting and Releasing a
Lock [Page 115]
The selected isolation level affects both the degree of concurrency and the guaranteed
consistency.
User Manual: SAP DB 116