invalid SQL query
Hi all, from time to time I'm seeing messages like these on SystemOut.log:
[6/21/17 11:13:24:631 WEST] 00000250 webapp E com.ibm.ws.webcontainer.webapp.WebApp logServletError SRVE0293E: [Servlet Error]-[equinoxbridgeservlet]:
com.ibm.ws.webcontainer.webapp.WebAppErrorReport: CRJAZ1318E The server could not connect to the database. Try the operation again.
Serialization failure
SQL: select distinct t1.ITEM_ID, t1.STATE_ID, t1.JZ_DISCRIMINATOR from (select * from PROCESS.PROCESS_AREA z1 where z1.JZ_DISCRIMINATOR = 93) t1 left
outer join PROCESS.PROCESS_AREA_CONTRIBUTORS j1 on (t1.ITEM_ID = j1.JZ_PARENT_ID) left outer join REPOSITORY.CONTRIBUTOR t3 on (j1.ITEM_ID = t3.IT
EM_ID) where ((t1.PROJECT_AREA_ITEM_ID = ?) and (t3.ITEM_ID = ?))
SQL Exception #1
SQL Message: DB2 SQL Error: SQLCODE=-911, SQLSTATE=40001, SQLERRMC=2, DRIVER=4.17.28
SQL State: 40001
Error Code: -911
the -911 means a deadlock.
What strategies should I follow to diagnose this? End users are not affected (until now I had no complains...) environment is stable , no new fixes , or versions and suddenly this.
Thanks!
One answer
db2 \? SQL0911 says:
SQL0911N The current transaction has been rolled back because of a
deadlock or timeout. Reason code "<reason-code>".
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 ( SQLERRMC=2 from above )
The transaction was rolled back due to a deadlock.
....
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.
Deadlock is when a two (or more) DB2 agents are both vying for the same resources, neither willing to relinquish control to the other(s). There's probably nothing we DB admins can do about deadlock except to make sure performance on the database remains high.
Comments
thanks Kevin, for the explanation. But is supposed to happen on a running server? And the query with several "(" and ")" is it a valid query?
The #XX are encoded ascii chars.
40 = ( , #41 = )
A different error would come if the syntax of the query were wrong. E.g. a missing right parend:
SQL0104N An unexpected token "END-OF-STATEMENT" was found following "where
cr_id in (1,2". Expected tokens may include: ")". SQLSTATE=42601