DB2 死锁超时 Sqlstate:40001，原因代码 68 由于使用 SQL 从 servlet 调用的更新语句 [英] DB2 deadlock timeout Sqlstate: 40001, reason code 68 due to update statements called from servlet using SQL
问题描述
我从一个 servlet 到 DB2 一个接一个地调用更新语句.我收到错误 sqlstate 40001，原因代码 68，我发现这是由于死锁超时.
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.
- 我该如何解决这个问题?
- 可以通过设置查询超时来解决吗?
- 如果是，那么如何将它与 servlet 中的更新语句一起使用或在哪里使用它?
推荐答案
原因码 68 已经告诉你这是由于锁超时(死锁是原因码 2)可能是由于其他用户同时运行查询使用您正在访问的相同数据或您自己的多次更新的时间.
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.
首先从 db2 命令行运行 db2pd -db locktest -locks show detail 以查看锁的位置.然后你需要运行类似的东西:
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 = #
用您从 db2pd 命令输出中获得的 ID 号填充 # 符号.
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:
◦有时可以通过确保所有应用程序以相同的顺序访问它们的公共数据来降低死锁频率——例如，它们访问(并因此锁定)表 A 中的行，然后是表 B，然后是表C，以此类推.
◦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.
推荐阅读:http://www.ibm.com/developerworks/data/library/techarticle/dm-0511bond/index.html
附录:如果您的 servlet 或其他有罪的应用程序正在使用发现与死锁有关的 select 语句，您可以尝试附加 with ur 到选择声明新更新(或插入)数据的准确性是否不重要.
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.
这篇关于DB2 死锁超时 Sqlstate:40001，原因代码 68 由于使用 SQL 从 servlet 调用的更新语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
