IFCID 002 - Locking Data

This topic shows detailed information about Record Trace - IFCID 002 - Locking Data.

Record Trace - IFCID 002 - Locking Data

The field labels shown in the following sample layout of Record Trace - IFCID 002 - Locking Data are described in the following section.


                                                     LOCKING DATA
DEADLOCKS............................:                    0   TIMEOUTS.............................:                    0
ESCALATIONS(SHR).....................:                    0   LOCK REQUEST.........................:              5164997
ESCALATIONS(EXC).....................:                  579   UNLOCK REQUEST.......................:              3765096
LOCK SUSPENSIONS.....................:                  120   QUERY REQUEST........................:                   48
IRLM LATCH SUSPENSIONS...............:                17388   CHANGE REQUEST.......................:               483228
OTHER SUSPENSIONS....................:               103530   CLAIM REQUESTS.......................:              1738448
MAXIMUM PAGE/ROW LOCKS HELD..........:                  N/A   CLAIM REQUEST FAILED.................:                   28
MAIN CONT - MAIN LATCH HELD..........:                    0   DRAIN REQUESTS.......................:                21203
MAIN CONT - USE COUNT NOT 0..........:                   26   DRAIN REQUEST FAILED.................:                    0
SECONDARY CONT - MAIN LATCH HELD.....:                15148   OTHER REQUEST........................:                    2
NOTIFY CB LATCH CONT - NOTIFY........:                   25   SUSPEND EXITS........................:               121338
GENERIC LATCH CONTENTIONS............:                    0   STATUS EXITS.........................:                    0
RESOURCE LATCH CONTENTIONS...........:                    0   TIMEOUT EXITS........................:                    0
LOCAL DEADLOCKS......................:                    0   RESUME EXITS.........................:               121337
IRLM PURGED - TIMEOUT................:                    0   DEADLOCK EXITS.......................:                    0
IRLM ABENDS RETRYABLE................:                    0   CQE USE COUNT........................:                    0
IRLM ABENDS NON RETRYABLE............:                    0   CQE GENERATED........................:                  120
RESOURCE HASH TABLE CONTENTIONS                               WORKUNIT HASH TABLE CONTENTIONS
    LOCK.............................:                 1922       LOCK.............................:                   38
    UNLOCK...........................:                 1549       UNLOCK...........................:                   41
    CHANGE...........................:                    1       CHANGE...........................:                    0
    COMPAT...........................:                    0       COMPAT...........................:                    0
    ASYNC LOCK.......................:                    0       ASYNC LOCK.......................:                    0
    QUERY FAST.......................:                    0       QUERY FAST.......................:                    0
    NOTIFY...........................:                    1       SYNC.............................:                    0

DEADLOCKS

The number of times deadlocks were detected. This number should be low, ideally 0.

Background and Tuning Information

Deadlocks occur when two or more application processes each hold locks on resources that the others need, without which they cannot proceed. Ensure that all applications accessing the same tables access them in the same order.

To improve concurrency:

Use row level locking instead of page level locking to minimize deadlocks.
For small tables use page level locking with MAXROWS 1.

To minimize deadlocks:

Delay updates to just before commit.
Use SELECT with the FOR UPDATE clause to use U lock.
Adjust the deadlock detection cycle parameter DEADLOK in the IRLM procedure.

This field is incremented once for each deadlock encountered. There is no correlation between this field and the deadlock events reported in the Locking report set or the number of IFCID 172 records written. This field reports all deadlocks, regardless of how they were resolved. The locking report and record trace IFCID 172 show only those deadlocks that were resolved by DB2.

Field Name: QTXADEA

TIMEOUTS

The number of times a unit of work was suspended for a time exceeding the timeout value. This number should be low, ideally 0.

Field Name: QTXATIM

ESCALATIONS(SHR)

The number of times the maximum page locks per table space are exceeded, and the table space lock escalates from a page lock (IS) to a table space lock (S) for this thread. You can specify the number of locks allowed per table space with the LOCKS PER TABLE(SPACE) parameter on the DB2 install panel DSNTIPJ.

Background and Tuning Information

Escalations can cause unpredictable response times. Lock escalations should only happen when an application process updates or references (if repeatable read is used) more pages than normal.

Field Name: QTXALES

LOCK REQUEST

The number of requests to lock a resource.

Field Name: QTXALOCK

ESCALATIONS(EXC)

The number of times the maximum page locks per table space are exceeded and the table space lock escalates from a page lock (IX) to a table space lock (X).

Background and Tuning Information

Escalations can cause unpredictable response times. Lock escalations should only happen when an application process updates or references (if repeatable read is used) more pages than it normally does.

A useful rule of thumb is to compare the number of escalations (shared and exclusive) to the successful escalations (those that did not cause deadlocks and timeouts). If this value, or the number Lock escalations - shared and if the number of timeouts or deadlocks is also not 0, the timeout or deadlock is probably caused by the escalation.

If many escalations cause deadlocks and timeouts, the recommendation is to change the escalation threshold value. Use of ANY is extremely useful to prevent unnecessary and expensive page locks, for example locking all pages in a tablespace.

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

Field Name: QTXALEX

UNLOCK REQUEST

The number of requests to unlock a resource.

This value can be less than the number of lock requests because DB2 can release several locks with a single unlock request.

Field Name: QTXAUNLK

LOCK SUSPENSIONS

The number of times a lock could not be obtained and the unit of work was suspended.

Background and Tuning Information

This number should be low, ideally 0.

The number of lock suspensions is a function of the lock requests. Lock suspensions (or conflicts) can happen on either LOCK REQUEST or CHANGE REQUEST.

Suspensions are highly dependent on the application and table space locking protocols.

Field Name: QTXASLOC

QUERY REQUEST

The number of query requests.

Field Name: QTXAQRY

IRLM LATCH SUSPENSIONS

The number of latch suspensions.

Field Name: QTXASLAT

CHANGE REQUEST

The number of change requests.

Field Name: QTXACHG

OTHER SUSPENSIONS

The number of suspensions caused by something other than lock or latch.

Field Name: QTXASOTH

CLAIM REQUESTS

The number of claim requests.

Field Name: QTXACLNO

MAXIMUM PAGE/ROW LOCKS HELD

The maximum number of page or row locks concurrently held against all table spaces by a single application during its execution. This count is a high-water mark. It cannot exceed the LOCKS PER USER parameter on panel DSNTIPJ.

Field Name: QTXANPL

CLAIM REQUEST FAILED

The number of unsuccessful claim requests.

Field Name: QTXACLUN

MAIN CONT - MAIN LATCH HELD

The number of unsuccessful claim requests.

Field Name: QTXALCMM

DRAIN REQUESTS

The number of drain requests.

Field Name: QTXADRNO

MAIN CONT - USE COUNT NOT 0

The number of drain requests.

Field Name: QTXALCMU

DRAIN REQUEST FAILED

The number of unsuccessful drain requests.

Field Name: QTXADRUN

SECONDARY CONT - MAIN LATCH HELD

The number of unsuccessful drain requests.

Field Name: QTXALCSM

OTHER REQUEST