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