Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2008
    Posts
    2

    Unanswered: deadlock with uncommitted read

    Hi all,

    We encountered a deadlock while executing the following(straightforward) query in DB2 8.1:

    SELECT
    DEBT.ACCOUNTING_A as "accountingA",
    DEBT.ACCOUNTING_B as "accountingB",
    DEBT.CREATED_BY_DECLARATION as "createdByDeclaration",
    ...
    FROM debt
    WHERE DECLARATION_ID = #value# WITH UR

    Does someone know how is it possible to get a deadlock whereas the "WITH UR" clause is applied ?
    I thought that when using dirty reads, the database does not try to acquire locks... Am I missing something ?

    I 've read an article which states that "when uncommitted read is used, rows retrieved by a transaction are only locked if another transaction attempts to drop or alter the rows' table". (http://www.db2mag.com/showArticle.jh...eID=186500771).
    Would that be the only possible explanation ?

    Thx,
    Johan.

    Caused by: com.ibm.db2.jcc.a.SqlException: DB2 SQL error: SQLCODE: -911, SQLSTATE: 40001, SQLERRMC: 2


    org.springframework.dao.DeadlockLoserDataAccessExc eption: SqlMapClient operation; SQL [];
    --- The error occurred in be/fgov/minfin/plda/dao/implementation/ibatis/xml/financial/Debt.xml.
    --- The error occurred while applying a parameter map.
    --- Check the loadDebtForDeclaration-InlineParameterMap.
    --- Check the results (failed to retrieve results).
    --- Cause: com.ibm.db2.jcc.a.SqlException: DB2 SQL error: SQLCODE: -911, SQLSTATE: 40001, SQLERRMC: 2; nested exception is com.ibatis.common.jdbc.exception.NestedSQLExceptio n:
    --- The error occurred in be/fgov/minfin/plda/dao/implementation/ibatis/xml/financial/Debt.xml.
    --- The error occurred while applying a parameter map.
    --- Check the loadDebtForDeclaration-InlineParameterMap.
    --- Check the results (failed to retrieve results).
    --- Cause: com.ibm.db2.jcc.a.SqlException: DB2 SQL error: SQLCODE: -911, SQLSTATE: 40001, SQLERRMC: 2
    Caused by: com.ibm.db2.jcc.a.SqlException: DB2 SQL error: SQLCODE: -911, SQLSTATE: 40001, SQLERRMC: 2
    com.ibatis.common.jdbc.exception.NestedSQLExceptio n:
    --- The error occurred in be/fgov/minfin/plda/dao/implementation/ibatis/xml/financial/Debt.xml.
    --- The error occurred while applying a parameter map.
    --- Check the loadDebtForDeclaration-InlineParameterMap.
    --- Check the results (failed to retrieve results).
    --- Cause: com.ibm.db2.jcc.a.SqlException: DB2 SQL error: SQLCODE: -911, SQLSTATE: 40001, SQLERRMC: 2
    Caused by: com.ibm.db2.jcc.a.SqlException: DB2 SQL error: SQLCODE: -911, SQLSTATE: 40001, SQLERRMC: 2
    at com.ibatis.sqlmap.engine.mapping.statement.General Statement.executeQueryWithCallback(GeneralStatemen t.java:185)
    at com.ibatis.sqlmap.engine.mapping.statement.General Statement.executeQueryForList(GeneralStatement.jav a:123)
    at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelega te.queryForList(SqlMapExecutorDelegate.java:610)
    at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelega te.queryForList(SqlMapExecutorDelegate.java:584)
    at com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.qu eryForList(SqlMapSessionImpl.java:101)

  2. #2
    Join Date
    Jan 2008
    Posts
    88
    try to set DB2_EVALUNCOMMITTED=YES and
    DB2_SKIPINSERTED=YES.

    that would evaluate the uncommitted data and if not required can skip that part for locks.

  3. #3
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Have a look at the db2diag output. If DIAGLEVEL is set to 4, you should get some more information on the reason for the deadlock.

    Even if an application runs in uncommitted read, some locks may be needed internally. For example, it may not be such a great idea if someone drops the table while you are reading from it. Combine this with some non-WITH-UR statements in the same transactions, not enough COMMITs, I'd say that you can indeed run into a deadlock.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  4. #4
    Join Date
    Mar 2006
    Posts
    104
    try

    Select .... for Read Only With UR

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I would get a printout of the deadlock event monitor output to make sure that you know what SQL statements were involved in the deadlock. If a deadlock event monitor is not already running, you can create one.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

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