Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2002
    Posts
    12

    Unanswered: deadlock or timeout

    when i am trying to drop a table through an application, i am getting the
    following error. This table is a copy of the master table and would not be
    participated any of the application process.

    COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2/SUN] SQL0911N The
    current transaction has been rolled back because of a deadlock or timeout.
    Reason code "68". SQLSTATE=40001

    Later some time i am able to drop the table. I am not getting this error
    all the times. what is the reason for this kind of behavior ?

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: deadlock or timeout

    Have you had the opportunity to look at the application snapshot?

    Cheers

    Sathyaram


    Originally posted by bondalapati100
    when i am trying to drop a table through an application, i am getting the
    following error. This table is a copy of the master table and would not be
    participated any of the application process.

    COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2/SUN] SQL0911N The
    current transaction has been rolled back because of a deadlock or timeout.
    Reason code "68". SQLSTATE=40001

    Later some time i am able to drop the table. I am not getting this error
    all the times. what is the reason for this kind of behavior ?

  3. #3
    Join Date
    Sep 2002
    Posts
    12

    Re: deadlock or timeout

    Originally posted by sathyaram_s
    Have you had the opportunity to look at the application snapshot?

    Cheers

    Sathyaram
    Here is an attachment of database snapshot, following is a partial information from the snapshot.

    Deadlocks detected = 0
    Lock escalations = 6
    Exclusive lock escalations = 6
    Agents currently waiting on locks = 0
    Lock Timeouts = 6

    The application process tried recursively 6 times to drop the table with a time elapse of 2 min.
    Please let me know what to do ?
    Attached Files Attached Files

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: deadlock or timeout

    The db snapshot suggests that the reason for your error is lock timeout.

    Well, take an application snapshot using

    db2 get snapshot for applications on <dbname>

    when you feel your application is waiting on locks.

    Is there any reason you have not switched on the monitor switches.

    Default database monitor switches
    Buffer pool (DFT_MON_BUFPOOL) Lock (DFT_MON_LOCK)
    Sort (DFT_MON_SORT)
    Statement (DFT_MON_STMT)
    Table (DFT_MON_TABLE)
    Unit of work (DFT_MON_UOW)

    db2 update dbm cfg using DFT_MON_BUFPOOL ON

    etc. Don't forget to restart the instance.

    I storongly recommend keeping these switches on atleast in dev and testing environments.

    Well, coming back to your error ... Once you turn on this monitor switches, at the time of lock wait, your application snapshot will show

    Status of Application = Lock-Wait

    and at the tail-end of that specific application's snapshot, you will find more information on the application holding the lock which the current application wants.

    Good luck

    Cheers

    Sathyaram




    Originally posted by bondalapati100
    Here is an attachment of database snapshot, following is a partial information from the snapshot.

    Deadlocks detected = 0
    Lock escalations = 6
    Exclusive lock escalations = 6
    Agents currently waiting on locks = 0
    Lock Timeouts = 6

    The application process tried recursively 6 times to drop the table with a time elapse of 2 min.
    Please let me know what to do ?

  5. #5
    Join Date
    Sep 2002
    Posts
    12

    Re: deadlock or timeout

    Thanks ! I will do that.

  6. #6
    Join Date
    Jul 2002
    Posts
    86

    Re: deadlock or timeout

    The resone is simple, when you are trying to drop the child table, sombody else or yourself is using the parent table and you get "68" error, you should release the parent table and then try to drop the table.
    Thanks

  7. #7
    Join Date
    Sep 2002
    Posts
    12

    Re: deadlock or timeout

    Originally posted by David2002
    The resone is simple, when you are trying to drop the child table, sombody else or yourself is using the parent table and you get "68" error, you should release the parent table and then try to drop the table.
    Thanks

    What do you mean by child table and parent table ? There is only one table that is to drop. Could you please explain more clear on this ?

  8. #8
    Join Date
    Jul 2002
    Posts
    86

    Re: deadlock or timeout

    OK, sombody is using the table and you can not drop the table, table should be released and then drop it.
    Thanks

Posting Permissions

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