IBM Support

LI73352: AUTOMATIC RUNSTATS MAY CAUSE SQL0911N, A DEADLOCK.

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • If your application and Automatic runstats are running against
    to the same table, your application may receive SQL0911N (-911)
    and SQLN0991N as below application log and you may see a
    "Dead lock detected" message on your db2diag.log.
    
    From an application log:
    -----------------------------
    State:40001 Code:-911 [IBM][CLI Driver][DB2/LINUXX8664]
    SQLN0991N The current transaction has been
    rolled back because of a deadlock or timeout.
    Reason code "2". SQLSTATE=40001
    
    From a db2diag.log:
    -----------------------------
    2008-03-31-01.06.50.013901+540 I41419121E378     LEVEL: Event
    PID     : 25317               TID  : 1115699552 PROC : db2acd 0
    INSTANCE: db2inst1            NODE : 000
    APPID   : *LOCAL.db2inst1.080627131531
    FUNCTION: DB2 UDB, Automatic Table Maintenance,
    AtmTable::runstats, probe:500
    START   : Automatic Runstats: runstats has started on table
    "DB2INST1"."DEADLOCKTABLE"
    .
    2008-03-31-01.06.59.532045+540 I41419500E506     LEVEL: Warning
    PID     : 16469             TID  : 183030253472PROC : db2agent
    (NSDB) 0
    INSTANCE: db2inst1          NODE : 000         DB   : DB
    APPHDL  : 0-808             APPID: *LOCAL.db2inst1.080614204828
    AUTHID  : DB2INST1
    FUNCTION: DB2 UDB, catcache support,
    sqlrlc_systables_fetch_from_disk, probe:80
    RETCODE : ZRC=0x80100002=-2146435070=SQLP_LDED
    "Dead lock detected"
    DIA8002C A deadlock has occurred, rolling back transaction.
    
    This problem is a timing issue which should not happen
    frequently, and the user application can retry.
    

Local fix

  • 1. Changing auto maintenance to run at a period when there
       is lower level of database activity.
    2. Setting AUTO_RUNSTATS database configuration parameter to
       OFF.
    

Problem summary

  • n/a
    

Problem conclusion

  • Problem was first fixed in DB2 UDB Version 9.5 FixPack 3
    

Temporary fix

Comments

APAR Information

  • APAR number

    LI73352

  • Reported component name

    DB2 UDE ESE LIN

  • Reported component ID

    5765F4104

  • Reported release

    950

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2008-04-17

  • Closed date

    2009-03-09

  • Last modified date

    2009-03-09

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

    LI73351

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

Fix information

  • Fixed component name

    DB2 UDE ESE LIN

  • Fixed component ID

    5765F4104

Applicable component levels

  • R950 PSY

       UP

[{"Business Unit":{"code":"BU048","label":"IBM Software"},"Product":{"code":"SSEPGG","label":"DB2 for Linux, UNIX and Windows"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"950","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
09 March 2009