DB2 FOR LUW / DB2FORLUW / B50 - DB2

An ISCAN-FETCH using CS isolation with Currently Committed in a
pureScale
environment may return results that are not committed if certain
timing events occur between the  concurrent ISCAN-FETCH on one
member and an update on another member.

In order for the uncommitted results to be seen, the following
timing has to all line up:
1) Member 1 has row1 locked in X mode but has not updated the
row yet.
2) Member 2 does an Currently Committed  ISCAN-FETCH of row1 and
the index keys looks committed so locking if deferred  to the
fetch.
The fetch tries to lock but cannot because the lock held by
member 1.
It asks member 1 to send the currently committed record.
Member 1 responds that row1 has not been updated, so member 2
prepares to do a retry.
3) Member 1 now updates row1.    The update is not yet committed
but the page with the updated row1 is flushed to the CF.  It is
flushed either due to aggressive page cleaning or because
another member wants to update it or read it in non-concurrent
access mode.   The default for UR/CS/RS scans is to use
concurrent
access mode.   The index is either not affected by the update or
is not
updated before step 4 or the updated index page is not flushed
to the CF before step 4.
4) Member 2 carries on doing its retry and reads the unchanged
index page and the updated data page.   If the index page has
not changed on this member since step 2, then the ISCAN-FETCH
may incorrectly return the newly updated uncommitted version of
row1.

A lot of timing has to line up for this to happen.  Between step
2 and step 4 the data page has to be updated and flushed to the
CF while the index page is not updated or not flushed.
Often, but not always, the timing window between steps 1 and 3
is very small.  It can be larger if  'use and keep exclusive
locks' is used or a plan is chosen that does X locking up front
during the scan that is driving an update.  An example of one
such a plan is an index scan where there are no predicates other
than the index range predicates.

****************************************************************
* USERS AFFECTED:                                              *
* pureScale users that do updates concurrently with Iscan      *
* Fetch using CUR_COMMIT.                                      *
****************************************************************
* PROBLEM DESCRIPTION:                                         *
* See Error Description                                        *
****************************************************************
* RECOMMENDATION:                                              *
* Upgrade to Db2 11.5 Mod 2 Fixpack 0 or higher                *
****************************************************************

If using CUR_COMMIT in a pureScale enviroment, avoid the
possibility of getting uncommitted results from an ISCAN-FETCH
by turning off the ability to return currently committed values
to avoid waiting for a remote lock conflict   This can be done
by setting the undocumented registry variable
DB2_ALLOW_CUR_COMMIT_IN_PS=OFF and recycling the instance.  With
this registry variable set, currently committed values will
continue to be returned to avoid waiting for local lock
conflicts, but queries will fall back to waiting for remote lock
conflicts.  This undocumented registry variable may be disabled
after this fix is available.

Until the fix is available and applied, if using CUR_COMMIT in a
pureScale enviroment, avoid the possibility of getting
uncommitted results from an ISCAN-FETCH by turning off the
ability to return currently committed values to avoid waiting
for a remote lock conflict   This can be done by setting the
undocumented registry variable DB2_ALLOW_CUR_COMMIT_IN_PS=OFF
and recycling the instance.  With this registry variable set,
currently committed values will continue to be returned to avoid
waiting for local lock conflicts, but queries will fall back to
waiting for remote lock conflicts.  This undocumented registry
variable may be disabled after this fix is available.

Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :

15.11.2019
18.02.2020
18.02.2020