JR25587: RUNNING CONCURRENT OPERATIONS ON TABLE WITH RI DEFINED ON FOREIGN KEY: ONE OF THE OPERATIONS GETS SQL0911.

DB2 UDB Version 8.1 FixPak 16 (also known as Version 8.2 FixPak 9 )

APAR status

• Closed as program error.

Error description

• This APAR will improve concurrency on tables which have
  referential integrity constraints.
  .
  When tables have defined referential constraints, for a delete
  statement involving a parent table, or an insert involving
  a child table, RI checking logic is
  compiled in.  The RI checking needs to access the other table.
  Under certain circumstances, the scan on the other table cannot
  be done at the isolation level defined for the statement and
  must be upgraded internally to Repeatable Read in order to
  protect the integrity of the scan.  The use of Repeatable Read
  on the other table scan results in additional locking that can
  cause concurrency problems.
  .
  Note that unlike primary keys, the declaration of a foreign key
  on a table does not result in the creation of an index
  internally.  It is recommended that an index be created that
  matches the foreign key definition.  If the optimizer chooses to
  use this index to scan the child table, then there is no
  isolation level upgrade.  However, the optimizer is cost-based,
  and it chooses the access method that provides the cheapest
  plan.  Locking and isolation levels are not factors in this
  decision.
  .
  If the optimizer chooses to use an index scan to access the
  child table, and the chosen index is defined on the foreign
  key columns plus additional trailing columns, then the
  isolation level must be upgraded to RR.  This is required if
  the delete rule is CASCADE or SET NULL, to prevent child rows
  from being missed.
  .
  A similar situation can occur when inserting into a child
  table.  If the optimizer chooses a different index than the
  primary key to do the RI scan on the parent, it could result
  in the isolation level for that scan being upgraded internally
  to Repeatable Read, for the same reasons.
  .
  This APAR includes two fixes.
  .
  1. When the RI action rule is RESTRICT or NO ACTION, the
     isolation level does not need to be upgraded regardless of
     how the table is accessed.  This will result in improved
     concurrency for these cases.
  .
  2. A new optimizer heuristic is being provided to allow an
     end user to force the optimizer to choose the index that
     matches the RI key columns only, even if that index does
     not provide the cheapest access plan.  This will result in
     reduced locking and better concurrency, but may also result
     in a less than optimal access plan.  Caution should be
     used to determine if this behaviour is desirable for your
     specific scenario.  The heuristic setting is applied
     instance-wide and will affect all applicable statements.
  .
  The heuristic is enabled by setting the registry variable
  DB2_REDUCED_OPTIMIZATION=JULIE and recycling the database.
  .
  Scenario:
  .
  Here are the table and index definitions -
  .
    CREATE TABLE TEST_TABLE (ROW1 INT NOT NULL, ROW2 INT NOT NULL,
           constraint pk1 primary key (ROW1), constraint fk1
           foreign key (ROW2) references TEST_TABLE (ROW1) on
           delete cascade)
  .
    CREATE INDEX IX1 ON TEST_TABLE(ROW2,ROW1)
  .
  inserts the following rows into the table TEST_TABLE -
  .
   ROW1 ROW2
   ---- ----
   0    0
   101  0
   102  0
   103  0
   104  0
   11   101
   12   102
   13   103
   14   104
   501  11
   502  12
   503  13
   504  14
  .
  The application starts 4 threads, and each makes a connection to
  the database and does a DELETE from table TEST_TABLE i.e
  .
    delete from test_table where row1 = ?
  .
  For thread 1, it does a delete where row1 = 11.
  For thread 2, it does a delete where row1 = 12.
  For thread 3, it does a delete where row1 = 13.
  For thread 4, it does a delete where row1 = 14.
  .
  One of the thread would eventually get an exception with
  SQL0911.
  

Local fix

• For the DELETE case, define an index on the child table that
  matches the foreign key columns exactly
  and make sure the statistics are up to date.  If the optimizer
  chooses this index for the child table scan, the isolation
  level upgrade can be avoided.  This may not work when the
  statistics and cost estimation do not favour this index.
  

Problem summary

• Users Affected:
  Only applicable if you are doing concurrent DELETE on the table
  with Referential Integrity (RI) defined on primary key with
  "on delete cascade".
  
  Problem Description:
  If this APAR is not applied, it is possible to cause problem
  as described in the Error Description.
  
  Problem Summary: See Error Description.
  

Problem conclusion

• First fixed in DB2 UDB Version 8, FixPak 15
  

Temporary fix

• n/a
  

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

  JR25588 JR25591 JR25592

Fix information

• Fixed component name

  DB2 UDB ESE WIN

• Fixed component ID

  5765F4101

Applicable component levels

• R810 PSY

     UP

• R820 PSY

     UP