Results 1 to 10 of 10
  1. #1
    Join Date
    Nov 2007
    Posts
    27

    Unanswered: deadlock or timeout

    Hi
    i want to use 'WITH UR' option in a select query.
    actually the query is causing a deadlock or timeout.
    the following error is coming
    SQL0913N Unsuccessful execution caused by deadlock or timeout. Reason code
    "". SQLSTATE=57033

    is this the correct way of using WITH UR

    select c, d from mno , uvw where mno.a=uvw.b
    WITH UR;

    the db is db2V8.1 fp 14 solaris
    any help would be appreciated.

  2. #2
    Join Date
    Sep 2006
    Posts
    97
    Dead lock mean two applications(SQL) are trying to use same information (tables or rows). You got find out why other SQL or application holding that long. Yes you can use with ur (UNCOMMITED READ) for select stament.

  3. #3
    Join Date
    Mar 2006
    Posts
    104
    try

    Select .... FOR READ ONLY WITH UR

    Regards

    Harikumar

  4. #4
    Join Date
    Jan 2008
    Posts
    88
    set the following registry variables

    DB2_EVALUNCOMMITTED=YES
    DB2_SKIPINSERTED=YES

    that should help. u can search for these if u want more info

  5. #5
    Join Date
    Jan 2008
    Posts
    88
    but please post ur LOCKLIST, MAXLOCKS and LOCKTIMEOUT parameter and keep taking some snapshots to know what actually happening

  6. #6
    Join Date
    Dec 2006
    Posts
    6

    Have you converted your indexes to Type II?

    And were your files schema-bound after running the fix pack?

  7. #7
    Join Date
    Sep 2008
    Posts
    6
    Hi,

    This may help:
    db2 " ? SQL0913N"


    SQL0913N Unsuccessful execution caused by deadlock or timeout.
    Reason code "<reason-code>".

    Explanation:

    The request issued was involved in an unresolved contention for
    use of an object and the execution failed.

    The reason codes are as follows:


    2 transaction branch failed due to deadlock.

    68 transaction branch failed due to lock timeout.

    72 transaction rolled back due to an error concerning a DB2 Data
    Links Manager involved in the transaction.

    80 statement failed due to timeout.



    User Response:



    o For reason code 80, you can retry the failed statement
    without terminating the application. If the application
    accesses multiple remote databases, it may be better to
    rollback the transaction to avoid the possibility of a global
    deadlock.

    o For other reason codes, issue a request to rollback the
    transaction. The transaction cannot be committed due to the
    failure of the current transaction branch.

    o To help avoid deadlock or lock timeout, issue frequent COMMIT
    operations, if possible, within long-running applications or
    applications requiring data with high concurrent access.



    sqlcode : -913

    sqlstate : 57033

  8. #8
    Join Date
    Dec 2006
    Posts
    6
    Quote Originally Posted by rajaryan4545
    Hi
    i want to use 'WITH UR' option in a select query.
    actually the query is causing a deadlock or timeout.
    the following error is coming
    SQL0913N Unsuccessful execution caused by deadlock or timeout. Reason code
    "". SQLSTATE=57033

    is this the correct way of using WITH UR

    select c, d from mno , uvw where mno.a=uvw.b
    WITH UR;

    the db is db2V8.1 fp 14 solaris
    any help would be appreciated.
    When you try:
    SELECT c,d
    FROM mno , uvw
    WHERE mno.a = uvw.b
    WITH UR;

    and add "FETCH FIRST 1
    ROW ONLY"

    does it work?

  9. #9
    Join Date
    Sep 2008
    Posts
    6

    Smile Need to add one "s"

    add "FETCH FIRST 1 ROWS ONLY"

  10. #10
    Join Date
    Dec 2007
    Posts
    15
    select .... FETCH FIRST n ROWS ONLY FOR READ ONLY WITH UR

Posting Permissions

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