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)

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.

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.

try

Select .... for Read Only With UR

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.