Explicit hierarchical locking state changes and performance implications

In a Db2® pureScale® environment, regular tables, range partitioned tables, or partitioned indexes exist in the SHARED state, the NOT_SHARED state, or in transition between these two states.

Objects entering or already in NOT_SHARED state

A table might enter the NOT_SHARED state as a result of the following activity:
  • If an INSERT, UPDATE or DELETE operation, or a scan is performed on a table.
  • If a table is range partitioned, a data partition that is accessed by using the preceding actions might enter NOT_SHARED state, while the logical table is unaffected.
  • The CREATE INDEX operation in non-partitioned indexes triggers a NOT_SHARED state transition of the logical table and the index anchors. If these indexes might enter the NOT_SHARED state independent of the data partitions.

As tables in a NOT_SHARED state are running in a mode similar to Db2 Enterprise Server Edition, they also have properties similar to Db2 Enterprise Server Edition tables. When a regular table is in the NOT_SHARED state, all of its table objects, such as data, index, LONG, LOB, XDA, are also in the NOT_SHARED state. However, only the table and row locks are behaving as in the NOT_SHARED state.

A range partitioned table can have its partitions enter or leave the NOT_SHARED state independently of each other, which is beneficial because not all transactions would access all the partitions on a table. So, partition independence allows members to access different partitions without conflict on the DATA_SHARING lock. Furthermore, all table objects (such partitioned indexes) within a partition also inherit properties as in the regular table case.
Note: Nonpartitioned indexes on a partitioned table all must enter or leave the NOT_SHARED state simultaneously. This simultaneous action is because all index anchor objects are under the protection of a single logical table lock. In other words, when a nonpartitioned index is being accessed its logical table lock enters the NOT_SHARED state, which causes all nonpartitioned indexes to enter the NOT_SHARED state as well. This behavior has no effect on the partitions since they have their own partition locks for protection.

Objects exiting the NOT_SHARED state

A table exits NOT_SHARED state as a result of the following activity:
  • Any access to the table from another member
  • Drop table or database deactivation
  • ATTACH or DETACH partition for a partitioned table

Any access to a table from another member results in an exit from NOT_SHARED state on the first member to allow the Db2 database server to grant a lock on the table to the other member. When a table is moving out of NOT_SHARED to the SHARED state, its table lock is held in Z mode (super exclusive mode) until all page locks and row locks are registered on the global lock manager (GLM) and all dirty buffer pool pages are written to the group buffer pool (GBP). This can be a lengthy process.

The Db2 database server can detect when a transition to NOT_SHARED state is not optimal, and it avoids the state change.

For member crash recovery, the entire table is unavailable until recovery is completed. When EHL is enabled, member crash recovery takes a length of time similar to crash recovery for an Db2 Enterprise Server Edition database. Time is required because changed data pages are not cached in the caching facility (CF), only in the local buffer pool. So modified pages must be rebuilt by replaying log records. Use the page_age_trgt_mcr database configuration parameter to control the length of time the pages remain buffered in the local buffer pool.

Exiting EHL is not immediate and involves CF communication for locks and pages for the table. Memory and time that is required for this operation is proportional to the number of locks and pages that are held for the object in the buffer pool. A small table typically takes only a few seconds to exit EHL. However, a very large table might take several minutes to exit EHL.

In extremely rare circumstances, it is possible for the GLM to become full during EHL exit and you are unable to send all required locks to the CF. This will prevent EHL exit from completing. If this condition occurs, other members will not be able to access this table until EHL exit is able to complete. This may result in lock time out events on other members accessing the table. When this condition is detected, and if the CF_GBP_SZ and CF_LOCK_SZ database configuration parameters are both configured to AUTOMATIC, the Db2 database server will attempt to trade memory from the group buffer pool (GBP) to the GLM, to allow lock registration to complete. The size of the GBP will be slightly reduced and the size of the GLM will be increased by this amount. There is a limit on the amount of GBP memory that can be traded in this way. However, if the CF_GBP_SZ an CF_LOCK_SZ database configuration parameters are not configured to AUTOMATIC, or if these actions do not free up enough GLM memory to allow EHL exit to complete within 40 seconds of detecting this condition, then all applications holding the lock that cannot be sent to the GLM will be forced. Forcing the applications will allow the lock to be released so that it does not need to be sent to the CF and this allows EHL exit to continue. ADM1504 will be logged when this memory trading occurs and ADM1503 will be logged for each application that is forced.

Lock timeout consideration due to time required to exit the NOT_SHARED state

It might be difficult to predict the time required for objects to exit the NOT_SHARED state. This depends on the number of pages and locks in use at the time. A long duration can result in an undesirable lock timeout error, (SQL0911N). With the release of Db2 11.5.8, the DB2_AVOID_TIMEOUT_ON_EHL_LOCKS registry variable can be set to prevent an application from timing-out when waiting for objects to exit the NOT_SHARED state.

Monitoring EHL

EHL can be monitored using the following monitors and APIs:
  • The LOCK WAIT event monitor
  • MON_GET_DATABASE() administrative API
  • MON_GET_TABLE() administrative API
  • MON_GET_LOCKS() administrative API
  • MON_GET_APPL_LOCKWAIT()administrative API