Timeout table update

Question

I am doing an update for 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 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 that this REASON CODE means this is a timeout. The resource type, 00000302 is a tablespace page, and I don’t recognize the resource name at all.

When I run SQL on my own, it works fine:

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

I can SELECT and see that the status is updated. (Although when I run this SQL for a long period of time before the timeout expires, I have the same problem. This is required forever and I just canceled it).

Several things happen in a transaction, and I do not see any other updates to this table or record. There are create / delete triggers in the table, but update triggers are missing. I do not see any options with cursors or strange changes in the isolation level. I do not see anything else in the transaction that could cause this.

Why am I getting this error? What else should I look for in a transaction?

---

EDIT:

I stepped through the code from the beginning of the request to where it was "stuck". It seems that there are 2 DAOs, and both of them create a transaction. I think this may be a problem.

Answer 1

Sorry to answer my own question, but I figured out this problem. This is a somewhat do-it-yourself structure where the DAO tracks its own mix.

 conn = getConn(); 

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

While I was going through the code, I found out that the method that I called in my transaction was to create a new transaction, a new DAO and, therefore, a new database connection. So now I have 2 open transactions and 2 connections. At this point, it is easy to see that I am actually a dead end myself.

This came as a little surprise to me, since the previous application I was working on allowed nested transactions. (Using the same DB connection for both transactions)

Answer 2

That way, the SQL connections will timeout, you will need to specify the connection you want to "test" the connection before executing the query so that it can reconnect if it is not already open.

I only have the code for apache commons DBCP and pool, but here is what I do with my own connections. The important lines are connectionPool.setTestOnBorrow(true); and setting the check request 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