Retained P-locks

Just as with transaction locks, certain P-locks can be retained because of a system failure.

A retained P-lock means that other members cannot access the data that the P-lock is protecting if the accessing member requests a P-lock in an incompatible state. Thus, if a member fails holding an IX page set P-lock, it is still possible for another member to obtain an IX page set P-lock on the data.

Use the DISPLAY DATABASE command with the LOCKS option to determine if retained locks exist on a table space, index, or partition. An R in the LOCKINFO column indicates that a lock is retained.

The following table shows the possible modes of access for a page set and the P-lock state that is retained if the member that is represented in the first column fails.

Table 1. Determining retained P-lock state
One member's interest Other members' interest Retained P-lock states of single member
Read-only None, Read-only None
Read-only Read/Write None
Read/Write None X or NSU 1
Read/Write Read-only IX 2
Read/Write Read/Write IX
Notes:
  1. NSU stands for non-shared update. It acts like an X lock, but is only used during P-lock negotiation from an X to an SIX.
  2. The P-lock is retained in SIX mode if the page set or partition is an index that is not on a Db2 catalog or directory table space.