For Universal tablespaces (UTS), which include both Partition-by-Range (PBR) and Partition-by-Growth (PBG) types, the partition serves as a primary "parent" object in the locking hierarchy. The finer-grained "child" objects are the data pages or individual rows within that partition, depending on the LOCKSIZE attribute defined for the tablespace. A fundamental rule of this hierarchy is that before a process can acquire a lock on a child object (a page or row), it must first successfully obtain an appropriate lock on the parent object (the partition).This parent-level lock serves as a traffic control mechanism, signaling the nature of the operations occurring within that partition to other concurrent processes.

The Role of Intent Locks

In most data modification scenarios, the parent lock acquired is not a "gross" lock that would prevent all other access. Instead, Db2 uses a more sophisticated mechanism known as an intent lock. For a data-changing operation like an INSERT, UPDATE, or DELETE, the process must acquire an Intent Exclusive lock (denoted as IX) on the partition. An IX lock on a partition signals to other transactions that the lock holder intends to acquire exclusive ( X ) locks on one or more child objects (pages or rows) within that partition.

The IX lock is compatible with other intent locks (e.g., another IX or an Intent Share IS ), allowing multiple transactions to concurrently modify different rows or pages within the same partition. However, it is not compatible with gross share ( S ) or exclusive ( X ) locks on the partition itself. This is the source of the contention problem: if a utility or another statement has placed an S or X lock on the entire partition, any subsequent INSERT statements targeting that partition will be blocked because they cannot acquire the necessary parent IX lock.

Partitioning Schemes (PBG vs. PBR)
The locking behavior is further influenced by the type of partitioning. In a PBR tablespace, the target partition for an incoming row is rigidly determined by the value of its partitioning key column(s). The INSERT operation must target that specific partition.

In contrast, a PBG tablespace offers greater flexibility. While Db2 may have a preferred partition based on clustering or space availability, an incoming row is not restricted to a single partition. It can, in theory, be placed in any partition that has sufficient free space.6 It is this architectural flexibility—the ability to choose from multiple potential target partitions—that the Db2 13 enhancement intelligently exploits to improve the success rate of INSERT operations.

The Contention Scenario:

PBG Insert Logic Prior to Db2 13

In Db2 12 and earlier releases, the logic for handling INSERT statements into PBG tablespaces was designed for speed but lacked resilience against transient contention. This "one and done" approach could lead to premature failures in highly concurrent environments.

The process began with Db2 identifying a preferred target partition and page for the new row. It would then issue a conditional lock request for the required parent IX lock on that partition. The term "conditional" is of paramount importance here; it signifies that the Db2 Internal Resource Lock Manager (IRLM) would not place the requestor in a wait state if the lock could not be granted immediately.6 If another process held an incompatible lock on the partition (for instance, an S lock from a LOCK TABLE statement or an X lock from an online utility), the conditional lock request would fail instantly.

Upon this immediate failure, Db2 would not wait or retry. It would simply abandon the attempt
on that partition and move to the next available partition in the tablespace, repeating the conditional lock request. This search for an unlocked partition would continue sequentially, potentially wrapping around from the last partition back to the first.

The point of failure occurred if this process completed a full cycle through every existing partition without being able to acquire an IX lock on any of them. At that moment, Db2 would terminate the INSERT statement, returning SQLCODE -904 (Resource Unavailable) with the specific reason code 00C90090, indicating a partition lock failure.

This behavior was suboptimal because it was inherently brittle. It could not differentiate between a long-term lock held by a major utility operation and a transient lock held for a few milliseconds by another quick transaction. In a high-throughput OLTP system, where such momentary contention is common, this logic resulted in a significant number of unnecessary transaction failures. The burden was then placed on the application to handle the -904 error, often by retrying the entire logical unit of work, which introduced complexity, increased latency, and ultimately reduced the overall throughput of the system.

The Db2 13 Solution:

Intelligent, Multi-Phase Lock Acquisition
With Db2 13, IBM introduced a fundamentally more intelligent and resilient multi-phase lock acquisition algorithm for PBG inserts. This enhancement, officially named "Improved locking for INSERT to partition-by-growth (PBG) tablespaces," is a prime example of the shift from a "fail-fast" to a "resilient-wait" philosophy.11 It internalizes the retry logic directly within the database engine, making applications inherently more robust without requiring any code changes. The new process unfolds across several distinct phases.

Phase 1: The Initial Conditional Lock Sweep
The process begins identically to the behavior in previous versions. Db2 identifies a target partition and issues a conditional request for an IX lock. If the lock is unavailable, it immediately moves to the next partition and tries again.6 This initial phase ensures that in low-contention environments, where the lock is likely to be acquired on the first attempt, there is no performance degradation. The operation remains as fast and efficient as before.

Phase 2: The Intelligent Retry Mechanism
This phase represents the core innovation of the Db2 13 enhancement. If the initial sweep through all partitions fails to secure a lock, Db2 does not immediately fail the INSERT statement. Instead, it activates an intelligent retry loop. Based on the empirical understanding that most partition-level contention is of very short duration, Db2 will retry the conditional lock request for up to 5 of the partitions that it recently found to be locked.6 This brief, targeted retry phase is specifically designed to wait out these common, transient lock conditions, dramatically increasing the probability of success without ever propagating a failure back to the application.

Phase 3: The Non-Conditional Wait
If the intelligent retry loop also proves unsuccessful, Db2 escalates its strategy one final time before considering failure. It reverts to the original target partition and issues a non-conditional lock request. This is a critical change in behavior. The request is no longer conditional, meaning the process will now enter a wait state until the lock becomes available or a timeout occurs.6 The duration of this wait is governed by the value of the IRLMRWT subsystem parameter, which defaults to 30 seconds.14 This phase handles scenarios with slightly longer, but still temporary, lock contention, providing a final, patient attempt to complete the operation.

Phase 4: Final Resolution - Growth vs. Full
Only if the non-conditional wait in Phase 3 times out does Db2 proceed to the final resolution logic, which depends on the physical state and definition of the tablespace.

● If MAXPARTITIONS has not been reached: Db2 will perform a check to see if all existing partitions are physically full. If they are, it will add a new partition to the tablespace, and the INSERT will proceed into this newly created space. If space is available in existing partitions but the lock could not be obtained after all prior phases, the INSERT will finally fail with reason code 00C90090 (partition lock failure).6
● If MAXPARTITIONS has been reached: The logic is similar, but without the option to grow. If all partitions are physically full, the INSERT fails with reason code 00C9009C (partition full). If space is available but the lock was unobtainable, it fails with 00C90090.6
This multi-phase approach transforms the INSERT process from a brittle, single-attempt action into a robust, persistent operation that can intelligently navigate the realities of a highly concurrent environment.