Topic
  • 2 replies
  • Latest Post - ‏2012-10-17T17:00:27Z by SystemAdmin
Sammy1984
Sammy1984
1 Post

Pinned topic DB2 deadlock or timeout error

‏2012-10-17T05:03:33Z |
Hello Everyone,

I am trying to execute below query to keep last 3 months of data in the table and delete rest of the data.

DELETE FROM REPORTER_STATUS WHERE FIRSTOCCURRENCE < current timestamp - 3 MONTHS;


However when I run the above query, I am getting deadlock or timeout error as below.

DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0911N The current transaction has been rolled back because of a deadlock or timeout. Reason code 
"2". SQLSTATE=40001   SQL0911N The current transaction has been rolled back because of a deadlock or timeout. Reason code 
"2".   Explanation:   The current unit of work was involved in an unresolved contention 

for use of an object and had to be rolled back.   The reason codes are as follows:   2 The transaction was rolled back due to a deadlock.   68 The transaction was rolled back due to a lock timeout.     72 The transaction was rolled back due to a DB2 Data Links Manager error during the transaction.     73 The transaction was rolled back because a queuing threshold such as the CONCURRENTDBCOORDACTIVITIES threshold caused two or more activities to reach a deadlock state. For more information, see 
"CONCURRENTDBCOORDACTIVITIES threshold" in the DB2 Information Center.   The application was rolled back to the previous COMMIT.   User response:   The changes associated with the unit of work must be entered again.   To help avoid deadlock or lock timeout, issue frequent COMMIT operations, 

if possible, 

for a long-running application, or 

for an application likely to encounter a deadlock.   Federated system users: the deadlock can occur at the federated server or at the data source. There is no mechanism to detect deadlocks that span data sources and potentially the federated system. It is possible to identify the data source failing the request (refer to the problem determination guide to determine which data source is failing to process the SQL statement).   Deadlocks are often normal or expected 

while processing certain combinations of SQL statements. It is recommended that you design applications to avoid deadlocks to the extent possible.   For more detailed information about preventing deadlocks or lock timeouts search the DB2 Information Center (http:
//publib.boulder.ibm.com/infocenter/db2luw/v9) using phrases such as "deadlock prevention", and terms such as "deadlocks" and "lock timeouts".   If a deadlock state was reached because of a queuing threshold such as the CONCURRENTDBCOORDACTIVITIES threshod, increase the value of the queuing threshold.   sqlcode: -911   sqlstate: 40001


I have restarted DB2 and the server to release the locks, but it did not help me in fixing the issue. Even issued 'COMMIT' staement to release locks, but in vain. :(

Environment details:

OS : RHEL 5.5 64-bit
Database : DB2 V9.7

Please help me out in fixing this issue.
Regards,
G Ravi Chandran.
Updated on 2012-10-17T17:00:27Z at 2012-10-17T17:00:27Z by SystemAdmin
  • SystemAdmin
    SystemAdmin
    17917 Posts

    Re: DB2 deadlock or timeout error

    ‏2012-10-17T10:46:52Z  
    Hi Ravi,

    Try to increase your LOCKTIMEOUT databse configuraton parameter and then check.
    If not, try to use the following method.

    1. Create similar table with LIKE statement (REPORTER_STATUS_DUP)
    2. Insert into dup_table Select statement
    Please check you have got correct data in your duplicate table
    INSERT INTO REPORTER_STATUS_DUP SELECT * FROM REPORTER_STATUS WHERE FIRSTOCCURRENCE < current timestamp - 3 MONTHS
    3. Truncate data
    TRUNCATE TABLE REPORTER_STATUS IMMEDIATE
    4. Now move the data from REPORTER_STATUS_DUP to REPORTER_STATUS table.
    INSERT INTO XXXXXXX SELECT XXXXX

    Thanks,
    Manoj
  • SystemAdmin
    SystemAdmin
    17917 Posts

    Re: DB2 deadlock or timeout error

    ‏2012-10-17T17:00:27Z  
    Hi Ravi,

    Try to increase your LOCKTIMEOUT databse configuraton parameter and then check.
    If not, try to use the following method.

    1. Create similar table with LIKE statement (REPORTER_STATUS_DUP)
    2. Insert into dup_table Select statement
    Please check you have got correct data in your duplicate table
    INSERT INTO REPORTER_STATUS_DUP SELECT * FROM REPORTER_STATUS WHERE FIRSTOCCURRENCE < current timestamp - 3 MONTHS
    3. Truncate data
    TRUNCATE TABLE REPORTER_STATUS IMMEDIATE
    4. Now move the data from REPORTER_STATUS_DUP to REPORTER_STATUS table.
    INSERT INTO XXXXXXX SELECT XXXXX

    Thanks,
    Manoj
    Increasing the LOCTIMEOUT will not help you here. You are getting Deadlocks, not timeouts.

    You can do the duplicate table trick described, just copy the rows you want to keep (the example is the rows you want to get rid of).

    You can also setup a deadlock event monitor to see what is actually happening.

    Andy