Analyzing global lock contention

One of the most important issues in data sharing is to control global lock contention. Contention not only causes the usual problems of application availability in one Db2, but it can also significantly degrade performance in a data sharing group.

To analyze global lock contention

Related topic

Monitoring-a-Db2-data-sharing-group

  1. From the Data Sharing Wizard (WZDSHAR), select Lock Contention Wizard.The Global Lock Wizard (WZLKGOPT) is displayed.

     W1 =WZLKGOPT=========(DBGHC====*=======)ddmmmyyyy==16:20:49====MVDB2====D====1
    ** LOCK WIZARD - GROUP **              Group Name........ DBGHC
                                           # Db2s............        2           
    . Check Member Contention?                                Interval   Session
      (& Select any Member)                Global Suspends...       45      2384
                                           %Global Cont.(Max)      4.0       3.9
      . Review History and set TIME?
        (If Current Interval OK)
      . Any Gross Locks? (2 hrs)             GrossLocks......        0         0
        (Reduced Concurrency)
      . List Group Lockouts?                 Timeouts........        0         1
        (Contention Victims)                 Deadlocks.......        0         3
      . Which Resources Involved?
        (Data Hot Spots)
      . Compatible Workloads?
        (Connections Involved)
      Analyze Plans In Lockouts?
      . Global Blockers/Waiters
      . Blocker Plans
      . Waiter Plans

    The Global Lock Wizard helps you step through the analysis of lock contention in Db2 for a data sharing group. You can look at current status or choose an earlier time interval, perhaps one that has previously caused problems.

    A history of the most important symptoms of lock problems—timeouts and deadlocks—is available, and information about system considerations and statistics that can point out potential causes of problems.

  2. Select Check Member Contention? to see the key indicators for all of the members together.This view lets you quickly identify any global contention issues that need to be analyzed. The counts shown are for the time since Db2 started.

     W1 =WZLKGOPT=WZLKGMEM(DBGHC====*=======)ddmmmyyyy==16:20:49====MVDB2====D====2
     Db2      MVS      Total    Local    False  Global         % of Group   SyncXES
     Target   System   Lockouts Suspends  Susp. Suspends ..... 0....50..100 Lock Re
     DHH1     SJSE            0      134    848     1168  49.0                 1421
     DHH2     SJSE            1      154    848     1216  51.0                 8384

  3. From here, you can hyperlink on any Db2 to analyze its contribution to (or victimization by) global lock contention.

     W1 =WZLKGOPT=WZLKGCON(DBGHC====Db2HC===)ddmmmyyyy==16:20:49====MVDB2====D====1
      Global / Local Contention
                                  Interval          Session
      Lockouts
       Timeouts                          0                 3
       Deadlocks                         0                 3
      Indicators
       % Global Contention             5.6               1.5
       . If High - Tune GBP-DEP
       % False Contention             23.3              39.7
       . If High-Tune CF (MVMVS)
      Global Lock Activity
       Sync XES Lock Reqs              641             83846         
       Sync XES Change Reqs              0                54         
       Sync XES Unlock Reqs              0             84707         
       IRLM Suspensions                 23               513
       XES Suspensions                   0                16
       False Suspensions                 7               348
       . More Info...
      Local Lock Activity
       Lock Requests                 21959            121627
       Lock Suspensions                  0                 4
       Latch Suspensions                10                57
       . More Info...

    This view provides the information to analyze both global and local lock contention for a selected member. Besides the most critical statistics, both for the current interval and since Db2 startup, the two key indicators of % global contention and % false contention are calculated for you. Thresholds are defined to highlight any value that exceeds the recommended value. Placing the cursor on the % Global Contention header or fields explains the calculation.

  4. Press F3 to return to the Lock Wizard - Group panel (WZLKGOPT).

  5. Select List Group Lockouts?.A list of the latest timeouts and deadlocks that have occurred in all the members of the group is displayed.

    Tip

    The Global Contention flag on the right is set to YES if the conflict occurred between threads running on different members.

    >W1 =LKEVSSI==========(DBGHC====*=======)ddmmmyyyy==16:25:20====MVDB2====D====7
     Date / Time    Lockout   Victim   Victim   Victim   Victim   Victim   Global
     -------------- Type      Member   Plan     AuthID   Connect  CorrName Cont.
     17SEP-16:12:37 DEADLOCK  DB2H     RXDB2    BOLLAA2  DB2CALL  DMRDLK1   YES
     17SEP-16:10:18 TIMEOUT   DB1H     RXDB2    BOLLAA2  DB2CALL  DMRTMO2   YES
     17SEP-16:10:15 TIMEOUT   DB2H     RXDB2    BOLLAA2  DB2CALL  DMRTMO3
     17SEP-16:06:43 TIMEOUT   DB2H     RXDB2    BOLLAA2  DB2CALL  DMRDLK3
     17SEP-16:06:42 DEADLOCK  DB2H     RXDB2    BOLLAA2  DB2CALL  DMRDLK1   YES
     17SEP-15:45:46 DEADLOCK  DB2H     RXDB2    BOLLAA2  DB2CALL  DMRDLK1   YES
     17SEP-15:45:26 TIMEOUT   DB2H     RXDB2    BOLLAA2  DB2CALL  DMRDLK3

    From here, you can drill down for more information about the resources involved in a specific event, and then to a detail view of each lock holder or waiter.

    Tip

    If you are looking at a distributed thread, scroll right to see the additional workstation identifiers and identifiers that are specially formatted for SAP.

To analyze resource conflicts

  1. Press F3 to return to the Lock Wizard - Group panel (WZLKGOPT).

  2. Select Which Resources Involved?.A list of resource conflicts in the group is displayed.

    >W1 =LKRESZ===========(DBGHC====*=======)ddmmmyyyy==16:27:12====MVDB2====D====2
     --Resource Name--  Total           % Total                         Global
     Database Object    Conflicts       0...50..100   PAGE   ROW INDEX  Conflicts
     DSN8D51A DSN8S51E          8  72.7 ********         8     0     0          5
     DSN8D51A DSN8S51P          3  27.3 ***              0     3     0          3

    The first view summarizes all conflicts by table space and index space, so that you can quickly identify the objects with the most contention. The count on the right shows how many conflicts exist between members (global).

  3. Hyperlink on a resource name that shows several conflicts to see a breakdown of these conflicts by specific resource, down to a page or row level (LKRESNRZ).With this view, hot spots in your tables are immediately visible.

    >W1 =LKRESZ===LKRESNRZ(DBGHC====*=======)ddmmmyyyy==16:27:12====MVDB2====D====1
     --Resource Name-- Resource   Resource  Total           % Total     Global
     Database Object   Number     Type      Conflicts       0...50..100 Conflicts
     DSN8D51A DSN8S51E 0000001200 DATAPAGE          8  72.7 ********            5

  4. Hyperlink on either the resource name or the resource number to view a list of each lockout event that involved this resource.

    >W1 =LKRESZ===LKRESD==(DBGHC====*=======)ddmmmyyyy==16:27:12====MVDB2====D====8
     --Resource Name-- Resource   Resource   Time   Lockout  Blocker  Waiter   Gbl
     Database Object   Number     Type     -------- Type     PlanName PlanName Con
     DSN8D51A DSN8S51E 0000001200 DATAPAGE 16:12:37 DEADLOCK RXDB2    RXDB2    Yes
     DSN8D51A DSN8S51E 0000001200 DATAPAGE 16:10:18 TIMEOUT  RXDB2    RXDB2    Yes
     DSN8D51A DSN8S51E 0000001200 DATAPAGE 16:10:18 TIMEOUT  RXDB2    RXDB2    Yes
     DSN8D51A DSN8S51E 0000001200 DATAPAGE 16:10:15 TIMEOUT  RXDB2    RXDB2
     DSN8D51A DSN8S51E 0000001200 DATAPAGE 16:06:43 TIMEOUT  RXDB2    RXDB2
     DSN8D51A DSN8S51E 0000001200 DATAPAGE 16:06:42 DEADLOCK RXDB2    RXDB2    Yes
     DSN8D51A DSN8S51E 0000001200 DATAPAGE 15:45:46 DEADLOCK RXDB2    RXDB2    Yes
     DSN8D51A DSN8S51E 0000001200 DATAPAGE 15:45:26 TIMEOUT  RXDB2    RXDB2

    This view helps you quickly determine which plans are involved in the contention and whether the problem was occurring only at a particular time, perhaps because of an application affinity problem.

  5. Press F3 to return to the Lock Wizard - Group panel (WZLKGOPT).

  6. Select Global Blockers/Waiters.A list of plans that are involved in the lockouts is displayed.

    >W1 =LKBWZSSI=========(DBGHC====*=======)ddmmmyyyy==16:31:37====MVDB2====D====3
     Blocker  Blocker  Waiter   Waiter   Timeout Deadlock        % Conflicts   Gbl
     PlanName Member   PlanName Member   Involv.  Invovl.   .... 0....50..100  Con
     RXDB2    DB1H     RXDB2    DB2H           1        3   36.4 ****            4
     RXDB2    DB2H     RXDB2    DB1H           1        3   36.4 ****            4
     RXDB2    DB2H     RXDB2    DB2H           3        0   27.3 ***             0

    This view summarizes blocker/waiter plans and systems. You can identify conflicting plans across multiple data sharing Db2 members. Again, the list of events for a particular combination is available with a hyperlink, so you can quickly check to see if any application scheduling problems exist.

  7. Press PF3 until you return to EZDSSI.

  8. Select Locking Menu to access the Db2 Locking easy menu, EZDLOCK (shown in Analyzing-lock-contention), which shows current lock contention in SSI mode for the data sharing group.The steps in Viewing-current-lock-contention that you followed to analyze current lock contention for a single Db2 work the same in SSI mode for a data sharing group.

    Tip

    If you want to print an SSI lockout report, use the view LKPRINT, which combines the data from LKEVSSI with the resource information from LKEVRES. Customize LKPRINT to include the two hidden fields that show the waiter and blocker Db2 member names. For more information about the lockout views, see Application-analysis-views.

  9. Press F3 until you return to the Data Sharing Wizard (WZDSHAR).

 

Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*