12

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?
Adam Wagner
  • 15,469
  • 7
  • 52
  • 66
user1022467
  • 121
  • 1
  • 1
  • 3

2 Answers2

12

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.

Stephen Bailey
  • 1,931
  • 13
  • 20
MrG
  • 1,525
  • 13
  • 24
  • It's important to note that the article talks about **reducing** the frequency of deadlocks, not **preventing** them altogether. According to http://stackoverflow.com/a/112256/14731 consistent locking order does not prevent deadlocks. The most we can do is attempt to reduce their frequency. – Gili May 14 '13 at 22:48
0

For me, the solution was adding FOR READ ONLY WITH UR at the end of all my SELECT statements. (Apparently my select statements were returning so much data, it locked the tables long enough to interfere with other SQL statements)

See https://www.ibm.com/support/knowledgecenter/SSEPEK_10.0.0/sqlref/src/tpc/db2z_sql_isolationclause.html

john k
  • 6,268
  • 4
  • 55
  • 59