If you are experiencing wait situations or generally a bad performance of your database or application, SQL locks that are held for a long time could be the reason.
Multiple database transactions can access the same database object at the same time. To synchronize access, SQL locks are used. The following database objects can be locked: tables, rows, and entries in the database catalog.
Lock Type |
Objects that Can Be Locked |
Description |
---|---|---|
Exclusive |
|
The system sets exclusive locks for all SQL statements that change data, such as INSERT, UPDATE, or DELETE. Other transactions have neither read nor write access to the locked database object. This means that other users cannot access the locked database object. If another transaction also tries to set an exclusive lock, a lock collision occurs (the lock request is rejected). Caution
Exclusive locks can significantly interfere with the performance of the database and the SAP system. |
Share |
|
Other transactions have read access to the locked database object. They can still set share locks for the database object. However, other transactions cannot set exclusive locks for the database object and therefore cannot change it. |
Optimistic |
|
An optimistic lock keeps track of changes to a database object. This enables database applications to determine whether the data that they have read while setting the lock is still up-to-date, or whether they must read it again. Other transactions can still set share, exclusive, or optimistic locks for the locked database object. Before a transaction can change a row for which it has set an optimistic lock, the system checks whether the row has been changed by another user since the optimistic lock was set.
An optimistic lock is only useful in isolation levels 0, 1, 10, and 15. |
You are logged on to the database as the database system administrator or as a database user.
The database is in the ONLINE operational state.
In the context menu of the database system administrator or the logged-on database user, choose
.Choose the type of SQL locks that you want to display:
Waits: Only exclusive locks that lead to wait situations
Use this option, for example, if you have been monitoring tasks with task state Vwait for a longer period of time (see Displaying Information About Tasks).
Property |
Comment |
---|---|
Holder Task-ID |
Task ID of the work process that is holding the lock |
Holder Appl. ID |
Application ID of the work process that is holding the lock |
Holder Appl. Server |
Application server of the work process that is holding the lock |
Lock Type |
Database object type that is locked, and lock type:
* = exclusive, share, optimistic |
Table Name |
Table that is locked, or contains the locked database object |
Requester Task-ID |
Task ID of the work process that is requesting the locked database object |
Requester Appl. ID |
Application ID of the work process that is requesting the locked database object |
Requester Appl. Server |
Application server of the work process that is requesting the locked database object |
Lock Request |
- |
Lock Wait Time |
Time that the requesting work process has been waiting to access the locked database object. |
Overview: All active and requested SQL locks
Property |
Comment |
---|---|
Task-ID |
Task ID of the work process that is holding the lock |
Appl. ID |
Application ID of the work process that is holding the lock |
Appl. Server |
Application server of the work process that is holding the lock |
Lock Type |
Database object type that is locked, and lock type:
* = exclusive, share, optimistic |
Lock Status |
- |
Lock Request |
- |
Lock Request Status |
- |
Lock Wait Time |
Time that the requesting work process has been waiting to access the locked database object. |
Last Write Access |
Time that has elapsed since the data change by an SQL statement |
Table Name |
Table that is locked, or contains the locked database object |
Row ID |
Start (prefix) of the key of the locked row in hexadecimal notation |
Definitions of the System Tables, LOCKS