IBM Support

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

Subscribe

You can track all active APARs for this component.

 

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 LUW Version 9, Fixpack 3
    

Temporary fix

  • n/a
    

Comments

APAR Information

  • APAR number

    JR25592

  • Reported component name

    DB2 UDB ESE WIN

  • Reported component ID

    5765F4101

  • Reported release

    910

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2006-12-18

  • Closed date

    2007-08-15

  • Last modified date

    2008-03-24

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

    JR25587

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

Fix information

  • Fixed component name

    DB2 UDB ESE WIN

  • Fixed component ID

    5765F4101

Applicable component levels

  • R910 PSY

       UP

[{"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSEPGG","label":"DB2 for Linux- UNIX and Windows"},"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"910","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
07 January 2022