Displaying SQL Locks

Use

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.

Table 1: SQL Locks: Types
Lock Type	Objects that Can Be Locked	Description
Exclusive	Tables Rows Entries in the database catalog	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	Tables Rows Entries in the database catalog	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	Rows	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. If the row has not been changed, the system converts the optimistic lock into an exclusive lock, and then makes the changes. If the row has been changed, the system rejects the change and releases the optimistic lock. An optimistic lock is only useful in isolation levels 0, 1, 10, and 15.

Prerequisites

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.

Procedure

In the context menu of the database system administrator or the logged-on database user, choose Performance SQL Locks .

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: tab_: table row_: row sys_: entry in the database catalog = 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: tab_: table row_: row sys_: entry in the database catalog = 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

More Information

Definitions of the System Tables, LOCKS