IBM Support

Referential Integrity and Julie's Scenario

Technote (FAQ)


Question

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.

Cause

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 may 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 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. Assume, we are doing 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. 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.


Answer

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).

Document information

More support for: DB2 for Linux, UNIX and Windows
Performance - Lock-Latch

Software version: 9.1, 9.5, 9.7, 9.8, 10.1, 10.5

Operating system(s): AIX, HP-UX, Linux, Solaris, Windows

Reference #: 1326755

Modified date: 05 July 2013


Translate this page: