Incorrect results might be returned when an index jump scan is
used in referential integrity checking, and when another
concurrent transaction moves a child row during the referential
integrity scan of the child table by updating the index key of
the child table.
For example, assume we have a child table c (c1, c2, c3), where
(c1, c2) is foreign key with delete cascade. And we have an
index on (c1, c3, c2).
The index has the following key values
('b', 'a', 'a'), ('b', 'c', 'a'), ('b', 'x', 'b'),
('b', 'y', 'b'), ('b', 'z', 'b').
Transaction A executes a delete statement:
delete from p where c1 = 'b' and c2 ='b'
A referential integrity scan is needed on the child table to
delete the rows that have the corresponding foreign key value.
If we choose to use index jump scan using key predicates c1='b'
and c2='b', when the scan is positioned at ('b', 'x', 'b'),
transaction B updates ('b', 'z', 'b') to ('b', 'b', 'b'); that
is, transaction B moves the row behind the current position of
the referential integrity scan.
After both transactions finish, the child table has a foreign
key value that doesn't exist in the parent table.
$ db2 "select * from p"
C1 C2
-- --
a a
b a
2 record(s) selected.
$ db2 "select * from c"
C1 C2 C3
-- -- --
b a a
b b b
b a c
3 record(s) selected.
To confirm if you hit the problem, get a query plan of the SQL
statement. If you see index jump scan is used in the referential
integrity validation scan (Look for "Gap Info" to identify jump
scan), e.g.
Gap Info: Status
--------- ------
Index Column 1: No Gap
Index Column 2: Gap
Index Column 3: No Gap
and the following in the operator details of the index jump
scan:
ROWLOCK : (Row Lock intent)
ROWLOCK : ... WITHOUT END OF RANGE
TBISOLVL: (Table access Isolation Level)
TBISOLVL: REPEATABLE READ
Then you will probably hit the problem. |
