DB2 deadlock timeout Sqlstate: 40001, reason code 68 due to update statements called from servlet using SQL

Question

I am calling update statements one after the other from a servlet to DB2. I am getting error sqlstate 40001, reason code 68 which i found it is due to deadlock timeout.

• How can I resolve this issue?
• Can it be resolved by setting query timeout?
• If yes then how to use it with update statements in servlet or where to use it?

Answer 1

The reason code 68 already tells you this is due to a lock timeout (deadlock is reason code 2) It could be due to other users running queries at the same time that use the same data you are accessing, or your own multiple updates.

Begin by running db2pd -db locktest -locks show detail from a db2 command line to see where the locks are. You'll then need to run something like:

select tabschema, tabname, tableid, tbspaceid 
from syscat.tables where tbspaceid = # and tableid = #

filling in the # symbols with the ID number you get from the db2pd command output.

Once you see where the locks are, here are some tips:

◦Deadlock frequency can sometimes be reduced by ensuring that all applications access their common data in the same order – meaning, for example, that they access (and therefore lock) rows in Table A, followed by Table B, followed by Table C, and so on.

taken from: http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.admin.trb.doc/doc/t0055074.html

recommended reading: http://www.ibm.com/developerworks/data/library/techarticle/dm-0511bond/index.html

Addendum: if your servlet or another guilty application is using select statements found to be involved in the deadlock, you can try appending with ur to the select statements if accuracy of the newly updated (or inserted) data isn't important.