Results 1 to 15 of 15
  1. #1
    Join Date
    Nov 2008
    Posts
    7

    Question Unanswered: SQL Error: -911, SQLState: 40001 ]ROLLBACK DUE TO DEADLOCK/TIMEOUT; REASON 68

    We are using Database server = DB2/SUN64 8.2.4 fixpack 11.

    Our J2EE Application will do batch inserts in DB2. recently we are getting Exceptions while we doing batch inserts on table.

    net.sf.hibernate.util.JDBCExceptionReporter.logExc eptions(JDBCExceptionReporter.java:58)] ERROR - [BEA][DB2 JDBC Driver][DB2]Abnormal end unit of work condition occurred.
    net.sf.hibernate.util.JDBCExceptionReporter.logExc eptions(JDBCExceptionReporter.java:57)] WARN - SQL Error: -911, SQLState: 40001
    [net.sf.hibernate.util.JDBCExceptionReporter.logExc eptions(JDBCExceptionReporter.java:58)] ERROR - [BEA][DB2 JDBC Driver][DB2]ROLLBACK DUE TO DEADLOCK/TIMEOUT; REASON 68

    We tried a lot to replicate the error in Our Dev environments . but we could not able to do...

    It seems issue is with Database configurations.

    And what we observed is ....when we query the data from table , at sametime bacth inserts are happeing on that table, we are getting exceptions.

    This table has 900K rows have one column with CLOB(50080)
    Is there anything todo with Tablespace and Bufferpool configurations?

    Can someone pls advise me if u have idea?

    Thanks.

  2. #2
    Join Date
    Oct 2004
    Location
    DELHI INDIA
    Posts
    338
    Try to make the following changes at Database Configuration Level:

    1> change the LOCKTIMEOUT to "60". Currently it can be "-1" in your system.
    Lateron you can change it to any value based on your application (OLTP or DSS).

    2> Increase the LOCKLIST memory parameter from its default value. If it is 5000, increase it by atleast 4-5 times.

    Now check the behavior. I am assuming you don't want to use "WITH UR" in the SELECT statements in your application. If you could have used "WITH UR", then the lock-escalation comes down drastically. But it all depends on your business requirement whether you are fine with reading UNCOMMITED records.

    Thanks.
    Jayanta Datta
    New Delhi, India

  3. #3
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Quote Originally Posted by JAYANTA_DATTA
    Try to make the following changes at Database Configuration Level:

    1> change the LOCKTIMEOUT to "60". Currently it can be "-1" in your system.
    Lateron you can change it to any value based on your application (OLTP or DSS).

    2> Increase the LOCKLIST memory parameter from its default value. If it is 5000, increase it by atleast 4-5 times.

    Now check the behavior. I am assuming you don't want to use "WITH UR" in the SELECT statements in your application. If you could have used "WITH UR", then the lock-escalation comes down drastically. But it all depends on your business requirement whether you are fine with reading UNCOMMITED records.

    Thanks.
    Jayanta Datta
    New Delhi, India
    The OP already has locktimeout set to something other than -1. Hence he would not have gotten the RC = 68 (this is a timeout).

    Increasing the locklist probably will not help since it only takes one lock to cause this to occur.

    His best bet is to commit the transaction as soon as possible.

    Andy

  4. #4
    Join Date
    Nov 2008
    Posts
    7
    Quote Originally Posted by ARWinner
    The OP already has locktimeout set to something other than -1. Hence he would not have gotten the RC = 68 (this is a timeout).

    Increasing the locklist probably will not help since it only takes one lock to cause this to occur.

    His best bet is to commit the transaction as soon as possible.

    Andy
    Thank you Andy and Jayanta for your Quick response.
    we have LOCKTIMEOUT as 30. And we are commiting the bacth transactions size of 100.
    And we are getting this message only when we Query the data from the Application, and inserts also happens at same time.

    And also what we observed recently is while taking of that table dump, error popup again.

    And we are setting Isolation level as UR while our application querying Table.

    i'm wondering Isolation UR also cause locks on Table?

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by rodex2mail

    i'm wondering Isolation UR also cause locks on Table?
    It shouldn't. There may be various side effects that cause the timeout, e.g. lookups related to the referential integrity constraints, or triggers firing upon an insert. You probably should study the lock snapshot to determine what causes the problem.

    You could also try setting the DB2_SKIPINSERTED registry variable to ON (you will need to deactivate and re-activate the database for the change to take effect).
    ---
    "It does not work" is not a valid problem statement.

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Who is getting the lock timeout, the application or the batch transactions?

    Andy

  7. #7
    Join Date
    Nov 2008
    Posts
    7
    Quote Originally Posted by ARWinner
    Who is getting the lock timeout, the application or the batch transactions?

    Andy
    Andy, batch transactions is getting timeout

  8. #8
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    That is what I guessed. You need to have the application commit/rollback after the read, or any locks will stay there and the batch process wants to uses locks that have to wait for the read locks.

    Andy

  9. #9
    Join Date
    Nov 2008
    Posts
    7
    Quote Originally Posted by ARWinner
    That is what I guessed. You need to have the application commit/rollback after the read, or any locks will stay there and the batch process wants to uses locks that have to wait for the read locks.

    Andy
    But my application Querying data with UR isolation level.

  10. #10
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    The locks used for the read are incompatible with the locks used by the batch process. Hence the batch process waits. Just commit/rollback after the application read.

    Andy

  11. #11
    Join Date
    Nov 2008
    Posts
    7
    Quote Originally Posted by ARWinner
    The locks used for the read are incompatible with the locks used by the batch process. Hence the batch process waits. Just commit/rollback after the application read.

    Andy
    Andy, I could not understand. you mean Uncommitted Read isolation level also hold locks.

    if so is there any DB register which can be turned on/off?
    Bcaz i could not replicate the same issue in Our Dev/UAT environments.it is happening only in Prod.

  12. #12
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Use the snapshot monitor to see what locks the application is using.

    Andy

  13. #13
    Join Date
    Oct 2004
    Location
    DELHI INDIA
    Posts
    338
    Rodex,

    Are you running this application in many threads together? Did you try to see the Diag.log to see any other information regarding this -911.

    Can you increase the LOCKTIMEOUT from 30 to 60 or some other higher value to see whats going wrong.



    Regards,
    Jayanta
    New Delhi, India
    Last edited by JAYANTA_DATTA; 11-12-08 at 04:17.

  14. #14
    Join Date
    Nov 2008
    Posts
    7
    Quote Originally Posted by JAYANTA_DATTA
    Rodex,

    Are you running this application in many threads together? Did you try to see the Diag.log to see any other information regarding this -911.

    Can you increase the LOCKTIMEOUT from 30 to 60 or some other higher value to see whats going wrong.



    Regards,
    Jayanta
    New Delhi, India
    We tried to replicate in Dev environement...but no luck...
    our Database server is DB2/SUN64 8.2.4 fixpack 11.

    has DB2 LOCKSIZE at tablespace level?

  15. #15
    Join Date
    Oct 2004
    Location
    DELHI INDIA
    Posts
    338
    LOCKLIST is at Database Level. Next time whenever you run the application in Prod, try to gather some Snapshot on all applications. Then in those SNAPSHOTs, search for the "SELECT and INSERT" statements. You will get to see some more details, on how many Locks Hold, whether there was any EXCLUSIVE locks etc. Till we get some more information on what's happening inside, it will be tough for others to help you much on this problem.

Posting Permissions

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