Hunting down an issue in Oracle

[George Kowalski]

All, 

We've been hunting down an issue with our 1.7.06 version of I2b2 running against Oracle seeming to take forever for queries to complete when I ran across this in the server.log 

10:11:10,688 ERROR [edu.harvard.i2b2.crc.ejb.ExecRunnable] (Thread-2174) Got an excpetion in ExecRunnable (RUN): PreparedStatementCallback; uncategorized SQLException for SQL [select *  from CDWDEMODATA.qt_query_instance  where query_instance_id =?]; SQL state [null]; error code [0]; Interrupted attempting lock: org.jboss.jca.adapters.jdbc.local.LocalManagedConnection@54d2e3e; nested exception is java.sql.SQLException: Interrupted attempting lock: org.jboss.jca.adapters.jdbc.local.LocalManagedConnection@54d2e3e

10:11:10,688 ERROR [stderr] (Thread-2174) org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [select *  from CDWDEMODATA.qt_query_instance  where query_instance_id =?]; SQL state [null]; error code [0]; Interrupted attempting lock: org.jboss.jca.adapters.jdbc.local.LocalManagedConnection@54d2e3e; nested exception is java.sql.SQLException: Interrupted attempting lock: org.jboss.jca.adapters.jdbc.local.LocalManagedConnection@54d2e3e

10:11:10,689 ERROR [stderr] (Thread-2174) java.sql.SQLException: Interrupted attempting lock: org.jboss.jca.adapters.jdbc.local.LocalManagedConnection@54d2e3e

10:11:10,689 ERROR [stderr] (Thread-2174)       at org.jboss.jca.adapters.jdbc.BaseWrapperManagedConnection.tryLock(BaseWrapperManagedConnection.java:384)

10:11:10,690 ERROR [stderr] (Thread-2174)       at org.jboss.jca.adapters.jdbc.WrappedConnection.lock(WrappedConnection.java:146)

10:11:10,690 ERROR [stderr] (Thread-2174)       at org.jboss.jca.adapters.jdbc.WrappedConnection.prepareStatement(WrappedConnection.java:394)

10:11:10,690 ERROR [stderr] (Thread-2174)       at org.springframework.jdbc.core.JdbcTemplate$SimplePreparedStatementCreator.createPreparedStatement(JdbcTemplate.java:1129)

10:11:10,690 ERROR [stderr] (Thread-2174)       at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:471)

10:11:10,691 ERROR [stderr] (Thread-2174)       at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:529)

10:11:10,691 ERROR [stderr] (Thread-2174)       at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:562)

10:11:10,691 ERROR [stderr] (Thread-2174)       at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:571)

10:11:10,691 ERROR [stderr] (Thread-2174)       at org.springframework.jdbc.core.JdbcTemplate.queryForObject(JdbcTemplate.java:620)

10:11:10,691 ERROR [stderr] (Thread-2174)       at edu.harvard.i2b2.crc.dao.setfinder.QueryInstanceSpringDao.getQueryInstanceByInstanceId(QueryInstanceSpringDao.java:128)

10:11:10,692 ERROR [stderr] (Thread-2174)       at edu.harvard.i2b2.crc.dao.setfinder.QueryExecutorDao.setQueryInstanceStatus(QueryExecutorDao.java:372)

10:11:10,692 ERROR [stderr] (Thread-2174)       at edu.harvard.i2b2.crc.dao.setfinder.QueryExecutorDao.executeSQL(QueryExecutorDao.java:339)

10:11:10,692 ERROR [stderr] (Thread-2174)       at edu.harvard.i2b2.crc.ejb.ExecRunnable.processQueryRequest(ExecRunnable.java:469)

10:11:10,692 ERROR [stderr] (Thread-2174)       at edu.harvard.i2b2.crc.ejb.ExecRunnable.run(ExecRunnable.java:184)

10:11:10,692 ERROR [stderr] (Thread-2174)       at java.lang.Thread.run(Thread.java:744)

10:13:57,639 INFO  [edu.harvard.i2b2.crc.dao.DataSourceLookupHelper] (http--0.0.0.0-8080-61) Located DataSource for hiveId=[i2b2demo

Anyone ever seen this . Don't see how a select requires a lock as it's not doing a select for update . 

G

[Michael Horvath]

That error is thrown by the jboss jdbc adapters when the thread for the query is interrupted in order to move the query to a lower priority queue.  See http://community.i2b2.org/jira/browse/CORE-149.  As far as I know, the error itself no longer has any real effect on the query since the fix in 1.7.06.  It does however still show up in the logs for queries that run long enough to be put into the Medium/Large queue.  I think it’s likely the cause of your long run time lies elsewhere.

 

Michael Horvath     
Programmer / Analyst          

Biomedical Research Services & Administration   
Medical Center Boulevard  \  Winston-Salem, NC 27157
p 336.716.9755  \  f 336.716.5055

--
You received this message because you are subscribed to the Google Groups "i2b2 Install Help" group.
To unsubscribe from this group and stop receiving emails from it, send an email to i2b2-install-h...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[George Kowalski]

So this exception is getting thrown , makes it to the Log , and has no effect on queries going from Med -> Long queue ?  A little misleading 

[Tom Morris]

On Sat, Jan 23, 2016 at 4:43 PM, George Kowalski <george....@gmail.com> wrote:

So this exception is getting thrown , makes it to the Log , and has no effect on queries going from Med -> Long queue ?  A little misleading 

This is not unusual for i2b2 logging, at least for the CRC which is what I'm most familiar with. Many exception handlers log and throw either the original exception or a wrapped exception. As the exception percolates up through multiple exception handlers, you might find each of them logging it.

Tom