HD69058: MULTIPLE INTROSPECTION PROCESSES ARE CAUSING DB2 DEADLOCKS ON E NOVFIQ TABLE

APAR status

• Closed as program error.

Error description

• Original Custoemr scenario:
  ******************************************
  ****************
  We currently execute multiple Introspection
  processes on multiple servers for the same LCA
  environment.
  It appears that a db2 deadlock occurs when one
  Introspection process locks up rows in the
  ENOVFIQ thru a Select and another introspection
  job attempts to update the same rows to identify
  them as being processed.
  Here is the Select SQL, from the first Introspection
  process :
  SELECT "OID", "TYPE", "DATADOMAIN",
  "MIMETYPE", "REQSERIAL", "REQTYPE",
  "VAULTNAME", "VAULTDOC", "VUSER" ,
  "VORGANIZATION", "FORMAT", "PROCID",
  "PROCTYPE", "SUBMISSION" FROM
  "ENOADM"."ENOVFIQ" WHERE
  "STATUS"='T' AND "PROCID" IS NULL AND
  "VAULTNAME"='ENOVIAVaultServer' ORDER BY
  "REQSERIAL" ASC FETCH FIRST 15 ROWS ONLY
  FOR UPDATE
  Here is the Update SQL from the second
  Intropsection process :
  UPDATE "ENOADM"."ENOVFIQ" SET "PROCID"
  = ? WHERE "REQSERIAL"=? AND "OID"=? AND
  "TYPE"=? AND "DATADOMAIN"=?
  AND "REQTYPE"=? "TYPE"=?
  AND "DATADOMAIN"=? AND
  "REQTYPE"=?
  Right now the deadlock check time is set to 10,000
  ms or 10 seconds.
  DB2 will wait 10 seconds for the deadlock to clear
  up and then the transaction will fail.
  Followup information at FE's request:
  1. How often does this problem occur?
  1) on average about 10 times per day.
  2. Can you recreate this problem in the test
  environment?
  2) I do not know if it can be recreated in the test
  env.
  You might possibly recreate the lock problem, by
  establishing about 20 Introspection jobs and
  randomly submit about 20,000 entries into
  the queue spread out over a 12 hour period.
  You should lock only the row that will be upated.
  Additional information:
  ENOVFIQ is the Introspection Query table.  When a
  row in the table is selected, the row is locked so we
  can update the Process ID.  That is a good thing.
  We should update the Process ID as soon as the
  process information is obtained.  This information is
  necessary to update the row later with information
  after the Introspection job relating to the row is
  completed.  The problem is we do not commit the
  row after obtaining the Process ID.  This ultimately
  creates a table lock.  When we have many
  application servers running introspection this is a
  major problem and causes time-puts.
  .
  

Local fix

Problem summary

• Multiple Introspection processes are causing DB2 deadlocks on E
  NOVFIQ table
  Original Custoemr scenario:
  ******************************************
  ****************
  We currently execute multiple Introspection
  processes on multiple servers for the same LCA
  environment.
  It appears that a db2 deadlock occurs when one
  Introspection process locks up rows in the
  ENOVFIQ thru a Select and another introspection
  job attempts to update the same rows to identify
  them as being processed.
  Here is the Select SQL, from the first Introspection
  process :
  SELECT "OID", "TYPE", "DATADOMAIN",
  "MIMETYPE", "REQSERIAL", "REQTYPE",
  "VAULTNAME", "VAULTDOC", "VUSER" ,
  "VORGANIZATION", "FORMAT", "PROCID",
  "PROCTYPE", "SUBMISSION" FROM
  "ENOADM"."ENOVFIQ" WHERE
  "STATUS"='T' AND "PROCID" IS NULL AND
  "VAULTNAME"='ENOVIAVaultServer' ORDER BY
  "REQSERIAL" ASC FETCH FIRST 15 ROWS ONLY
  FOR UPDATE
  Here is the Update SQL from the second
  Intropsection process :
  UPDATE "ENOADM"."ENOVFIQ" SET "PROCID"
  = ? WHERE "REQSERIAL"=? AND "OID"=? AND
  "TYPE"=? AND "DATADOMAIN"=?
  AND "REQTYPE"=? "TYPE"=?
  AND "DATADOMAIN"=? AND
  "REQTYPE"=?
  Right now the deadlock check time is set to 10,000
  ms or 10 seconds.
  DB2 will wait 10 seconds for the deadlock to clear
  up and then the transaction will fail.
  Followup information at FE's request:
  1. How often does this problem occur?
  1) on average about 10 times per day.
  2. Can you recreate this problem in the test
  environment?
  2) I do not know if it can be recreated in the test
  env.
  You might possibly recreate the lock problem, by
  establishing about 20 Introspection jobs and
  randomly submit about 20,000 entries into
  the queue spread out over a 12 hour period.
  You should lock only the row that will be upated.
  Additional information:
  ENOVFIQ is the Introspection Query table.  When a
  row in the table is selected, the row is locked so we
  can update the Process ID.  That is a good thing.
  We should update the Process ID as soon as the
  process information is obtained.  This information is
  necessary to update the row later with information
  after the Introspection job relating to the row is
  completed.  The problem is we do not commit the
  row after obtaining the Process ID.  This ultimately
  creates a table lock.  When we have many
  application servers running introspection this is a
  major problem and causes time-puts.
  .
  

Problem conclusion

• THIS PROBLEM WILL BE FIXED ON ENOVIA
  VERSION 5 RELEASE 19 GA level.
  NOTE THAT THIS PROBLEM WILL ALSO BE FIXED
  ON V5R18SP03.
  .
  

Temporary fix

Comments

APAR Information

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

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

Fix information

• Fixed component name

  ENOVIA LCA NT>X

• Fixed component ID

  569151700

Applicable component levels

• R518 PSN SP51803

     UP08/01/25 I 1000