Locking (IRLM) Activity window

Use the Locking (IRLM) Activity window to view information about locking activity for the thread being monitored.

To display this window, select Locking Activity from the Thread Detail panel.
Figure 1. Locking (IRLM) Activity window
 DGOMTWLK            Locking (IRLM) Activity

                                                   More:     +
 Lock and latch suspensions . . . . . . . . . . . . :        1
 Elapsed time . . . . . . . . . . . . . . . . :       0.000355

 Timeouts . . . . . . . . . . . . . . . . . . . . . :        0
 Deadlocks  . . . . . . . . . . . . . . . . . . . . :        0
 Lock requests  . . . . . . . . . . . . . . . . . . :       19
 Unlock requests  . . . . . . . . . . . . . . . . . :       18
 Change requests  . . . . . . . . . . . . . . . . . :        1
 Query requests . . . . . . . . . . . . . . . . . . :        0
 Other IRLM requests  . . . . . . . . . . . . . . . :        0
 Suspensions - lock . . . . . . . . . . . . . . . . :        0
 Suspensions - latch  . . . . . . . . . . . . . . . :        1
 Suspensions - other  . . . . . . . . . . . . . . . :        0
 Lock escalations - shared  . . . . . . . . . . . . :        0
 Lock escalations - exclusive . . . . . . . . . . . :        0
 Maximum page or row locks held . . . . . . . . . . :        2
 Claim requests . . . . . . . . . . . . . . . . . . :        6
 Unsuccessful claim requests  . . . . . . . . . . . :        0
 Drain requests . . . . . . . . . . . . . . . . . . :        0
 Unsuccessful drain requests  . . . . . . . . . . . :        0

 Command ===> ________________________________________________
  F1=Help     F2=Split     F3=Exit     F5=Auto     F6=History
  F7=Up       F8=Down      F9=Swap    F12=Cancel  F16=Look
 F17=Collect F22=Purge

Deadlocks and timeouts displayed in this window can indicate the existence of locking problems. These problems can be further investigated using the Locked Resources window to examine resources that are either locked by or, if they are locked by another thread, unavailable to, the monitored thread.

The following list describes some of the important fields that are shown in this window:
Timeouts
Number of times lock suspension ultimately resulted in a timeout. This happens when a requester for a lock on a resource has waited longer than the installation-specified RESOURCE TIMEOUT limit in the DB2® Install panel DSNTIPI.
Deadlocks
Number of times lock suspension ultimately resulted in a deadlock. This happens when two or more application processes each hold locks on resources that the others need, without which they cannot proceed. A single process accessing data through an unclustered index can sometimes experience a deadlock between a data page and an index page. If deadlocks are not very frequent, they might not affect performance.
Lock requests
Number of times a lock on a resource was requested.
Change requests
Number of times a lock change was requested, for example, to promote a shared page lock to exclusive lock.
Suspensions - lock
Number of resource conflicts. A suspension is a wait for a lock, and each of these waits can adversely affect application performance. The suspension might ultimately result in normal resumption, timeout, or deadlock. The number of lock suspensions is a function of the lock requests. Lock suspensions (or conflicts) can occur on either lock requests or change requests.

The ratio of suspensions to lock requests is largely application dependent.

Lock escalations - shared
Count of lock escalations to shared mode. Number of times the LOCKS PER TABLE(SPACE) parameter in the Db2 Install panel DSNTIPJ was exceeded and the table space lock was promoted from a page lock (IS) to a table space lock (S) for this thread. Escalation can cause unpredictable response times. The lock escalation to shared mode should only happen on an exception basis. For example, if a REPEATABLE READ application references most pages in a table.
Lock escalations - exclusive
Count of lock escalations to exclusive mode. Number of times the LOCKS PER TABLE(SPACE) parameter in the Db2 Install panel DSNTIPJ was exceeded and the table space lock was promoted from a page lock (IX) to a table space lock (X) for this thread. Escalation can cause unpredictable response times. The lock escalation to exclusive mode should happen rarely, for example, if an application updates many pages in a table without issuing commits.

Check the exception event display for deadlocks occurring against the agent that causes the lock escalation. In a transaction processing environment, a moderate level of lockout is tolerable. If many escalations cause deadlocks and timeouts, you can change the escalation threshold value. Use of ANY is extremely desirable to prevent unnecessary and expensive page locks, for example locking all pages in a table space.

Lock escalations, shared or exclusive, should not be expected in a transaction environment.

If escalation occurs when LOCKSIZE ROW or LOCKSIZE PAGE is specified, the lock is escalated to the table level for a segmented table space and to the table space level for a nonsegmented table space.

A new clause LOCKMAX has been added to the CREATE TABLESPACE statement. This provides for control of the maximum number of locks for an individual table space in addition to the system-wide NUMLKTS parameter currently provided.

The default is LOCKMAX 0 to ensure that PAGE level locking is consistent with previous releases of Db2. If LOCKSIZE ANY is specified, Db2 chooses LOCKSIZE PAGE LOCKMAX SYSTEM.

Escalation is now possible with PAGE locking by specifying the LOCKMAX clause. This provides for initial PAGE level locking with escalation to table or table space locking for segmented and nonsegmented table spaces respectively, based on the number of locks specified on the LOCKMAX clause. To disable escalation, specify LOCKMAX 0.

For high volume updates where contention is an issue, use type-2 indexes and specify one of the following:
  • LOCKSIZE PAGE
  • LOCKSIZE ROW for the high contention tables to improve concurrency. However, LOCKSIZE ROW should be considered only as an exceptional case, because of a potential for significant overhead. In fact, it is even possible that LOCKSIZE ROW causes deadlocks, which would not happen with LOCKSIZE PAGE.

Omitting LOCKMAX results in LOCKMAX 0, which disables escalation. If escalation is to be allowed, users should carefully consider the threshold at which it should take place for each individual table.

Maximum page or row locks held
Count of the maximum number of page or row locks concurrently held against all table spaces by a single thread during its execution. This count cannot exceed the value of the LOCKS PER USER Db2 installation parameter (panel DSNTIPJ). After the limit is reached, the next attempt to obtain a lock results in a RESOURCE UNAVAILABLE return code, and the SQL request is not processed.

Locking considerations

The following aspects should be considered if concurrency is an issue:
  • Consistent data access sequence in applications
  • The mix of concurrent processing types (batch and transaction)
  • IRLM startup procedure options and Db2 installation options
  • DDL LOCKSIZE
  • DDL LOCKMAX
  • BIND parameters

See IBM® Db2 for z/OS® in the IBM Knowledge Center for detailed information.