Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2011
    Posts
    2

    Unanswered: Table is always in Deadlock State

    Hi,

    I am running DB28.2 on SLES 10. I was executing a Reorg on a very large table, and for some reason the reorg hung. After 7 hours waiting, I tried cancelling the application, but it wouldn´t stop, so I stopped the instance and started it again. The are currently no active applications running and no locks on the table, but when I try to execute any command (select, reorg, drop, etc...) it returns:

    SQL0911N The current transaction has been rolled back because of a deadlock
    or timeout. Reason code "68". SQLSTATE=40001

    How is this possible, there are no applications actives and no locks are shown when I run db2bp. How can I bypass this problem, or try to remove a deadlock that doesn´t exist.

    Thanks,

    Fabio

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    when the application is waiting, take a snapshot in another window

    db2 update monitor switches using bufferpool on lock on sort on statement on table on uow on timestamp on
    db2 get snapshot for all on <dbname> > snap1
    db2 list utilities show detail > snap1

    if you lock timeout value is small that you are unable to run these commands, set it to -1 temporarily

    Look through the snapshots - Application and lock snapshot for clues on the lock wait condition.

    Are there any entries in db2diag.log related to this table or tablespace ?
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Oct 2007
    Posts
    246
    select, reorg , drop etc r u executing against the table in which the reorg is hung, what is mode ur running the reorg offline online, chk in wht phase the reorg is using db2pd if it has crossed the build phase then you need to wait to till it get finished.

    regd
    Pual

  4. #4
    Join Date
    Mar 2011
    Posts
    2
    Hi,

    Apparently, after the reorg failed, and the database was started again, an internal rebuild of the index(which got corrupted) was run by DB2. It took 3 hours, but it finally finished.

    Thanks for all the help.

    Fabio

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •