Page P-locks

At times, a P-lock must be obtained on a page to preserve physical consistency of the data between members. These locks are known as page P-locks.

Page P-locks, are used, for example, when two subsystems attempt to update the same page of data and row locking is in effect. These locks are also used for GBP-dependent space map pages and GBP-dependent leaf pages for indexes, regardless of locking level. Page P-locks can also be retained if a member fails.

If an index page set or partition is GBP-dependent, Db2 does not use page P-locks for that index page set or partition if all of the following are true:

Only one member is updating the index page set or partition
No repeatable read claimers exist on the read-only members for the index page set or partition
The index is not on a Db2 catalog or directory table space

Because of the possible increase in P-lock activity with row locking, evaluate row locking carefully before using it in a data sharing environment. If you have an update-intensive application process, the amount of page P-lock activity might increase the overhead of data sharing.

To decrease the possible contention on those page P-locks, consider using page locking and a MAXROWS value of one on the table space to simulate row locking. You can get the benefits of row locking without the data page P-lock contention that comes with it. A new MAXROWS value does not take effect until you run REORG on the table space.