Order-dependent transactions

In limited cases, one transaction, called an originating transaction, updates Db2 data with INSERT, UPDATE, or DELETE. Before completing phase 1 of the commit process, the originating transaction spawns a second transaction that is dependent on the updates of the first transaction. These types of transactions are referred to as order-dependent transactions.

Description of the problem

In some situations, a dependent transaction can encounter a row not found condition that does not occur in non-data sharing environments. Even in non-data sharing environments, dependent transactions need to tolerate the row not found condition in cases where the originating transactions roll back.

When the problem might occur

The row not found condition can occur if all of the following conditions are true:
  • The originating transaction spawns a dependent transaction before it completes phase 1 of the commit process.
  • The dependent transaction runs on a different member than the member on which the originating transaction runs.

    Because each member has its own local buffer pools, the uncommitted buffered pages that are updated by the originating transaction are not immediately visible to the dependent transaction.

  • The dependent transaction attempts to access a row that was updated by the originating transaction.
  • The dependent transaction is not bound with an isolation level of repeatable read.

Preventing the problem

To prevent these periodic row not found situations, consider using the IMMEDWRITE(YES) option of the BIND and REBIND commands for plans or packages that spawn dependent transactions that might run on other members. You can also use the IMMEDWRI subsystem parameter. IMMEDWRITE(YES) means that Db2 writes the page as soon as the buffer update completes. Db2 writes the data to one of the following structures or devices:
  • The group buffer pool
  • Disk storage for GBPCACHE NO group buffer pools
  • Disk storage for GBPCACHE NONE page sets
  • Disk storage for GBPCACHE SYSTEM page sets

The IMMEDWRI parameter (IMMEDIATE WRITE field on installation panel DSNTIP8) can override the value of the IMMEDWRITE bind option on a data sharing member. The following table illustrates the implied hierarchy when using the IMMEDWRI subsystem parameter and the IMMEDWRITE option of the BIND and REBIND commands.

Table 1. The implied hierarchy of the immediate write option
IMMEDWRITE bind option IMMEDWRI subsystem parameter Value at run time
NO NO NO
NO YES YES
YES NO YES
YES YES YES
Note: YES always has precedence whether it is defined by the subsystem parameter or the bind option. Updated pages that are group buffer pool-dependent are written at, or before, phase 1 of the commit process.
The following alternatives can help solve the order-dependent transaction problem:
  • Ensure that the originating transaction does not schedule the dependent transaction until the originating transaction has completed phase 1 of commit.
  • Run the dependent transaction with an isolation level of repeatable read.
  • If the dependent transaction is currently running with an isolation level of cursor stability AND CURRENTDATA(NO), changing it to use CURRENTDATA(YES) can sometimes solve the problem.
  • Add statement retry logic to handle the return of a row not found condition.
  • Run the dependent transaction on the same member as the originating transaction.