Lock requests between members

In a Db2® pureScale® environment, an application on one member might request a lock for an object currently locked by another member. The Db2 pureScale Feature introduces monitor elements that specifically report information about locks across members.

Processing within members in a Db2 pureScale environment can result in locking of objects as one application attempts to perform an operation that is not compatible with an operation being performed by another application. This can occur, for example, if two applications attempt to update the same row of data at the same time. You can monitor the extent to which this type of locking within a member takes place using the locking event monitor to view lock-related information. In a Db2 pureScale environment, lock waits might also occur between members, as one member requests a lock for an object currently locked by an application on a different member. So, in a Db2 pureScale environment, in addition to examining locking on individual members, you might also want to look at cross-member lock information.

Lock waits between members

The following monitor elements report only the portion of time that an application has been awaiting a lock held by another member:
  • lock_wait_time_global
  • lock_wait_time_global_top
  • lock_waits_global
  • lock_timeouts_global

The time reported by these monitor elements is included as part of the overall lock_wait_time and lock_wait_time_top monitor elements when these elements are viewed from the perspective of the Db2 pureScale instance as a whole. Similarly, the count of lock waits and time-outs is reported as part of both the lock_waits and lock_timeouts monitor elements when these monitor elements are viewed at the level of the instance as a whole.

The following scenario illustrates the relationship between these cross-member, or global lock-wait monitor elements and the monitor elements that report locks within a member:

  1. Application 1 on member 1 holds a shared (S) lock on a row.
  2. Application 2 on member 2 requests an exclusive (X) lock on the same row. Application 2 is forced to wait, as the row is currently locked by application 1.
  3. 2 ms later, application 3 on member 2 requests an exclusive (X) lock on the same row. Application 3 is also forced to wait.
  4. 8 ms later, application 1 releases its lock, and application 2 acquires its lock.
  5. 5 ms later, application 2 releases its lock, and application 3 acquires its lock.
The total time spent waiting for locks, as reported by lock_wait_time is 23 ms; application 2 had to wait for 10 ms in total, while application 3 had to wait for 13 ms in total. However, the amount of time spent waiting for a lock between members, lock_wait_time_global is only 10 ms, as this wait time is the only portion of the overall lock wait time that involved one member waiting on a lock held by another.

Similarly, the count of the number of locks held reported by lock_waits_global is 1. The wait by application 2 on application 1 counts as one member waiting on another. Even though application 3 was, for a portion of the time it was waiting, held up by an application on member 1, this lock wait is not counted as a lock wait between members because it obtained its lock from the local lock manager on member 2.

Reporting of applications holding locks

The locking event monitor shows information about applications holding locks. Generally speaking, the locking event monitor reports application information regardless of which member the application is running on. However, in rare cases, it might not be possible to determine which application is holding a lock when that application is running on a remote member. Consider the following example: 
ALTER WORKLOAD finance COLLECT LOCK WAIT DATA WITH HISTORY AND VALUES
   FOR LOCKS WAITING MORE THAN 5 SECONDS
This statement causes locks held longer than 5 seconds for the finance workload to be recorded in a lock event monitor. Now consider the following scenario.
  • A lock is held by application 1 on member 1.
  • After 5 seconds, the system attempts to write information about the lock to the lock event monitor, including application and member information. However, if the lock is released by application 1 and then a lock is immediately granted for the same object to another application on another member, the application holding the lock might be recorded as Lock holder was unavailable for collection.

Also note that if a member that held a lock fails, the locking event monitor is not able to report information about which applications running on that member might have held a lock on a required piece of data.

Finally, there are some limitations on reporting applications holding locks when you use deprecated monitoring features. See Monitoring locking with snapshot monitor for more information.