Db2 13 for z/OS delivered an enhancement to reduce the likelihood that an insert targeting a table in a partition-by-growth (PBG) table space will fail due to partition-level lock contention. In this blog entry I'll describe that enhancement, and I'll provide some additional information that I hope will help you understand how partition-level locking affects insert actions involving PBG table spaces.
First, an important distinction between PBG and partition-by-range (PBR) table spaces: for a row-insert targeting a table in a PBR table space, the row in question must be inserted into a particular partition, based on the value of the partitioning key of the to-be-inserted row. In the case of an insert into a table in a PBG table space, the new row can go into any of the table space's partitions. Yes, a particular partition will be preferred for the new row (information on that follows), but the insert operation will not be restricted to that preferred partition.
OK, so how is the preferred partition of a PBG table space determined for an insert operation? Simple: that will be the partition containing the target page for the insert. What's the target page? It's the page that, per the table's clustering index, would best preserve row-clustering for the table if the new row were to go into that page.
Now, you might think that with the target page for the new row identified, Db2's next step would be to get an X-lock on the page (or for the row, if row-level locking is in effect), once it has been determined that the page has room for the row. In fact, that doesn't happen - not yet, anyway. See, before Db2 can request a child lock (i.e., a lock on a page or a row), it has to obtain the associated parent lock (for a universal table space, whether PBG or PBR, the parent lock will be on the partition within which a new row is to be inserted). If you're concerned about an entire partition being locked just for an insert of a row, relax - almost always, the partition lock acquired for an insert (or for an update or a delete) will be an intent lock (specifically, for a data-change action such as insert, an IX lock - short for intent exclusive), and intent locks do not conflict with each other.
There's an interesting aspect of the partition lock request Db2 makes for the PBG partition containing the target page: it will be a conditional lock request. What does that mean? It means that if Db2 can't get the requested partition lock right away, it will move on to another of the PBG table space's partitions (if the PBG table space has more than one partition). In other words, Db2 will not wait for up to the subsystem's lock timeout limit to obtain the requested partition lock (that lock timeout limit is the value of the ZPARM parameter IRLMRWT, which has a default value of 30 seconds).
"But wait," you might be thinking. "Didn't you just say that partition-level locks are non-exclusive?" I said that they're almost always non-exclusive. In unusual circumstances, some process may have an exclusive lock on the partition containing the target page. In that case, because the lock on the partition requested by Db2 for the insert action was conditional in nature, Db2 will effectively say, "Oh, I can't get an IX lock on this partition? No prob. I'll just request a conditional lock on the next partition." What's the "next" partition? Well, it could be the next one forward from the one containing the target page, or the next one going backward from that partition (Db2 mixes up the direction of partition progression in these situations, to kind of even things out). Let's say that in this case the PBG table space has 10 partitions, and the target page is in partition 5, and the direction of partition progression is "forward." Db2 requests a conditional lock on partition 6, because the conditional lock request for partition 5 was a no-go. If the conditional lock request for partttion 6 is unsuccessful, Db2 will try again with partition 7.If Db2 gets to partition 10 and the conditional lock request for that partition is unsuccessful, it will wrap around to partition 1 and try for that one.
Suppose this progression continues, and again and again the conditional requests for partition-level locks are unsuccessful, and Db2 ends up back where it started - partition 5. Here's where the Db2 13 enhancement comes in. In a Db2 12 environment, if Db2 tried and tried the conditional partition lock requests and those were unsuccessful every time and Db2 wrapped around the PBG table space's partitions and got back to the partition containing the target page, the insert would fail at that time with a -904 reason code (resource unavailable) and a reason code 00C90090 (partition lock failure). In a Db2 13 environment, Db2 will NOT fail the isert when it has "wrapped back around to" (in our example) partition 5; instead, Db2 13 at that point will retry up to 5 of the conditional partition lock requests that had been unsuccessful the first time around. Given that exclusive partition locks (which block the intent-exclusive partition locks that Db2 has been requesting for the insert) tend to be of relatively short duration, there is a pretty good chance that one of those retries will be successful, and the insert operation can then proceed.
If the (up to 5) conditional partition lock request re-tries are all unsuccessful, will Db2 13 fail the insert? Not yet. At that point, Db2 13 will issue a "regular" (i.e., not conditional) request for a partition-level lock (for the partition containing the target page), and will wait for up to the IRLMRWT-specified timeout period for that lock.
If the "regular" partition lock request times out, will Db2 13 fail the insert? Maybe not yet. Here, there is a dependency on whether or not the PBG table space in question has reached its MAXPARTITIONS limit. I've been using the example of a PBG table space that has 10 partitions. If the MAXPARTITIONS value for the table space is greater than 10, here's what will happen if the conditional partition lock requests (original and retry requests) and the "regular" (non-conditional) partition lock request all fail: Db2 13 will check to see if all of the table space's partitions are full. If they are, Db2 will add a new partition to the table space, and the row-insert into the newly added partition should be successful. If Db2 sees that the table space's partitions are not all full, Db2 will fail the insert with a 00C90090 reason code (partition lock failure). Let's change the scenario slightly, so that the table space's MAXPARTITIONS value is 10 (equal to the current number of partitions for the table space). If that is the case then after all the partition lock requests (the conditional requests - including retries - and the non-conditional request) have been unsuccessful, Db2 will fail the insert with the aforementioned 00C90090 reason code.
If the "regular" partition lock request times out, will Db2 13 fail the insert? Maybe not yet. Here, there is a dependency on whether or not the PBG table space in question has reached its MAXPARTITIONS limit. I've been using the example of a PBG table space that has 10 partitions. If the MAXPARTITIONS value for the table space is greater than 10, here's what will happen if the conditional partition lock requests (original and retry requests) and the "regular" (non-conditional) partition lock request all fail: Db2 13 will check to see if all of the table space's partitions are full. If they are, Db2 will add a new partition to the table space, and the row-insert into the newly added partition should be successful. If Db2 sees that the table space's partitions are not all full, Db2 will fail the insert with a 00C90090 reason code (partition lock failure). Let's change the scenario slightly, so that the table space's MAXPARTITIONS value is 10 (equal to the current number of partitions for the table space). If that is the case then after all the partition lock requests (the conditional requests - including retries - and the non-conditional request) have been unsuccessful, Db2 will fail the insert with the aforementioned 00C90090 reason code.
A word on the "check for partition full" action just mentioned: this is a "speed check," using cached-in-memory information, and that cached-in-memory information might be a little off from reality. To explain: when Db2 searches a partition for space for a new row (and Db2 will do this if it was able to get the partition-level lock required for an insert), and finds that the partition in question is actually full, it will record that finding in memory. If there are then some deletes that free up space in the partition, will Db2 adjust the cached-in-memory information indicating "partition full?" No - doing that would add undesirable overhead and contention for delete operations. When will the cached-in-memory "partition full/not full" information be updated? After the next search for space in a given partition; so, at a particular time there could be a slight difference between what the cached-in-memory information says about the full-ness of partitions, and the reality of the full-ness of those partitions. What the "sometimes slightly fuzzy" cached-in-memory information enables is a very quick check on partition full-ness after Db2 has been unable to obtain the partition lock needed for an insert. This, I think, is probably what you want, After Db2 has spent time trying (maybe trying and trying and trying and...) to get a partition lock in order to get an insert done, you probably don't want Db2 to add the time needed for a comprehensive search for space to be added to the process. Without getting a partition lock, no insert is going to happen anyway - the real quick partition full-ness check using the "pretty accurate but maybe kind of fuzzy" cached-in-memory information is done just to see if adding a new partition is warranted following failure by Db2 to get a lock on any of the table's existing partitions.
One more thing: if getting a the partition lock needed for a row-insert is not a problem (and usually, this will in fact not be a problem), then Db2 might find that all of the table space's partitions are actually full (done by actually searching the partitions for space - not based on a check of the cached-in-memory partition full/not full information). In that case, Db2 will add a new partition for the table space if the table space's MAXPARTITIONS limit has not been reached. If the MAXPARTITIONS limit has been reached, the "partitions actually all full" situation will cause Db2 to fail the insert with a -904 (resource unavailable) and a 00C9009C reason code (partition full).
I hope that this look at some insert scenarios involving Db2 for z/OS partition-by-growth table spaces has provided you with some useful information. As always, thanks for visiting the blog.