Locking Activity window

Use the Locking Activity window to view statistics on lock suspensions, timeouts, deadlocks, and lock escalations.

To display this window, select Locking Activity from the DB2® Statistics Detail panel.
Figure 1. Locking Activity window (Statistics)
 DGOMSWLK        Locking Activity

                                 INTERVAL 12.634
                                      More:   - +
 Timeouts  . . . . . . . . . .     . . . :      0
 Deadlocks . . . . . . . . . . . . . . . :      0

 Lock requests . . . . . . . . . . . . . : 277897
 Unlock requests . . . . . . . . . . . . :  84033

 Query requests  . . . . . . . . . . . . :  24763
 Change requests . . . . . . . . . . . . :  25798
 Other requests  . . . . . . . . . . . . :      3
 Suspensions - lock  . . . . . . . . . . :      3
 Suspensions - latch . . . . . . . . . . :   1540
 Suspensions - other . . . . . . . . . . :  24764
 Lock escalations - shared . . . . . . . :      0
 Lock escalations - exclusive  . . . . . :      0
 Claim requests  . . . . . . . . . . . . :  15149
 Unsuccessful claim requests . . . . . . :      6
 Drain requests  . . . . . . . . . . . . :   1353
 Unsuccessful drain requests . . . . . . :      0

 Lock and change requests
   per lock suspensions  . . . . . . . . :   101K

 Command ===> ____________________________________
  F1=Help      F2=Split      F3=Exit     F5=Auto
  F6=History   F7=Up         F8=Down     F9=Swap
 F10=Delta    F11=Interval  F12=Cancel  F16=Look
 F17=Collect

Application deadlocks and timeouts should be avoided if possible. Unusually high values for these fields indicate the existence of a problem application. Exception events and console messages indicate deadlock participants. Use history to view details of the event surrounding the deadlock. You can use the Locked Resources window in thread activity to investigate thread resources locked or held.

Lock escalation counts greater than zero usually indicate an application process that updates more pages than expected. If this is not the case, examine fields Max page locks per table space and Max locks per user in the Db2 System Parameters panel to determine whether to increase DSNZPARM fields NUMLKTS and NUMLKUS (LOCKS PER TABLE(SPACE) and LOCKS PER USER respectively in the INSTALL Db2 - IRLM PANEL 2 panel DSNTIPJ).

The following list describes some of the important fields that are shown in this panel:
Lock requests
Number of times a lock on a resource was requested.
Suspensions - lock
Number of resource conflicts. A suspension is a wait for a lock and each of these waits can contribute adversely to Db2 performance. The suspension can ultimately result in normal resumption, or, in the case of lock contention, in a timeout or deadlock. The number of lock suspensions is a function of the lock requests. Lock suspensions (or conflicts) can occur on either lock request or change request.
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). Escalation can cause unpredictable response times. The lock escalation to shared mode should only happen on an exception basis. For example, 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). Escalation can cause unpredictable response times. The lock escalation to exclusive mode should only happen on an exception basis. For example, an application updates most pages in a table.

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

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.