Timeout updating DB2 table

Question

I am doing an update to a DB2 table like this (java code):

// 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

The documentation for error -913 says 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.

When I run the SQL by itself, it works fine:

UPDATE MY.THREADS
SET STATUS = 1
WHERE ID = 156

I can SELECT and 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).

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.

Solution 1

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.

conn = getConn();

This will return the same connection for each DAO method while in an explicit transaction.

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.

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)

Solution 2

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.

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");

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