How to Monitor Exclusive Lockwaits?

SM66: Global Process Overview

• At the runtime,  ensure that the setting 'Do not search for exclusive database locks' is not selected. The search for exclusive locks is deactivated in the settings by default, since it can take a long time to run on certain database platforms. However, you can save individual preferences for transaction code SM66. If you search for database locks, the 'Action' column displays the 'Wait for DB lock' for blocked processes and the 'Stop DB lock' for blocking processes.

DB01: Database Lock Overview

• At the runtime. The lock overview displays which exclusive lockwaits exist on the database at the moment. For each process (SPID), the system table "master..sysprocesses" displays in the'Blocked' column the SPID that has blocked that process. A value of '0′ means that the process is not locked. The lock overview only displays processes that are blocked or that block anotherprocess. (That is, all rows where 'Blocked' is not equal to 0, or where the SPID is displayed in the 'Blocked' column). The lock overview displays the hierarchy of the lock chain in the 'nesting level'column, and (depending upon the release) by setting the SPIDs back according to their position in the lock chain. The 'Lockmode' column tells you if the process causes the lock ('grant') or if it iswaiting for the lock ('wait'). What is important is the information about the locked object: The'Locktype' column specifies if it is a single table row('key' or 'row'), a database page ('pag') or an entire table ('tab'). Other 'Locktypes' are also possible, such as database ('db'), if two backups block commands, for example. The 'Lock text' and 'Wait type' columns describe the type of the lock, where 'SH' means 'shared', 'U' means 'update' and 'EX' means exclusive. The 'table' and 'index' specifications are very useful.

ST04: Performance Overview

• In ST04, go to Details Analysis Menu -> Blocking lockstats. The monitor displays data abouthow often locks occur in the system, and how long they last. It also specifies the tables in which the locks occur. Ideally, there are no exclusive lockwaits in the system, and the display is empty. However, it is normal for such locks to occur in a system that has many users working in parallel. Whether the locks cause a problem or not, depends upon the transactions that are affected and the requirements of the users. The monitor displays all exclusive lockwait situations that lasted more than one minute over the course of the last 7 to 13 days. Shorter locks are only partially recorded, since the data is recorded as a snapshot every minute. The program displays the blocking processes (as of SAP_BASIS 6.40, the program also displays the blocked processes). The lock situation information contains a timestamp and the lock duration. The technical specifications are the same as those of the lock overview.