Question & Answer
When evaluating referential constraints, there are some situations where the DB2 software might internally upgrade the isolation level used on the scan of the child table to Repeatable Read (RR), regardless of the isolation level set by the user. This might result in additional locks being held until commit, which increases the possibility of a deadlock or lock timeout occurring. One such case is known as Julie's scenario.
When a referential integrity relationship is defined between two tables, the DB2 software automatically creates unique indexes to support the defined primary keys, if no such index already exists. However, the DB2 software might not create indexes to support the foreign keys. As a result, when validating a referential integrity relationship, the resulting scan on the child table might use any access method the Optimizer chooses, from a table scan or an index scan using the existing indexes on the table.
Julie's scenario refers to the case when an index scan is being used for integrity checking. More specifically, the case when the index definition contains a column that is not referenced in the referential integrity relationship. The child table is being scanned under RR isolation in this scenario to prevent another application from moving a child row during the referential integrity scan of the child table by updating the index key of the child table. This displaced child row might be missed if it moves behind the current position in the index scan, which might cause a referential integrity violation.
The following is an example of Julie's Scenario:
Consider a table has a foreign key defined on columns (a,b) and an index on defined (a,b,c). Julie's Scenario initiates when a and b are set, but there can be different values for c in the table. This is the case during an RI scan. There is a specific value from the parent table that we are looking for in the child table.
There might be entries where RID stands for "row identifier," the pointer to the row in the table that this index key refers to:
a, b, c1 RID1
a, b, c2 RID2
a, b, c3 RID3
If you are scanning this index, and we are on the entry for RID2, and another user updates the row (with RID3) where c=c3 to set c to c0 where c0 < c1, the index might change to:
a, b, c0 RID3
a, b, c1 RID1
a, b, c2 RID2
The RI index scan also misses RID3. In contrast, the row from the child table is not deleted if a cascade delete process is performed.
If an index is created on the foreign key columns (a, b), the index entry might look like:
a, b RID1,RID2,RID3
The result is unaffected by updates to column c in the table since that column is not referenced in the index. The upgrade to isolation level RR is not necessary if this index is used for the referential integrity scan.
Julie's Scenario also occurs when the child table is an MDC (multidimensional clustering) table and the foreign key is not a superset of the MDC (dimension) key.
We are scanning the child table under RR in such a scenario to prevent another application from moving a child row (by updating the MDC key of the child table) while performing the referential integrity scan of the child table. The scan might miss the child row that has moved if it moves behind our current position in the index scan. This might lead to a referential integrity violation.
For example, we might have a parent MDC table named MESSAGE which is organized on column GENID_MESSAGE and has a primary key defined on column MSG_SA. The child table, OUTGOING_MESSAGE, is organized on column GENID_OUTGOING_MESSAGE and has a foreign key defined on column MSG_ID that references MESSAGE (MSG_SA) with delete rules.
Assume that we are deleting a parent row with MSG_SA=3, the delete statement might perform a scan of the child table with a predicate MSG_ID=3. Assuming that we are performing a scan from block1 to block3 of the child table, there is a child row with MSG_ID=3 in block3 which we shall refer to as the candidate child row.
Now assume the RI scan has finished scanning block1 of the child table, however, it has not scanned block3 yet. If another application updates the MDC key of the candidate child row and moves it from block3 to block1, which the delete statement has already scanned, the scan misses this candidate child row during the RI scan and cause an RI violation. The upgrade to RR isolation on the RI scan of the child prevents this by blocking the update of MDC key on the child table until the delete from the parent table commits or rolls back.
A similar situation might also occur during database partitioning if the foreign key is not a superset of the partitioning key, or when the child table is an RPT (range partitioned table) and the foreign key is not a superset of the partitioning key. The use of isolation RR for the child table scan prevents other users from moving rows into ranges of the index that have already been scanned by updating the partitioning key column.
To avoid the isolation level upgrade, you can provide an index that can be used for the referential integrity scan that matches the foreign key definition.
For a regular table in a non-partitioned environment, you can create an index that only contains the column(s) of the foreign key. For an MDC table like the one in the above example, this would mean organizing the child table OUTGOING_MESSAGE on (GENID_OUTGOING_MESSAGE, MSG_ID); that is, including the foreign key column(s) in the ORGANIZE BY clause. For a DPF environment, this means changing the partitioning key of the child table to include the foreign key column(s). For a range partitioned table, the foreign key must include all table partitioning key columns.
However, note that if the optimizer does not choose to use this index, the isolation level upgrade might still occur. Because the optimizer is cost-based, it will choose the access method that provides the cheapest plan; locking and isolation levels are not factors in this decision. As a result, a new optimizer heuristic was provided as of the v91 fp8 and v95 fp5 updates (and it GA level code for v97 and beyond) to allow an end user to force the optimizer to choose the index that avoids the isolation upgrade, even if that index does not provide the cheapest access plan. The heuristic setting is applied instance-wide and will affect all applicable statements.
The heuristic is enabled by setting the registry variable
DB2_REDUCED_OPTIMIZATION=JULIE and recycling the database.
For more information about this heuristic, please see APARs JR25592 and IZ16958.
As of the DB2 version 10.1 software, the JULIE setting is on by default, so there is no need to set the optimizer heuristic on that code level.
16 June 2018