ITEM: AU8289L

DB2v1.2 -Deadlock from doing a select w/ a REORGCHK w/ UPDATE running

ENV:
   DB2 v1.2 or v2.1
   AIX any level

DESC:
  I start a REORGCHK with update and let it go.  I then try to 
  do a select frm the sysibm.systables table and get a deadlock
  error (SQL0911).  The REORGCHK does finish successfully.  My
  question is, "Does the RUNSTAT initiated by the REORGCHK run
  at a reference or change level?"  Furthermore, while I was doing
  a this I took a snapshot of the locks and I can not figure out 
  what the "OBJECT NAME" column is referring to.  Can you tell
  tell me what this refers to?

ACTION:
  Checking the documentation I can find nothing which tells at 
  what level the RUNSTAT is executed.  I would have guessed its
  default level (change); however, the problem description makes it
  sound like the level is reference.  I have called Toronto and
  asked that they look into this as well.  

  While reading through the documentation on the Lock Timeout parameters
  I found that it is reported as a deadlock error (reason code 68). 
  This supports the hypothesis I gave the customer that the table is
  being locked and his select is timing out.  It also causes more
  confusion since this would imply an exclusive lock in either the
  REORGCHK or the RUNSTATS which I cannot find documented.

  When I look though the output of a lock snapshot taken before the
  REORGCHK starts but still during the RUNSTATS phase I can see six
  shared locks on the sysibm tables.  After the RUNSTATS every table
  in the database is either in an IN or IX mode. Those which are in 
  the the IX mode are:
    SYSIBM.SYSTABLES
    SYSIBM.SYSCOLDIST
    SYSIBM.SYSCOLUMNS

  So, the problem here is not which level the RUNSTAT is running at
  but that the REORGCHK is placing and exclusive lock on the some
  of the sysibm tables and the customer happened to select on the 
  wrong table at the wrong time.  Furthermore, if you a select
  from sysibm.systables before the RUNSTATS completes if works fine.

  CONCLUSIONS
  ============
  This then implies that the RUNSTATS is running in the default mode
  of change; however, by the time you would normally issue a select
  command you have entered the REORGCHK phase and the three tables
  before mentioned have an exclusive lock on them.  Furthermore,
  the testing implies that if these three tables are X-locked then
  effectively the entire database is X-locked since I cannot access
  anyother table until the REORGCHK is complete.

  Can you please tell me if this correct or I have gone off track
  somewhere?

  Object Name
  ============
   For ROW locks this is the row id RID of the object.
   For TABLE locks this is the file id of the object.

ACTION:
  Toronto has confimred that the RUNSTATS exec'd from a REORGCHK
  is of level CHANGE.  The locking problem is working as designed
  from the REORGCHK.




Support Line: DB2v1.2 -Deadlock from doing a select w/ a REORGCHK w/ UPDATE running ITEM: AU8289L

Dated: March 1996 Category: N/A

This HTML file was generated 99/06/24~13:30:24

Comments or suggestions?
Contact us