java 更新 DB2 表超时
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16530919/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Timeout updating DB2 table
提问by Jess
I am doing an update to a DB2 table like this (java code):
我正在更新这样的 DB2 表（java 代码）：
// Some code ripped out for brevity...
sql.append("UPDATE " + TABLE_THREADS + " ");
sql.append("SET STATUS = ? ");
sql.append("WHERE ID = ?");
conn = getConn();
pstmt = conn.prepareStatement(sql.toString());
int idx1 = 0;
pstmt.setInt(++idx1, status);
pstmt.setInt(++idx1, id);
int rowsUpdated = pstmt.executeUpdate();
return rowsUpdated;
After a long while, I get a rollback and an error message:
很长一段时间后，我收到回滚和错误消息：
UNSUCCESSFUL EXECUTION CAUSED BY DEADLOCK OR TIMEOUT. REASON CODE 00C9008E, TYPE OF RESOURCE 00000302, AND RESOURCE NAME SOME.THING.X'000002'. SQLCODE=-913, SQLSTATE=57033, DRIVER=3.57.82
UNSUCCESSFUL EXECUTION CAUSED BY DEADLOCK OR TIMEOUT. REASON CODE 00C9008E, TYPE OF RESOURCE 00000302, AND RESOURCE NAME SOME.THING.X'000002'. SQLCODE=-913, SQLSTATE=57033, DRIVER=3.57.82
The documentation for error -913says this REASON CODE means it is a timeout. The resource type, 00000302 is a table space page, and I do not recognize the resource name at all.
错误 -913的文档说这个 REASON CODE 意味着它是一个超时。资源类型 00000302 是表空间页，我根本不认识资源名称。
When I run the SQL by itself, it works fine:
当我自己运行 SQL 时，它工作正常：
UPDATE MY.THREADS
SET STATUS = 1
WHERE ID = 156
I can SELECTand see the status has been updated. (Although when I run this SQL during the long wait period before the timeout, I have the same issue. It takes forever and I just cancel it).
我可以SELECT看到状态已更新。（虽然当我在超时前的长时间等待期间运行此 SQL 时，我遇到了同样的问题。它需要永远，我只是取消它）。
There are several things happening in the transaction and I don't see any other updates to this table or record. There are create/delete triggers on the table, but no update triggers. I don't see any selects with cursors, or weird isolation level changes. I don't see much else in the transaction that would cause this.
事务中发生了几件事，我没有看到对此表或记录的任何其他更新。表上有创建/删除触发器，但没有更新触发器。我没有看到任何带有游标的选择或奇怪的隔离级别更改。我在交易中没有看到其他会导致这种情况的东西。
Why am I getting this error? What else should I look for in the transaction?
为什么我收到这个错误？我还应该在交易中寻找什么？
EDIT:
编辑：
I stepped through the code from the beginning of the request to where it gets 'stuck'. It seems as if there are 2 DAO's and both of them are creating a transaction. I think that might be the problem.
我从请求的开始到它被“卡住”的地方逐步完成了代码。似乎有 2 个 DAO，并且它们都在创建交易。我认为这可能是问题所在。
采纳答案by Jess
Sorry to answer my own question, but I found out the problem. This is a somewhat homemade framework where a DAO keeps track of it's own connection.
很抱歉回答我自己的问题，但我发现了问题所在。这是一个有点自制的框架，其中 DAO 跟踪它自己的连接。
conn = getConn();
This will return the same connection for each DAO method while in an explicit transaction.
在显式事务中，这将为每个 DAO 方法返回相同的连接。
While I was stepping through the code, I found out that a method I was calling in my transaction was creating a new transaction, a new DAO, and therefore a new DB connection. So now I have 2 transactions open and 2 connections. It's easy to see at this point, that I am in fact deadlocking myself.
当我逐步执行代码时，我发现我在事务中调用的方法正在创建一个新事务、一个新 DAO，因此也创建了一个新的 DB 连接。所以现在我有 2 个事务打开和 2 个连接。在这一点上很容易看出，我实际上陷入了僵局。
This caught me a little by surprise since the previous app I worked on allowed nested transactions. (Using the same DB connection for both transactions)
这让我有点意外，因为我之前开发的应用程序允许嵌套事务。（对两个事务使用相同的数据库连接）
回答by greedybuddha
So SQL connections will timeout, you will need to specify to the connection that you want to "test" the connection before executing a query so that it can then reconnect if it's not still open.
因此 SQL 连接将超时，您需要在执行查询之前指定要“测试”该连接的连接，以便在它尚未打开时重新连接。
I only have code for the apache commons DBCP and pooling, but here is what I do with my own connections. The important lines are connectionPool.setTestOnBorrow(true);and specifying the validation query factory.setValidationQuery("select 1");
我只有 apache 公共 DBCP 和池的代码，但这是我对自己的连接所做的。重要的几行是connectionPool.setTestOnBorrow(true);并指定验证查询factory.setValidationQuery("select 1");
GenericObjectPool connectionPool = new GenericObjectPool(null);
...
connectionPool.setTestOnBorrow(true); // test the connection before its made
ConnectionFactory connectionFactory = new DriverManagerConnectionFactory(connectURI,username, password);
final String validationQuery = null;
KeyedObjectPoolFactory statementPool = new GenericKeyedObjectPoolFactory(null);
PoolableConnectionFactory factory = new PoolableConnectionFactory(connectionFactory, connectionPool,
statementPool, validationQuery, defaultReadOnly, defaultAutoCommit);
factory.setValidationQuery("select 1"); // validate the connection with this statement
